java excel添加公式_Java添加、读取Excel公式

import com.spire.xls.*;public classAddFormula {public static voidmain(String[] args) {//创建Workbook对象

Workbook wb = newWorkbook();//获取第一个工作表

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();

}

}