Incorrect formula calculation result

Aspose.Cells Product Family
1

from aspose 24.6 onwards for some inputs the formulas give wrong results.
it seems to be related to using match with an {} array literal.

The following code demonstrates the issue:

import com.aspose.cells.CellsHelper;
import com.aspose.cells.Workbook;
import com.aspose.cells.Worksheet;

public class AsposeMatchArrayLiteral {

    public static void main(String[] args) throws Exception {
        Workbook workbook = new Workbook();
        workbook.getSettings().getFormulaSettings().setCalculateOnOpen(false);
        Worksheet worksheet = workbook.getWorksheets().get(0);

        worksheet.getCells().get("A1").setValue("a");
        worksheet.getCells().get("B1").setValue("b");
        worksheet.getCells().get("C1").setValue("c");
        worksheet.getCells().get("D1").setValue("d");
        worksheet.getCells().get("E1").setValue("e");
        worksheet.getCells().get("F1").setValue("match result");

        worksheet.getCells().get("A2").setValue(80);
        worksheet.getCells().get("B2").setValue("Some text");
        worksheet.getCells().get("D2").setValue("A152");
        worksheet.getCells().get("A3").setValue(81);
        worksheet.getCells().get("B3").setValue("Some text");
        worksheet.getCells().get("D3").setValue("A123");

        worksheet.getListObjects().add("A1", "F3", true);

        worksheet.getCells().get("E2").setFormula("=IF(ISNUMBER(MATCH(MID([@d],3,1),{\"4\"},0)),IF(IF((NOT(([@b]=\"\"))),NOT(([@c]=\"\")),(TRUE)),\"\"),\"expected\")");
        worksheet.getCells().get("E3").setFormula("=IF(ISNUMBER(MATCH(MID([@d],3,1),{\"4\"},0)),IF(IF((NOT(([@b]=\"\"))),NOT(([@c]=\"\")),(TRUE)),\"\"),\"expected\")");

        worksheet.getCells().get("F2").setFormula("=MATCH(MID([@d],3,1),{\"4\"},0)");
        worksheet.getCells().get("F3").setFormula("=MATCH(MID([@d],3,1),{\"4\"},0)");

        workbook.calculateFormula();

        System.out.println("Running in Aspose version " + CellsHelper.getVersion());

        // in 24.6+ incorrect result based on A152 input:
        System.out.println("Expecting 'expected', got: " + worksheet.getCells().get("E2").getValue());
        // correct result based on A123 input:
        System.out.println("Expecting 'expected', got: " + worksheet.getCells().get("E3").getValue());

        // in 24.6+ incorrect result based on A152 input:
        System.out.println("Expecting '#N/A', got: " + worksheet.getCells().get("F2").getValue());
        // correct result based on A123 input:
        System.out.println("Expecting '#N/A', got: " + worksheet.getCells().get("F3").getValue());

//        workbook.save("/tmp/result.xlsx");
    }
}

In this sample both E2 and E3 should result in “expected”, but from 24.6 onwards E2 results in false.

2

@wscheele_sf
By testing the sample code on the latest version v24.11, we can obtain the same results as Excel. There is a discrepancy between your expected value and the Excel calculation result. Please refer to the attachment. result.png (23.5 KB)

3

Hi, the screenshot in the attachment looks like the result produced by the code.
In the code, recalculate on open is disabled such that the erroneous result is still visible in excel.
If you go to cell D2 and re-enter the same number (or recalculate otherwise), you will see the discrepancy.

or when disabling

        workbook.getSettings().getFormulaSettings().setCalculateOnOpen(false);

on line 9, the discrepancy will show immediately upon opening of the resulting workbook.

4

@wscheele_sf
We can reproduce the issue by testing on the latest version v24.11 using the following sample code. After calling the formula calculation, some incorrect calculation results were obtained.

Workbook workbook = new Workbook();
workbook.getSettings().getFormulaSettings().setCalculateOnOpen(true);
Worksheet worksheet = workbook.getWorksheets().get(0);

worksheet.getCells().get("A1").setValue("a");
worksheet.getCells().get("B1").setValue("b");
worksheet.getCells().get("C1").setValue("c");
worksheet.getCells().get("D1").setValue("d");
worksheet.getCells().get("E1").setValue("e");
worksheet.getCells().get("F1").setValue("match result");

worksheet.getCells().get("A2").setValue(80);
worksheet.getCells().get("B2").setValue("Some text");
worksheet.getCells().get("D2").setValue("A152");
worksheet.getCells().get("A3").setValue(81);
worksheet.getCells().get("B3").setValue("Some text");
worksheet.getCells().get("D3").setValue("A123");

worksheet.getListObjects().add("A1", "F3", true);

worksheet.getCells().get("E2").setFormula("=IF(ISNUMBER(MATCH(MID([@d],3,1),{\"4\"},0)),IF(IF((NOT(([@b]=\"\"))),NOT(([@c]=\"\")),(TRUE)),\"\"),\"expected\")");
worksheet.getCells().get("E3").setFormula("=IF(ISNUMBER(MATCH(MID([@d],3,1),{\"4\"},0)),IF(IF((NOT(([@b]=\"\"))),NOT(([@c]=\"\")),(TRUE)),\"\"),\"expected\")");

worksheet.getCells().get("F2").setFormula("=MATCH(MID([@d],3,1),{\"4\"},0)");
worksheet.getCells().get("F3").setFormula("=MATCH(MID([@d],3,1),{\"4\"},0)");

workbook.calculateFormula();

System.out.println("Running in Aspose version " + CellsHelper.getVersion());

// in 24.6+ incorrect result based on A152 input:
System.out.println("Expecting 'expected', got: " + worksheet.getCells().get("E2").getValue());
// correct result based on A123 input:
System.out.println("Expecting 'expected', got: " + worksheet.getCells().get("E3").getValue());

// in 24.6+ incorrect result based on A152 input:
System.out.println("Expecting '#N/A', got: " + worksheet.getCells().get("F2").getValue());
// correct result based on A123 input:
System.out.println("Expecting '#N/A', got: " + worksheet.getCells().get("F3").getValue());

workbook.save(filePath + "out_error.xlsx");

We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.

Issue ID(s): CELLSJAVA-46178

You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.