Java POI excel单元格计算的三种方式

约定不等于承诺〃 2023-10-13 15:27 16阅读 0赞

#### 文章目录 ####

*  1、方式一：填写数字计算
 *  2、方式二：excel函数计算（示例SUM求和函数）
 *  3、方式三：按指定单元格计算
 *  4、求和函数升级计算：求指定行一行数据的和，结果填到最后
 *  参考链接

Maven环境

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

## 1、方式一：填写数字计算 ##

/**
     * excel单元格公式计算
     * <pre>
     *     workbook三种文件格式的导出对比
     *     https://blog.csdn.net/sufu1065/article/details/115301974
     * </pre>
     */
    public static void setCellFormulaCalc1() throws IOException {
        
        /*
         * POI关于 工作簿的三个对象
         *      1. new HSSFWorkbook(): 格式为office 2003专用格式，即.xls，优点是导出数据速度快，但是最多65536行数据，行数多了就会报错
         *      2. new XSSFWorkbook(): 导出的文件格式为office 2007专用格式，即.xlsx，优点是导出的数据不受行数限制，缺点导出速度慢
         *      3. new SXSSFWorkbook(): SXSSF 是 XSSF API的兼容流式扩展，主要解决当使用 XSSF 方式导出大数据量时，内存溢出的问题，支持导出大批量的excel数据
         */
        SXSSFWorkbook workbook = new SXSSFWorkbook();
        SXSSFSheet sheet = workbook.createSheet("excel单元格公式计算");
    
        SXSSFRow row1 = sheet.createRow(0);
    
        // 第一列
        SXSSFCell cell1 = row1.createCell(0);
        // 不推荐使用指定单元格的类型
        // cell1.setCellType(CellType.FORMULA); // 公式类型：在直接写计算方式的时候不能指定CellType.FORMULA类型，否则会报类型非法的错误
        cell1.setCellFormula("2+3*4"); // 计算公式 结果14
    
        FileOutputStream outputStream = new FileOutputStream("D:/temp/Excel单元格公式计算.xlsx");
        workbook.write(outputStream);
        outputStream.close();
        workbook.close();
    }

直接由数字计算来最终结果  
![image.png][]

## 2、方式二：excel函数计算（示例SUM求和函数） ##

/**
     * excel单元格公式计算
     * <pre>
     *     workbook三种文件格式的导出对比
     *     https://blog.csdn.net/sufu1065/article/details/115301974
     * </pre>
     */
    public static void setCellFormulaCalc1() throws IOException {
        
        /*
         * POI关于 工作簿的三个对象
         *      1. new HSSFWorkbook(): 格式为office 2003专用格式，即.xls，优点是导出数据速度快，但是最多65536行数据，行数多了就会报错
         *      2. new XSSFWorkbook(): 导出的文件格式为office 2007专用格式，即.xlsx，优点是导出的数据不受行数限制，缺点导出速度慢
         *      3. new SXSSFWorkbook(): SXSSF 是 XSSF API的兼容流式扩展，主要解决当使用 XSSF 方式导出大数据量时，内存溢出的问题，支持导出大批量的excel数据
         */
        SXSSFWorkbook workbook = new SXSSFWorkbook();
        SXSSFSheet sheet = workbook.createSheet("excel单元格公式计算");
    
        SXSSFRow row1 = sheet.createRow(0);
    
        // 第一列
        SXSSFCell cell1 = row1.createCell(0);
        // 不推荐使用指定单元格的类型
        // cell1.setCellType(CellType.FORMULA); // 公式类型：在直接写计算方式的时候不能指定CellType.FORMULA类型，否则会报类型非法的错误
        cell1.setCellFormula("2+3*4"); // 计算公式 结果14
    
        // 第二列
        SXSSFCell cell2 = row1.createCell(1);
        cell2.setCellValue(10);
    
        // 第三列
        SXSSFCell cell3 = row1.createCell(2);
    //        cell3.setCellType(CellType.FORMULA);
        // 计算A列1行+B列1行的和
        cell3.setCellFormula("SUM(A1,B1)");
    
        // 第四列
        SXSSFCell cell4 = row1.createCell(3);
    //        cell3.setCellType(CellType.FORMULA);
        // 计算A列1行+B列1行的和+C列1行的和
        cell4.setCellFormula("SUM(A1,B1,C1)");
    
        FileOutputStream outputStream = new FileOutputStream("D:/temp/Excel单元格公式计算.xlsx");
        workbook.write(outputStream);
        outputStream.close();
        workbook.close();
    }

