阅读:1069回复:0
Apache POI公式
本章将介绍如何使用Java编程应用不同单元公式的过程。 Excel应用程序的基本目的是通过应用公式就可以保持数值数据。
在公式中,我们通过动态值,或在Excel工作表中的值的位置。在执行这个公式,就会得到想要的结果。下表列出了常用的在Excel中的几个基本公式。 [table=100%,top][tr][td]操作[/td][td]语法[/td][/tr][tr][td]添加多个数值[/td][td]= SUM(Loc1:Locn) or = SUM(n1,n2,)[/td][/tr][tr][td]计数[/td][td]= COUNT(Loc1:Locn) or = COUNT(n1,n2,)[/td][/tr][tr][td]两个数的幂[/td][td]= POWER(Loc1,Loc2) or = POWER(number, power)[/td][/tr][tr][td]多个数的最大值[/td][td]= MAX(Loc1:Locn) or = MAX(n1,n2,)[/td][/tr][tr][td]乘积[/td][td]= PRODUCT(Loc1:Locn) or = PRODUCT(n1,n2,)[/td][/tr][tr][td]阶乘[/td][td]= FACT(Locn) or = FACT(number)[/td][/tr][tr][td]绝对数字[/td][td]= ABS(Locn) or = ABS(number)[/td][/tr][tr][td]今天的日期[/td][td]=TODAY()[/td][/tr][tr][td]转换成小写[/td][td]= LOWER(Locn) or = LOWER(text)[/td][/tr][tr][td]平方根[/td][td]= SQRT(locn) or = SQRT(number)[/td][/tr][/table]以下代码用于公式添加至单元格,并执行它。 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文件如下所示。 图片:1-1412020U3545B.jpg ![]()
|
|