Java: Insert or Read Formulas and Functions in Excel

Apart from recording data, Excel also has calculation functions, which make analyzing and processing data efficient and simple. There are two calculation tools in Excel, formulas and functions. Formulas are user-defined statements of calculations, while functions are predefined formulas. Users can either input their own formulas in cells or simply invoke functions to calculate. This article is going to show how to insert or read formulas and functions in Excel workbooks using Spire.XLS for Java.

Install Spire.XLS for Java

First of all, you're required to add the Spire.Xls.jar file as a dependency in your Java program. The JAR file can be downloaded from this link. If you use Maven, you can easily import the JAR file in your application by adding the following code to your project's pom.xml file.

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

Insert Formulas and Functions into an Excel Workbook

Spire.XLS for Java provides a Worksheet.getCellRange().setFormula() method to add a formula or a function to a specific cell. The detailed steps for inserting a formula or a function into a worksheet are as follows.

  • Create an object of Workbook.
  • Get the first worksheet using Workbook.getWorksheets().get() method.
  • Write data in cells and format the cells.
  • Add formulas or functions to specific cells using Worksheet.getCellRange().setFormula() method.
  • Save the workbook using Workbook.saveToFile() method.
  • Java
import com.spire.xls.*;

public class insertFormulas {
    public static void main(String[] args) {

        //Create an object of Workbook
        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 data in 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 the Workbook
        workbook.saveToFile("InsertFormulas.xlsx",FileFormat.Version2013);

    }
}

Java: Insert or Read Formulas and Functions in Excel

Read Formulas and Functions from an Excel Workbook

To read formulas in Excel worksheets, we can use CellRange.hasFormula() method to detect if a cell contains a formula and then use CellRange.getFormula() method to get the formula if there is. The detailed steps are as follows.

  • Create an object of Workbook.
  • Load an Excel workbook using Workbook.loadFromFile() method.
  • Get a Worksheet using Workbook.getWorksheets().get() method.
  • Loop through the cells in the worksheet.
  • Detect if a cell contains a formula using CellRange.hasFormula() method. If it does, get the formula using CellRange.getFormula() method and print it out.
  • Save the workbook using Workbook.saveToFile() method.
  • Java
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 an object of Workbook
        Workbook workbook = new Workbook();

        //Load an Excel file
        workbook.loadFromFile("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 has a formula
            if (cellRange.hasFormula()){

                //Print out the formula
                String certainCell = String.format("Cell[%d, %d] has a formula: ",cellRange.getRow(),cellRange.getColumn());
                System.out.println(certainCell + cellRange.getFormula());
            }
        }
    }
}

Java: Insert or Read Formulas and Functions in Excel

Apply for a Temporary License

If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.