MATCH function behaves differently in Aspose.Cells, let’s take the below example,
=MATCH(“error”,#REF!,0)
In Excel, above function returns #REF! whereas in Aspose.Cells it returns #N/A
@sajad.deyargaroo
By creating a sample file and testing it on the latest version v25.2 using the following sample code, we can reproduce the issue. MATCH function returns error value after calling Workkbook.CalculateFormula() method. Please refer to the attachment. a.zip (6.1 KB)
Workbook wb = new Workbook(filePath + "a.xlsx");
Cells cells = wb.Worksheets[0].Cells;
Console.WriteLine(cells["A1"].StringValue);
wb.RefreshDynamicArrayFormulas(true);
wb.CalculateFormula();
Console.WriteLine(cells["A1"].StringValue);
The output:
#REF!
#N/A
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): CELLSNET-57868
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.
We have found another instance where MATCH function results in Aspose don’t match with Excel.
e.g.,
MATCH(#DIV/0!,A1:A5,0)
In case of Excel, above function will return #DIV/0! whereas in Aspose it returns #N/A!
It seems that if value is not found by MATCH function then both Excel and Aspose return #N/A! but in case of error Excel returns the error per se whereas Aspose returns #N/A in that case as well
I tested your scenario/case using latest version/fix (Aspose.Cells for .NET v25.2) and it works fine and as expected. I am using the following sample code with the template Excel file (attached).
e.g.,
Sample code:
Workbook wb = new Workbook("e:\\test2\\b.xlsx");
Cells cells = wb.Worksheets[0].Cells;
Console.WriteLine(cells["B1"].StringValue);
wb.RefreshDynamicArrayFormulas(true);
wb.CalculateFormula();
Console.WriteLine(cells["B1"].StringValue);
output:
#DIV/0!
#DIV/0!
Please find attached the template Excel file for your reference.
b.zip (6.0 KB)
If you still find any issue with Aspose.Cells for .NET v25.2, kindly do share your sample code and resource file, we will check it soon.
I have updated the sample file that you have created. In the updated workbook, the formula in “A1” cell returns #DIV/0! in Excel whereas Aspose returns #N/A!.
Please let me know, if you would like me to create a new case for this scenario.
b.zip (6.6 KB)
Thanks for providing the Excel file.
I used your Excel file to reproduce the similar issue. I have logged it with your existing ticket “CELLSNET-57868” into our database. We will evaluate and figure it out as well.
This is to inform you that your issue (logged earlier as “CELLSNET-57868”) has been resolved. The fix/enhancement will be included in the next release (Aspose.Cells v25.3) scheduled for release first half of March (next month). You will be notified once the new version is published.
That is Wonderful. Thanks!
You’re welcome. We’ll keep you updated as soon as the new version becomes available.
The issues you have found earlier (filed as CELLSNET-57868) have been fixed in this update. This message was posted using Bugs notification tool by leoluo
Great! Appreciate your help.
Will test it and let you know.
@sajad.deyargaroo
You are welcome! If you have any questions, please feel free to contact us at any time.
Thank you for your feedback! We’re glad to hear that the new version has resolved your issue. If you have any further questions or comments, please don’t hesitate to reach out to us.