We are evaluating Spire.XLS for use on a project, however we ran across an issue where a formula appears to be calculated differently in Spire than it is in Excel. Essentially, on a formula such as this "=IFERROR(DATEVALUE(B1),DATEVALUE(B2))" when cell B1 is blank, Excel sees the first expression as an error and thus uses the value from B2. However, Spire doesn't see an error here and instead interprets DATEVALUE(B1), with B1 being blank, as the date 12/31/1899.
I created an example demonstrating this behavior. The input file is attached and the code below:
- Code: Select all
using Spire.Xls;
Workbook wb = new Workbook();
wb.LoadFromFile("test.xlsx");
wb.CalculateAllValue();
Worksheet ws = wb.Worksheets[0];
string val = ws.Range[3, 2].FormulaValue.ToString();
Console.WriteLine(val);
This is in a console application using the latest version of Spire.XLS 15.4.0 targeting .NET 8.0.
Any assistance here is much appreciated!
