CODEWITHSUNDEEP
javaspringapache-poi
Porter
Porter

Reputation: 143

Using XSSFWorkbook and AbstractExcelView

It is my understanding that the AbstractExcelView class function buildExcelDocument does not support XSSFWorkbook (https://jira.spring.io/browse/SPR-6898).

I am trying to work around this by implementing the buildExcelDocument function as follows:

Workbook workbook = null;
protected void buildExcelDocument(Map model, 
    HSSFWorkbook wbook, 
    HttpServletRequest request, 
    HttpServletResponse response) throws Exception {
       if(request.getRequestURL().toString().contains("xlsx")){
          workbook = new XSSFWorkbook();
          workbook.createSheet();
          excelVersion = "xlsx"; //Used to determine response
       }else{
          workbook = wbook;
       }
       buildBothExcelDocument(model,workbook,request,response);
}

Here, my buildBothExcelDocument function will use the Apache SS usermodel to generate and create both excel versions, HSSFWorkbook and XSSFWorkbook. Once the workbook is created I then create a header which will prompt the user to save or open the excel file:

if(excelVersion.equals("xlsx")){
   response.setHeader("Pragma", "public");
   response.setHeader("Cache-Control", "max-age=0");
   response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
   response.setHeader("Content-Disposition", "attachment; filename=test.xlsx");
}else{
   response.setHeader("Pragma", "public");
   response.setHeader("Cache-Control", "max-age=0");
   response.setContentType("application/vnd.ms-excel");
   response.setHeader("Content-Disposition", "attachment; filename=\"test.xls\"");
}

All of the aforementioned code works when using HSSFWorkbook. The workbook's data gets created correctly, can be downloaded, and will open correctly using either Microsoft Excel 2003 or Microsoft Excel 2007.

When I attempt to create an .xlsx file, I get an error when opening stating "Excel cannot open the file 'test.xlsx' becuase 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". This leads me to believe that somewhere along Spring corrupted my file. My questions are:

1 - Is my response contentType and header correct? (source: What is correct content-type for excel files?)

2 - Is there any way to use Spring and AbstractExcelView to create an xlsx excel file?

3 - Does Spring support XSSFWorkbook's at all?

Some additional notes:

Apache POI Version - v3.9

I have tried the following when creating my header:

    response.setHeader("Content-Disposition", "attachment; filename=\"test.xlsx\"");

and

    response.setHeader("Content-Disposition", "attachment; filename='test.xlsx'");

All yield the same failed results.

I used the following link to help convert my old HSSF code to use the SS usermodel, http://poi.apache.org/spreadsheet/converting.html. I tested this conversion process without the interference of Spring and was able to successfully create both xls and xlsx files. Thanks for the help.

Upvotes: 2

Views: 6171

Answers (3)

Beydulla Zeynalov
Beydulla Zeynalov

Reputation: 23

You can create custom AbstractExcelView and extend your view from that class. See the below sample code.

import java.io.ByteArrayOutputStream;
import java.util.Map;

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

import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.servlet.view.AbstractView;

public abstract class CustomAbstractExcelView extends AbstractView {


    @Override
    protected boolean generatesDownloadContent() {
        return true;
    }

    @Override
    protected final void renderMergedOutputModel(Map<String, Object> model, HttpServletRequest request, HttpServletResponse response) throws Exception {
        final ByteArrayOutputStream baos = createTemporaryOutputStream();
        final Workbook workbook = new XSSFWorkbook();

        this.buildExcelDocument(model, workbook, request, response);
        workbook.write(baos);
        this.writeToResponse(response, baos);
    }

    protected abstract void buildExcelDocument(Map<String, Object> model, Workbook workbook, HttpServletRequest request, HttpServletResponse response) throws Exception;

}

Upvotes: 1

swapnil
swapnil

Reputation: 21

Please make sure that you have given content type correctly,

("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); Generates the excel in .xlsx format which is compatible from MS Office 2007 onwords

("application/vnd.ms-excel");->Generates the excel in .xls format

Upvotes: 0

Nathan Teague
Nathan Teague

Reputation: 875

I know the .Net implementation of POI has a method to write to a stream. If you write your Workbook interface to Stream, and return the stream as a byte array with the parametric content and headers, it should work -- I had a similar issue in the .Net MVC controllers not serving the response correctly.

Upvotes: 0

Related Questions