鍍金池/ 教程/ Linux/ Apache POI公式
Apache POI電子表格/Spreadsheet
Apache POI公式
Apache POI打印區(qū)域
Apache POI - Java Excel APIs
Apache POI數(shù)據(jù)庫(kù)
Apache POI單元格/Cells
Apache POI環(huán)境設(shè)置
Apache POI字體/Fonts
Apache POI超鏈接
POI教程
POI核心類
Apache POI工作簿

Apache POI公式

本章將介紹如何使用Java編程應(yīng)用不同單元公式的過(guò)程。 Excel應(yīng)用程序的基本目的是通過(guò)應(yīng)用公式就可以保持?jǐn)?shù)值數(shù)據(jù)。

在公式中，我們通過(guò)動(dòng)態(tài)值，或在Excel工作表中的值的位置。在執(zhí)行這個(gè)公式，就會(huì)得到想要的結(jié)果。下表列出了常用的在Excel中的幾個(gè)基本公式。

操作 語(yǔ)法
添加多個(gè)數(shù)值 = SUM(Loc1:Locn) or = SUM(n1,n2,)
計(jì)數(shù) = COUNT(Loc1:Locn) or = COUNT(n1,n2,)
兩個(gè)數(shù)的冪 = POWER(Loc1,Loc2) or = POWER(number, power)
多個(gè)數(shù)的最大值 = MAX(Loc1:Locn) or = MAX(n1,n2,)
乘積 = PRODUCT(Loc1:Locn) or = PRODUCT(n1,n2,)
階乘 = FACT(Locn) or = FACT(number)
絕對(duì)數(shù)字 = ABS(Locn) or = ABS(number)
今天的日期 =TODAY()
轉(zhuǎn)換成小寫 = LOWER(Locn) or = LOWER(text)
平方根 = SQRT(locn) or = SQRT(number)

以下代碼用于公式添加至單元格，并執(zhí)行它。

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.setCellType(XSSFCell.CELL_TYPE_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.setCellType(XSSFCell.CELL_TYPE_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.setCellType(XSSFCell.CELL_TYPE_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.setCellType(XSSFCell.CELL_TYPE_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.setCellType(XSSFCell.CELL_TYPE_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，然后編譯并從命令提示符如下執(zhí)行它。

$javac Formula.java
$java Formula

它會(huì)生成一個(gè)名為formula.xlsx在當(dāng)前目錄中的Excel文件并顯示在命令提示符處鍵入以下輸出。

fromula.xlsx written successfully

formula.xlsx文件如下所示。

Formula