Bug 59458 - Exporting xlsx using apache poi 3.13 on weblogic : File format or extension not valid
Summary: Exporting xlsx using apache poi 3.13 on weblogic : File format or extension n...
Status: RESOLVED INVALID
Alias: None
Product: POI
Classification: Unclassified
Component: XSSF (show other bugs)
Version: 3.13-FINAL
Hardware: PC Linux
: P1 blocker (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2016-05-11 12:07 UTC by Kishan Sarsecha
Modified: 2016-05-17 08:55 UTC (History)
1 user (show)



Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Kishan Sarsecha 2016-05-11 12:07:47 UTC
Earlier I was exporting xls with 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 or it is POI bug?

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:**
 1. Updated mime type in response header from `application/vnd.ms-excel`
    to `vnd.openxmlformats-officedocument.spreadsheetml.sheet`
 2. Added `xlsx=vnd.openxmlformats-officedocument.spreadsheetml.sheet` in custom mime mapping file for WebLogic

Notes:
WebLogic running on Linux SuSe
WebLogic version 10.3.5.0
Comment 1 Javen O'Neal 2016-05-11 15:02:04 UTC
Your example is missing a few critical lines, such as what os is. FileOutputStream("/tmp/Workbook.xlsx") I hope.
Your example probably contains more lines than are relevant. Can any of the lines in the body be deleted and still recreate your problem? The more you cut out irrelevant parts of your code, the easier it is to provide suggestions.

Have you tried using XSSFWorkbook yet?

SO post
http://stackoverflow.com/q/37159328/2683399
Looks like you're using poi-3.13, poi-ooxml-3.13, and poi-ooxml-schemas-3.13, so at least you're not mixing POI versions. Make sure you don't have any other versions of POI on your class path.

It's probably better to ask usage questions on one of the POI mailing lists. http://poi.apache.org/mailinglists.html

Closing this bug unless someone can demonstrate a bug in POI rather than an environment configuration problem.
Comment 2 Kishan Sarsecha 2016-05-12 05:38:59 UTC
Responses are given inline.

Your example is missing a few critical lines, such as what os is. 
- WebLogic running on Linux Suse

Your example probably contains more lines than are relevant. Can any of the lines in the body be deleted and still recreate your problem? The more you cut out irrelevant parts of your code, the easier it is to provide suggestions.
- I have tried removing CellRangeAddress and all CellStyle, still facing the issue.

Have you tried using XSSFWorkbook yet?
- Yes tried using XSSFWorkbook as well, still facing the issue.

SO post
http://stackoverflow.com/q/37159328/2683399
Looks like you're using poi-3.13, poi-ooxml-3.13, and poi-ooxml-schemas-3.13, so at least you're not mixing POI versions. Make sure you don't have any other versions of POI on your class path.
- Confirmed, no conflicts.

Closing this bug unless someone can demonstrate a bug in POI rather than an environment configuration problem.
- Seems like issue is on this specific version of WebLogic, I have tested on higher versions and its working fine (Please refer: http://stackoverflow.com/q/37159328/2683399)
Comment 3 Kishan Sarsecha 2016-05-12 06:38:46 UTC
Tested with,

FileOutputStream stream = new FileOutputStream("/tmp/testfile.xlsx");
wb.write(stream);
stream.flush();
stream.close();

Still not working, facing the same issue.

Reopening.
Comment 4 Kishan Sarsecha 2016-05-12 08:58:18 UTC
Minimal code which is not working on WebLogic 10.3.5

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

        String[] displayCols = new String[] {"FirstColumn", "SecondColumn", "ThirdColumn"};
        String[][] values = new String[][] {
                {"Value1", "Value2", "Value3"},
                {"Value4", "Value5", "Value6"},
                {"Value7", "Value8", "Value9"}
        };

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

        // Creating the Title line
        Cell cell1 = row1.createCell(0);
        cell1.setCellValue("This is test report");

        // Column Name Row
        int j =0;
        Row row2 = sheet.createRow(1);
        for (String col : displayCols)
        {
            Cell cell2 = row2.createCell(j++);
            cell2.setCellValue(col);
        }

        int i = 2;
        for( String[] strs: values) {
            Row rows = sheet.createRow(i);
            int k = 0;
            for ( String value: strs) {
                Cell cell = rows.createCell(k++);
                cell.setCellValue(value);
            }
            i++;
        }

        try {
            wb.write(os); // os is ServletOutputStream of HTTPServletResponse
        } catch (IOException e) {
            e.printStackTrace();
        }
Comment 5 Nick Burch 2016-05-12 18:42:48 UTC
As this is working on other platforms, I'd suggest you'd be best off speaking to Weblogic support
Comment 6 Kishan Sarsecha 2016-05-16 05:16:28 UTC
Want to confirm whether Apache POI 3.13 supports all WebLogic version ?
Could someone please confirm here that exporting .xlsx using SXSSFWorkbook supported on WebLogic 10.3.5.0 ?
Comment 7 Nick Burch 2016-05-16 05:48:25 UTC
Apache POI supports all standards-compliant Java Virtual Machines (JVMs), and is routinely tested with most of the main freely available ones. For compatibility with paid-for java platforms, you'll be best off asking the support team of the product you're paying money for - they're much more likely to know than volunteers giving up their spare time to support software they've given to you for free...
Comment 8 Kishan Sarsecha 2016-05-16 14:06:03 UTC
Thanks Nick :)
Comment 9 Dominik Stadler 2016-05-17 08:55:45 UTC
Resolving this one again here as we do not see anything being done incorrectly in POI at the moment.