You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Paul Dobson <pd...@viranim.com> on 2009/03/20 20:28:39 UTC
Unable to chance cell value after using setCellFormula()
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: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Unable to chance cell value after using setCellFormula()
Posted by Yegor Kozlov <ye...@dinom.ru>.
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: user-unsubscribe@poi.apache.org
>> For additional commands, e-mail: user-help@poi.apache.org
>>
>>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Unable to chance cell value after using setCellFormula()
Posted by Josh Micich <jo...@gmail.com>.
I've proposed a patch to fix the corrupted workbook issue:
https://issues.apache.org/bugzilla/show_bug.cgi?id=46885
After that fix, POI still may not be behaving the way you are expecting.
Cell.setValue() has some slightly unusual behaviour as Yegor pointed out.
When the cell is not a formula, the cell type will automatically change to
suit the value passed to setValue(). However, when the cell type *is*
formula, the formula is left in place, and only the 'cached formula result'
is changed. To clear a cell formula, you must call setCellType() with some
type other than CELL_TYPE_FORMULA. After that, you can call setValue() to
put plain values in the cell.
Yegor, can you look at the proposed patch, because I'm not 100% familiar
with the ooxml code (in particular the 3 string cell types).
I think it may not be feasible to make the IllegalArgumentException
suggestion work. Excel formulas can return different types. For example:
IF(A1=0, "abc", IF(A1=1, 42, #NUM!))
As a result of this (I think) Excel is quite happy to have any type in
the 'cached formula result', regardless of what the formula looks like.
Considering the original code posted, if the formula was:
IF(A1=0, "Help, I will not change!", NA())
then the line:
cell.setCellValue("Help, I will not change!");
now makes complete sense
Re: Unable to chance cell value after using setCellFormula()
Posted by Yegor Kozlov <ye...@dinom.ru>.
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: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org