Java implements creating and reading formulas in excel

When operating excel tables and using formulas to process data, you can create formulas to calculate the data, or read formulas to obtain the source of data information. The java class library (Free Spire.XLS for Java free version) is used here to obtain the file package, decompress it, and import the jar file in the lib folder into the Java program.

Pictured:

(Free video tutorial sharing: java video tutorial)

1 , Create formula

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

Formula creation result:

2. Read formula

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

Formula reading result:

Recommended related articles and tutorials: javaQuick Start

The above is the detailed content of Java implements creating and reading formulas in excel. For more information, please follow other related articles on the PHP Chinese website!