Apache POI - 公式

本章將引導您完成使用 Java 程式設計在單元格上應用不同公式的過程。Excel 應用程式的基本目的是透過在其上應用公式來維護數值資料。

在公式中，我們傳遞 Excel 表格中值的動態值或位置。執行此公式後，您將獲得所需的結果。下表列出了 Excel 中經常使用的一些基本公式。

運算	語法
新增多個數字	= SUM(Loc1:Locn) 或 = SUM(n1,n2,)
計數	= COUNT(Loc1:Locn) 或 = COUNT(n1,n2,)
兩個數字的冪	= POWER(Loc1,Loc2) 或 = POWER(number, power)
多個數字中的最大值	= MAX(Loc1:Locn) 或 = MAX(n1,n2,)
乘積	= PRODUCT(Loc1:Locn) 或 = PRODUCT(n1,n2,)
階乘	= FACT(Locn) 或 = FACT(number)
絕對值	= ABS(Locn) 或 = ABS(number)
今日日期	=TODAY()
轉換為小寫	= LOWER(Locn) 或 = LOWER(text)
平方根	= SQRT(locn) 或 = SQRT(number)

以下程式碼用於向單元格新增公式並執行它。

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，然後從命令提示符編譯並執行它，如下所示：

$javac Formula.java
$java Formula

它將在您的當前目錄中生成一個名為formula.xlsx的 Excel 檔案，並在命令提示符上顯示以下輸出。

fromula.xlsx written successfully

formula.xlsx檔案如下所示：

列印頁面