Spring MVC and Excel file via AbstractExcelView

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

1. Apache POI

Get the Apache POI library to create the excel file.


   <!-- Excel library --> 
   <dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi</artifactId>
	<version>3.6</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 (AbstractExcelView).

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. AbstractExcelView

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

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

File : ExcelRevenueReportView.java


package com.mkyong.common.view;

import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.web.servlet.view.document.AbstractExcelView;

public class ExcelRevenueReportView extends AbstractExcelView{
	
	@Override
	protected void buildExcelDocument(Map model, HSSFWorkbook workbook,
		HttpServletRequest request, HttpServletResponse response)
		throws Exception {
	
		Map<String,String> revenueData = (Map<String,String>) model.get("revenueData");
		//create a wordsheet
		HSSFSheet sheet = workbook.createSheet("Revenue Report");
		
		HSSFRow header = sheet.createRow(0);
		header.createCell(0).setCellValue("Month");
		header.createCell(1).setCellValue("Revenue");
		
		int rowNum = 1;
		for (Map.Entry<String, String> entry : revenueData.entrySet()) {
			//create the row data
			HSSFRow row = sheet.createRow(rowNum++);
			row.createCell(0).setCellValue(entry.getKey());
			row.createCell(1).setCellValue(entry.getValue());
                }
	}
}
Note
Alternatively, you can use the AbstractJExcelView, which is using the JExcelAPI to create the same Excel view, see this AbstractJExcelView 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


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

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. Apache POI
  2. AbstractExcelView Javadoc
  3. Spring MVC export data to Excel file via AbstractJExcelView
  4. Spring MVC XmlViewResolver example

About the Author

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

Comments

avatar
42 Comment threads
16 Thread replies
0 Followers
 
Most reacted comment
Hottest comment thread
45 Comment authors
Allan SeeSunilkumar Kotajava_confuLeang SocheatSuraj Dighodkar Recent comment authors
newest oldest most voted
Leang Socheat
Guest
Leang Socheat

Hi mkyong, when I try to practise to create sample like you I cannot return to ExcelRevenueReportView class. it’s always return to ExcelRevenuSummary.jsp. have any I forgot or ..?

Allan See
Guest
Allan See

I have the same method above using Apache POI

it seems to be running through the code to create the Excel but doesn’t seem to trigger any download of the file.

Need you help

Sunilkumar Kota
Guest
Sunilkumar Kota

Hi sir,I am geeting response as response header in browser development tools(after pressing F12 button)….no excel file is downloading…but same data is printing on browser response header in the Network console…Please reply me as early as possible….i am in work now

java_confu
Guest
java_confu

Can you please help with Excel download functionality with Spring 4 as AbstractExcelView is deprecated in spring 4

Suraj Dighodkar
Guest
Suraj Dighodkar

Hi can you please tell me how to remove warning in excel Number store as text i am using AbstractJExcelView

krishna
Guest
krishna

