1. Overview

1.概述

In this quick tutorial, we’re going to talk about how we can set formulas in a Microsoft Excel spreadsheet using Apache POI through a simple example.

在这个快速教程中，我们将通过一个简单的例子讲述如何在Microsoft Excel电子表格中设置公式使用Apache POI。

2. Apache POI

2.阿帕奇POI

Apache POI is a popular open-source Java library that provides programmers with APIs to create, modify, and display MS Office files.

Apache POI是一个流行的开源Java库，为程序员提供了创建、修改和显示MS Office文件的API。

It uses Workbook to represent an Excel file and its elements. A Cell in an Excel file can have different types such as FORMULA.

它使用Workbook来表示一个Excel文件及其元素。Excel文件中的Cell可以有不同的类型，如FORMULA。

In order to see Apache POI in action, we’re going to set a formula to subtract the sum of the values in columns A and B in an Excel file. The linked file contains the data below:

为了看到Apache POI的运行情况，我们将设置一个公式来减去一个Excel文件中A列和B列的数值之和。该链接文件包含以下数据。

3. Dependencies

3.依赖性

First, we need to add the POI dependency to our project pom.xml file. To work with Excel 2007+ workbooks, we should use poi-ooxml:

首先，我们需要在我们的项目pom.xml文件中添加POI依赖性。为了与Excel 2007+工作簿一起工作，我们应该使用poi-ooxml/strong>。

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

Note that for earlier versions of Excel, we should use the poi dependency instead.

注意，对于早期版本的Excel，我们应该使用poi依赖性来代替。。

4. Cell Lookup

4.单元查询

To begin, let’s first open our file and construct the appropriate workbook:

要开始，让我们首先打开我们的文件，构建相应的工作簿。

FileInputStream inputStream = new FileInputStream(new File(fileLocation));
XSSFWorkbook excel = new XSSFWorkbook(inputStream);

Then, we need to create or look up the cell we’re going be using. Using the data shared earlier, we want to edit cell C1.

然后，我们需要创建或查找我们将要使用的单元格。使用前面分享的数据，我们要编辑C1单元格。

That’s on the first sheet and the first row, and we can ask POI for the first blank column:

这是在第一张表和第一行，我们可以要求POI提供第一个空白列。

XSSFSheet sheet = excel.getSheetAt(0);
int lastCellNum = sheet.getRow(0).getLastCellNum();
XSSFCell formulaCell = sheet.getRow(0).createCell(lastCellNum + 1);

5. Formulas

5.公式

Next, we want to set a formula on the cell we’ve looked up.

接下来，我们要在我们查到的单元格上设置一个公式。

As stated earlier, let’s subtract the sum of column B from the sum of column A. In Excel, this would be:

如前所述，让我们用A列的总和减去B列的总和，在Excel中，这将是。

=SUM(A:A)-SUM(B:B)

And we can write that into our formulaCell with the setCellFormula method:

我们可以用setCellFormula方法将其写入我们的formulaCell中。

formulaCell.setCellFormula("SUM(A:A)-SUM(B:B)");

Now, this won’t evaluate the formula. To do that, we’ll need to use POI’s XSSFFormulaEvaluator:

现在，这不会评估公式。要做到这一点，我们需要使用POI的XSSFFormulaEvaluator。

XSSFFormulaEvaluator formulaEvaluator = 
  excel.getCreationHelper().createFormulaEvaluator();
formulaEvaluator.evaluateFormulaCell(formulaCell);

The result will be set in the first Cell of the next empty column:

结果将被设置在下一个空列的第一个单元格中：

As we can see, the result is calculated and saved in the first cell of column C. Also the formula is shown in the formula bar.

我们可以看到，结果被计算出来并保存在C列的第一个单元格中，公式也显示在公式栏中。

Note that the FormulaEvaluator class provides us with other methods to evaluate FORMULA in Excel workbooks, like evaluateAll, which will loop over all cells and evaluate them.

请注意，FormulaEvaluator类为我们提供了在Excel工作簿中评估FORMULA的其他方法，比如evaluateAll，它将在所有单元格中循环并评估它们。

6. Conclusion

6.结语

In this tutorial, we showed how to set a formula on a cell in an Excel file in Java using the Apache POI API.

在本教程中，我们展示了如何使用Apache POI API在Java中对Excel文件中的一个单元格设置公式。

The complete source code for this article is available over on GitHub.

本文的完整源代码可在GitHub上获取。