https://bz.apache.org/bugzilla/show_bug.cgi?id=62215
Bug ID: 62215
Summary: SXSSFWorbook.write() doesn't write cached values for
formula cells (when the value is not numeric?)
Product: POI
Version: 3.17-FINAL
Hardware: PC
Status: NEW
Severity: normal
Priority: P2
Component: SXSSF
Assignee: [email protected]
Reporter: [email protected]
Target Milestone: ---
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>
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
