Spring MVC, Excel file upload, corrupts file
I am working on an excel export function in one of my web applications. I installed a small test case and got the download but the xlsx file is corrupted and doesn't know what else I can try. If I write excel to a file, it opens without problem, so the error should happen on upload.
Setting:
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 character set will be set when it is binary data. Could this be the problem?
source to share
Don't return ModelAndView, just write excel file in response to 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 dumped / closed streams
source to share
I would suggest using an existing solution rather than trying to handle the response stream on your own.
I used AbstractExcelView instead of yours AbstractPOIExcelView
to complete the assignment. Check out this tutorial using AbstractExcelView for inspiration.
For Spring 4.2 or newer, use AbstractXlsView (or AbstractXlsxView ) as the original is AbstractExcelView
deprecated.
source to share