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
