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

import com.spire.xls.*;

public class InsertFormulas {

public static void main(String[] args) {

//创建Workbook对象 Workbook workbook = new Workbook();

//获取第一个工作表 Worksheet sheet = workbook.getWorksheets().get(0);

//声明两个变量 int currentRow = 1;

String currentFormula = null;

//设置列宽 sheet.setColumnWidth(1, 26);

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

//保存文档 workbook.saveToFile("InsertFormulas.xlsx",FileFormat.Version2013);

}

}