公式定义为执行计算并以单元格形式返回结果值的方程式。 它包含几个函数，单元格引用，函数运算符。 根据用户需求，公式可以简单也可以复杂。 可以将函数定义为Excel中的内置操作，该操作在单元格中返回结果值。 函数仅包含单元格引用和值。

在本文中，我将向您展示如何使用Free Spire.XLS for Java在Excel单元格中应用公式和函数。

Add Spire.Xls.jar as dependency

Method 1: Download Free Spire.XLS for Java pack, unzip it and you’ll get Spire.Doc.jar file from the “lib” folder. Import the jar file in your project as a dependency.

方法2：如果要创建Maven项目，则可以通过将以下配置添加到pom.xml来轻松添加jar依赖项。 

<repositories>
        <repository>
            <id>com.e-iceblue</id>
            <name>e-iceblue</name>
            <url>http://repo.e-iceblue/nexus/content/groups/public/</url>
        </repository>
</repositories>
<dependencies>
    <dependency>
        <groupId> e-iceblue </groupId>
        <artifactId>spire.xls.free</artifactId>
        <version>2.2.0</version>
    </dependency>
</dependencies>

Using the code 

import com.spire.xls.*;

public class InsertFormulas {

    public static void main(String[] args) {

        //Create a Workbook object
        Workbook workbook = new Workbook();

        //Get the first worksheet
        Worksheet sheet = workbook.getWorksheets().get(0);

        //Declare two variables: currentRow、currentFormula
        int currentRow = 1;
        String currentFormula = null;

        //Set the column width
        sheet.setColumnWidth(1, 32);
        sheet.setColumnWidth(2, 16);

        //Write test data into cells
        sheet.getCellRange(currentRow,1).setValue("Test data:");
        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);

        //Write text in cells
        currentRow += 2;
        sheet.getCellRange(currentRow,1).setValue("Formulas or functions:") ; ;
        sheet.getCellRange(currentRow,2).setValue("result:");

        //Format cells
        CellRange range = sheet.getCellRange(currentRow,1,currentRow,2);
        range.getStyle().getFont().isBold(true);
        range.getStyle().setKnownColor(ExcelColors.BlueGray);
        range.getStyle().setFillPattern(ExcelPatternType.Solid);
        range.getStyle().getBorders().getByBordersLineType(BordersLineType.EdgeBottom).setLineStyle(LineStyleType.Medium);

        //Formula
        currentFormula = "=1/2+3*4";
        sheet.getCellRange(++currentRow,1).setText(currentFormula);
        sheet.getCellRange(currentRow,2).setFormula(currentFormula);

        //IF function
        currentFormula = "=IF(B1=5,\"Yes\",\"No\")";
        sheet.getCellRange(++currentRow,1).setText(currentFormula);
        sheet.getCellRange(currentRow,2).setFormula(currentFormula);

        //Count function
        currentFormula = "=Count(B1:F1)";
        sheet.getCellRange(++currentRow,1).setText(currentFormula);
        sheet.getCellRange(currentRow,2).setFormula(currentFormula);

        //MAX function
        currentFormula = "=MAX(B1:F1)";
        sheet.getCellRange(++currentRow,1).setText(currentFormula);
        sheet.getCellRange(currentRow,2).setFormula(currentFormula);

        //AVERAGE function
        currentFormula = "=AVERAGE(B1:F1)";
        sheet.getCellRange(++currentRow,1).setText(currentFormula);
        sheet.getCellRange(currentRow,2).setFormula(currentFormula);

        //SUM function
        currentFormula = "=SUM(B1:F1)";
        sheet.getCellRange(++currentRow,1).setText(currentFormula);
        sheet.getCellRange(currentRow,2).setFormula(currentFormula);

        //NOW function
        currentFormula = "=NOW()";
        sheet.getCellRange(++currentRow,1).setText(currentFormula);
        sheet.getCellRange(currentRow,2).setFormula(currentFormula);
        sheet.getCellRange(currentRow,2).getStyle().setNumberFormat("H:MM AM/PM");

        //SECOND function
        currentFormula = "=SECOND(11)";
        sheet.getCellRange(++currentRow, 1).setText(currentFormula);
        sheet.getCellRange(currentRow++, 2).setFormula(currentFormula);

        //MINUTE function
        currentFormula = "=MINUTE(12)";
        sheet.getCellRange(currentRow, 1).setText(currentFormula);
        sheet.getCellRange(currentRow++, 2).setFormula(currentFormula);

        //MONTH function
        currentFormula = "=MONTH(9)";
        sheet.getCellRange(currentRow, 1).setText(currentFormula);
        sheet.getCellRange(currentRow++, 2).setFormula(currentFormula);

        //DAY function
        currentFormula = "=DAY(10)";
        sheet.getCellRange(currentRow, 1).setText(currentFormula);
        sheet.getCellRange(currentRow++, 2).setFormula(currentFormula);

        //TIME function
        currentFormula = "=TIME(4,5,7)";
        sheet.getCellRange(currentRow, 1).setText(currentFormula);
        sheet.getCellRange(currentRow++, 2).setFormula(currentFormula);

