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 the Author

author image
mkyong
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

avatar
6 Comment threads
0 Thread replies
0 Followers
 
Most reacted comment
Hottest comment thread
6 Comment authors
BrunoAmith PereraramakrishnahatiGurdiljl Recent comment authors
newest oldest most voted
Bruno
Guest
Bruno

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 = {… Read more »

Amith Perera
Guest
Amith Perera

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
Guest
ramakrishnahati

Thank you very much Sir for this nice post!

Gurdil
Guest
Gurdil

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
Guest
jl

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
Guest
jl

Is this example easy to integrate with Spring 3?