Insert and Read Formulas in Excel in Java

Excel formulas play a very important role in making excel have powerful data analysis and processing capabilities. Therefore, mastering the ability to handle formulas is conducive to enhancing the level of using excel and improving work efficienty. In this article, I’ll introduce how to insert and read formulas in excel by using Free Spire.XLS for Java.

Before Start

Download the latest version of Free Spire.XLS for Java, unzip the package, and you’ll find the Spire.Xls.jar file in the lib folder. Then import the jar file in your Java IED. The following screenshot is what it finally looks like.

Example 1  Insert Formulas

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:") ; ;
        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.LightGreen1);
        range.getStyle().setFillPattern(ExcelPatternType.Solid);
        range.getStyle().getBorders().getByBordersLineType(BordersLineType.EdgeBottom).setLineStyle(LineStyleType.Medium);

        //Arithmetic operation
        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);

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

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

        //Save to file
        workbook.saveToFile("output/InsertFormulas.xlsx",FileFormat.Version2013);
    }
}

output:

Example 2 Read Formulas

import com.spire.xls.CellRange;

import com.spire.xls.Workbook;

import com.spire.xls.Worksheet;

public class ReadFormulas {

    public static void main(String[] args) {

        //Create a Workbook object

        Workbook workbook = new Workbook();


        //Load an Excel file

        workbook.loadFromFile("C:\\Users\\Test1\\Desktop\\InsertFormulas.xlsx");

        //Get the first worksheet

        Worksheet sheet = workbook.getWorksheets().get(0);

        //Loop through the cells within B1:B13

        for (Object cell: sheet.getCellRange("B1:B13")

        ) {

         CellRange cellRange = (CellRange)cell;

        //Detect if a cell range has formula

        if (cellRange.hasFormula()){

        //Print out the cell containing a formula and the formula itself

        String certainCell = String.format("Cell[%d, %d] has a formula: ",cellRange.getRow(),cellRange.getColumn());

        System.out.println(certainCell + cellRange.getFormula());

            }

        }

    }

}

output: