This guide demonstrates how to programmatically add and retreive Excel formulas within a Java application using the Spire.XLS library.

Project Setup

Before you begin, download the Free Spire.XLS for Java package. After extraction, add the Spire.Xls.jar file from the lib folder to your project's build path. Alternatively, you can entegrate it via Maven by configuring you're pom.xml file as follows:

<repositories>
    <repository>
        <id>com.e-iceblue</id>
        <url>http://repo.e-iceblue.cn/repository/maven-public/</url>
    </repository>
</repositories>
<dependencies>
    <dependency>
        <groupId>e-iceblue</groupId>
        <artifactId>spire.xls.free</artifactId>
        <version>2.2.0</version>
    </dependency>
</dependencies>

Inserting Formulas into Excel

The following Java code illustrates how to populate an Excel sheet with various formulas and their corresponding results.

import com.spire.xls.*;

public class FormulaInsertionDemo {

    public static void main(String[] args) {
        // Initialize a new Excel workbook
        Workbook workbook = new Workbook();
        // Access the first worksheet
        Worksheet sheet = workbook.getWorksheets().get(0);

        int currentRow = 1;
        String currentFormula;

        // Adjust column widths for better readability
        sheet.setColumnWidth(1, 26);
        sheet.setColumnWidth(2, 16);

        // Populate initial data for formula testing
        sheet.getCellRange(currentRow, 1).setValue("Sample Data:");
        sheet.getCellRange(currentRow, 2).setNumberValue(10);
        sheet.getCellRange(currentRow, 3).setNumberValue(25);
        sheet.getCellRange(currentRow, 4).setNumberValue(15);
        sheet.getCellRange(currentRow, 5).setNumberValue(30);
        sheet.getCellRange(currentRow, 6).setNumberValue(20);

        // Add headers for formula examples
        currentRow += 2;
        sheet.getCellRange(currentRow, 1).setValue("Formula Expression");
        sheet.getCellRange(currentRow, 2).setValue("Calculated Result");

        // Apply styling to the header row
        CellRange headerRange = sheet.getCellRange(currentRow, 1, currentRow, 2);
        headerRange.getStyle().getFont().isBold(true);
        headerRange.getStyle().setKnownColor(ExcelColors.LightGreen1);
        headerRange.getStyle().setFillPattern(ExcelPatternType.Solid);
        headerRange.getStyle().getBorders().getByBordersLineType(BordersLineType.EdgeBottom).setLineStyle(LineStyleType.Medium);

        // Arithmetic Operations
        currentRow++;
        currentFormula = "=10/5+2*3";
        sheet.getCellRange(currentRow, 1).setText(currentFormula);
        sheet.getCellRange(currentRow, 2).setFormula(currentFormula);

        // Date Function
        currentRow++;
        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
        currentRow++;
        currentFormula = "=NOW()";
        sheet.getCellRange(currentRow, 1).setText(currentFormula);
        sheet.getCellRange(currentRow, 2).setFormula(currentFormula);
        sheet.getCellRange(currentRow, 2).getStyle().setNumberFormat("h:mm AM/PM");

        // Conditional Logic (IF Function)
        currentRow++;
        currentFormula = "=IF(B1=10,"Yes","No")"; // Assumes data in B1
        sheet.getCellRange(currentRow, 1).setText(currentFormula);
        sheet.getCellRange(currentRow, 2).setFormula(currentFormula);

        // Mathematical Constants (PI)
        currentRow++;
        currentFormula = "=PI()";
        sheet.getCellRange(currentRow, 1).setText(currentFormula);
        sheet.getCellRange(currentRow, 2).setFormula(currentFormula);

        // Trigonometric Function (SIN)
        currentRow++;
        currentFormula = "=SIN(PI()/6)";
        sheet.getCellRange(currentRow, 1).setText(currentFormula);
        sheet.getCellRange(currentRow, 2).setFormula(currentFormula);

        // Counting Function (COUNT)
        currentRow++;
        currentFormula = "=COUNT(B1:F1)";
        sheet.getCellRange(currentRow, 1).setText(currentFormula);
        sheet.getCellRange(currentRow, 2).setFormula(currentFormula);

        // Statistical Functions (MAX, AVERAGE, SUM)
        currentRow++;
        currentFormula = "=MAX(B1:F1)";
        sheet.getCellRange(currentRow, 1).setText(currentFormula);
        sheet.getCellRange(currentRow, 2).setFormula(currentFormula);

        currentRow++;
        currentFormula = "=AVERAGE(B1:F1)";
        sheet.getCellRange(currentRow, 1).setText(currentFormula);
        sheet.getCellRange(currentRow, 2).setFormula(currentFormula);

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

        // Save the workbook with formulas
        workbook.saveToFile("ExcelFormulasDemo.xlsx", FileFormat.Version2013);
    }
}

Reading Formulas from Excel

This code snippet demonstrates how to iterate through a specified range of cells in an existing Excel file, identify cells containing formulas, and print the formulas to the console.

import com.spire.xls.*;

public class FormulaReadingDemo {

    public static void main(String[] args) {
        // Initialize a new Excel workbook
        Workbook workbook = new Workbook();
        // Load the Excel file containing formulas
        workbook.loadFromFile("ExcelFormulasDemo.xlsx");

        // Access the first worksheet
        Worksheet sheet = workbook.getWorksheets().get(0);

        // Define the range to check for formulas
        String cellRangeAddress = "B2:B14"; // Adjust range as needed

        // Iterate over each cell in the specified range
        for (Object cellObject : sheet.getCellRange(cellRangeAddress)) {
            CellRange currentCell = (CellRange) cellObject;

            // Check if the cell contains a formula
            if (currentCell.hasFormula()) {
                // Construct and print the cell coordinates and its formula
                String cellInfo = String.format("Cell [%d, %d] contains formula: %s", 
                                                currentCell.getRow(), 
                                                currentCell.getColumn(), 
                                                currentCell.getFormula());
                System.out.println(cellInfo);
            }
        }
    }
}