We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Cannot apply Aspose.Cells.SortOrder to tables with an Array Formula

Hi,

We are having an issue with creating a file containing an Array Formula inside a table. Creating the file works fine if the table containing the array formula is NOT sorted. If however the table is sorted using Aspose.Cells.SortOrder i.e.

ListObject.AutoFilter.Sorter.AddKey(0, Aspose.Cells.SortOrder.Ascending);
ListObject.AutoFilter.Sorter.Sort();
I have attached a small project to demonstrate the issue. Click the “…” button in the application to bring up an file select dialog, select the input file “input file for sorting and array formula issue.xlsx” then click the “Execute” button and the code will do the following:
  1. Open the input file, populate the tables
  2. Re-size the tables to include all added data.
  3. Extends the array formula to fill the whole column containing the array formula
  4. Sorts the “Department” table bases on column index 0 using Aspose.Cells.SortOrder.Ascending
  5. Calculate the workbook’s formulas
  6. Refresh the AutoFilter
  7. Saves the Workbook as “generated file.xlsx” in the same folder as the input file.

The generated file will not open successfully when you try to open it.

Additional Information:

  • The Sort will work fine if the table containing the array formula does not change it’s size i.e. using “ListObject.Resize()”.
  • If you comment out line 93-94 in Form1.cs the file generated will be stable and can be opened successfully; Of course the table containing the array formula will not have a sorting setting set.
  • If I did something wrong in the way set the array formula in all cells within the array formula’s column, then the issue could be that my process in step 3 above (“ExtendFormula” method in the project) is wrong. If this is the case please suggest the correct way of making sure the formula is present in the entire column
  • If my “ExtendFormula()” method is not called and the formula is not in all cells within the array formula’s column, an exception is fired as shown in the attached snippet “Error when calculating formulas.PNG”.

Hi,

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

We were able to observe this issue. The sample project generates corrupt file. We have logged this issue in our database. We will look into it and resolve 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-41806.

Hi,

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

We have fixed the issue.

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

Hi,

Thank you for providing a fix for the issue; I have updated our project to version 7.5.0.5 of Aspose.Cells and we now seem to be able to sort tables with array formulas.

Hi,


Good to know that your issue is resolved by the new fix. We have closed your ticket now.

Feel free to contact us any time if you need help or have some other issue, we will be happy to assist you soon.

Thank you.

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


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