SetArrayFormula Error

Aspose.Cells Product Family
1

Hi,

I got an error message “Index was outside the bounds of the array” when executing method wb.CalculateFormula(). The error cell has an array formula. If you comment the code wb.CalculateFormula(), you can see the correct value on Excel cell but you are not able to get it by C# code. Following is the code I use to set the array formula. Thanks!

data.Cells[0, 0].SetArrayFormula("=VLOOKUP(F1&K1, IF({{1,0}}, Reference!$F$2:$F$501&Reference!$G$2:$G$501, Reference!$H$2:$H$501), 2, FALSE)", 1, 1);

wb.CalculateFormula();

2

Can anyone help?

3

Hi,

Thanks for your posting and using Aspose.Cells for .NET.

We will soon look into your issue and help you.

Could you please provide us your source xls/xlsx file and the sample code replicating this issue?

Please also download and try the latest version:
Aspose.Cells for .NET v7.3.2 and see if it resolves your issue.

4

Hi,

I was not able to replicate to generate any exception using the latest version:

Aspose.Cells for .NET 7.3.2
with the following code.

However, I see the formula does not generate correct results but in order to further look into it, please provide us your sample expected output xls/xlsx file containing your formula.

I have attached the output xlsx file for your reference.

C#


Workbook workbook = new Workbook();


Worksheet data = workbook.Worksheets[0];


data.Cells[0, 0].SetArrayFormula(“=VLOOKUP(F1&K1, IF({{1,0}}, Reference!$F$2:$F$501&Reference!$G$2:$G$501, Reference!$H$2:$H$501), 2, FALSE)”, 1, 1);


workbook.CalculateFormula();


workbook.Save(“output.xlsx”);


5

Hi,

Can you try the following code? It will throw the error.

C#

Workbook wb = new Workbook();
wb.Worksheets.Clear();

Worksheet refer = wb.Worksheets.Add(“Reference”);
refer.Cells[“A1”].PutValue(1);
refer.Cells[“B1”].PutValue(1);
refer.Cells[“C1”].PutValue(1);
refer.Cells[“A2”].PutValue(1);
refer.Cells[“B2”].PutValue(2);
refer.Cells[“C2”].PutValue(2);
refer.Cells[“A3”].PutValue(1);
refer.Cells[“B3”].PutValue(3);
refer.Cells[“C3”].PutValue(3);
refer.Cells[“A4”].PutValue(1);
refer.Cells[“B4”].PutValue(4);
refer.Cells[“C4”].PutValue(4);
refer.Cells[“A5”].PutValue(2);
refer.Cells[“B5”].PutValue(1);
refer.Cells[“C5”].PutValue(5);
refer.Cells[“A6”].PutValue(2);
refer.Cells[“B6”].PutValue(2);
refer.Cells[“C6”].PutValue(6);
refer.Cells[“A7”].PutValue(2);
refer.Cells[“B7”].PutValue(3);
refer.Cells[“C7”].PutValue(7);
refer.Cells[“A8”].PutValue(2);
refer.Cells[“B8”].PutValue(4);
refer.Cells[“C8”].PutValue(8);

Worksheet data = wb.Worksheets.Add(“Data”);
data.Cells[“A1”].PutValue(2);
data.Cells[“B1”].PutValue(2);
data.Cells[“C1”].SetArrayFormula("=VLOOKUP(A1&B1, IF({1,0}, Reference!$A$1:$A$8&Reference!$B$1:$B$8, Reference!$C$1:$C$8), 2, FALSE)", 1, 1);
data.Cells[“D1”].Formula = “=C1”;

wb.CalculateFormula();

6

Hi,

Thanks for your sample code.

We were able to replicate this exception using the latest version.

We have logged this issue in our database. We will look into it and fix this issue. Once the issue is fixed or we have some other update for you, we will let you know asap.

This issue has been logged as CELLSNET-41090.

7

Hi,

Thanks for using Aspose.Cells.

We were working on this issue and have fixed it. We will soon release a fix after some additional testing in next coming few days.

Once, the fix is released, we will let you know by posting on this thread.

8

Hi,

We have fixed this issue.

Please download and try this fix: Aspose.Cells for .NET v7.3.2.1 and let us know your feedback.

9

The issues you have found earlier (filed as CELLSNET-41090) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by aspose.notifier.