POI 解析Excel，以及引用公式、计算公式的正常解析

此案例能够正常解析引用公式的值，以及计算公式的值

@SpringBootTest
class DemoApplicationTests {

    @Test
    void contextLoads() throws IOException {
        // 流读取文件
        FileInputStream is = new FileInputStream(new File("C:\\Users\\T011921\\Desktop\\测试引用1.xlsx"));
        // 根据流创建文件
        Workbook wb = new XSSFWorkbook(is);
        // 获取sheet
        Sheet sheet = wb.getSheetAt(0);
        int totalCells =sheet.getRow(0).getPhysicalNumberOfCells();
        List list = new ArrayList();
        // 遍历row
        for(Row row:sheet){
            // 第一行是标题，不需要读取
            if(row.getRowNum() < 2){
                continue;
            }
            // 当遇到空行，跳过
            Map map = new HashMap();
            for (int c =0; c < totalCells; c++) {
                Cell cell = row.getCell(c);
                String value = getCellValue(cell);
                map.put(c,value);
            }
            list.add(map);
        }
        System.out.println(list);
    }

    /**
     * 读取单元格内容 包括计算公式的结果，引用公式的结果（引用公式值当前的sheet单元格，引用了另一个Excel文件的内容例：='C:\Users\T011921\Desktop\[测试引用.xlsx]Sheet1'!A3）
     * @param cell
     * @return
     */
    public static String getCellValue(Cell cell){
        System.out.println(cell);
        String value = null;
        if(cell != null){
            System.out.println(cell.getCellType());
            switch (cell.getCellType()){
                case BLANK:
                    value = "";
                    break;
                case BOOLEAN:
                    value = String.valueOf(cell.getBooleanCellValue());
                    break;
                case FORMULA:
                    switch (cell.getCachedFormulaResultType()){
                        case NUMERIC:
                            if(DateUtil.isCellDateFormatted(cell)){
                                Date date = cell.getDateCellValue();
                                SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
                                value = sdf.format(date);
                            }else{
                                BigDecimal n = new BigDecimal(cell.getNumericCellValue());
                                DecimalFormat decimalFormat = new DecimalFormat("0");
                                decimalFormat.setMaximumFractionDigits(18);
                                value = decimalFormat.format(n.doubleValue());
                            }
                         break;
                        case STRING:
                            value = String.valueOf(cell.getStringCellValue());
                            if(value != null){
                                value = value.trim();
                            }
                            break;
                        case BOOLEAN:
                            value = String.valueOf(cell.getBooleanCellValue());
                            break;
                        case ERROR: value = "";
                        break;
                        default:
                            value = cell.getRichStringCellValue().getString();
                            break;
                    }
                    break;
                case NUMERIC:
                    if(DateUtil.isCellDateFormatted(cell)){
                        Date date = cell.getDateCellValue();
                        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
                        value = sdf.format(date);
                    }else{
                        BigDecimal n = new BigDecimal(cell.getNumericCellValue());
                        DecimalFormat decimalFormat = new DecimalFormat("0");
                        decimalFormat.setMaximumFractionDigits(18);
                        value = decimalFormat.format(n.doubleValue());
                    }
                    break;
                case STRING:
                    value = String.valueOf(cell.getStringCellValue());
                    if(value != null){
                        value = value.trim();
                    }
                    break;
                default:
                    value = cell.getRichStringCellValue().getString();
                    break;
            }
        }
        return value;
    }

}

所需POM

		<dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.1.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.2</version>
        </dependency>
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi-ooxml-schemas</artifactId>
              <version>4.1.2</version>
            </dependency>