Apache Poi 简明教程

Apache POI - Formula

本章将引导您了解使用 Java 编程在单元格上应用不同的公式。Excel 应用程序的基本目的是通过在其上应用公式来维护数字数据。

This chapter takes you through the process of applying different formulas on cells using Java programming. The basic purpose of Excel application is to maintain numerical data by applying formulas on it.

在公式中，我们传递 Excel 表中值动态值或位置。在执行此公式后，您将获得所需的结果。下表列出了 Excel 中经常使用的一些基本公式。

In a formula, we pass dynamic values or locations of the values in the Excel sheet. On executing this formula, you get the desired result. The following table lists a few basic formulas that are frequently used in Excel.

Operation

Syntax

Adding multiple numbers

= SUM(Loc1:Locn) or = SUM(n1,n2,)

Count

= COUNT(Loc1:Locn) or = COUNT(n1,n2,)

Power of two numbers

= POWER(Loc1,Loc2) or = POWER(number, power)

Max of multiple numbers

= MAX(Loc1:Locn) or = MAX(n1,n2,)

Product

= PRODUCT(Loc1:Locn) or = PRODUCT(n1,n2,)

Factorial

= FACT(Locn) or = FACT(number)

Absolute number

= ABS(Locn) or = ABS(number)

Today date

=TODAY()

Converts lowercase

= LOWER(Locn) or = LOWER(text)

Square root

= SQRT(locn) or = SQRT(number)

以下代码用来给单元格添加公式并执行它。

The following code is used to add formulas to a cell and execute it.

import java.io.File;
import java.io.FileOutputStream;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class Formula {
   public static void main(String[] args)throws Exception {
      XSSFWorkbook workbook = new XSSFWorkbook();
      XSSFSheet spreadsheet = workbook.createSheet("formula");
      XSSFRow row = spreadsheet.createRow(1);
      XSSFCell cell = row.createCell(1);

      cell.setCellValue("A = ");
      cell = row.createCell(2);
      cell.setCellValue(2);
      row = spreadsheet.createRow(2);
      cell = row.createCell(1);
      cell.setCellValue("B = ");
      cell = row.createCell(2);
      cell.setCellValue(4);
      row = spreadsheet.createRow(3);
      cell = row.createCell(1);
      cell.setCellValue("Total = ");
      cell = row.createCell(2);

      // Create SUM formula
      cell.setCellFormula("SUM(C2:C3)");
      cell = row.createCell(3);
      cell.setCellValue("SUM(C2:C3)");
      row = spreadsheet.createRow(4);
      cell = row.createCell(1);
      cell.setCellValue("POWER =");
      cell=row.createCell(2);

      // Create POWER formula
      cell.setCellFormula("POWER(C2,C3)");
      cell = row.createCell(3);
      cell.setCellValue("POWER(C2,C3)");
      row = spreadsheet.createRow(5);
      cell = row.createCell(1);
      cell.setCellValue("MAX = ");
      cell = row.createCell(2);

      // Create MAX formula
      cell.setCellFormula("MAX(C2,C3)");
      cell = row.createCell(3);
      cell.setCellValue("MAX(C2,C3)");
      row = spreadsheet.createRow(6);
      cell = row.createCell(1);
      cell.setCellValue("FACT = ");
      cell = row.createCell(2);

      // Create FACT formula
      cell.setCellFormula("FACT(C3)");
      cell = row.createCell(3);
      cell.setCellValue("FACT(C3)");
      row = spreadsheet.createRow(7);
      cell = row.createCell(1);
      cell.setCellValue("SQRT = ");
      cell = row.createCell(2);

      // Create SQRT formula
      cell.setCellFormula("SQRT(C5)");
      cell = row.createCell(3);
      cell.setCellValue("SQRT(C5)");
      workbook.getCreationHelper().createFormulaEvaluator().evaluateAll();
      FileOutputStream out = new FileOutputStream(new File("formula.xlsx"));
      workbook.write(out);
      out.close();
      System.out.println("fromula.xlsx written successfully");
   }
}

将以上代码保存在 Formula.java 中，然后从命令行处编译并执行它，如下所示 −

Save the above code as Formula.java and then compile and execute it from the command prompt as follows −

$javac Formula.java
$java Formula

它将在你的当前目录中生成名为 formula.xlsx 的 Excel 文件，并在命令行中显示以下输出。

It will generate an Excel file named formula.xlsx in your current directory and display the following output on the command prompt.

fromula.xlsx written successfully

formula.xlsx 文件如下： −

The formula.xlsx file looks as follows −

formula