Using XSSFWorkbook and AbstractExcelView

Question

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.

You appear to be trying to assign a `XSSFWorkbook` to a `HSSFWorkbook` object, which'll never work, needs to be just `Workbook`. Otherwise, if you write your output straight to a file, can Excel read it? i.e. is this a generation problem, or a serving one? — Gagravarr, Mar 11 '14 at 04:42
I may just be to close to this, but I dont see where I'm assigning an XSSFWorkbook to a HSSFWorkbook. I create a Workbook called workbook and, depending on the case in the buildExcelDocument function, create a new XSSFWorkbook and assigned it to workbook or set workbook equal to the HSSFWorkbook passed in as a parameter. The function buildBothExcelDocument then uese all usermodel functionality, that way it can deal with the Workbook parameter passed in. It is very possible that I'm not overlooking something though. — Porter, Mar 11 '14 at 16:16

score 1 · Answer 1 · answered Nov 12 '21 at 13:35

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;

}

score 0 · Answer 2 · answered May 05 '15 at 17:11

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.

score 0 · Answer 3 · answered Jul 25 '16 at 07:27

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

Using XSSFWorkbook and AbstractExcelView

3 Answers3