本章将指导您使用Java编程在单元格上应用不同的公式. Excel应用程序的基本目的是通过在其上应用公式来维护数值数据.
在公式中，我们在Excel工作表中传递值的动态值或位置.执行此公式时，您将获得所需的结果.下表列出了Excel中经常使用的一些基本公式.
| 操作 | 语法 |
|---|---|
| 添加多个数字 | = SUM(Loc1:Locn)或 = SUM(n1，n2，) |
| 计数 | = COUNT(Loc1:Locn)或 = COUNT( n1，n2，) |
| 两个数字的幂 | = POWER(Loc1 ，Loc2)或 = POWER(数字，功率) |
| 多个数字的最大值 | = MAX(Loc1:Locn)或 = MAX(n1，n2，) |
| 产品 | = PRODUCT(Loc1:Locn)或 = PRODUCT(n1，n2，) |
| 因子 | = FACT(Locn)或 = FACT(数字) |
| 绝对数字 | = ABS(Locn)或 = ABS(数字) |
| 今天日期 | = TODAY() |
| 转换小写 | = LOWER(Locn)或 = LOWER(文本) |
| 平方根 | = SQRT(locn)或 = SQRT(数字) |
以下代码用于向单元格添加公式并执行它.
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 ，然后从中编译并执行命令提示符如下.
$javac Formula.java$java Formula
它将在当前目录中生成名为 formula.xlsx 的Excel文件，并在命令提示符下显示以下输出.
fromula.xlsx written successfully
formula.xlsx 文件如下所示.
