In some cases, SXSSFWorkbook.write() produces a file with missing cached values for formula cells. Seems that this happens when the cached value is not numeric. Here's a setup to reproduce this behavior (uses JUnit4). If the same setup uses XSSFWorkbook, both formula and value are written correctly. If the value and type are changed to numeric (and value-getting method), the test passes. @Test public void testSXSSF() throws IOException { String sheetName = "1"; int rowIndex = 0; int colIndex = 0; String formula = "1"; String value = "yes"; CellType valueType = CellType.STRING; Workbook wb = new SXSSFWorkbook(); Sheet sheet = wb.createSheet(sheetName); Row row = sheet.createRow(rowIndex); Cell cell = row.createCell(colIndex); // this order ensures that value will not be overwritten by setting the formula cell.setCellFormula(formula); cell.setCellValue(value); assertEquals(CellType.FORMULA, cell.getCellTypeEnum()); assertEquals(formula, cell.getCellFormula()); assertEquals(valueType, cell.getCachedFormulaResultTypeEnum()); assertEquals(value, cell.getStringCellValue()); // so far so good File tmpFile = File.createTempFile("sxssf-write", ".xlsx"); tmpFile.deleteOnExit(); wb.write(new FileOutputStream(tmpFile)); wb.close(); XSSFWorkbook test = new XSSFWorkbook(tmpFile.getAbsolutePath()); Cell testCell = test.getSheet(sheetName).getRow(rowIndex).getCell(colIndex); assertEquals(CellType.FORMULA, testCell.getCellTypeEnum()); assertEquals(formula, testCell.getCellFormula()); // actually, no value is stored within the file, see file listing System.out.println(testCell.getNumericCellValue()); // fails assertEquals(CellType.STRING, testCell.getCachedFormulaResultTypeEnum()); // consequently fails assertEquals(value, testCell.getStringCellValue()); } Here's the extracted XML from the sxssf-written workbook: <?xml version="1.0" encoding="UTF-8"?> <worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"> <dimension ref="A1"/> <sheetViews> <sheetView workbookViewId="0" tabSelected="true"/> </sheetViews> <sheetFormatPr defaultRowHeight="15.0"/> <sheetData> <row r="1"> <c r="A1"> <f>1</f> </c> </row> </sheetData> <pageMargins bottom="0.75" footer="0.3" header="0.3" left="0.7" right="0.7" top="0.75"/> </worksheet>
Created attachment 35810 [details] Test case to demonstrate the behavior
Created attachment 35811 [details] proposed fix (diff)
I came up with a solution. Attached go a test case to demonstrate the behavior and the solution. The reason is that SheetDataWriter.writeCell, when the cell is a formula cell, actually only handles numeric cached values, ignoring all other cell types. In my solution I rearranged the whole method to follow the routine: * write cell header * if cell is blank, finalize cell and return * get value type (either getCellTypeEnum for regular cells or getCachedFormulaResultTypeEnum for formula cells) * base on type, write type tag to the header and close header * write formula, if any * based on type, write value * finalize cell
apologies - we missed this patch - issue seems to already have been independently fixed - I added your test case for regression purposes - r1894128