Main Tutorials

Spring MVC and Excel file via AbstractJExcelView

Spring MVC comes with AbstractJExcelView class to export data to Excel file via JExcelAPI library. In this tutorial, it show the use of AbstractJExcelView class in Spring MVC application to export data to Excel file for download.

1. JExcelAPI

Get the JExcelAPI library.


    <!-- JExcelAPI library --> 
    <dependency>
	<groupId>net.sourceforge.jexcelapi</groupId>
	<artifactId>jxl</artifactId>
	<version>2.6.3</version>
    </dependency>

2. Controller

A controller class, generate dummy data for demonstration, and get the request parameter to determine which view to return. If the request parameter is equal to “EXCEL”, then return an Excel view (AbstractJExcelView).

File : RevenueReportController.java


package com.mkyong.common.controller;

import java.util.HashMap;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.springframework.web.bind.ServletRequestUtils;
import org.springframework.web.servlet.ModelAndView;
import org.springframework.web.servlet.mvc.AbstractController;

public class RevenueReportController extends AbstractController{

	@Override
	protected ModelAndView handleRequestInternal(HttpServletRequest request,
		HttpServletResponse response) throws Exception {
		
		String output =
			ServletRequestUtils.getStringParameter(request, "output");
		
		//dummy data
		Map<String,String> revenueData = new HashMap<String,String>();
		revenueData.put("Jan-2010", "$100,000,000");
		revenueData.put("Feb-2010", "$110,000,000");
		revenueData.put("Mar-2010", "$130,000,000");
		revenueData.put("Apr-2010", "$140,000,000");
		revenueData.put("May-2010", "$200,000,000");
		
		if(output ==null || "".equals(output)){
			//return normal view
			return new ModelAndView("RevenueSummary","revenueData",revenueData);
			
		}else if("EXCEL".equals(output.toUpperCase())){
			//return excel view
			return new ModelAndView("ExcelRevenueSummary","revenueData",revenueData);
			
		}else{
			//return normal view
			return new ModelAndView("RevenueSummary","revenueData",revenueData);
			
		}	
	}
}

3. AbstractJExcelView

Create an Excel view by extends the AbstractJExcelView class, and override the buildExcelDocument() method to populate the data to Excel file. The AbstractJExcelView is using the JExcelAPI to create the Excel file detail.

Note
For detail about how to use the JExcelAPI , please refer to the JExcelAPI documentation

File : ExcelRevenueReportView.java


package com.mkyong.common.view;

import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import org.springframework.web.servlet.view.document.AbstractJExcelView;

public class ExcelRevenueReportView extends AbstractJExcelView{

	@Override
	protected void buildExcelDocument(Map model, WritableWorkbook workbook,
	   HttpServletRequest request, HttpServletResponse response)
	   throws Exception {

	   Map<String,String> revenueData = (Map<String,String>) model.get("revenueData");
	   WritableSheet sheet = workbook.createSheet("Revenue Report", 0);
		
           sheet.addCell(new Label(0, 0, "Month"));
           sheet.addCell(new Label(1, 0, "Revenue"));

           int rowNum = 1;
	   for (Map.Entry<String, String> entry : revenueData.entrySet()) {
		//create the row data
		sheet.addCell(new Label(0, rowNum, entry.getKey()));
	        sheet.addCell(new Label(1, rowNum, entry.getValue()));
	        rowNum++;
           }
       }
}
Note
Alternatively, you can use the AbstractExcelView, which is using the Apache POI API to create the same Excel view, see this AbstractExcelView example.

4. Spring Configuration

Create a XmlViewResolver for the Excel view.


<beans ...>

 <bean 
  class="org.springframework.web.servlet.mvc.support.ControllerClassNameHandlerMapping" />
    
    <bean class="com.mkyong.common.controller.RevenueReportController" />
    
    <bean class="org.springframework.web.servlet.view.XmlViewResolver">
       <property name="location">
          <value>/WEB-INF/spring-excel-views.xml</value>
       </property>
    </bean>
   
</beans>

File : spring-excel-views.xml


<beans ...">
 
   <bean id="ExcelRevenueSummary"
   	class="com.mkyong.common.view.ExcelRevenueReportView">
   </bean>
 
</beans>

5. Demo

URL : http://localhost:8080/SpringMVC/revenuereport.htm?output=excel

It generates an Excel file for user to download.

SpringMVC-ExcelFile-Example

Download Source Code

References

  1. JExcelAPI documentation
  2. AbstractJExcelView Javadoc
  3. Spring MVC export data to Excel file via AbstractExcelView

About Author

author image
Founder of Mkyong.com, love Java and open source stuff. Follow him on Twitter. If you like my tutorials, consider make a donation to these charities.

Comments

Subscribe
Notify of
6 Comments
Most Voted
Newest Oldest
Inline Feedbacks
View all comments
Bruno
10 years ago

Hi. Thanks for the post.
However I have a problem trying to do the same without xml.
Everything is OK with the normal page but with xml, my app search ExcelRevenueSummary.jsp and I don’t know what to do to correct that. Whould you have any idea ?

Here is my WebAppConfig

@Configuration
@ComponentScan("com.mkyong.common")
@EnableWebMvc
public class WebAppConfig {
  @Bean
  public InternalResourceViewResolver setupViewResolver() {
    InternalResourceViewResolver resolver = new InternalResourceViewResolver();
    resolver.setPrefix("/WEB-INF/pages/");
    resolver.setSuffix(".jsp");
    return resolver;
  }
}

And my WebAppInitializer

 */
public class WebAppInitializer implements WebApplicationInitializer {

    public void onStartup(ServletContext servletContext) throws ServletException {
        XmlWebApplicationContext appContext = new XmlWebApplicationContext();
        String[] locations = { "classpath*:applicationContext.xml" };
        appContext.setConfigLocations(locations);
        ServletRegistration.Dynamic dispatcher = servletContext.addServlet("dispatcher", new DispatcherServlet(appContext));
        dispatcher.setLoadOnStartup(1);
        dispatcher.addMapping("/");
        servletContext.addListener(new ContextLoaderListener(appContext));
        servletContext.getServletRegistration ("default").addMapping ("*.js", "*.css", "*.jpg", "*.gif", "*.png");
    }
}
Amith Perera
11 years ago

This was really a useful tutorial.But there is a little issue when using the app from Mozilla firefox.Alweys it asks for a file with extension “xxx.htm” insted of “xxx.xls”.For Internet explorer it works fine.Plus for the built in browser in eClipse.Can u please look in to this. Thnak you!

ramakrishnahati
11 years ago

Thank you very much Sir for this nice post!

Gurdil
12 years ago

I tried adding those two lines so that the response would know that this is an attachment but it didn’t work:

response.setContentType(“application/vnd.ms-excel”);
response.setHeader(“Content-disposition”, “attachment; filename=Report.xls”);

Any help would be appreciated

jl
13 years ago

Please take a look with my problem assosiated with this:

http://stackoverflow.com/questions/4447740/viewresolvers-with-excel-views

Can you please help me?

jl
13 years ago

Is this example easy to integrate with Spring 3?