I improved setting of cell values in r757198. Daily builds can be downloaded
from http://encore.torchbox.com/poi-svn-build/
cell.setCellValue(string) and cell.setCellValue(number) should not corrupt workbook anymore, regardless if a formula is
set or not.
All that I wrote earlier is true.
If a cell contains formula then cell.setCellValue just updates the cached
formula value.
To remove formula and convert the cell to string or number you need to call
cell.setCellFormula(null) first.
Regards,
Yegor
There are two possible scenarios when setting value for a formula cell;
1. Update the pre-calculated value of the formula.
If a cell contains formula then cell.setCellValue just updates the
pre-calculated (cached) formula value, the formula itself remains and
the cell type is not changed
2. Remove the formula and change the cell type to String or Number:
cell.setCellFormula(null); //Remove the formula
then
cell.setCellValue("I changed! My type is CELL_TYPE_STRING now"");
or
cell.setCellValue(200); //NA() is gone, the real value is 200
I think we can improve cell.setCellValue for the case (1). If the new
value conflicts with formula type then IllegalArgumentException should
be thrown.
Regards,
Yegor
I am using 3.5 beta 5 ss usermodel.
When I attempt to set a cell containing a formula to another value, it
seems to corrupt the workbook. Setting it to a String will cause
excel to do a rapair on the workbook when opening it, resulting in the
formula still as the cell value. Changing it to a number will cause
the value showing to be the number. However, in the formula bar, the
formula still shows as the cell value. It is like the cell somehow has
both the number and formula value at the same time.
Did I find a bug or do I just not understand what I am doing?
Example code:
String xlsxFilePath = "Path to a new .xlsx file";
FileInputStream in = new FileInputStream(xlsxFilePath);
Workbook wb = WorkbookFactory.create(in);
Sheet sh = wb.getSheet("Sheet1");
Row row = sh.createRow(1);
Cell cell = row.createCell(1);
cell.setCellFormula("NA()");
//comment out 1 of the 2 following lines when testing
cell.setCellValue("Help, I will not change!"); //corrupts workbook
cell.setCellValue(200); //shows 200 but real value is NA()
in.close();
FileOutputStream out = new FileOutputStream(xlsxFilePath);
wb.write(out);
out.close();
Any suggestions are greatly appreciated.
Paul Dobson
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
