Java 在 Excel 工作表中插入或读取公式和函数

除了记录数据的功能外，计算功能也是 Excel 的一个重要功能。计算功能可以帮助用户简单高效地分析和处理工作表中的数据。Excel 中有公式和函数两种计算工具。其中，公式是用户自定义的计算语句，而函数则是预定义的公式。用户可以在单元格中使用常数、运算符、引用值、函数来自定义公式进行计算，或直接引用函数进行计算。本文将介绍如何使用 Spire.XLS for Java 通过程序在 Excel 工作簿中插入或读取公式和函数。

插入公式和函数到 Excel 工作表
读取 Excel 工作表中的公式和函数

安装 Spire.XLS for Java

首先，您需要在 Java 程序中添加 Spire.Xls.jar 文件作为依赖项。您可以从此链接下载 JAR 文件；如果您使用 Maven，则可以通过在 pom.xml 文件中添加以下代码导入 JAR 文件。

<repositories>
    <repository>
        <id>com.e-iceblue</id>
        <name>e-iceblue</name>
        <url>https://repo.e-iceblue.cn/repository/maven-public/</url>
    </repository>
</repositories>
<dependencies>
    <dependency>
        <groupId>e-iceblue</groupId>
        <artifactId>spire.xls</artifactId>
        <version>14.11.0</version>
    </dependency>
</dependencies>

插入公式和函数到 Excel 工作表

Spire.XLS for Java 提供的 Worksheet.getCellRange().setFormula() 方法可在特定的单元格中添加公式或函数。详细步骤如下：

创建一个 Workbook 的对象。
使用 Workbook.getWorksheets().get() 方法获取第一个工作表。
在单元格中写入数据并格式化单元格。
使用 Worksheet.getCellRange().setFormula() 方法向特定的单元格添加公式或函数。
使用 Workbook.saveToFile() 方法保存工作簿。

Java

import com.spire.xls.*;

public class insertFormulas {
    public static void main(String[] args) {

        //创建一个Workbook的对象
        Workbook workbook = new Workbook();

        //获取第一个工作表
        Worksheet sheet = workbook.getWorksheets().get(0);

        //创建currentRow和currentFormula两个变量
        int currentRow = 1;
        String currentFormula = null;

        //设置列宽
        sheet.setColumnWidth(1, 32);
        sheet.setColumnWidth(2, 16);

        //写入数据到单元格
        sheet.getCellRange(currentRow,1).setValue("测试数据：");
        sheet.getCellRange(currentRow,2).setNumberValue(1);
        sheet.getCellRange(currentRow,3).setNumberValue(2);
        sheet.getCellRange(currentRow,4).setNumberValue(3);
        sheet.getCellRange(currentRow,5).setNumberValue(4);
        sheet.getCellRange(currentRow,6).setNumberValue(5);

        //写入文本到单元格
        currentRow += 2;
        sheet.getCellRange(currentRow,1).setValue("公式、函数：") ; ;
        sheet.getCellRange(currentRow,2).setValue("运算结果：");

        //设置单元格格式
        CellRange range = sheet.getCellRange(currentRow,1,currentRow,2);
        range.getStyle().getFont().isBold(true);
        range.getStyle().setKnownColor(ExcelColors.LightGreen1);
        range.getStyle().setFillPattern(ExcelPatternType.Solid);
        range.getStyle().getBorders().getByBordersLineType(BordersLineType.EdgeBottom).setLineStyle(LineStyleType.Medium);

        //算术运算
        currentFormula = "=1/2+3*4";
        sheet.getCellRange(++currentRow,1).setText("'"+ currentFormula);
        sheet.getCellRange(currentRow,2).setFormula(currentFormula);

        //日期函数
        currentFormula = "=TODAY()";
        sheet.getCellRange(++currentRow,1).setText("'"+currentFormula);
        sheet.getCellRange(currentRow,2).setFormula(currentFormula);
        sheet.getCellRange(currentRow,2).getStyle().setNumberFormat("YYYY/MM/DD");

        //时间函数
        currentFormula = "=NOW()";
        sheet.getCellRange(++currentRow,1).setText("'"+currentFormula);
        sheet.getCellRange(currentRow,2).setFormula(currentFormula);
        sheet.getCellRange(currentRow,2).getStyle().setNumberFormat("H:MM AM/PM");

        //IF函数
        currentFormula = "=IF(B1=5,\"Yes\",\"No\")";
        sheet.getCellRange(++currentRow,1).setText("'"+currentFormula);
        sheet.getCellRange(currentRow,2).setFormula(currentFormula);

        //PI函数
        currentFormula = "=PI()";
        sheet.getCellRange(++currentRow,1).setText("'"+currentFormula);
        sheet.getCellRange(currentRow,2).setFormula(currentFormula);

        //三角函数
        currentFormula = "=SIN(PI()/6)";
        sheet.getCellRange(++currentRow,1).setText("'"+currentFormula);
        sheet.getCellRange(currentRow,2).setFormula(currentFormula);

        //计数函数
        currentFormula = "=Count(B1:F1)";
        sheet.getCellRange(++currentRow,1).setText("'"+currentFormula);
        sheet.getCellRange(currentRow,2).setFormula(currentFormula);

        //最大值函数
        currentFormula = "=MAX(B1:F1)";
        sheet.getCellRange(++currentRow,1).setText("'"+currentFormula);
        sheet.getCellRange(currentRow,2).setFormula(currentFormula);

        //平均值函数
        currentFormula = "=AVERAGE(B1:F1)";
        sheet.getCellRange(++currentRow,1).setText("'"+currentFormula);
        sheet.getCellRange(currentRow,2).setFormula(currentFormula);

        //求和函数
        currentFormula = "=SUM(B1:F1)";
        sheet.getCellRange(++currentRow,1).setText("'"+currentFormula);
        sheet.getCellRange(currentRow,2).setFormula(currentFormula);

        //保存工作簿
        workbook.saveToFile("插入公式和函数.xlsx",FileFormat.Version2013);

    }
}

