Bug 62215 - SXSSFWorbook.write() doesn't write cached values for formula cells when the value is not numeric
Summary: SXSSFWorbook.write() doesn't write cached values for formula cells when the v...
Status: NEW
Alias: None
Product: POI
Classification: Unclassified
Component: SXSSF (show other bugs)
Version: 3.17-FINAL
Hardware: PC All
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2018-03-23 13:41 UTC by gallon.fizik@gmail.com
Modified: 2018-03-23 22:51 UTC (History)
0 users

Test case to demonstrate the behavior (4.17 KB, text/plain)
2018-03-23 22:34 UTC, gallon.fizik@gmail.com
proposed fix (diff) (3.81 KB, patch)
2018-03-23 22:50 UTC, gallon.fizik@gmail.com
Details | Diff

Note You need to log in before you can comment on or make changes to this bug.
Description gallon.fizik@gmail.com 2018-03-23 13:41:36 UTC
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.

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

    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");
    wb.write(new FileOutputStream(tmpFile));

    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

    // 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"/>
    <sheetView workbookViewId="0" tabSelected="true"/>
  <sheetFormatPr defaultRowHeight="15.0"/>
    <row r="1">
      <c r="A1">
  <pageMargins bottom="0.75" footer="0.3" header="0.3" left="0.7" right="0.7" top="0.75"/>
Comment 1 gallon.fizik@gmail.com 2018-03-23 22:34:37 UTC
Created attachment 35810 [details]
Test case to demonstrate the behavior
Comment 2 gallon.fizik@gmail.com 2018-03-23 22:50:47 UTC
Created attachment 35811 [details]
proposed fix (diff)
Comment 3 gallon.fizik@gmail.com 2018-03-23 22:51:13 UTC
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