Geek Answers Handbook

Export xlsx using apache poi 3.13 to weblogic: file format or extension is not valid

I used to use Apache POI 2.5.1 to export the .xls file using HSSFWorkbook . With the updated Apache POI to 3.13, I export the .xlsx file using SXSSFWorkbook , but exporting the damaged file.

MS Excel could not open the file with the error Incorrect file format or extension .

Please note that this problem is only related to the WebLogic server, it works fine with JBoss .

Can anyone help what I'm doing wrong here?

the 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 to custom mime mapping file for WebLogic
+6
source share
1 answer

This may be a harder resource, but you tried:

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

instead of SXSSF.

There are several discussions between the different types: HSSFWorkbook vs XSSFWorkbook and the advantages / disadvantages of XSSFWorkbook and SXSSFWorkbook?

0
source

More articles:


All Articles