Exporting xlsx using apache poi 3.13 on weblogic : File format or extension not valid

Question

Earlier I was using Apache POI 2.5.1 to export .xls file using HSSFWorkbook. With updated Apache POI to 3.13 I am exporting .xlsx file using SXSSFWorkbook but its exporting corrupted file.

MS Excel failed to open file with File format or extension not valid error.

Note that this issue I am facing only on WebLogic server, it works fine with JBoss.

Anybody can help what I am doing wrong here ?

Code:

    List<JRField> fields = ds.getFields();
    SXSSFWorkbook wb = new SXSSFWorkbook();
    SXSSFSheet sheet = wb.createSheet("Sheet1");

    try {
        CellStyle cellStyle         = wb.createCellStyle();
        CellStyle cellStyleColName  = wb.createCellStyle();
        CellStyle cellStyleTitle    = wb.createCellStyle();

        Font boldFont = wb.createFont();
        boldFont.setFontHeightInPoints((short)16);
        boldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

        // Cell Style for body
        cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("($#,##0_);[Red]($#,##0)"));
        cellStyle.setWrapText(true);

        // Cell Style for Column Names
        cellStyleColName.setDataFormat(HSSFDataFormat.getBuiltinFormat("($#,##0_);[Red]($#,##0)"));
        cellStyleColName.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        cellStyleColName.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); // single line border
        cellStyleColName.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); // single line border

        // Cell Style for Title
        cellStyleTitle.setDataFormat(HSSFDataFormat.getBuiltinFormat("($#,##0_);[Red]($#,##0)"));
        cellStyleTitle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        cellStyleTitle.setFont(boldFont);

        // Creating Title Row
        Row row1 = sheet.createRow((short) 0);

        // Creating the Title line
        Cell cell1 = row1.createCell((short) 0);
        cell1.setCellValue("Demo Title");
        cell1.setCellStyle(cellStyleTitle);

        // Title Region
        CellRangeAddress regionTitle = new CellRangeAddress(  (short) 0,       // From Row
                (short) 0,                                    // From Col
                (short) 0,                                    // To Row
                (short) (this.displayCols.size()-1)           // To Col

        );
        sheet.addMergedRegion(regionTitle);

        // Column Name Row
        int j =0;
        Row row2 = sheet.createRow((short) 1);
        for (ReportColumn col : this.displayCols)
        {
            Cell cell2 = row2.createCell((short) j++);
            cell2.setCellValue(col.getDisplayName());
            cell2.setCellStyle(cellStyleColName);
        }

        int i =2;
        while (ds.next()) {
            Row rows = sheet.createRow((short) 0 + i);
            int k = 0;
            for (JRField field : fields) {
                String fieldAsString = (ds.getFieldValue(field) != null ? ds.getFieldValue(field).toString():null);
                Cell cell = rows.createCell((short) k++);
                cell.setCellStyle(cellStyle);
                cell.setCellValue(fieldAsString);
            }
            i++;
            if (i > RECORD_LIMIT_FROM_POI){
                log.info("Row limit from poi reached #1048576 and exported data is truncated.");
                break;
            }
        }

        wb.write(os);
    }
    catch (Exception e) {
        log.error("error in createXlsFile method", e);
    }

Failed attempts:

Updated mime type in response header from application/vnd.ms-excel to vnd.openxmlformats-officedocument.spreadsheetml.sheet
Added xlsx=vnd.openxmlformats-officedocument.spreadsheetml.sheet in custom mime mapping file for WebLogic

okay, well your code works without problems on a WL 12.1.3. Don't have a 10.3.5 installation :( — Slettal, May 11 '16 at 12:04
The oldest version I could get my hands on is 10.3.6, which also works. I only added the following 3 POI Libraries: poi-3.13, poi-ooxml-3.13, poi-ooxml-schema-3.13. All on a windows 7 system, running on java 8 SDK — Slettal, May 11 '16 at 12:23
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/111726/discussion-between-kishan-sarsecha-gajjar-and-slettal). — Not a bug, May 12 '16 at 09:23
May be the output stream is not flushed correctly in this version? try forcing a flush on the output stream. — gavioto, May 17 '16 at 21:26

score 0 · Answer 1 · edited May 23 '17 at 12:23

0

This may be more resource heavy, but have you tried:

XSSFWorkbook wb = new SXSSFWorkbook();
XSSFSheet sheet = wb.createSheet("Sheet1");

instead of SXSSF.

There is some discussion between the various types here: HSSFWorkbook vs XSSFWorkbook and the advantages/disadvantages of XSSFWorkbook and SXSSFWorkbook?

edited May 23 '17 at 12:23

Community

1
1

answered May 19 '16 at 17:02

Karlomanio

344
4
11

Exporting xlsx using apache poi 3.13 on weblogic : File format or extension not valid

1 Answers1