Java 在 Excel 工作表中插入或读取公式和函数

读取 Excel 工作表中的公式和函数

读取 Excel 工作表中的公式时，我们需要先用 CellRange.hasFormula() 方法来检测单元格是否包含公式。如果有，再用 CellRange.getFormula() 方法来获取该公式。详细操作步骤如下：

创建一个 Workbook 的对象。
使用 Workbook.loadFromFile() 方法载入 Excel 工作簿。
使用 Workbook.getWorksheets().get() 方法获取第一个工作表。
循环遍历工作表中的单元格。
使用 CellRange.hasFormula() 方法检测单元格是否包含公式。如果有，则使用 CellRange.getFormula() 方法获取该公式并输出。
使用 Workbook.saveToFile() 方法保存工作簿。

Java

import com.spire.xls.CellRange;
import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;

public class readFormulas {

    public static void main(String[] args) {

        //创建一个Workbook的对象
        Workbook workbook = new Workbook();

        //载入Excel工作簿
        workbook.loadFromFile("插入公式和函数.xlsx");

        //获取第一个工作表
        Worksheet sheet = workbook.getWorksheets().get(0);

        //循环B1:B13内的单元格
        for (Object cell: sheet.getCellRange("B1:B13")
        ) {
            CellRange cellRange = (CellRange)cell;

            //判断单元格中是否包含公式
            if (cellRange.hasFormula()){

                //输出公式
                String certainCell = String.format("单元格[%d, %d]包含公式，",cellRange.getRow(),cellRange.getColumn());
                System.out.println(certainCell + cellRange.getFormula());
            }
        }
    }
}

Java 在 Excel 工作表中插入或读取公式和函数

申请临时 License

如果您希望删除结果文档中的评估消息，或者摆脱功能限制，请该Email地址已收到反垃圾邮件插件保护。要显示它您需要在浏览器中启用JavaScript。获取有效期 30 天的临时许可证。