JAVA-Apache-POI-java.lang.IllegalStateException: Cannot get a STRING value from a NUMERIC cell

Bertha 。 2022-05-16 09:45 191阅读 0赞

使用Apache-POI读取excel文件时，如果获取数据的方法与实际类型不符，会抛出IllegalStateException错误，如下：

Exception in thread "main" java.lang.IllegalStateException: Cannot get a STRING value from a NUMERIC cell
        at org.apache.poi.hssf.usermodel.HSSFCell.typeMismatch(HSSFCell.java:654)
        at org.apache.poi.hssf.usermodel.HSSFCell.getRichStringCellValue(HSSFCell.java:731)
        at org.apache.poi.hssf.usermodel.HSSFCell.getStringCellValue(HSSFCell.java:714)
        at ExcelTest.readExcel(ExcelTest.java:31)
        at ExcelTest.main(ExcelTest.java:18)

解决方法是在读取数据前设置cell的type，如下：

public static void readExcel() throws IOException, InvalidFormatException {
            Workbook workbook = WorkbookFactory.create(new File(SAMPLE_XLSX_FILE_PATH2));
        System.out.println("sheets" + workbook.getNumberOfSheets());
        //获取一张表
        Sheet sheet = workbook.getSheetAt(0);
        for (Row row : sheet) {
            int index = 0;
            for (Cell cell : row) {
                //读取数据前设置单元格类型
                cell.setCellType(CellType.STRING);
                String value = cell.getStringCellValue();
                System.out.print("value:" + value + " ");
                index++;
            }
            System.out.println();
        }
    }