Bug 47026 - Problems with Cell Formula
Summary: Problems with Cell Formula
Status: RESOLVED FIXED
Alias: None
Product: POI
Classification: Unclassified
Component: XSSF (show other bugs)
Version: 3.5-dev
Hardware: PC Windows XP
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2009-04-14 02:51 UTC by Matthew
Modified: 2009-04-20 09:51 UTC (History)
0 users



Attachments
test.xlsm (8.01 KB, application/vnd.ms-excel.sheet.macroenabled.12)
2009-04-14 02:51 UTC, Matthew
Details

Note You need to log in before you can comment on or make changes to this bug.
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