Apache POI generates corrupted Excel File

Question

I am using Apache POI to create an excel file with the help of database. I tried searching a lot with different codes and all I got was corrupted excel sheet over and over again. As of now, here is the code which I am using:

    public void  generateExcel(ResultSet rs, String excelFilename, String newDesc){

    try {
        Workbook wb = new XSSFWorkbook();
        Cell c = null;
        //Cell style for header row
        CellStyle cs = wb.createCellStyle();
        cs.setFillForegroundColor(IndexedColors.LIME.getIndex());
        cs.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
        Font f = wb.createFont();
        f.setBoldweight(Font.BOLDWEIGHT_BOLD);
        f.setFontHeightInPoints((short) 12);
        cs.setFont(f);

        //New Sheet
        Sheet sheet1 = null;
        sheet1 = wb.createSheet(newDesc);


        ResultSetMetaData metaData = rs.getMetaData();
        int colCount = metaData.getColumnCount();

        //Create Hash Map of Field Definitions
        LinkedHashMap<Integer, MyTableInfo> hashMap = new LinkedHashMap<Integer, MyTableInfo>(colCount);

        for (int i = 0; i < colCount; i++) {
            MyTableInfo db2TableInfo = new MyTableInfo(); 
            db2TableInfo.setFieldName(metaData.getColumnName(i + 1).trim());
            db2TableInfo.setFieldText(metaData.getColumnLabel(i + 1));
            db2TableInfo.setFieldSize(metaData.getPrecision(i + 1));
            db2TableInfo.setFieldDecimal(metaData.getScale(i + 1));
            db2TableInfo.setFieldType(metaData.getColumnType(i + 1));
            db2TableInfo.setCellStyle(getCellAttributes(wb, c, db2TableInfo));
            hashMap.put(i, db2TableInfo);
        }

        // Row and column indexes
        int idx = 0;
        int idy = 0;

        // Generate column headings
        Row row = sheet1.createRow(idx);
        MyTableInfo db2TableInfo = new MyTableInfo();

        Iterator<Integer> iterator = hashMap.keySet().iterator();
        while (iterator.hasNext()) {
            Integer key = (Integer) iterator.next();
            db2TableInfo = hashMap.get(key); 
            c = row.createCell(idy);
            c.setCellValue(db2TableInfo.getFieldText());
            c.setCellStyle(cs);
            if(db2TableInfo.getFieldSize() > db2TableInfo.getFieldText().trim().length()){
                sheet1.setColumnWidth(idy, (db2TableInfo.getFieldSize()* 500));
            }
            else {
                sheet1.setColumnWidth(idy, (db2TableInfo.getFieldText().trim().length() * 500));
            }
            idy++;
        }

        while (rs.next()) {

            idx++;
            row = sheet1.createRow(idx);
            System.out.println(idx);
            for (int i = 0; i < colCount; i++) {

                c = row.createCell(i);
                db2TableInfo = hashMap.get(i);

                switch (db2TableInfo.getFieldType()) {
                case 1:
                    c.setCellValue(rs.getString(i+1));
                    break;
                case 2:
                    c.setCellValue(rs.getDouble(i+1));
                    break;
                case 3:
                    c.setCellValue(rs.getDouble(i+1));
                    break;
                default:
                    c.setCellValue(rs.getString(i+1));
                    break;
                }
                c.setCellStyle(db2TableInfo.getCellStyle());
            }

        }

        rs.close();


        FileOutputStream fileOut = new FileOutputStream(excelFilename);

        wb.write(fileOut);
        fileOut.close();

    }
    catch (Exception e) {
        System.out.println(e);
    }

}


private static CellStyle getCellAttributes (Workbook wb, Cell c, MyTableInfo db2TableInfo){

    CellStyle cs= wb.createCellStyle();
    DataFormat df = wb.createDataFormat();
    Font f = wb.createFont();

    switch (db2TableInfo.getFieldDecimal()) {
    case 1:
        cs.setDataFormat(df.getFormat("#,##0.0"));
        break;
    case 2:
        cs.setDataFormat(df.getFormat("#,##0.00"));
        break;
    case 3:
        cs.setDataFormat(df.getFormat("#,##0.000"));
        break;
    case 4:
        cs.setDataFormat(df.getFormat("#,##0.0000"));
        break;
    case 5:
        cs.setDataFormat(df.getFormat("#,##0.00000"));
        break;
    default:
        break;
    }

    cs.setFont(f);

    return cs;

}

I get the following message:

"Excel cannot open the file "filename" because 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."

I used to generate excel files using JXL earlier but recently it started giving me the same thing too hence i opted to switch to Apache POI.

Thanks in advance.

Answer 1

Your HttpServletResponse has been setted correctly? What file extension are you using?

Try with this:

ServletOutputStream os = response.getOutputStream();
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment; filename=\"example.xls\"");

workbook = getWorkbook(....)

workbook.write(os);
workbook.close();
os.flush();

response.flushBuffer(); 

Where 'response' is a HttpServletResponse from the controller method