I am trying to implement some filtering based on multiple search inputs, but the calculation returns a #VALUE! error instead of 20 for the lines with Germany, and 15 for Hungary. See the attached example code. I also needed to implement a custom calculation engine extension with my implementation of MAP and LAMBDA, but that part seems to be working properly (see the output of the console application).
Also, when I try to set the formula of a cell to the final formula of a row, I get the same error, so the issue is not my two extension methods probably.
I believe the issue is related to that the formulas on the Countries sheet are not evaluated correctly, and that’s why the engine cannot find the proper value. If I added a row with Germany as the country and 2022-01-01 as the date to this spreadsheet, then I would get a proper result for that single line. Unfortunately, I was able to see that result in MS Excel as well previously, but now I cannot reproduce it because it recalculates all the formulas within the file when I open it.
Also, is there a plan to support MAP and LAMBDA out of the box by the library?
I am looking forward to your answer.
After conducting initial testing, I was able to reproduce the issue as you described by using sample files. Upon evaluation of the dynamic array formula within our custom calculation engine, I discovered an issue. We will need to investigate your code snippet and determine if any tweaks are necessary for your sample code.
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-53886
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.
Regarding the LAMBDA function, we have investigated the formula. We can support parsing it, but when opening the generated XLSX file with the formula, it might become invalid. We have made some progress in implementing it. Anyways, we will provide the latest updates on the MAP and LAMBDA functions.
We have evaluated your issue in detail and found that the problem was caused by invoking the Worksheet.CalculateArrayFormula() method in the implementation of your custom calculation engine. This approach starts another calculation process within the main formula calculation process, which can lead to inconsistent data and flags for the calculation. In some cases, it may even cause recursive calculations and circular reference errors.
We will investigate whether there is a workaround for your requirements. However, the ultimate solution should be to support the calculation of LET/LAMBDA/MAP functions. If there is no suitable workaround for you, we will prioritize supporting the calculation of these functions as soon as possible. The current estimated time for this is about three months, but it may change depending on other tasks with higher priority.
This is to inform you that your issue has been resolved. The enhancement/fix will be included in an upcoming release (Aspose.Cells v23.9) that we plan to release in the first half of September 2023. You will be notified when the next version is released.
To make your custom engine work, we will provide new method for calculating static formulas:
CalculateArrayFormula(string formula, FormulaParseOptions pOpts, CalculationOptions cOpts, int baseCellRow, int baseCellColumn, int maxRowCount, int maxColumnCount, CalculationData calculationData)
with this new method, your code also needs to change a bit:
e.g. Sample code:
class ExtendedCalculationEngine : AbstractCalculationEngine
{
...
private object[][] ExtractValuesFromParameter(CalculationData data, int index, string paramText)
{
var value = data.GetParamValue(index);
if (value is ReferredArea ra)
{
var calculatedValues = ra.GetValues();
if (calculatedValues is object[][] arr)
{
return arr;
}
return new[] { new[] { calculatedValues } };
}
return data.Worksheet.CalculateArrayFormula(paramText, new FormulaParseOptions(),
_calcOptions, 0, 0, -1, -1, data);
}
protected virtual void CalculateMap(CalculationData data)
{
...
object[] parameters = Enumerable.Range(0, data.ParamCount - 1)
.Select(k => values[k][i][j])
.ToArray();
object[] np = new object[parameters.Length + 1];
Array.Copy(parameters, np, parameters.Length);
np[parameters.Length] = data;
Console.WriteLine($"Evaluate lambda: {i + 1} / {rowCount}");
var res = lambdaReference.Invoke(np);
...
}
...
protected virtual void CalculateLambda(CalculationData data)
{
...
for (var i = 0; i < input.Length-1; i++)
{
sb = sb.Replace(paramTexts[i], GetFormattedValue(input[i]));
}
Console.WriteLine($"Input values: {string.Join(", ", input)}");
Console.WriteLine($"Formula to evaluate: {sb}");
var value = worksheet.CalculateArrayFormula(sb.ToString(),
new FormulaParseOptions(), _calcOptions, 0, 0, -1, -1, (CalculationData)input[input.Length-1]);
...
}
}
The issues you have found earlier (filed as CELLSNET-53886) have been fixed in this update. This message was posted using Bugs notification tool by johnson.shi
Thank you for the update. I see that in the new release (23.9) the MAP and LAMBDA functions are supported. However, after the upgrade and making your suggested changes, two of my unit tests are failing (see attached), one of them running into StackOverflowException and the other returning incorrect results.
Based on what I know about the calculation engine, this supposed to be the output of the built-in functions. When I override the ProcessBuiltInFunctions property to true, then my implementation is still not called, so I am not sure at this point how can I make it working with the new version. As far as I understand, even if my methods are called, the built-in implementation would be called first, which can cause StackOverflowException making upgrading to version 23.9 a very risky move from my point of view.
@marton.varsanyi,
We will investigate the issue and get back to you soon. Yes, we have supported the LAMBDA and MAP functions in the new version. Firstly we will figure the issue of StackOverflowException and then we will investigate whether we can make user’s custom implementation work with ProcessBuiltInFunctions property to true.
@marton.varsanyi
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-54205
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.
The issues you have found earlier (filed as CELLSNET-54205) have been fixed in this update. This message was posted using Bugs notification tool by johnson.shi