How to refresh Sort of Table after Merge

Aspose.Cells Product Family
1

Hi !

When i merge a Table (2007/2010) with:
m_designer.SetDataSource(ds);
m_designer.Process();

In the merge document, my table don’t sort the column2 ! (see the file attachment)

The user is obliged to refresh himself sorting!

I tried to look into m_wb.Worksheets[0].ListObjects[0].?, but I have not found a method Sort.refresh


Thanks !

2

Hi,

I think you may get the AutoFilter’s DataSorter for the list object or table, e.g

DataSorter sorter = listObject.AutoFilter.Sorter;

Now use Aspose.Cells’ Data Sorting feature to accomplish your task. See the document for your reference:
Data Sorting

Thank you.

3

Hi Amjad Sahi !
I have a xlsx model with tab sorting (set manually through Excel)
when i merge this document, the column don’t sort (but the icon sorting appears in Excel… see my file attachment)

4

I try
var sort = m_wb.Worksheets[m.NomFeuille].ListObjects[i].AutoFilter.Sorter;
sort.Sort();

but not effect…

5

Hi,


I have tested your scenario with the following code and it works fine, the list object / table is sorted accordingly. See the attached output file. I used your attached file as an input file.

Sample code:

Workbook workbook = new Workbook(“e:\test2\tmp6F1D.tmp.xlsx”);
ListObject listObject = workbook.Worksheets[0].ListObjects[0];
DataSorter sorter = listObject.AutoFilter.Sorter;
//Set the first order for datasorter object.
sorter.Order1 = Aspose.Cells.SortOrder.Ascending;

//Define the first key.
sorter.Key1 = 2;

//Create a cells area (range).
CellArea ca = new CellArea();

//Specify the start row index.
ca.StartRow = 4;

//Specify the start column index.
ca.StartColumn = 2;

//Specify the last row index.
ca.EndRow = 7;

//Specify the last column index.
ca.EndColumn = 2;

//Sort data in the specified data range
sorter.Sort(workbook.Worksheets[0].Cells, ca);

workbook.Save(“e:\test2\out_tmp6F1D.tmp.xlsx”);

Thank you.
6

Hi Amjad Sahi !

Thanks !

But your solution is manually solution.
Ideally, I want this to happen automatically from the model.

From the Excel interface, the user selects the columns to sort. And when i merge with Aspose.Cells:
m_designer.SetDataSource(ds);
m_designer.Process();

I wish the new document automatically refresh the sorted columns.

Thanks and Happy new year !

7

Hi,


Well, I am afraid, you have to devise my suggested approach to do the sorting for the Table/List Object fields/data as there is not any automatic or other way for the task. If we have any, we will let you know here.

Thank you and wish you a happy new year to you as well from our side.
8

Hi !

Workaround (but use Excel Interop), for refresh Sort and Filter :

foreach (Microsoft.Office.Interop.Excel._Worksheet s in Globals.ThisAddIn.Application.ActiveWorkbook.Worksheets)
{
foreach (Microsoft.Office.Interop.Excel.ListObject lo in s.ListObjects)
{
Microsoft.Office.Interop.Excel.Sort so = lo.Sort;
if (so != null) so.Apply();
Microsoft.Office.Interop.Excel.AutoFilter af = lo.AutoFilter;
if (af != null) af.ApplyFilter();
lo.Refresh();
}
}
Globals.ThisAddIn.Application.WindowState = XlWindowState.xlMinimized; // Hack
Globals.ThisAddIn.Application.WindowState = XlWindowState.xlNormal;

There is no equivalent of Aspose Cells I suppose?

9

Hi,


Thanks for sharing the workaround regarding Excel Interop.

I am afraid, by using Aspose.Cells APIs, you have to do manually as I suggested earlier.

However, if in future, we have any direct or more compact approach we will share it here.

Thank you.
10

Amjad Sahi, Thanks for your reply !

11

Hi,

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

If you face any other issue, please feel free to post, we will like to help you further.

Besides if you are new to Aspose.Cells APIs, we will like to inform you that we have lately done some major enhancements and added new features in our product, so you should also download and give a try to latest offline demos of Aspose.Cells which you can find from theses links.

These demos can work with Visual Studio 2005 , 2008 or 2010 or 2012 or 2015or 2019. Please read the readme.txt file before running the demos.