public class ExcelLIb {

public static String filePath;

public String getExcelData(String sheetName , String testID , String columnHeader) throws InvalidFormatException, IOException{

String userDir = System.getProperty(“user.dir”);

filePath = userDir+”\testdata\Test_Data.xlsx”;

String data = null;

FileInputStream fis = new FileInputStream(filePath);

Workbook wb = WorkbookFactory.create(fis);

Sheet sh = wb.getSheet(sheetName);

int rowcount =getRowCount(sheetName);

for(int r=0 ; r<rowcount; r++){

Row row = sh.getRow(r);

if(row.getCell(0).getStringCellValue().toLowerCase().equals(testID.toLowerCase())){

int col = row.getLastCellNum();

for(int c=0; c<col ; c++){

if(row.getCell(c).getStringCellValue().toLowerCase().equals(columnHeader.toLowerCase())){

row = sh.getRow(r+1);

data = row.getCell(c).getStringCellValue();

break;

}

}

}

}

return data;

}

robbo_uk
Guest
robbo_uk
vinod kumar
Guest
vinod kumar

hello sir I am following you and you hava provided very good material. I want a one help. How to download a file if we send post request rather than simple link. I want to make ajax call for downloading the file. Thank in advance.

AOB
Guest
AOB

It is probably a stupid question, but any help would be appreciated.
The set up works fine, xls is also created and sent as a response to the browser(visible in Firebug) but there is no popup to save the file – i.e. the browser is kind of ignoring the response.

Kiran
Guest
Kiran

can you please give me example or some hint for group by and subtotal in excel.

trackback
Excel export configuration in spring mvcCopyQuery CopyQuery | Question & Answer Tool for your Technical Queries,CopyQuery, ejjuit, query, copyquery, copyquery.com, android doubt, ios question, sql query, sqlite query, nodejsquery, dns query, update qu

[…] I have a requirement to export data to excel using spring mvc when we click on export button. i have used AbstractExcelView for exporting excel sheet in spring mvc. Followed the process mentioned in this link http://www.mkyong.com/spring-mvc/spring-mvc-export-data-to-excel-file-via-abstractexcelview/ […]

trackback
Upload, Download using Spring MVC and Xlsx Manipulation using apache poi | snippetjournal

[…] Download export file xlsx […]

Shaun
Guest
Shaun

Very helpful, thank you!

Peter
Guest
Peter

Great job! Works brilliant! Thank you!

Agradeep
Guest
Agradeep

How to get the Apache POI library?

oliver
Guest
oliver

Hi MkYong

Great explication.

I see also your paper about “Exception Handling” here : http://www.mkyong.com/spring-mvc/spring-mvc-exception-handling-example/

But SimpleMappingExceptionResolver seem not working especially if an exception is throwing during buildExcelDocument of my class who extends AbstractExcelView

How can I put AbstractExcelView’s exception in my JSP form ?

abdou
Guest
abdou

can you give an exemple with annotation ?

Porfirio
Guest
Porfirio

hello there and thank you for your info _ I’ve certainly picked up something new from right here. I did however expertise several technical points using this site, as I experienced to reload the website a lot of times previous to I could get it to load properly. I had been wondering if your web hosting is OK? Not that I’m complaining, but sluggish loading instances times will often affect your placement in google and can damage your quality score if ads and marketing with Adwords. Well I am adding this RSS to my e-mail and can look out for… Read more »

Maricruz
Guest
Maricruz

Ahaa, its nice discussion regarding this article at this place at this webpage, I
have read all that, so now me also commenting here.

Melinda
Guest
Melinda

Thanks on your marvelous posting! I actually enjoyed reading it,
you can be a great author.I will make certain to bookmark your
blog and will come back down the road. I want to encourage
you to definitely continue your great job, have a nice evening!

compare one day car insurance
Guest
compare one day car insurance

I really like your blog.. very nice colors & theme. Did you create this website yourself or did you hire someone to do it for you?

Plz reply as I’m looking to design my own blog and would like to know where u got this from. thank you

www.npacl.com
Guest
www.npacl.com

This design is wicked! You most certainly know how to keep a reader amused.

Between your wit and your videos, I was almost moved to start my own
blog (well, almost…HaHa!) Great job. I really enjoyed what you had to say, and more than that, how you presented
it. Too cool!

Click Here
Guest
Click Here

This is really interesting, You are a very skilled blogger.
I’ve joined your feed and look forward to seeking more of your wonderful post. Also, I have shared your web site in my social networks!

shepherd
Guest
shepherd

hi i use spring mvc annotation configure project , Can now access the controller layer but cannot call AbstractExcelView extends classes, can you help me,

shepherd
Guest
shepherd

I encounter result “Could not resolve view with name ‘AbstractExcelView extends bean id’ in servlet with name projectName”

Damaris
Guest
Damaris

If you are going for best contents like myself, simply pay a visit this website every day as it offers feature contents, thanks

springMVCUser
Guest
springMVCUser

man u r simply the great!!!

Javi
Guest
Javi

Hi, did you try to implement XSLT and JSP at the same time?

Srisudhir T
Guest
Srisudhir T

Hi Kong,

Very nice example, i was able to implement this.

But is there a way to zip the contents of the excel and download as a zip file?

The excel i am generaiting is 15 MB and manually compressing it the size reduced to 700KB

So wanted to check if there is a way to compress the file which is being downloaded

berikbol
Guest
berikbol

cool . . . Tnx a lot

pilot
Guest
pilot

How to use Apache POI in Tiles?

Zach
Guest
Zach

Great tutorial, thanks!

I don’t mind saying It gave me some trouble trying to do this in Spring 3. I made a couple small changes, but aside from that this was invaluable. It also helped me understand Spring a little better – thanks for putting it out there!