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 2013/08/07 22:58:14 UTC
[Bug 55384] New: Setting a precalculated String value on a formula
cell clears out the cell
https://issues.apache.org/bugzilla/show_bug.cgi?id=55384
Bug ID: 55384
Summary: Setting a precalculated String value on a formula cell
clears out the cell
Product: POI
Version: 3.9
Hardware: PC
Status: NEW
Severity: normal
Priority: P2
Component: SXSSF
Assignee: dev@poi.apache.org
Reporter: akokskis@gmail.com
Created attachment 30713
--> https://issues.apache.org/bugzilla/attachment.cgi?id=30713&action=edit
The generated spreadsheet showing the missing information in cell B11
Trying to set a precalculated String value on a formula cell fails totally,
leaving absolutely no information in that cell in the generated spreadsheet.
This is counter to what is written in the API for SXSSFCell (and for that
matter, the Cell interface as well), which states: "Note, this method only sets
the formula string and does not calculate the formula value. To set the
precalculated value use setCellValue(double) or setCellValue(String)"
Here is a snippet of code that demonstrates this incorrect behavior. This code
will generate a spreadsheet with 11 rows, the last one (row #11) being sums of
their respective columns. In column A (cell A11), no precalculated value is
set. In column B (cell B11), a precalculated value is set as a String and here
is where we see the failure, with absolutely no information existing in the
generated spreadsheet. In column C (cell C11), a precalculated value is set as
an integer, and that performs as expected.
I've also attached a copy of the generated spreadsheet to this report.
SXSSFWorkbook wb = new SXSSFWorkbook(100);
Sheet sh = wb.createSheet();
for(int rownum = 0; rownum < 10; rownum++){
org.apache.poi.ss.usermodel.Row row = sh.createRow(rownum);
for(int cellnum = 0; cellnum < 3; cellnum++){
Cell cell = row.createCell(cellnum);
cell.setCellValue(rownum + cellnum);
}
}
org.apache.poi.ss.usermodel.Row row = sh.createRow(10);
// setting no precalculated value works just fine.
Cell cell1 = row.createCell(0);
cell1.setCellFormula("SUM(A1:A10)");
// but setting a precalculated STRING value fails totally
Cell cell2 = row.createCell(1);
cell2.setCellFormula("SUM(B1:B10)");
cell2.setCellValue("55");
// setting a precalculated int value works as expected
Cell cell3 = row.createCell(2);
cell3.setCellFormula("SUM(C1:C10)");
cell3.setCellValue(65);
FileOutputStream out = new FileOutputStream("/temp/sxssf.xlsx");
wb.write(out);
out.close();
Please also note that I have not had a chance to test this outside of the SXSSF
component, so I'm unsure as to whether or not this bug is isolated to SXSSF or
exists in the general case.
Thanks!
--
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 55384] Setting a precalculated String value on a formula cell
clears out the cell
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=55384
--- Comment #3 from Adrian K <ak...@gmail.com> ---
Ok, so I finally got around to writing this as a general unit test and it looks
like this bug is limited to SXSSF.
Here's a snippet of code that demonstrates the failure in SXSSF, while showing
that XSSF and HSSF work as one would expect. I'm not too familiar with using
JUnit assertions, etc, so please excuse my code if it's blatantly wrong.
I've also attached the three spreadsheets that this code will generate.
Workbook [] wbs = new Workbook[] { new HSSFWorkbook(), new XSSFWorkbook(),
new SXSSFWorkbook() };
String fileBase = "/temp/";
for (Workbook wb : wbs) {
Sheet sh = wb.createSheet();
for(int rownum = 0; rownum < 10; rownum++){
org.apache.poi.ss.usermodel.Row row = sh.createRow(rownum);
for(int cellnum = 0; cellnum < 3; cellnum++){
Cell cell = row.createCell(cellnum);
cell.setCellValue(rownum + cellnum);
}
}
Row row = sh.createRow(10);
// setting no precalculated value works just fine.
Cell cell1 = row.createCell(0);
cell1.setCellFormula("SUM(A1:A10)");
// but setting a precalculated STRING value fails totally in SXSSF
Cell cell2 = row.createCell(1);
cell2.setCellFormula("SUM(B1:B10)");
cell2.setCellValue("55");
// setting a precalculated int value works as expected
Cell cell3 = row.createCell(2);
cell3.setCellFormula("SUM(C1:C10)");
cell3.setCellValue(65);
String name = wb.getClass().getCanonicalName();
String ext = (wb instanceof HSSFWorkbook) ? ".xls" : ".xlsx";
try {
FileOutputStream output = new FileOutputStream(fileBase + name + ext);
wb.write(output);
output.close();
} catch (Exception ignored) {}
}
for (Workbook wb : wbs) {
String name = wb.getClass().getCanonicalName();
String ext = (wb instanceof HSSFWorkbook) ? ".xls" : ".xlsx";
FileInputStream fis = null;
int cellIdx = 0;
try {
fis = new FileInputStream(fileBase + name + ext);
Workbook readFile = WorkbookFactory.create(fis);
Sheet sheet = readFile.getSheetAt(0);
Row row = sheet.getRow(sheet.getLastRowNum());
for (Cell cell : row) {
cellIdx++;
String cellValue = null;
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
cellValue = cell.getRichStringCellValue().getString();
break;
case Cell.CELL_TYPE_FORMULA:
cellValue = cell.getCellFormula();
break;
}
cellValue = cellValue.isEmpty() ? null : cellValue;
Assert.assertNotNull(cellValue);
}
} catch (AssertionFailedError e) {
System.out.println("!!!!!!!!");
System.out.println("Assertion Error on %s at cellIdx %d", name, cellIdx);
} catch (Exception ignored) {
// log exe
} finally {
if (fis != null)
fis.close();
}
}
--
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 55384] Setting a precalculated String value on a formula cell
clears out the cell
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=55384
--- Comment #2 from Adrian K <ak...@gmail.com> ---
>Any chance you could re-write this as a general unit test across all implementations?
Sure, I'll do that sometime within the next few days. We'll see just how slow
work is ;)
--
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 55384] Setting a precalculated String value on a formula cell
clears out the cell
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=55384
--- Comment #6 from Adrian K <ak...@gmail.com> ---
Created attachment 30734
--> https://issues.apache.org/bugzilla/attachment.cgi?id=30734&action=edit
SXSSF spreadsheet generated using new code; SHOWS BUG
--
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 55384] Setting a precalculated String value on a formula cell
clears out the cell
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=55384
--- Comment #4 from Adrian K <ak...@gmail.com> ---
Created attachment 30732
--> https://issues.apache.org/bugzilla/attachment.cgi?id=30732&action=edit
HSSF spreadsheet generated using new code; shows NO bug
--
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 55384] Setting a precalculated String value on a formula cell
clears out the cell
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=55384
Nick Burch <ap...@gagravarr.org> changed:
What |Removed |Added
----------------------------------------------------------------------------
OS| |All
--- Comment #1 from Nick Burch <ap...@gagravarr.org> ---
Any chance you could re-write this as a general unit test across all
implementations? Probably something starting with
for (Workbook wb : new Workbook[] { new HSSFWorkbook(), new XSSFWorkbook, new
SXSSFWorkbook() }) {
....
You'd need to use one hssf/xssf to read back afterwards to check, but otherwise
it ought to be possible to make a generic test to check how it behaves across
all versions
--
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 55384] Setting a precalculated String value on a formula cell
clears out the cell using SXSSF
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=55384
Adrian K <ak...@gmail.com> changed:
What |Removed |Added
----------------------------------------------------------------------------
Summary|Setting a precalculated |Setting a precalculated
|String value on a formula |String value on a formula
|cell clears out the cell |cell clears out the cell
| |using SXSSF
--
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 55384] Setting a precalculated String value on a formula cell
clears out the cell
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=55384
--- Comment #5 from Adrian K <ak...@gmail.com> ---
Created attachment 30733
--> https://issues.apache.org/bugzilla/attachment.cgi?id=30733&action=edit
XSSF spreadsheet generated using new code; shows NO bug
--
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