Add and read Excel formula in Java

When operating excel tables to process data with formulas, you can create formulas to calculate data, or read formulas to obtain data information sources. This article uses Java code to demonstrate how to create and read formulas in Excel. The Excel Java class library (free spirit.xls for Java free version) is used here Download official website After getting the package, extract it and import the jar file under the lib folder into the Java program; or maven warehouse Download And import. The import results are as follows:

 

 

1. Create formula

import com.spire.xls.*;

public class AddFormula {
    public static void main(String[] args) {
        //Establish Workbook object
        Workbook wb = new Workbook();

        //Get first sheet
        Worksheet sheet = wb.getWorksheets().get(0);

        //Declare two variables
        int currentRow = 1;
        String currentFormula = null;

        //Set column width
        sheet.setColumnWidth(1, 32);
        sheet.setColumnWidth(2, 16);

        //Write data for test to cell
        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
        currentRow += 2;
        sheet.getCellRange(currentRow,1).setValue("Formula:") ; ;
        sheet.getCellRange(currentRow,2).setValue("Result:");

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

        //Counting 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 document
        wb.saveToFile("AddFormulas.xlsx",FileFormat.Version2013);
        wb.dispose();
    }
}

 

Formula creation result:

 

2. Read formula

import com.spire.xls.*;

public class ReadFormula {
    public static void main(String[] args) {
        //Load Excel File
        Workbook wb = new Workbook();
        wb.loadFromFile("AddFormulas.xlsx");

        //Get first sheet
        Worksheet sheet = wb.getWorksheets().get(0);

        //ergodic B1 reach B13 Cell
        for (Object cell: sheet.getCellRange("B1:B13"))
        {
            CellRange cellRange = (CellRange)cell;

            //Determine whether the cell contains formula
            if (cellRange.hasFormula())
            {
                //Print cells and formulas
                String certainCell = String.format("Cell[%d, %d]Formula included:", cellRange.getRow(), cellRange.getColumn());
                System.out.println(certainCell + cellRange.getFormula());
            }
        }
    }
}

Formula read result:

 

(end of this paper)

Tags: Java Excel Maven

Posted on Fri, 10 Jan 2020 10:31:19 -0500 by GBS