以下经验内容将分享通过Java程序在Excel表格中创建公式的方法。
工具/原料
Free Spire.XLS for Java （免费版）
IntelliJ IDEA
jar文件获取及导入：
1、可在e-iceblue中文官网下载包，解压后，将lib文件夹中的门钙蹲茌jar文件导入Java程序；或者通过maven仓库下载导入jar，maven仓库路径配置及依赖添加可以参考官网教程（https://www.e-iceblue.cn/licensing/install-spirepdf-for-java-from-maven-repository.html）。导入效果如下图：
Java代码示例
1、import com.spire.xls.*; public class AddFormula { public static void main(String[] args) { //创建Workbook对象 Workbook wb = new Workbook(); //获取第一个工作表 Worksheet sheet = wb.getWorksheets().get(0); //声明两个变量 int currentRow = 1; String currentFormula = null; //设置列宽 sheet.setColumnWidth(1, 32); sheet.setColumnWidth(2, 16); //写入用于测试的数据到单元格 sheet.getCellRange(currentRow,1).setValue("测试数据："); sheet.getCellRange(currentRow,2).setNumberValue(1); sheet.getCellRange(currentRow,3).setNumberValue(2); sheet.getCellRange(currentRow,4).setNumberValue(3); sheet.getCellRange(currentRow,5).setNumberValue(4); sheet.getCellRange(currentRow,6).setNumberValue(5); //写入文本 currentRow += 2; sheet.getCellRange(currentRow,1).setValue("公式：") ; ; sheet.getCellRange(currentRow,2).setValue("结果："); //设置单元格格式 CellRange range = sheet.getCellRange(currentRow,1,currentRow,2); range.getStyle().getFont().isBold(true); range.getStyle().setKnownColor(ExcelColors.LightGreen1); range.getStyle().setFillPattern(ExcelPatternType.Solid); range.getStyle().getBorders().getByBordersLineType(BordersLineType.EdgeBottom).setLineStyle(LineStyleType.Medium); //算数运算 currentFormula = "=1/2+3*4"; sheet.getCellRange(++currentRow,1).setText(currentFormula); sheet.getCellRange(currentRow,2).setFormula(currentFormula); //日期函数 currentFormula = "=TODAY()"; sheet.getCellRange(++currentRow,1).setText(currentFormula); sheet.getCellRange(currentRow,2).setFormula(currentFormula); sheet.getCellRange(currentRow,2).getStyle().setNumberFormat("YYYY/MM/DD"); //时间函数 currentFormula = "=NOW()"; sheet.getCellRange(++currentRow,1).setText(currentFormula); sheet.getCellRange(currentRow,2).setFormula(currentFormula); sheet.getCellRange(currentRow,2).getStyle().setNumberFormat("H:MM AM/PM"); //IF函数 currentFormula = "=IF(B1=5,\"Yes\",\"No\")"; sheet.getCellRange(++currentRow,1).setText(currentFormula); sheet.getCellRange(currentRow,2).setFormula(currentFormula); //PI函数 currentFormula = "=PI()"; sheet.getCellRange(++currentRow,1).setText(currentFormula); sheet.getCellRange(currentRow,2).setFormula(currentFormula); //三角函数 currentFormula = "=SIN(PI()/6)"; sheet.getCellRange(++currentRow,1).setText(currentFormula); sheet.getCellRange(currentRow,2).setFormula(currentFormula); //计数函数 currentFormula = "=Count(B1:F1)"; sheet.getCellRange(++currentRow,1).setText(currentFormula); sheet.getCellRange(currentRow,2).setFormula(currentFormula); //最大值函数 currentFormula = "=MAX(B1:F1)"; sheet.getCellRange(++currentRow,1).setText(currentFormula); sheet.getCellRange(currentRow,2).setFormula(currentFormula); //平均值函数 currentFormula = "=AVERAGE(B1:F1)"; sheet.getCellRange(++currentRow,1).setText(currentFormula); sheet.getCellRange(currentRow,2).setFormula(currentFormula); //求和函数 currentFormula = "=SUM(B1:F1)"; sheet.getCellRange(++currentRow,1).setText(currentFormula); sheet.getCellRange(currentRow,2).setFormula(currentFormula); //保存文档 wb.saveToFile("AddFormulas.xlsx",FileFormat.Version2013); wb.dispose(); } }
2、公式创建结果：
