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]
