I pased null to setCellFormula as you suggested and it seemed to work. Thanks Yegor and Gosh for your help and dedication to improving POI.
Paul ---- POI Users List <[email protected]> wrote: > > 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] > > --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
