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);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:
ListObject.AutoFilter.Sorter.Sort();
- Open the input file, populate the tables
- Re-size the tables to include all added data.
- Extends the array formula to fill the whole column containing the array formula
- Sorts the “Department” table bases on column index 0 using Aspose.Cells.SortOrder.Ascending
- Calculate the workbook’s formulas
- Refresh the AutoFilter
- 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”.