Using version 8.5.0.
I’m converting a Report Tool from an old MS Access application to a VB.NET application, using Aspose.Cells.
The way the Tool Works is that it opens a blank report , which is an Excel template that contains several Tables.
I can access these using the ListObject and populate it with data using the ImportDataReader function.
But after that’s finished, I’d like to apply the default sort that each table already have in place in the Excel template.
Is there any way to do this - e.g. by reading the tables sort and then use this to construct a DataSorter with the same sort specifications and applying it to the area that contains the data (then I can run through each of the tables applying the sorter using the same piece of code)?
Or do I have to build each DataSorter from scratch and apply it to the relevant table (they all have different data sorting and on top of that, the template is not maintained by me so it would be preferable if I didn’t have to change the code when they wish to make changes to the default sort order of a specific table)?
Regards,
Martin
Hi,
Thanks for your posting and using Aspose.Cells.
Once, you import your data from your database to your worksheet, then you will have to sort your data using the technique mentioned in this article.
( Data Sorting|Documentation )
If you find any problem, then please provide us your sample excel files and let us know the data range you want to sort. We will look into your issue and help you asap.
Hi.
I already looked at that article and while it does explain how to sort a range it doesn’t quite do the trick for me.
If you look a tthe attached Excel template, which is used for one of our reports, you can see that the tables already have sorting defined.
But when I populate the tables with data using ImportDataReader, the sort isn’t applied. What I’m looking for is a way to “read” the sort already defined on each table so that I can build a DataSorter and apply it to the tables afterwards.
By doing it that way, we can make changes to the sort order of each table without having to alter the code.
Is that possible using Aspose,Cells?
Regards,
Martin
Hi Martin,
Thanks for your posting and using Aspose.Cells.
Please see the following sample code that illustrates how to sort out your list object. Please check the source excel file used in this code and the output excel files generated by the code for your reference.
If your column is Jth column, then your sort key will be 9 and if your Hth column, then your sort key will be 7, similarly if your column is Ath or Bth, then your sort keys will be 0 and 1 respectively.
C#
Workbook workbook = new Workbook(“source.xlsx”);
Worksheet worksheet = workbook.Worksheets[0];
ListObject lo = worksheet.ListObjects[0];
//Sort by column J which is 9th column
lo.AutoFilter.Sorter.Key1 = 9;
lo.AutoFilter.Sorter.Order1 = Aspose.Cells.SortOrder.Descending;
lo.AutoFilter.Sorter.Sort();
workbook.Save(“output-ColumnJ.xlsx”);
//Sort by column H which is 7th column
lo.AutoFilter.Sorter.Key1 = 7;
lo.AutoFilter.Sorter.Order1 = Aspose.Cells.SortOrder.Descending;
lo.AutoFilter.Sorter.Sort();
workbook.Save(“output-ColumnH.xlsx”);
Hi Shakeel.
Thanks for the sample code - it almost works 
But it seems like if there is a total row in the table, then that’s also included as a row when you apply the sort, which I believe is an error.
If you save the attached SortDataTemplate in C:\Temp and execute the following code and open the file SortTest you’ll see that Excel complains that the file contains unreadable data and if you proceed to open it, the table (that only contained one blank line in the template) has been sorted in such a way that the total row is now above the blank line.
Dim workbook As Workbook = New Workbook(“C:\temp\SortDataTemplate.xltx”)
Dim currentSheet As Worksheet = workbook.Worksheets(0)
Dim tableList As Tables.ListObject = currentSheet.ListObjects(“Table1”)
tableList.AutoFilter.Sorter.Sort()
workbook.Save(“C:\temp\sortTest.xlsx”, SaveFormat.Xlsx)
And another odd thing is that if I import data to the table, using ImportDataReader, the blank line remains at the bottom of the table, just above the total and I have to delete it afterwards using DeleteRow.
In Excel, if you import data to an empty table (which a table that only contains one blank line is considered since you cannot have a table that only has a header and a total and no datarows), the blank line is removed automatically so that’s something that you could consider changing.
Regards,
Martin
Hi Martin,
Thanks for your posting and using Aspose.Cells.
We were able to observe this issue by executing your sample code with your source excel file using the latest version: Aspose.Cells
for .NET v8.5.0.2. Sorting Table with Total Row makes the Excel file unreadable.
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-43765 - Sorting Table with Total Row makes the Excel file unreadable
I have attached the output excel file generated with the following code. The output excel file is corrupt and does not open in Microsoft Excel.
VB.NET
Dim filePath As String = “F:\Shak-Data-RW\Downloads\SortDataTemplate.xltx”
Dim workbook As Workbook = New Workbook(filePath)
Dim currentSheet As Worksheet = workbook.Worksheets(0)
Dim tableList As Tables.ListObject = currentSheet.ListObjects(“Table1”)
tableList.AutoFilter.Sorter.Sort()
workbook.Save(filePath & “.out.xlsx”, SaveFormat.Xlsx)
Hi,
Thanks for your using Aspose.Cells.
Please download and try the latest fix: Aspose.Cells for .NET v8.5.0.4 and let us know your feedback.