Java使用POI获取Excel公式并计算公式得到值-20220530

参考代码 新版

    @Test
    public void testEvaluator() throws Exception{
        String path = "D:\\测试获取公式.xlsx";
        FileInputStream fileInputStream = new FileInputStream(path);
        //获取工作簿，这里使用的07版
        Workbook workbook = new XSSFWorkbook(fileInputStream);
        //获取工作表
        Sheet sheet = workbook.getSheetAt(0);
        //获取公式所在单元格
        Row row = sheet.getRow(3);
        Cell cell = row.getCell(0);
        //后面使用它来执行计算公式
        FormulaEvaluator formulaEvaluator = new XSSFFormulaEvaluator((XSSFWorkbook) workbook);
        //获取单元格内容的类型
        CellType cellType = cell.getCellType();
        //判断是否存储的为公式，此处本可以不加判断
        if (cellType.equals(CellType.FORMULA)){
            //获取公式，可以理解为已String类型获取cell的值输出
            String cellFormula = cell.getCellFormula();
            System.out.println(cellFormula);
            //执行公式，此处cell的值就是公式
            CellValue evaluate = formulaEvaluator.evaluate(cell);
            System.out.println(evaluate.formatAsString());
        }
    }

参考文章

https://blog.csdn.net/ITarmi/article/details/123775069

本地测试

package com.dxy.demo_05_08.study.excelread;


import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.CellValue;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.xssf.usermodel.*;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileWriter;
import java.io.InputStream;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.util.Date;

public class ReadExcel {
    public static void main(String[] args) throws Exception {
        long t1 = new Date().getTime();
        excel();
        long t2 = new Date().getTime();

        System.out.println((t2 - t1) / 1000 + "秒");
    }

    public static void excel() throws Exception {
        //
        FileWriter fw = new FileWriter("E:\\other\\text1.txt");
        File file = new File("E:\\other\\test1.xlsx");
        if (!file.exists()) {
            throw new Exception("文件不存在!");
        }
        InputStream in = new FileInputStream(file);

        // 读取整个Excel
        XSSFWorkbook sheets = new XSSFWorkbook(in);
        // 获取第一个表单Sheet
        XSSFSheet sheetAt = sheets.getSheetAt(0);
        sheetAt.setForceFormulaRecalculation(true);
        //默认第一行为标题行，i = 0
        XSSFRow titleRow = sheetAt.getRow(0);

        //后面使用它来执行计算公式 核心代码
        FormulaEvaluator formulaEvaluator = new XSSFFormulaEvaluator((XSSFWorkbook) sheets);

        // 循环获取每一行数据
        for (int i = 0; i < sheetAt.getPhysicalNumberOfRows(); i++) {
            XSSFRow row = sheetAt.getRow(i);
            String var = null;
            // 读取每一格内容
            StringBuilder sb = new StringBuilder();
            for (int index = 0; index < row.getPhysicalNumberOfCells(); index++) {
                XSSFCell titleCell = titleRow.getCell(index);
                XSSFCell cell = row.getCell(index);


                //数字类型
              /*  if (cell.getCellType().equals(CellType.NUMERIC)) {
                    cell.setCellType(CellType.NUMERIC);
                   System.out.println("数字：" + cell);
                    var = String.valueOf(cell);
                }*/
                //公式类型
                if (cell.getCellType().equals(CellType.FORMULA)) {
                    /*cell.setCellType(CellType.STRING);
                    BigDecimal bigDecimal = new BigDecimal(String.valueOf(cell));
                    DecimalFormat decimalFormat = new DecimalFormat("0.0000#");
                    String format = decimalFormat.format(bigDecimal);
                    System.out.println("公式：" + bigDecimal);
                    var = format;*/
                   // System.out.println(cell.getCellFormula());
                    //获取公式，可以理解为已String类型获取cell的值输出
                    String cellFormula = cell.getCellFormula();
                    System.out.println(cellFormula);
                    //执行公式，此处cell的值就是公式  核心代码
                    CellValue evaluate = formulaEvaluator.evaluate(cell);
                    System.out.println(evaluate.formatAsString());
                }

             /*   if(cell.getCellType().equals(CellType.STRING)){
                    cell.setCellType(CellType.STRING);
                    var = String.valueOf(cell);
                }*/

                cell.setCellType(CellType.STRING);
                if (cell.getStringCellValue().equals("")) {
                    continue;
                }
                //sb.append(var + ",");
                sb.append(cell + ",");

            }
            System.out.println(i + "\t" + sb);
            //
            fw.write(String.valueOf(sb + "\n"));
        }
        fw.close();
    }
}

旧版

switch (thecell.getCellType()) {
    // 读取到的是公式
    case Cell.CELL_TYPE_FORMULA: 
    String valueStr = "";
    try {
        valueStr = String.valueOf(thecell.getNumericCellValue());    
    } catch (IllegalStateException e) {
        valueStr = String.valueOf(thecell.getRichStringCellValue());
    }
    double valueFN = new Double(valueStr));
    importValue.add(valueFN);
    break;
}

https://www.csdn.net/tags/MtTaAg2sNjcyNzIwLWJsb2cO0O0O.html