Apache POI : 更新 Excel 文件

Question

Apache POI ： Excel 更新问题， 将新值写入单元格后： java.lang.IllegalStateException：无法从 STRING 单元格中获取数值

可能是什么问题？如何解决？

我处理 5 个文件，其中 3 个按预期工作，另外 2 个则不然。 2 个错误文件的解决方法： Runtime.getRuntime((.exec("cmd/c start " + excel.getAbsolutePath(((;

// ---------------------------------------------------------------------------
// Here: new values are written into the cells
workbook.getCreationHelper().createFormulaEvaluator().evaluateAll();
workbook.setForceFormulaRecalculation(true);
OutputStream output = new FileOutputStream(excel.getAbsolutePath());
workbook.write(output);
output.flush();
output.close();
// Here, new values are subtracted from the cells, 
after Excel resolves with new values 

// ---------------------------------------------------------------------------
// Value in bad Cell: =B24
java.lang.IllegalStateException: Cannot get a NUMERIC value from a STRING cell
at org.apache.poi.xssf.usermodel.XSSFCell.typeMismatch(XSSFCell.java:1050)
at org.apache.poi.xssf.usermodel.XSSFCell.getNumericCellValue(XSSFCell.java:310)
at quicc.excel.api.ExcelHandlerXSSF.handleCell(ExcelHandlerXSSF.java:275)
at quicc.excel.api.ExcelHandlerXSSF.readCell(ExcelHandlerXSSF.java:251)

Answer 1

package poi.service;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.OutputStream;
public class POITestEva {
private String excelFilePath = "C:/Test/1.xlsm";
private FileInputStream inputStream;
private XSSFWorkbook workbook;
public static void main(String[] args) {
POITestEva pOITestEva = new POITestEva();
pOITestEva.updateCell(3.0);
System.out.println("D5 = " + pOITestEva.readCellTest("D5"));        // Line 23
}

public void updateCell(Double newData) {
try {
File excel = new File(excelFilePath);
inputStream = new FileInputStream(excel);
workbook = new XSSFWorkbook(inputStream);
workbook.setForceFormulaRecalculation(true);
Cell cell = getCell(1, "C8");
if (cell != null) {
cell.setCellValue(newData);
}
workbook.getCreationHelper().createFormulaEvaluator().evaluateAll();
OutputStream output = new FileOutputStream(excel);
workbook.write(output);
output.flush();
output.close();
workbook.close();
inputStream.close();
}
catch (Exception e) {
e.printStackTrace();
}
}

private Cell getCell(int sheetNr, String cellId) {
CellReference ref = new CellReference(cellId);
return getCell(sheetNr, ref.getCol(), ref.getRow());
}
private Cell getCell(int sheetNr, int col, int row) {
XSSFSheet sheet = workbook.getSheetAt(sheetNr);
if (sheet.getRow(row) != null
&& sheet.getRow(row).getCell(col) != null
&& !(sheet.getRow(row).getCell(col).getCellType() == Cell.CELL_TYPE_BLANK)) {
return sheet.getRow(row).getCell(col);
}
return null;
}

public Double readCellTest(String cellId) {
try {
File excel = new File(excelFilePath);
inputStream = new FileInputStream(excel);
workbook = new XSSFWorkbook(inputStream);
Double result = ( (Double) (readCell(cellId)) );            // Line 74
if (workbook != null) {
workbook.close();
}
if (inputStream != null) {
workbook.close();
}
return result;
}
catch (Exception e) {
e.printStackTrace();
return null;
}
}
private Object readCell(String cellId) {
Cell cell = getCell(1, cellId);
return handleCell(cell.getCellType(), cell);                    // Line 91
}

@SuppressWarnings("deprecation")
private Object handleCell(int type, Cell cell) {
switch (type) {
case XSSFCell.CELL_TYPE_STRING:
return cell.getStringCellValue();
case XSSFCell.CELL_TYPE_NUMERIC:
return cell.getNumericCellValue();
case XSSFCell.CELL_TYPE_BOOLEAN:
return cell.getBooleanCellValue();
case XSSFCell.CELL_TYPE_BLANK:
return null;
case XSSFCell.CELL_TYPE_ERROR:
return null;
case XSSFCell.CELL_TYPE_FORMULA:
return cell.getNumericCellValue();                  // Line 109
default:
return null;
}
}
}

Answer 2

"C:Program FilesJavajdk1.8.0_51binjava" -Didea.launcher.port=... ...java.lang.IllegalStateException: Cannot get a NUMERIC value from a STRING cell
D5 = null
at org.apache.poi.xssf.usermodel.XSSFCell.typeMismatch(XSSFCell.java:1050)
at org.apache.poi.xssf.usermodel.XSSFCell.getNumericCellValue(XSSFCell.java:310)
at service.POITestEva.handleCell(POITestEva.java:109)
at service.POITestEva.readCell(POITestEva.java:91)
at service.POITestEva.readCellTest(POITestEva.java:74)
at service.POITestEva.main(POITestEva.java:23)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:497)
at com.intellij.rt.execution.application.AppMain.main(AppMain.java:147)
Process finished with exit code 0

Answer 3

<apache.poi.version>3.16</apache.poi.version>
------------------------------------------------------------------------
D5      =SUM(C18:C20)
C18     =IFERROR(SUM(C31:32)
C19     =SUM(C33:34)
C20     =SUM(C35:36)
C31-C36 → next Formel etc.
-----------------------------------------------------------------------------