java 添加、读取 excel 公式-凯发线上登陆下载网址

本文介绍如何使用spire.xls for 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, 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);
        //保存文档
        workbook.savetofile("insertformulas.xlsx",fileformat.version2013);
    }
}

读取公式

import com.spire.xls.*;
public class readformulas {
    public static void main(string[] args) {
        //创建workbook对象
        workbook workbook = new workbook();
        //加载excel文档
        workbook.loadfromfile("c:\\users\\administrator\\desktop\\formulas.xlsx");
        //获取第一个工作表
        worksheet sheet = workbook.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());
            }
        }
    }