Formula Evaluation

DsExcel Java allows you to evaluate Excel formulas directly in code, without the need to write the formulas into worksheet cells. You can use the Evaluate2() method of the IWorksheet interface to pass any formula supported by MS Excel. The Evaluate2() method calculates the formula and returns an Object.

For a standard formula: the method returns a single value.
For a range (reference) formula: the method returns an IRange object.
For a dynamic array formula: the method returns a two-dimensional Object array.

Note: The Evaluate() method can also be used to calculate formulas, but it is only compatible with MS Excel 2019 and earlier versions and does not support dynamic arrays. It is recommended to use Evaluate2() for broader compatibility.

Example 1: Standard Formula

Refer to the following code to calculate the sum of the values in cells A2, A3, and A4, and write the result to cell B6.

// Create a new workbook.
Workbook workbook = new Workbook();
IWorksheet sheet = workbook.getWorksheets().get(0);

// Set data.
sheet.getRange("A1").setValue("Type");
sheet.getRange("B1").setValue("Number");
sheet.getRange("A2").setValue("Football");
sheet.getRange("A3").setValue("Basketball");
sheet.getRange("A4").setValue("rugby");
sheet.getRange("B2").setValue(80);
sheet.getRange("B3").setValue(90);
sheet.getRange("B4").setValue(100);

// Set the header style.
IRange header = sheet.getRange("A1:B1");
header.getFont().setBold(true);
header.setHorizontalAlignment(HorizontalAlignment.Center);

// Calculate the total number of balls and write the result to B6.
sheet.getRange("A6").setValue("Total");
Object sum = sheet.evaluate2("=SUM(B2:B4)");
sheet.getRange("B6").setValue(sum);

sheet.getColumns().get(0).autoFit();
sheet.getColumns().get(1).autoFit();

// Save the Excel file.
workbook.save("Evaluate2forSingleValue.xlsx");

The output is shown in the figure below:

Example 2: Range Formula

Refer to the following code to copy the contents of the B3:C11 range to E3:F11 using the Evaluate2() method. In this case, the result returned is an IRange object.

// Create a new workbook.
Workbook workbook = new Workbook();
IWorksheet sheet = workbook.getWorksheets().get(0);

// Set data.
Object[][] data = {
        { "Product","Number" },
        { "Apple",5},
        { "Grape",6},
        { "Pear",10},
        { "Banana",50},
        { "Coconut",20},
        { "Strawberry",15},
        { "Orange",30},
        { "Pineapple",30}
};
sheet.getRange("B3:C11").setValue(data);

ITable table = sheet.getTables().add(sheet.getRange("B3:C11"), true);

// Use the Evaluate2() method with a range formula as input to return an IRange object.
Object rangeres = sheet.evaluate2("=(B3:C11)");
if (rangeres instanceof IRange) {
    sheet.getRange("E3:F11").setValue(((IRange)rangeres).getValue());
}

// Save the Excel file.
workbook.save("Evaluate2forIRange.xlsx");

The output is shown in the figure below:

Example 3: Dynamic Array Formula

Refer to the following code to calculate the character length of each text in the range B4:B11 in the batch and output the results to the range D4:D11.

// Create a new workbook.
Workbook workbook = new Workbook();
IWorksheet sheet = workbook.getWorksheets().get(0);
// Set Data.
Object[][] data = new Object[][] {
        { "Product" },
        { "Apple" },
        { "Grape" },
        { "Pear" },
        { "Banana" },
        { "Coconut" },
        { "Strawberry" },
        { "Orange" },
        { "Pineapple" }
};
sheet.getRange("B3:B11").setValue(data);

ITable table = sheet.getTables().add(sheet.getRange("B3:B11"), true);
ITable table1 = sheet.getTables().add(sheet.getRange("D3:D11"), true);
table.convertToRange();
table1.convertToRange();
sheet.getRange("D3").setValue("Evaluate2 results:");
sheet.getRange("B:D").autoFit();

// Use the Evaluate2() method to calculate the length of the text in each cell in B4:B11, and output the result array to D4:D11.
Object evaluateRes = sheet.evaluate2("=LEN(B4:B11)");
sheet.getRange("D4:D11").setValue(evaluateRes);  
  
// Save to an excel file.
workbook.save("Evaluate2forDynamicArray.xlsx");

The output is shown in the figure below: