java实现在excel中创建及读取公式

互联网 20-1-10

操作excel表格用公式来处理数据时，可通过创建公式来运算数据，或通过读取公式来获取数据信息来源。这里使用了java类库（Free Spire.XLS for Java 免费版）获取文件包后，解压，将lib文件夹下的jar文件导入Java程序。

（免费视频教程分享：java视频教程）

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、读取公式

import com.spire.xls.*;  public class ReadFormula {     public static void main(String[] args) {         //加载Excel文档         Workbook wb = new Workbook();         wb.loadFromFile("AddFormulas.xlsx");          //获取第一个工作表         Worksheet sheet = wb.getWorksheets().get(0);          //遍历B1到B13的单元格         for (Object cell: sheet.getCellRange("B1:B13"))         {             CellRange cellRange = (CellRange)cell;              //判断单元格是否含有公式             if (cellRange.hasFormula())             {                 //打印单元格及公式                 String certainCell = String.format("单元格[%d, %d]含有公式：", cellRange.getRow(), cellRange.getColumn());                 System.out.println(certainCell + cellRange.getFormula());             }         }     } }