Problem in opening xlsx file when downloaded using a action class

[Meenakshi Prasanna]

Hi

Im using Apache POI library to generate a excel file. It works fine when i execute as stand alone application.
But in an web application it gets downloaded and when i try to open the file an error is popped up saying :

"Excel cannot open the file "workbook.xlsx" because the file format or file extension is not valid. Verify that the file has not been corrupted
and that the file extension matches the format of the file."

 
import java.io.FileOutputStream;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.struts.action.Action;
import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;

public class InboundShipmentExcelAction extends Action {
	
	public ActionForward execute(
			ActionMapping mapping,
			ActionForm form,
			HttpServletRequest request,
			HttpServletResponse response)
			throws Exception {
		String fileName="workbook.xlsx";
		System.out.println("In INBOUND shipment action class");
		response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); 
		   response.setHeader("Content-Disposition",     
	                "attachment; filename=\"" + fileName + "\""); 
		   
		   
		try {
			
		
			XSSFWorkbook wb = new XSSFWorkbook();
			   CreationHelper createHelper = wb.getCreationHelper();
			  Sheet sheet = wb.createSheet("new sheet");

			   // Create a row and put some cells in it. Rows are 0 based.
			   Row row = sheet.createRow((int) 0);
			   // Create a cell and put a value in it.
			   Cell cell = row.createCell(0);
			   cell.setCellValue(1);

			   // Or do it on one line.
			   row.createCell(1).setCellValue(1.2);
			   row.createCell(2).setCellValue(
			   createHelper.createRichTextString("This is a string"));
			   row.createCell(3).setCellValue(true);
			   FileOutputStream fileOut = new FileOutputStream("workbook.xlsx");
			   System.out.println("fileOut ");
			   wb.write(fileOut);
			   fileOut.close();


		} catch ( Exception ex ) {

		} 
		  return null;
	}

}

This is the test code which i used to integrate with the web app.
Any suggestions would be helpful.

[Meenakshi Prasanna]

Hi Guyz

Is there any problem with this code??

[mallikarjun dontamsetti]

are you try to run application on local? try to run in web it will defiantly works i face same problem too

[Meenakshi Prasanna]

Im executing it in the web itself .

Its deployed in tomcat server.

[Swastik Dey]

Meenakshi,

What happens if you use xls extension instead of xlsx extension?

[Meenakshi Prasanna]

Swastik
its giving the same error..

"Excel cannot open the file "workbook.xlsx" because the file format or file extension is not valid. Verify that the file has not been corrupted
and that the file extension matches the format of the file"

[Swastik Dey]

Try with the following content type

application/vnd.ms-excel

[Meenakshi Prasanna]

same error even with that mime type.

Earlier i tried with jsp with application/vnd.ms-excel it works fine. But the requirement was to get the excel file in .xlsx format.
When googled i got to know abt this apache poi. As i said with this class posted it works fine with main method with servlet im not able to get it correct.

Thanks a lot for the replies.

[Meenakshi Prasanna]

Thanks a lot for the replies.

I got the solution.. hope it helps someone who is facing same problem.
I changed the mime type to

response.setContentType("application/vnd.openxml");
and writing to servlet output stream instead of File output stream.

wb.write(response.getOutputStream());

This fixed the issue.

[r diwan]

Meenakshi,
would greately appreciate if you can provide more detail on your fix
i have the same issue cannot seem to get around it

thanks for the help

[Paul Clapham]

It's not difficult. Meenakshi's original code wrote the XLSX data to a local file on the server, instead of to the servlet's response stream. That was the wrong thing to do, since it resulted in no data being sent to the response. So changing the code to write the XLSX data to the servlet's response stream would have fixed that problem.

So if you have the same issue, i.e. if you are writing your XLSX data to a File, then apply that solution.

[Hmbj Shah]

I too had the same issue with downloading the xlsx file.
In addition to Meenakshi 's solution (using the servlet output stream + setting the content type in the header), I had to also set the content length in the header.

response.setHeader("Content-Length", String.valueOf(file.length()));
response.setHeader("Content-Disposition", "attachment;filename=" + filename);
response.setContentType("application/vnd.openxml");

Thanks!!