CODEWITHSUNDEEP
javaexcelspringspring-mvc
user3734130
user3734130

Reputation: 86

Spring MVC, Excel file download, corrupts file

I'am working on an excel export functionality in one of my webapps. I set up a little test case and got the download working, but the xlsx file is corrupted and don't know what else I could try. If I write the excel to file it opens without problem, so the error must occure when downloading.

The setup:

spring-mvc 3.2.7 poi 3.10.1 Tomcat 8.0

Controller method:

@RequestMapping(value = "/download", method = RequestMethod.GET)
public ModelAndView downloadExcel() {
    // create some sample data
    List<Book> listBooks = new ArrayList<Book>();
    listBooks.add(new Book("Effective Java", "Joshua Bloch", "0321356683",
            "May 28, 2008", 38.11F));
    listBooks.add(new Book("Head First Java", "Kathy Sierra & Bert Bates",
            "0596009208", "February 9, 2005", 30.80F));
    listBooks.add(new Book("Java Generics and Collections",
            "Philip Wadler", "0596527756", "Oct 24, 2006", 29.52F));
    listBooks.add(new Book("Thinking in Java", "Bruce Eckel", "0596527756",
            "February 20, 2006", 43.97F));
    listBooks.add(new Book("Spring in Action", "Craig Walls", "1935182358",
            "June 29, 2011", 31.98F));

    // return a view which will be resolved by an excel view resolver
    return new ModelAndView(new ExcelBuilder(listBooks));
}

Abstract Custom View:

public abstract class AbstractPOIExcelView extends AbstractView {

private static final String CONTENT_TYPE_XLSX = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

public AbstractPOIExcelView() {
}

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

@Override
protected final void renderMergedOutputModel(Map<String, Object> model, HttpServletRequest request,
        HttpServletResponse response) throws Exception {
    XSSFWorkbook workbook = new XSSFWorkbook();
    buildExcelDocument(model, workbook, request, response);
    ByteArrayOutputStream baos = createTemporaryOutputStream();
    response.setHeader("Content-Disposition", "attachment;filename=filename.xlsx");
    response.setContentType(CONTENT_TYPE_XLSX);
    workbook.write(baos);

    writeToResponse(response, baos);

}

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

}

ExcelBuilder:

public class ExcelBuilder extends AbstractPOIExcelView {

private List<Book> listBooks;

public ExcelBuilder(List<Book> books) {
    this.listBooks = books;
}

@Override
protected void buildExcelDocument(Map<String, Object> model, XSSFWorkbook workbook, HttpServletRequest request, HttpServletResponse response) throws Exception {
    Sheet sheet = workbook.createSheet("Java Books");
    sheet.setDefaultColumnWidth(30);

    Row header = sheet.createRow(0);
    header.createCell(0).setCellValue("Book Title");
    header.createCell(1).setCellValue("Author");
    header.createCell(2).setCellValue("ISBN");
    header.createCell(3).setCellValue("Published Date");
    header.createCell(4).setCellValue("Price");

    // create data rows
    int rowCount = 1;

    for (Book aBook : listBooks) {
        Row aRow = sheet.createRow(rowCount++);
        aRow.createCell(0).setCellValue(aBook.getTitle());
        aRow.createCell(1).setCellValue(aBook.getAuthor());
        aRow.createCell(2).setCellValue(aBook.getIsbn());
        aRow.createCell(3).setCellValue(aBook.getPublishedDate());
        aRow.createCell(4).setCellValue(aBook.getPrice());
    }
}
}

Response Header:

 Cache-Control:private, must-revalidate
 Content-Disposition:attachment;filename="filename.xlsx"
 Content-Language:de-DE
 Content-Length:3778
 Content-Type:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=ISO-8859-1
 Date:Wed, 19 Nov 2014 12:52:05 GMT
 Expires:Thu, 01 Jan 1970 00:00:00 GMT
 Pragma:private
 Set-Cookie:JSESSIONID=07F50FF2B63D4003311DE222782C4E89; Path=/abc/; HttpOnly
 X-Content-Type-Options:nosniff
 X-Frame-Options:DENY
 X-XSS-Protection:1; mode=block

It confuses me that the charset will be set, when this is binary data. Could that be the problem?

Upvotes: 5

Views: 22059

Answers (2)

betatester07
betatester07

Reputation: 691

I would suggest to use existing solution rather than trying to deal with the response stream by yourself.

I would use AbstractExcelView instead of your AbstractPOIExcelView to do the job. Check this tutorial using AbstractExcelView for inspiration.

For Spring 4.2 or newer use AbstractXlsView (or AbstractXlsxView) because the original AbstractExcelView is deprecated.

Upvotes: 0

StanislavL
StanislavL

Reputation: 57381

Don't return ModelAndView but just write the excel file to the response's outputStream

@RequestMapping(value = "/download", method = RequestMethod.GET)
@ResponseBody
public Object downloadExcel(HttpServletResponse response) {
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Content-disposition",
                "attachment; filename=" + theFileNameString + ".xls");
        try {
            generateExcel(response.getOutputStream());
        } catch (IOException e) {
            System.out.println("ERROR: " + e);
        }
    return null;
}

Check all the streams flushed/closed

Upvotes: 10

Related Questions