Custom data sorting

Aspose.Cells Product Family
1

Hello,


I saw in 7.5.2 release notes that aspose now supports Custom Data Sorting but I can’t find any documentation or class name that could help me in implementing such function. Could you tell me where I can find the matching exemple ?

I have to do 2 things, custom sort data in a sheet. And custom sort items in pivot table.

here are the way I do it in excel Interop way :

In an excel output, on one sheet I do custom sort data for the moment using interop excel :
worksheetTb.Select();
worksheetTb.Sort.SortFields.Clear();
worksheetTb.Sort.SortFields.Add(
worksheetTb.get_Range(“Q1:Q9999”),
Microsoft.Office.Interop.Excel.XlSortOn.xlSortOnValues,
Microsoft.Office.Interop.Excel.XlSortOrder.xlAscending,
“ZZZ,SSSS,AAA,DDD,CCC,WWW”,
Microsoft.Office.Interop.Excel.XlSortDataOption.xlSortNormal);

var sort = worksheetTb.Sort;
sort.SetRange(worksheetTb.get_Range(“A1:U9999”));
sort.Header = Microsoft.Office.Interop.Excel.XlYesNoGuess.xlYes;
sort.MatchCase = false;
sort.Orientation = Microsoft.Office.Interop.Excel.XlSortOrientation.xlSortColumns;
sort.SortMethod = Microsoft.Office.Interop.Excel.XlSortMethod.xlPinYin;
sort.Apply();


then in another sheet, in a pivot table I sort Pivot table this way :

excelApp.ActiveSheet.PivotTables(“pivot”).PivotFields(“field1”).ClearAllFilters();

try
{
excelApp.ActiveSheet.PivotTables(“pivot”).PivotFields(“field1”).PivotItems("(blank)").Visible = false;
}
catch (Exception)
{
}
try
{
excelApp.ActiveSheet.PivotTables(“pivot”).PivotFields(“field1”).PivotItems(“ZZZ”).Visible = true;
}
catch (Exception)
{
}
try
{
excelApp.ActiveSheet.PivotTables(“pivot”).PivotFields(“field1”).PivotItems(“SSSS”).Visible = true;
}
catch (Exception)
{
}
try
{
excelApp.ActiveSheet.PivotTables(“pivot”).PivotFields(“field1”).PivotItems(“AAA”).Visible = true;
}
catch (Exception)
{
}
…

If you have any Idea how to do it with aspose, I would be happy to read this.

Regards,

Thierry
2

Hi Thierry,


Sorry for the delay.

Yes, Aspose.Cells for .NET does support custom data sorting. Could you give us template Excel file(s) that you could manually create in MS Excel to perform your desired task(s) upon data, save the file(s) and post it here, we will check and help you through.

Thank you.
3

Hello ! Thank you for your answer.


Here you will find the requested example.

There is 2 sheets with 2 custom orders
the first one, Datas , is sorted using this order aaa,ddd,ccc,bbb
the second one, Pivot, contains a Pivot table using this order : aaa,ccc,ddd,bbb

note the difference between the place of ccc and ddd items on both sheets.

thanks for you kindly help !

4

Hi,


Thanks for the sample file.

We have observed your requirements seeing your second and third sheets in your template file. We are working over your requirements/ task. We may provide you some code snippet to accomplish your task (if possible). A ticket is logged into our database with an id “CELLSNET-42150” for your task.

We will get back to you soon.

Thank you.


5

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

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

@prophetix,
Could you re-attach your sample application with template file(s) here as we lost your attachment(s) (during new forums migration process). We will try to sort out your issue soon.

Sorry for any inconvenience caused!