Apply Formulas and Functions in Excel in Java
Tags: java, excel, formulas, functions
Published: 2020-03-03 06:43:31

---
title: Apply Formulas and Functions in Excel in Java
published: true
description: This article shows you how to apply formulas and functions in Excel cells in Java.
tags: java,excel,formulas,functions
---

A Formula is defined as an equation that performs calculations and returns the result value in cells. It contains several functions, cell references, operators on functions. A formula can be simple or complex based on users needs. A Function can be defined as a built-in operation in Excel that returns a result value in cells. A function contains only cell references and values.

In this article, I’ll show you how to apply formulas and functions in Excel cells by using Free Spire.XLS for Java.

##Add Spire.Xls.jar as dependency

Method 1: Download [Free Spire.XLS for Java](https://www.e-iceblue.com/Download/xls-for-java-free.html) 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.

Method 2:  If you are creating a Maven project, you can easily add the jar dependency by adding the following configurations to the pom.xml.
```java
<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>
```
##Using the code
```java
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
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/5z8du05ax1qd29vzjs5f.png)