        //DATE function
        currentFormula = "=DATE(6,4,2)";
        sheet.getCellRange(currentRow, 1).setText(currentFormula);
        sheet.getCellRange(currentRow++, 2).setFormula(currentFormula);

        //TODAY function
        currentFormula = "=TODAY()";
        sheet.getCellRange(currentRow,1).setText(currentFormula);
        sheet.getCellRange(currentRow++,2).getStyle().setNumberFormat("YYYY/MM/DD");

        //RAND function
        currentFormula = "=RAND()";
        sheet.getCellRange(currentRow, 1).setText(currentFormula);
        sheet.getCellRange(currentRow++, 2).setFormula(currentFormula);

        //HOUR function
        currentFormula = "=HOUR(12)";
        sheet.getCellRange(currentRow, 1).setText(currentFormula);
        sheet.getCellRange(currentRow++, 2).setFormula(currentFormula);

        //MOD function
        currentFormula = "=MOD(5,3)";
        sheet.getCellRange(currentRow, 1).setText(currentFormula);
        sheet.getCellRange(currentRow++, 2).setFormula(currentFormula);

        //WEEKDAY function
        currentFormula = "=WEEKDAY(3)";
        sheet.getCellRange(currentRow, 1).setText(currentFormula);
        sheet.getCellRange(currentRow++, 2).setFormula(currentFormula);

        //YEAR function
        currentFormula = "=YEAR(23)";
        sheet.getCellRange(currentRow, 1).setText(currentFormula);
        sheet.getCellRange(currentRow++, 2).setFormula(currentFormula);

        //NOT function
        currentFormula = "=NOT(true)";
        sheet.getCellRange(currentRow, 1).setText(currentFormula);
        sheet.getCellRange(currentRow++, 2).setFormula(currentFormula);

        //OR function
        currentFormula = "=OR(true)";
        sheet.getCellRange(currentRow, 1).setText(currentFormula);
        sheet.getCellRange(currentRow++, 2).setFormula(currentFormula);

        //AND function
        currentFormula = "=AND(TRUE)";
        sheet.getCellRange(currentRow, 1).setText(currentFormula);
        sheet.getCellRange(currentRow++, 2).setFormula(currentFormula);

        //VALUE function
        currentFormula = "=VALUE(30)";
        sheet.getCellRange(currentRow, 1).setText(currentFormula);
        sheet.getCellRange(currentRow++, 2).setFormula(currentFormula);

        //LEN function
        currentFormula = "=LEN(\"world\")";
        sheet.getCellRange(currentRow, 1).setText(currentFormula);
        sheet.getCellRange(currentRow++, 2).setFormula(currentFormula);

        //MID function
        currentFormula = "=MID(\"world\",4,2)";
        sheet.getCellRange(currentRow, 1).setText(currentFormula);
        sheet.getCellRange(currentRow++, 2).setFormula(currentFormula);

        //ROUND function
        currentFormula = "=ROUND(7,3)";
        sheet.getCellRange(currentRow, 1).setText(currentFormula);
        sheet.getCellRange(currentRow++, 2).setFormula(currentFormula);

        //SIGN function
        currentFormula = "=SIGN(4)";
        sheet.getCellRange(currentRow, 1).setText(currentFormula);
        sheet.getCellRange(currentRow++, 2).setFormula(currentFormula);

        //INT function
        currentFormula = "=INT(200)";
        sheet.getCellRange(currentRow, 1).setText(currentFormula);
        sheet.getCellRange(currentRow++, 2).setFormula(currentFormula);

        //ABS function
        currentFormula = "=ABS(-1.21)";
        sheet.getCellRange(currentRow, 1).setText(currentFormula);
        sheet.getCellRange(currentRow++, 2).setFormula(currentFormula);

        //LN function
        currentFormula = "=LN(15)";
        sheet.getCellRange(currentRow, 1).setText(currentFormula);
        sheet.getCellRange(currentRow++, 2).setFormula(currentFormula);

        //EXP function
        currentFormula = "=EXP(20)";
        sheet.getCellRange(currentRow, 1).setText(currentFormula);
        sheet.getCellRange(currentRow++, 2).setFormula(currentFormula);

        //SQRT function
        currentFormula = "=SQRT(40)";
        sheet.getCellRange(currentRow, 1).setText(currentFormula);
        sheet.getCellRange(currentRow++, 2).setFormula(currentFormula);

        //PI function
        currentFormula = "=PI()";
        sheet.getCellRange(currentRow, 1).setText(currentFormula);
        sheet.getCellRange(currentRow++, 2).setFormula(currentFormula);

        //COS function
        currentFormula = "=COS(9)";
        sheet.getCellRange(currentRow, 1).setText(currentFormula);
        sheet.getCellRange(currentRow++, 2).setFormula(currentFormula);

        //SIN function
        currentFormula = "=SIN(45)";
        sheet.getCellRange(currentRow, 1).setText(currentFormula);
        sheet.getCellRange(currentRow++, 2).setFormula(currentFormula);

        workbook.saveToFile("output/ApplyFormulas.xlsx",ExcelVersion.Version2013);
    }
}

Output

from: https://dev.to//eiceblue/apply-formulas-and-functions-in-excel-in-java-m17

更多推荐

在Java中的Excel中应用公式和函数