|Summary:||Cell type cannot be changed and the output file cannot be opened by MS Excel 2007|
|Component:||XSSF||Assignee:||POI Developers List <dev>|
the source excel file.
the ouput excel file.
error prompt by ms excel 2007
error fixed by ms excel 2007
xlsx file for test
excel error log
Description Matthew 2009-01-14 22:41:30 UTC
I use following codes which assign a string value into a cell. That cell is a formula cell originally, I found that after the value is assigned, the cell type is still remain "Formula Type". AND when I open the output excel file, it cannot be opened by MS Excel 2007. (In fact, it can be opened but which need to be recovered by MS Excel 2007.) -- Workbook wb = new XSSFWorkbook("original.xlsm"); String value = "ABC"; Sheet sheet = wb.getSheet("Test"); Row row = sheet.getRow(0); Cell cell = row.getCell(4); System.out.println("before cell.getCellType() :" + cell.getCellType()); cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellValue(value); cell.setCellType(Cell.CELL_TYPE_STRING); System.out.println("after cell.getCellType() :" + cell.getCellType()); FileOutputStream output = new FileOutputStream("output.xlsm"); wb.write(output); -- Thanks!
Comment 3 Matthew 2009-01-14 22:44:20 UTC
Created attachment 23122 [details] error prompt by ms excel 2007
Comment 4 Matthew 2009-01-14 22:44:41 UTC
Created attachment 23123 [details] error fixed by ms excel 2007
Comment 5 Matthew 2009-02-05 00:01:26 UTC
I have to delay my company project because it rely heavily on this function. I raise the severity to critical and hope POI developers can get this bug resolved. Many thanks!
Comment 6 David Fisher 2009-02-05 09:01:38 UTC
Hi - So far XSSFWorkbook supports xlsx and does not yet support xlsm. xlsm is the version that can carry macros and program code. I have two thoughts: (1) What happens if you make and open an xlsx files instead. You can just change the suffix. (2) The error box you attached show that there is a log file of repairs made. It might help if you would attach that as well. FYI - xlsm is on the list, but we aren't sure when we will get to it - SpreadsheetML support is already ahead of WordProcessingML (docx) and PresentationML (pptx) Regards, Dave
Comment 7 Matthew 2009-02-05 22:06:33 UTC
Thanks for your reply! Now I use a xlsx file for testing, it produces the same error, please check the attachment file.
Comment 10 Yegor Kozlov 2009-02-08 05:24:19 UTC
You are not supposed to call setCellType directly, instead setCellValue(String) does it for you behind the scene. There are two possible scenarios when setting value for a formula cell; 1. Update the precalculated value of the formula. If a cell contains formula then cell.setCellValue just updates the precalculated formula value, the formula iself remains and the cell type is not changed: XSSFWorkbook wb = new XSSFWorkbook("original.xlsx"); XSSFSheet sheet = wb.getSheet("Test"); XSSFRow row = sheet.getRow(0); XSSFCell cell = row.getCell(4); cell.setCellValue("ABC"); //only update the precalculated formula value, the cell type remains CELL_TYPE_FORMULA. FileOutputStream output = new FileOutputStream("output.xlsx"); wb.write(output); output.close(); 2. Remove the formula and change the cell type to String or Number: XSSFWorkbook wb = new XSSFWorkbook("original.xlsx"); XSSFSheet sheet = wb.getSheet("Test"); XSSFRow row = sheet.getRow(0); XSSFCell cell = row.getCell(4); cell.setCellFormula(null); //Remove the formula cell.setCellValue("ABC"); //set the string value, the cell type is changed to CELL_TYPE_STRING FileOutputStream output = new FileOutputStream("output.xlsx"); wb.write(output); output.close(); Excel 2007 files can contain supplementary data called Calculation Chain. This object specifies the order in which the cells in a workbook were last calculated. It turns out that when a formula is deleted we need to remove all references to it from the calculation chain, otherwise the resulting file cannot be opened. This problem was fixed in r742077. Regards, Yegor