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)
"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
I found that "Problem 3" still exists in 766563.
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
My test case call Cell.setCellFormula(null) in a non-formula cell, it throws NullPointerException. Is it normal and expected?
(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