Error While performing complex calculations

Hi,


I am using Aspose.cells in my company, one of ours needs is to use excel template file which contains array formulas. By writting data using Aspose and without calcute formulas with it, calculations perform well at the file opening; but the problem is, I want to save the file in pdf format, and I have this error

Error in calculating cell [[2]Feuil3!A1]: IndexOutOfRangeException



when applying worbook.CalcuteFormula method.


Here is an example of formula I use


{=SIERREUR(INDEX(Feuil2!$L$4:$P$35;PETITE.VALEUR(SI(INDEX(Feuil2!$L$4:$P35;;2)={“Correction”.“Preventive action”.“Corrective action”};LIGNE(INDIRECT(“1:”&LIGNES(Feuil2!$L$4:$P$35))));LIGNE(1:25));COLONNE(A:E));"")}


Best regards

Abdou


Hi,

Thanks for your posting and using Aspose.Cells.

Please download and try the latest version:
Aspose.Cells for .NET v8.8.0.2 and see if it makes any difference and resolves your issue.

If your issue still occurs with the latest version, then please provide us your sample code and source/template excel file which you are converting to Pdf and contains your mentioned formula. We will look into it and update you asap.

Hi,


Thanks for your quick reply, I used the lastest version and no difference, the issue is still here. Here is an example with the issue:

static void Main(string[] args)
{
var dataDir = Path.GetFullPath("…/…/Data/");
var wb = new Workbook(dataDir + “Template.xlsx”);
var dataTable = new DataTable(“Example”);

dataTable.Columns.Add(“Column 1”, typeof(string));
dataTable.Columns.Add(“Column 2”, typeof(string));
dataTable.Columns.Add(“Column 3”, typeof(string));
dataTable.Columns.Add(“Column 4”, typeof(string));

var dr = dataTable.NewRow();

dr[0] = “Name 1”;
dr[1] = “Analysis”;
dr[2] = “Pierre Dupond”;
dr[3] = “21/04/2015”;

dataTable.Rows.Add(dr);

dr = dataTable.NewRow();
dr[0] = “Name 2”;
dr[1] = “Correction”;
dr[2] = “Pierre Dupond”;
dr[3] = “28/04/2015”;

dataTable.Rows.Add(dr);

dr = dataTable.NewRow();
dr[0] = “Name 2”;
dr[1] = “Analysis”;
dr[2] = “John Doe”;
dr[3] = “28/04/2015”;

dataTable.Rows.Add(dr);

dr = dataTable.NewRow();
dr[0] = “Name 3”;
dr[1] = “Preventive action”;
dr[2] = “John Doe”;
dr[3] = “28/04/2015”;

dataTable.Rows.Add(dr);

dr = dataTable.NewRow();
dr[0] = “Name 4”;
dr[1] = “Warning”;
dr[2] = “John Doe”;
dr[3] = “28/04/2015”;

dataTable.Rows.Add(dr);

var sheet = wb.Worksheets[0];

sheet.Cells.ImportDataTable(dataTable, false, 4, 11, false);

wb.CalculateFormula();

wb.Save(dataDir + “output.pdf”);
}

You can try without CalculateFormula method and change the output format to .xlsx, you’ll have no error and calculation will be done at the file opening with MS Excel.

The array formula is in the range [A5:E29]

Best regards

Hi,

Thanks for your posting and using Aspose.Cells.

We were able to observe this issue by using your template file and sample code. We have tested this issue with the latest version:
Aspose.Cells for .NET v8.8.0.3 but it also generates exception on Workbook.CalculateFormula() method.

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

This issue has been logged as

  • CELLSNET-44430 - Error occurs while performing complex calculations

I have also showed the stack trace of the generated exception for a reference.

Exception:
Aspose.Cells.CellsException was unhandled
HResult=-2146232832
Message=Error in calculating cell [[0]Feuil2!A5]: IndexOutOfRangeException: Index was outside the bounds of the array.
Source=Aspose.Cells
StackTrace:
at . ( )
at Aspose.Cells.Workbook.CalculateFormula(CalculationOptions options)
at Aspose.Cells.Workbook.CalculateFormula(Boolean ignoreError, ICustomFunction customFunction)
at Aspose.Cells.Workbook.CalculateFormula()
at CellsNET.Program.f1() in d:\Aspose\Projects\NET\AsposeProject\CellsNET\Program.cs:line 103
at CellsNET.Program.Main(String[] args) in d:\Aspose\Projects\NET\AsposeProject\CellsNET\Program.cs:line 30
at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()
InnerException:

Hi,

Thanks for using Aspose.Cells.

This is to inform you that we have fixed your issue CELLSNET-44430 now. We will soon provide the fix after performing QA and including other enhancements and fixes.

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


This message was posted using Notification2Forum from Downloads module by Aspose Notifier.

Thanks for your reactivity, I’ll test it ASAP