I ran into a different issue with a different template after the changes.
Getting “System.ArgumentException: ‘Item has already been added. Key in dictionary: ‘1099511824384’ Key being added: ‘1099511824384’’” error when trying to refresh dynamic array formulas.
PFA workbook:
dictionary.zip (442.8 KB)
Sample code:
static void Main(string[] args)
{
var workbook = new Workbook(@“dictionary.xlsx”);
var manager = new UDFManager();
var options = new CalculationOptions
{
CustomEngine = manager,
IgnoreError = true,
PrecisionStrategy = CalculationPrecisionStrategy.Decimal
};
workbook.CalculateFormula(options);
workbook.RefreshDynamicArrayFormulas(true, options); //System.ArgumentException: 'Item has already been added. Key in dictionary: '1099511824384' Key being added: '1099511824384''
}
public class UDF
{
public string FunctionName { get; set; }
public List<object> Parameters { get; set; }
}
public class UDFManager : AbstractCalculationEngine
{
public List<UDF> Items = new List<UDF>();
public override void Calculate(CalculationData data)
{
var parameters = GetParameterList(data);
switch (data.FunctionName)
{
case "DCRefersToSheetName":
{
if (parameters.Count == 0)
{
var sheetName = data.Worksheet.Name;
Console.WriteLine($"'{data.Cell.Worksheet.Name}'!{data.Cell.Name} calculated value set to {sheetName}");
data.CalculatedValue = sheetName;
}
else if (parameters[0] is Cell reference)
{
var sheetName = reference.Worksheet.Name;
Console.WriteLine($"'{reference.Worksheet.Name}'!{reference.Name} calculated value set to {sheetName}");
data.CalculatedValue = sheetName;
}
break;
}
default:
var fn = new UDF
{
FunctionName = data.FunctionName,
Parameters = parameters
};
Items.Add(fn);
break;
}
}
}
private static List<object> GetParameterList(CalculationData data)
{
var result = new List<object>();
for (var parameter = 0; parameter < data.ParamCount; parameter++)
{
var param = data.GetParamValue(parameter);
if (param is ReferredArea ra)
result.Add(ReferredAreaToCell(data.Workbook, ra));
else
result.Add(param);
}
return result;
}
private static Cell ReferredAreaToCell(Workbook workbook, ReferredArea ra)
{
if (ra.SheetName == null) return null;
var worksheet = workbook.Worksheets[ra.SheetName];
return worksheet?.Cells[ra.StartRow, ra.StartColumn];
}
}