Apply Formulas and Functions in Excel in Java

#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 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.

<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

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);
    }
}