1. What is the error Cannot get a STRING value from a NUMERIC cell?
When using Apache-POI to read an excel file, if the method of obtaining data does not match the actual type, an IllegalStateException error will be thrown, as follows:
java.lang.IllegalStateException: Cannot get a STRING value from a NUMERIC cell
at org.apache.poi.xssf.usermodel.XSSFCell.typeMismatch(XSSFCell.java:1075)
at org.apache.poi.xssf.usermodel.XSSFCell.getRichStringCellValue(XSSFCell.java:396)
at org.apache.poi.xssf.usermodel.XSSFCell.getStringCellValue(XSSFCell.java:348)
at com.fr.function.POIExcel.createDownload(POIExcel.java:95)
at com.fr.function.POIExcel.run(POIExcel.java:30)
at com.fr.script.AbstractFunction.evalExpression(Unknown Source)
at com.fr.parser.FunctionCall.eval(Unknown Source)
at com.fr.script.Calculator.eval(Unknown Source)
at com.fr.stable.script.Expression.eval(Unknown Source)
at com.fr.script.Calculator.evalString(Unknown Source)
at com.fr.script.Calculator.eval(Unknown Source)
at com.fr.script.Calculator.evalValue(Unknown Source)
at com.fr.stable.AbstractFormulaProvider.evalValue(Unknown Source)
at com.fr.report.core.A.J.A(Unknown Source)
at com.fr.report.core.A.J.dealWithBENormal(Unknown Source)
at com.fr.report.core.A.m.cc_ce_array(Unknown Source)
at com.fr.report.core.A.J.A(Unknown Source)
at com.fr.report.core.A.J.A(Unknown Source)
at com.fr.report.core.A.J.A(Unknown Source)
at com.fr.report.core.A.J.A(Unknown Source)
at com.fr.report.core.A.J.A(Unknown Source)
at com.fr.report.core.A.J.C(Unknown Source)
at com.fr.report.core.A.J.execute4Expand(Unknown Source)
at com.fr.report.core.A.J.execute(Unknown Source)
at com.fr.report.worksheet.WorkSheet.execute(Unknown Source)
at com.fr.report.core.sheet.AbstractWorkBookExecutor._execute(Unknown Source)
at com.fr.report.core.sheet.AbstractWorkBookExecutor.execute(Unknown Source)
at com.fr.main.impl.WorkBook.execute(Unknown Source)
at com.fr.main.impl.WorkBook.execute(Unknown Source)
at com.fr.web.cache.ReportCache.getResultWorkBook(Unknown Source)
at com.fr.stable.PageActor.getResultBookFromCacheIfNeed(Unknown Source)
at com.fr.main.impl.WorkBookTemplateDelegate.execute(Unknown Source)
at com.fr.web.core.ReportSessionIDInfor.execute(Unknown Source)
at com.fr.web.core.ReportSessionIDInfor.computeBook2Show
o
r
i
g
i
n
a
l
original
originalvhZqY8Vf(Unknown Source)java.lang.IllegalStateException: Cannot get a STRING value from a NUMERIC cell
at org.apache.poi.xssf.usermodel.XSSFCell.typeMismatch(XSSFCell.java:1075)
at org.apache.poi.xssf.usermodel.XSSFCell.getRichStringCellValue(XSSFCell.java:396)
at org.apache.poi.xssf.usermodel.XSSFCell.getStringCellValue(XSSFCell.java:348)
at com.fr.function.POIExcel.createDownload(POIExcel.java:95)
at com.fr.function.POIExcel.run(POIExcel.java:30)
at com.fr.script.AbstractFunction.evalExpression(Unknown Source)
at com.fr.parser.FunctionCall.eval(Unknown Source)
at com.fr.script.Calculator.eval(Unknown Source)
at com.fr.stable.script.Expression.eval(Unknown Source)
at com.fr.script.Calculator.evalString(Unknown Source)
at com.fr.script.Calculator.eval(Unknown Source)
at com.fr.script.Calculator.evalValue(Unknown Source)
at com.fr.stable.AbstractFormulaProvider.evalValue(Unknown Source)
at com.fr.report.core.A.J.A(Unknown Source)
at com.fr.report.core.A.J.dealWithBENormal(Unknown Source)
at com.fr.report.core.A.m.cc_ce_array(Unknown Source)
at com.fr.report.core.A.J.A(Unknown Source)
at com.fr.report.core.A.J.A(Unknown Source)
at com.fr.report.core.A.J.A(Unknown Source)
at com.fr.report.core.A.J.A(Unknown Source)
at com.fr.report.core.A.J.A(Unknown Source)
at com.fr.report.core.A.J.C(Unknown Source)
at com.fr.report.core.A.J.execute4Expand(Unknown Source)
at com.fr.report.core.A.J.execute(Unknown Source)
at com.fr.report.worksheet.WorkSheet.execute(Unknown Source)
at com.fr.report.core.sheet.AbstractWorkBookExecutor._execute(Unknown Source)
at com.fr.report.core.sheet.AbstractWorkBookExecutor.execute(Unknown Source)
at com.fr.main.impl.WorkBook.execute(Unknown Source)
at com.fr.main.impl.WorkBook.execute(Unknown Source)
at com.fr.web.cache.ReportCache.getResultWorkBook(Unknown Source)
at com.fr.stable.PageActor.getResultBookFromCacheIfNeed(Unknown Source)
at com.fr.main.impl.WorkBookTemplateDelegate.execute(Unknown Source)
at com.fr.web.core.ReportSessionIDInfor.execute(Unknown Source)
at com.fr.web.core.ReportSessionIDInfor.computeBook2Show
o
r
i
g
i
n
a
l
original
originalvhZqY8Vf(Unknown Source)
2. Solution
The solution is to set the cell type before reading the data, as follows:
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import java.io.File;
import java.io.IOException;
public class ExcelTest {<!-- -->
public static final String SAMPLE_XLSX_FILE_PATH2 = "C:\Users\user\Desktop\test.xlsx";
public static void readExcel() throws IOException, InvalidFormatException {<!-- -->
Workbook workbook = WorkbookFactory.create(new File(SAMPLE_XLSX_FILE_PATH2));
//Get a table
Sheet sheet = workbook.getSheetAt(0);
for (Row row : sheet) {<!-- -->
int index = 0;
for (Cell cell : row) {<!-- -->
//Set the cell type before reading data. Just add this sentence.
cell.setCellType(CellType.STRING);
String cellvalue = cell.getStringCellValue();
System.out.print("cellvalue :" + cellvalue + " ");
index + + ;
}
}
}
public static void main(String arg[]) throws IOException, InvalidFormatException {<!-- -->
readExcel();
}
}