File Format and extension of the report.xlsx don't match

Question

The Servlet and DAO code looks like below:

        @WebServlet("/ExcelExportController")
        public class ExcelExportController extends HttpServlet {
            private static final long serialVersionUID = 1L;

            /**
             * @see HttpServlet#HttpServlet()
             */
            public ExcelExportController() {
                super();
                // TODO Auto-generated constructor stub
            }

            /**
             * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse
             *      response)
             */
            @SuppressWarnings("unchecked")
            protected void doPost(HttpServletRequest request,
                    HttpServletResponse response) throws ServletException, IOException {
                // TODO Auto-generated method stub
                HttpSession session = request.getSession();
                if (session != null) {
                    FileOutputStream fileOut;
                    List<LoanOffersDTO> loanOffersDTOs;
                    ExcelExportDAO excelExportDAO;
                    HSSFWorkbook hssfWorkbook;
                    if (session.getAttribute("loanOffers") != null) {
                        loanOffersDTOs = (List<LoanOffersDTO>) session
                                .getAttribute("loanOffers");
                        excelExportDAO = new ExcelExportDAOImpl();
                        hssfWorkbook = excelExportDAO
                                .createLoanOffersXls(loanOffersDTOs);
                        if (hssfWorkbook != null) {
                            fileOut = new FileOutputStream("report.xlsx");
                            response.setContentType("application/application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
                            response.setHeader("Content-Disposition",
                                    "attachment; filename=report.xlsx");
                            hssfWorkbook.write(fileOut);
                            fileOut.flush();
                            fileOut.close();
                        } else {

                        }
                    }
                }

            }

        }

public class ExcelExportDAOImpl implements ExcelExportDAO {

    @Override
    public HSSFWorkbook createLoanOffersXls(List<LoanOffersDTO> loanOffersDTOs) throws IOException {
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("LoanOffer");

        HSSFRow headRow = sheet.createRow(0);
        headRow.createCell(0).setCellValue("Bank Name");
        headRow.createCell(1).setCellValue("Offer Name");
        headRow.createCell(2).setCellValue("Loan Officer");
        headRow.createCell(3).setCellValue("Telephone");
        headRow.createCell(4).setCellValue("Email");
        headRow.createCell(5).setCellValue("Interest Rate");
        headRow.createCell(6).setCellValue("Period");
        headRow.createCell(7).setCellValue("Pre-Payment");
        headRow.createCell(8).setCellValue("Installment");
        headRow.createCell(9).setCellValue("Loan details");

        HSSFRow dataRow;
        int rowCount = 1;
        for (LoanOffersDTO loanOffersDTO : loanOffersDTOs) {
            dataRow = sheet.createRow(rowCount++);
            dataRow.createCell(0).setCellValue(loanOffersDTO.getBankName());
            dataRow.createCell(1).setCellValue(loanOffersDTO.getOfferName());
            dataRow.createCell(2).setCellValue(
                    loanOffersDTO.getLoanOfficerName());
            dataRow.createCell(3).setCellValue(
                    loanOffersDTO.getBankerContactNum());
            dataRow.createCell(4)
                    .setCellValue(loanOffersDTO.getBankerEmailId());
            dataRow.createCell(5).setCellValue(loanOffersDTO.getInterestRate());
            dataRow.createCell(6).setCellValue(loanOffersDTO.getDuration());
            dataRow.createCell(7).setCellValue(
                    loanOffersDTO.getPrePaymentValue());
            dataRow.createCell(8).setCellValue(loanOffersDTO.getInstallments());
            dataRow.createCell(9).setCellValue(
                    loanOffersDTO.getLoanDescription());
        }

        FileOutputStream fileOut  = new FileOutputStream("C:\\Users\\Test\\Desktop\\report_"+Calendar.getInstance().getTimeInMillis()+".xls");
        workbook.write(fileOut);
        fileOut.flush();
        fileOut.close();

        return workbook;
    }
}

Summary:

I have a jsp, when it is submitted the above servlet/controller is called.This sevelet would create the excel file and flush the Excel. Here the excel file is created using "apache poi". The excel gets created and flushed out, on trying to open it I'm seeing the error "File Format and extension of the report.xlsx don't match." with no data. But if I try to flush same file to some particular location, I'm receiving the file perfectly. I have even tried with content type as "application/vnd.ms-excel" but issue is same. Thanks

Solution

You seem to have two problems. One is that you're using the HSSF code which generates .xls files, but sending a .xlsx style response. Secondly, you're writing the workbook to a file on the server, not sending it back to the client!

I'd suggest changing your code to be generic to handle both, something like

Workbook workbook;
....
workbook = excelExportDAO.createLoanOffersXls(loanOffersDTOs);
if (workbook != null) {
    if (workbook instanceof HSSFWorkbook) {
       response.setContentType("application/vnd.ms-excel");
       response.setHeader("Content-Disposition",
                                "attachment; filename=report.xls");
    } else {
       response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
       response.setHeader("Content-Disposition",
                                "attachment; filename=report.xlsx");
    }
    OutputStream out = response.getOutputStream();
    hssfWorkbook.write(out);    
    out.close();
}

Comments

[Alex Nevidomsky]

Which application gives you the error? Why do you use content type "application/application/vnd.openxmlformats-officedocument.s‌​preadsheetml.sheet" ?

[3ph3r]

Try using XSSFWorkbook and other classes with XSSF prefix instead of HSSF. Saving locally works because you're saving xls. XSSF classes = xlsx, HSSF classes = xls. And as a sidenote - declare your variables by interface name: Cell, Row, Sheet, Workbook etc.

[macserv]

That content type doesn't look correct. At the least, you've got an extra application/ in there. And the MIME type for .xlsx is application/vnd.openxmlformats-officedocument.spreadsheetml.‌​sheet. See: technet.microsoft.com/en-us/library/…

[phemanthkumar28]

Thanks!! @Alex. I got error while open in the file using Microsoft Excel.

[phemanthkumar28]

@3ph3r I tried using XSSF. But the issue still exists.

[phemanthkumar28]

@macserv I'did get your point!! Sorry!! can you please elaborate.

[phemanthkumar28]

I tried using you suggestion. But a corrupted file is generated.

[Gagravarr]

Remove all the bits of your code which save the workbook to disk, you've got several of them for no apparent reason. Make sure the only time you write the workbook out is when you send it to the browser

[JFer]

for xlsx I set: response.setContentType("Application/x-msexcel"); And It worked for me. (JIC)

[Gagravarr]

@JFer That's the wrong mime type though! Just because some cases may let you get away with it, doesn't mean all will or should...