You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@poi.apache.org by bu...@apache.org on 2018/03/23 13:41:36 UTC
[Bug 62215] New: SXSSFWorbook.write() doesn't write cached values
for formula cells (when the value is not numeric?)
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: dev@poi.apache.org
Reporter: gallon.fizik@gmail.com
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: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 62215] SXSSFWorbook.write() doesn't write cached values for
formula cells when the value is not numeric
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=62215
gallon.fizik@gmail.com <ga...@gmail.com> changed:
What |Removed |Added
----------------------------------------------------------------------------
Summary|SXSSFWorbook.write() |SXSSFWorbook.write()
|doesn't write cached values |doesn't write cached values
|for formula cells (when the |for formula cells when the
|value is not numeric?) |value is not numeric
OS| |All
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 62215] SXSSFWorbook.write() doesn't write cached values for
formula cells when the value is not numeric
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=62215
--- Comment #2 from gallon.fizik@gmail.com <ga...@gmail.com> ---
Created attachment 35811
--> https://bz.apache.org/bugzilla/attachment.cgi?id=35811&action=edit
proposed fix (diff)
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 62215] SXSSFWorbook.write() doesn't write cached values for
formula cells when the value is not numeric
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=62215
--- Comment #3 from gallon.fizik@gmail.com <ga...@gmail.com> ---
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
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 62215] SXSSFWorbook.write() doesn't write cached values for
formula cells when the value is not numeric
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=62215
PJ Fanning <fa...@yahoo.com> changed:
What |Removed |Added
----------------------------------------------------------------------------
Status|NEW |RESOLVED
Resolution|--- |CLOSED
--- Comment #4 from PJ Fanning <fa...@yahoo.com> ---
apologies - we missed this patch - issue seems to already have been
independently fixed - I added your test case for regression purposes - r1894128
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 62215] SXSSFWorbook.write() doesn't write cached values for
formula cells when the value is not numeric
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=62215
--- Comment #1 from gallon.fizik@gmail.com <ga...@gmail.com> ---
Created attachment 35810
--> https://bz.apache.org/bugzilla/attachment.cgi?id=35810&action=edit
Test case to demonstrate the behavior
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org