Free Support Forum - aspose.com

Problem with Sorting and Grouping via code

Aspose.Cells Product Family
#1

Hi,

i have code that create and excel file that needs to include groups and be sorted.

When i'm using the below code to sort, the action cause the excel to group the wrong lines since they are re-ordered.

Am i missing something?

how can i group lines and also sort them?

Thanks in advance,

Asaf

sorting code:

#region Do Sort

DataSorter dataSorter = workBook.DataSorter;

CellArea ca = new CellArea();

dataSorter.Key1 = dataExportArgs.ColumnIDToSortBy;

if (dataExportArgs.SortAssending)

dataSorter.Order1 = SortOrder.Ascending;

else

dataSorter.Order1 = SortOrder.Descending;

ca.StartRow = DataExportConst.firstDataRow;

ca.StartColumn = DataExportConst.firstColumn;

ca.EndColumn = dataExportArgs.Columns.Count;

ca.EndRow = rowIndex;

dataSorter.Sort(MainSheet.Cells, ca);

#endregion

#2

Hi,

Thank you for considering Aspose.

We have found an issue when using DataSorting and GroupingRows together. We will look into it and get back to you soon.

Thank You & Best Regards,

#3

Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

Thank you for considering Aspose.

Please try the attached latest version of Aspose.Cells. We have fixed the issue regarding grouping and sorting.

Thank You & Best Regards,

#4

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

This message was posted using Notification2Forum from Downloads module by aspose.notifier.

Note: Just for your knowledge. In the new release v4.8.0, we have merged Aspose.Grid suite to Aspose.Cells for .NET msi installer as Aspose.Grid would be no longer offered as a separate product now. You need to install the installer (uninstall older one first if you have) and use only Aspose.Cells.dll library in your project for your need. You may also take advantage using Aspose.Cells Grid suite though.

#5

Hi,

could you please advice how the code should be imlemented in order to both group lines and sort them?

is there an order of the required steps, meaning, first group lines then sorts them (or vice versa)?

Thanks,

Asaf

#6

Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

Thank you for considering Aspose.

Well, it depends on your requirement whether you want to sort the data first and then group the rows based on sorted data or first group the rows and then perform the sorting. You may try it either way using Aspose.Cells as well. If you find any issue, please share your desired excel file (by creating in MS Excel) and we will check it soon. Also, following is my sample code which sorts the data first and then group the first two rows for your reference:

Sample Code:

//Instantiating a Workbook object

Workbook workbook = new Workbook();

//Accessing the first worksheet in the Excel file

Worksheet worksheet = workbook.Worksheets[0];

Cells cells = worksheet.Cells;

cells[0, 0].PutValue("WorldSoccerCupwinner");

cells[0, 1].PutValue("Year");

cells[0, 2].PutValue("Location");

cells[1, 0].PutValue("<?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-com:office:smarttags" />Italy");

cells[1, 1].PutValue(2006);

cells[1, 2].PutValue("Germany");

cells[2, 0].PutValue("Brazil");

cells[2, 1].PutValue(2002);

cells[2, 2].PutValue("Korea");

cells[3, 0].PutValue("France");

cells[3, 1].PutValue(1998);

cells[3, 2].PutValue("France");

cells[4, 0].PutValue("Brazil");

cells[4, 1].PutValue(1994);

cells[4, 2].PutValue("USA");

cells[5, 0].PutValue("Germany");

cells[5, 1].PutValue(1990);

cells[5, 2].PutValue("Italy");

cells[6, 0].PutValue("Argentina");

cells[6, 1].PutValue(1986);

cells[6, 2].PutValue("Maxico");

cells[7, 0].PutValue("Italy");

cells[7, 1].PutValue(1982);

cells[7, 2].PutValue("Spain");

cells[8, 0].PutValue("Argentina");

cells[8, 1].PutValue(1978);

cells[8, 2].PutValue("Argentina");

cells[9, 0].PutValue("Germany");

cells[9, 1].PutValue(1974);

cells[9, 2].PutValue("Germany");

DataSorter sorter = workbook.DataSorter;

sorter.Order1 = Aspose.Cells.SortOrder.Ascending;

//Define the first key.

sorter.Key1 = 0;

//Create a cells area (range).

CellArea ca = new CellArea();

//Specify the start row index.

ca.StartRow = 1;

//Specify the start column index.

ca.StartColumn = 0;

//Specify the last row index.

ca.EndRow = 9;

//Specify the last column index.

ca.EndColumn = 3;

//Sort data in the specified data range (A1:B14)

sorter.Sort(workbook.Worksheets[0].Cells, ca);

//Grouping first 2 rows

worksheet.Cells.GroupRows(1, 2);

//Saving the modified Excel file in default (that is Excel 2003) format

workbook.Save("C:\\output2.xls", FileFormatType.Default);

Thank You & Best Regards,

#7

Hi,

It's been a while since i've reported this issue of grouping and sorting together - only now i've found time to try and achieve any progress.

My use-case is as follow:

1) Get raw data from the DB

2) Via code, group all 'Child' rows to their 'Parent' row

3) Sort the data according to what the user specified

Now, the issue is, as i've mentioned before, when doing the Sorting after grouping the data, all 'child' rows get reordered - seems that the sorter does not take into consideration the groups created on the WorkBook.

Improtant - Sorting MUST comes AFTER the Grouping action (see code flow below).

Required behavior - Sort both the Parents rows and thier child rows according to the specified column WITHOUT breaking the groups.

This behavior is not supported on Aspose.Cells latest version (we are using version 4.8.2.4).

Please advice whether this issue can be fixed.

#region Create Groups

foreach (int id in groupsDIC.Keys)

MainSheet.Cells.GroupRows(groupsDIC[id].FirstRowOfGroup + 1, groupsDIC[id].LastRowOfGroup);

MainSheet.Outline.SummaryRowBelow = false;

#endregion

#region Do Sort

DataSorter dataSorter = workBook.DataSorter;

CellArea ca = new CellArea();

dataSorter.Key1 = dataExportArgs.ColumnIDToSortBy;

if (dataExportArgs.SortAssending)

dataSorter.Order1 = SortOrder.Ascending;

else

dataSorter.Order1 = SortOrder.Descending;

ca.StartRow = DataExportConst.firstDataRow;

ca.StartColumn = DataExportConst.firstColumn;

ca.EndColumn = dataExportArgs.Columns.Count;

ca.EndRow = rowIndex;

dataSorter.Sort(MainSheet.Cells, ca);

#endregion

Best regard,

Asaf

#8

Hi Asaf,

I have tested your scenario but could not reproduce the issue you have mentioned. Aspose.Cells for .NET just works fine in the same way as MS Excel.

We appreciate if you could provide us the sample console application with the excel file(s), zip the project and post it here to show the issue, we will check it soon.

Thank you.