![image.png][image.png 1]  
![image.png][image.png 2]

## 3、方式三：按指定单元格计算 ##

/**
     * 直接指定列名的计算方式
     */
    public static void calcCellByCellName() throws IOException {
        
        SXSSFWorkbook workbook = new SXSSFWorkbook();
        SXSSFSheet sheet = workbook.createSheet("excel指定列计算公式");
    
        int rowIndex = 0, rowNum = 1;
        int cellIndex1 = 0, cellIndex2 = 1, cellIndex3 = 2, cellIndex4 = 3; // 1-4列, 第四列为前三列计算结果 cellIndex1 - cellIndex2 + cellIndex3
    
        // 创建行列并设置值和计算公式
        SXSSFRow row = sheet.createRow(rowIndex);
        SXSSFCell cell1 = row.createCell(cellIndex1);
        cell1.setCellValue(10);
    
        SXSSFCell cell2 = row.createCell(cellIndex2);
        cell2.setCellValue(2);
    
        SXSSFCell cell3 = row.createCell(cellIndex3);
        cell3.setCellValue(4);
    
        SXSSFCell cell4 = row.createCell(cellIndex4);
        // ====> A1 - B1 + C1
        //cell4.setCellFormula((excelIndexToStr(cellIndex1) + rowNum) + "-" + (excelIndexToStr(cellIndex2) + rowNum) + "+" + (excelIndexToStr(cellIndex3) + rowNum));
        cell4.setCellFormula("A1-B1+C1");
    
        FileOutputStream outputStream = new FileOutputStream("D:/temp/Excel指定列计算公式.xlsx");
        workbook.write(outputStream);
        outputStream.close();
        workbook.close();
    }

![image.png][image.png 3]

## 4、求和函数升级计算：求指定行一行数据的和，结果填到最后 ##

/**
     * 单元格列尾求和
     */
    public static void calcCellEndSum() throws IOException {
        
        SXSSFWorkbook workbook = new SXSSFWorkbook();
        SXSSFSheet sheet = workbook.createSheet("excel单元格列尾求和计算");
    
        // 定义：定义列数据
        int[] dataList = {
        1, 2, 3, 4, 5, 6, 7, 8, 9, 10};
    
        // 第一行定义为列名
        SXSSFRow row1 = sheet.createRow(0);
        for (int cellIndex = 0; cellIndex <= dataList.length; cellIndex++) {
        
            SXSSFCell cell = row1.createCell(cellIndex);
            if (cellIndex == dataList.length) {
        
                cell.setCellValue("合计");
            } else {
        
                cell.setCellValue("数据" + (cellIndex + 1));
            }
        }
    
        SXSSFRow row2 = sheet.createRow(1);
        // 第二行为数据
        for (int cellIndex = 0; cellIndex < dataList.length; cellIndex++) {
        
            SXSSFCell cell = row2.createCell(cellIndex);
            cell.setCellValue(dataList[cellIndex]);
        }
        // 计算指定行的数据，结果填到最后
        lastCellSumByRow(row2);
    
    
        FileOutputStream outputStream = new FileOutputStream("D:/temp/Excel单元格列尾求和计算.xlsx");
        workbook.write(outputStream);
        outputStream.close();
        workbook.close();
    }
    
    /**
     * 指定行的末列求和: 填写计算结果
     */
    public static void lastCellSumByRow(SXSSFRow row) {
        
        short lastCellNum = row.getLastCellNum();
        // 统计一行数据的求和结果
        List<String> sumCell = new ArrayList<>();
        for (int index = 0; index < lastCellNum; index++) {
        
            String str = excelIndexToStr(index);
            // A+行号
            int rowNum = row.getRowNum() + 1;
            System.err.println(str + rowNum);
            sumCell.add(str + rowNum);
        }
    
        // 最后一列求和
        SXSSFCell lastCell = row.getCell(lastCellNum);
        if (lastCell == null) {
        
            lastCell = row.createCell(lastCellNum);
        }
        lastCell.setCellFormula("SUM(" + StringUtils.join(sumCell, ",") + ")");
    }
    
    /**
     * https://blog.csdn.net/leeo_may/article/details/123680350
     * 列索引转换为A，B，C...
     * <pre>
     *     如：cellIndex=0 --> A
     *     如：cellIndex=1 --> B
     *     如：cellIndex=25 --> Z
     *     如：cellIndex=26 --> AB
     * </pre>
     *
     * @param cellIndex Excel列索引
     */
    private static String excelIndexToStr(int cellIndex) {
        
        // 转26进制 0-25
        // int calculateValue = value - 1; (value = cellIndex + 1)
        int calculateValue = cellIndex; // 本身传进来的就是列索引0-25，的26进制
        // 取高位
        int high = calculateValue / 26;
        // 取低位
        int low = calculateValue % 26;
        // 低位可直接取出对应的字母
        String transStr = String.valueOf((char) (low + 65));
        if (high > 0) {
        
            // 高位递归取出字母
            transStr = excelIndexToStr(high) + transStr;
        }
        return transStr;
    }

