Bug 46535

Summary: Cell type cannot be changed and the output file cannot be opened by MS Excel 2007
Product: POI Reporter: Matthew <matthew.knl>
Component: XSSFAssignee: POI Developers List <dev>
Status: RESOLVED FIXED    
Severity: critical    
Priority: P2    
Version: 3.5-dev   
Target Milestone: ---   
Hardware: PC   
OS: Windows XP   
Attachments: 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 1 Matthew 2009-01-14 22:42:43 UTC
Created attachment 23120 [details]
the source excel file.
Comment 2 Matthew 2009-01-14 22:43:52 UTC
Created attachment 23121 [details]
the ouput excel file.
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 8 Matthew 2009-02-05 22:08:53 UTC
Created attachment 23236 [details]
xlsx file for test
Comment 9 Matthew 2009-02-05 22:09:09 UTC
Created attachment 23237 [details]
excel error log
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