Java adds and reads Excel formulas

When calculating and processing data in Excel tables, you often use various Excel function expressions to improve your work efficiency. In this article, I'll show you how to use Free Spire.XLS for Java to add formulas to Excel cells and read the formulas in the cells.

** Import JAR package ** ** Method 1: ** Download and unzip Free Spire.XLS for Java, then in the lib folder Import the Spire.Xls.jar package into your Java application as a dependency.

** Method 2: ** After installing the JAR package directly from the Maven repository, configure the pom.xml file as follows:

<repositories>
        <repository>
            <id>com.e-iceblue</id>
            <name>e-iceblue</name>
            <url>http://repo.e-iceblue.com/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>

** Add formula **

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
        int currentRow = 1;
        String currentFormula = null;

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

        //Write test data to cells
        sheet.getCellRange(currentRow,1).setValue("Test the 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 to text
        currentRow += 2;
        sheet.getCellRange(currentRow,1).setValue("equation:") ; ;
        sheet.getCellRange(currentRow,2).setValue("result:");

        //Format the cell
        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);

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

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

        //Time 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");

        //IF function
        currentFormula = "=IF(B1=5,\"Yes\",\"No\")";
        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);

        //Trigonometric function
        currentFormula = "=SIN(PI()/6)";
        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);

        //Save the document
        workbook.saveToFile("InsertFormulas.xlsx",FileFormat.Version2013);
    }
}

** Read formula **

import com.spire.xls.*;

public class ReadFormulas {

    public static void main(String[] args) {

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

        //Load the excel document
        workbook.loadFromFile("InsertFormulas.xlsx");

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

        //Traverse cells from B1 to B13
        for (Object cell : sheet.getCellRange("B1:B13")
                ) {
            CellRange cellRange = (CellRange) cell;

            //Determines if a cell contains a formula
            if (cellRange.hasFormula()) {

                //Print cells and formulas
                String certainCell = String.format("cell[%d, %d]Contains the formula:", cellRange.getRow(), cellRange.getColumn());
                System.out.println(certainCell + cellRange.getFormula());
            }
        }
    }
}