![image.png][image.png 4]

## 参考链接 ##

[Java实现Excel列号转字母\_幼稚园保安的博客-CSDN博客][Java_Excel_-CSDN]  
[Java中操作Excel的3种方法，太好用了！\_sufu1065的博客-CSDN博客][Java_Excel_3_sufu1065_-CSDN]

[image.png]: https://img-blog.csdnimg.cn/img_convert/90d873ac900965b661e6545d20f5404f.png#averageHue=#e3e4d1&clientId=ucf631374-9290-4&from=paste&height=141&id=uab091797&originHeight=190&originWidth=545&originalType=binary&ratio=1.3499999046325684&rotation=0&showTitle=false&size=13953&status=done&style=none&taskId=uba726b1d-23fc-4c7c-8974-da7f06fc871&title=&width=403.70373222236157
[image.png 1]: https://img-blog.csdnimg.cn/img_convert/640e11195d8976ccb771bb40a2249ef7.png#averageHue=#e6e7d5&clientId=ucf631374-9290-4&from=paste&height=138&id=u8c949937&originHeight=178&originWidth=592&originalType=binary&ratio=1.3499999046325684&rotation=0&showTitle=false&size=16485&status=done&style=none&taskId=u26946e39-0e00-4ea7-813f-5285c1312ee&title=&width=460.5185241699219
[image.png 2]: https://img-blog.csdnimg.cn/img_convert/5ef0967a1e358a94c8a19feda06b74b4.png#averageHue=#e3e7d6&clientId=ucf631374-9290-4&from=paste&height=127&id=ufbbca869&originHeight=171&originWidth=487&originalType=binary&ratio=1.3499999046325684&rotation=0&showTitle=false&size=16052&status=done&style=none&taskId=ubc27a61c-68ed-4635-a1ed-124be7c83ac&title=&width=360.7407662243855
[image.png 3]: https://img-blog.csdnimg.cn/img_convert/e9973fda988c43acf54bfe006a37cc6a.png#averageHue=#e2e4cf&clientId=ucf631374-9290-4&from=paste&height=142&id=ufe009445&originHeight=192&originWidth=446&originalType=binary&ratio=1.3499999046325684&rotation=0&showTitle=false&size=13362&status=done&style=none&taskId=u0a5e84de-0cb1-49b3-92f0-44d6962984a&title=&width=330.3703937085748
[image.png 4]: https://img-blog.csdnimg.cn/img_convert/4c79a9ce4f0600f81025dc2e0f86c114.png#averageHue=#e1ddcb&clientId=ucf631374-9290-4&from=paste&height=140&id=u187536cb&originHeight=189&originWidth=989&originalType=binary&ratio=1.3499999046325684&rotation=0&showTitle=false&size=33319&status=done&style=none&taskId=u6a8f598b-7db7-47ca-9e45-57cbf681991&title=&width=732.5926443447992
[Java_Excel_-CSDN]: https://blog.csdn.net/leeo_may/article/details/123680350
[Java_Excel_3_sufu1065_-CSDN]: https://blog.csdn.net/sufu1065/article/details/115301974