The code is being run in a docker container built from the following image: mcr.microsoft.com/dotnet/sdk:7.0
Whenever we try to calculate a cell in a worksheet with the following formula, nothing happens:
- Code: Select all
=IF($L10="","",XLOOKUP(L10,'BSA-21'!$F:$F,'BSA-21'!$G:$G))
We have validated that proper data is in all of the references in the formula and the formula works without issue when we open up the Excel file in a Windows machine and calculate it there. I cannot provide the whole file as it contains sensitive data.
I've also noticed that whenever we try to call `.EnvaluatedValue` on the Cell, it throws the following error:
- Code: Select all
Error Message:
sprば : Invalid formula in Student-count revenues:K9
Error in calculating range K9 in Worksheet Student-count revenues
Stack Trace:
at spr☣.攐(Boolean A_0, spr冡 A_1)
at Spire.Xls.Core.Spreadsheet.XlsRange.get_EnvalutedValue()
at nextgen_cs.Tests.ProgramTests.OneTimeSchoolLevelAFRTest() in /app/tests/afr-templater.Tests/ProgramTests.cs:line 1479
at System.Threading.Tasks.Task.<>c.<ThrowAsync>b__128_0(Object state)
Are there some Excel formulae that aren't supported by Spire.XLS? I saw that there was this other news article explaining that the XLOOKUP was fixed in v14.5.1 for Java, so I tried upgrading to the latest version but we ran into the same error in .NET.
Here's the code that we run in our test to run into this error
- Code: Select all
[Fact]
public async void OneTimeSchoolLevelAFRTest()
{
// Get the file where we can work with it
var filepath = "fixtures/test-school-level.xlsm";
var workbook = new Workbook();
workbook.LoadFromFile(filepath);
var worksheet = workbook.Worksheets[WorksheetNames.StudentCountRevenues];
// Check out differences
var goodDistrictName = worksheet.Range["K9"];
var badDistrictName = worksheet.Range["K10"];
Console.WriteLine($" Good | Bad");
Console.WriteLine(" ============");
Console.WriteLine($"Value: {goodDistrictName.Value} | {badDistrictName.Value}");
Console.WriteLine($"Value2: {goodDistrictName.Value2} | {badDistrictName.Value2}");
Console.WriteLine($"NumberValue: {goodDistrictName.NumberValue} | {badDistrictName.NumberValue}");
Console.WriteLine($"NumberText: {goodDistrictName.NumberText} | {badDistrictName.NumberText}");
Console.WriteLine($"NumberFormat: {goodDistrictName.NumberFormat} | {badDistrictName.NumberFormat}");
// This is the line that throws the error shown above
Console.WriteLine($"EvaluatedValue: {goodDistrictName.EnvalutedValue} | {badDistrictName.EnvalutedValue}");
Console.WriteLine($"BooleanValue: {goodDistrictName.BooleanValue} | {badDistrictName.BooleanValue}");
Console.WriteLine($"Formula: {goodDistrictName.Formula} | {badDistrictName.Formula}");
Console.WriteLine($"FormulaR1C1: {goodDistrictName.FormulaR1C1} | {badDistrictName.FormulaR1C1}");
Console.WriteLine($"FormulaArray: {goodDistrictName.FormulaArray} | {badDistrictName.FormulaArray}");
Console.WriteLine($"FormulaArrayR1C1: {goodDistrictName.FormulaArrayR1C1} | {badDistrictName.FormulaArrayR1C1}");
Console.WriteLine($"FormulaValue: {goodDistrictName.FormulaValue} | {badDistrictName.FormulaValue}");
Console.WriteLine($"FormulaBoolValue: {goodDistrictName.FormulaBoolValue} | {badDistrictName.FormulaBoolValue}");
Console.WriteLine($"FormulaErrorValue: {goodDistrictName.FormulaErrorValue} | {badDistrictName.FormulaErrorValue}");
Console.WriteLine($"FormulaNumberValue: {goodDistrictName.FormulaNumberValue} | {badDistrictName.FormulaNumberValue}");
Console.WriteLine($"FormulaStringValue: {goodDistrictName.FormulaStringValue} | {badDistrictName.FormulaStringValue}");
Console.WriteLine($"DateTimeValue: {goodDistrictName.DateTimeValue} | {badDistrictName.DateTimeValue}");
Console.WriteLine($"TimeSpanValue: {goodDistrictName.TimeSpanValue} | {badDistrictName.TimeSpanValue}");
Console.WriteLine($"ErrorValue: {goodDistrictName.ErrorValue} | {badDistrictName.ErrorValue}");
Console.WriteLine($"Type: {goodDistrictName.GetType()} | {badDistrictName.GetType()}");
Console.WriteLine($"IsAllNumber: {goodDistrictName.IsAllNumber} | {badDistrictName.IsAllNumber}");
Console.WriteLine($"IsInitialized: {goodDistrictName.IsInitialized} | {badDistrictName.IsInitialized}");
Console.WriteLine($"RichText: {goodDistrictName.RichText} | {badDistrictName.RichText}");
Console.WriteLine($"Text: {goodDistrictName.Text} | {badDistrictName.Text}");
}
Any insight or ideas would be greatly appreciated. Thank you!
