Bug 47026

Summary: Problems with Cell Formula
Product: POI Reporter: Matthew <matthew.knl>
Component: XSSFAssignee: POI Developers List <dev>
Status: RESOLVED FIXED    
Severity: normal    
Priority: P2    
Version: 3.5-dev   
Target Milestone: ---   
Hardware: PC   
OS: Windows XP   
Attachments: test.xlsm

Description Matthew 2009-04-14 02:51:41 UTC
Created attachment 23488 [details]
test.xlsm

Problem 1
=========
Cell "A1" contains a simple formula, following code assign a string value "456" to cell "A1". But the cell type is still Cell.CELL_TYPE_FORMULA after the assignment.
--
Workbook source = new XSSFWorkbook("test.xlsm");
Sheet sheet = source.getSheetAt(0);
Row row = sheet.getRow(0);
Cell cell = row.getCell(0);		
System.out.println("cell.getCellType() = " + cell.getCellType());
cell.setCellValue("456");
System.out.println("cell.getCellType() = " + cell.getCellType());
--

Problem 2
=========
NullPointerException is thrown if I explicit change the cell type to Cell.CELL_TYPE_STRING.
--
Workbook source = new XSSFWorkbook("test.xlsm");
Sheet sheet = source.getSheetAt(0);
Row row = sheet.getRow(0);
Cell cell = row.getCell(0);		
cell.setCellType(Cell.CELL_TYPE_STRING);		
cell.setCellValue("456");
--
java.lang.NullPointerException
	at org.apache.poi.xssf.usermodel.XSSFCell.setCellType(XSSFCell.java:693)

Problem 3
=========
NullPointerException is thrown if I explicit set the cell formula to null.
--
Workbook source = new XSSFWorkbook("test.xlsm");
Sheet sheet = source.getSheetAt(0);
Row row = sheet.getRow(0);
Cell cell = row.getCell(0);
cell.setCellFormula(null);
cell.setCellValue("456");
--
java.lang.NullPointerException
	at org.apache.poi.xssf.usermodel.XSSFCell.setCellType(XSSFCell.java:693)
--
(If I change the cell value from "456" to 456, no exception will be thrown and cell type will be changed to Cell.CELL_TYPE_NUMERIC)
Comment 1 Yegor Kozlov 2009-04-17 11:08:51 UTC
"Problem 1" actually is not a problem. setCellValue does not change the cell type of a formula cell, it just updated the pre-calculated (cached) formula value. If you want to remove the formula, call setCellFormula(null) first.

"Problem 2" and "Problem 2" had the same origin. The trouble happened when the source workbook didn't contain string and the internal string cache was null.

Fixed in 766103

Yegor
Comment 2 Matthew 2009-04-19 20:02:47 UTC
I found that "Problem 3" still exists in 766563.
Comment 3 Yegor Kozlov 2009-04-19 22:18:12 UTC
Did you use a different file for testing? I've just checked, "Problem 3" is not reproducible with the posted code and attached test.xlsm.

Yegor
Comment 4 Matthew 2009-04-20 03:11:55 UTC
My test case call Cell.setCellFormula(null) in a non-formula cell, it throws NullPointerException. Is it normal and expected?
Comment 5 Yegor Kozlov 2009-04-20 09:51:05 UTC
(In reply to comment #4)
> My test case call Cell.setCellFormula(null) in a non-formula cell, it throws
> NullPointerException. Is it normal and expected?

Sounds like "Problem 4" :)

Should be fixed in r766750.

Yegor