Data Sorting is a handy and versatile feature of MS Excel. The sorting feature is found by going to Data>Sort menu option to access the Sort dialog box. Generally, sorting is performed on a list, which is defined as a contiguous group of data where the data is displayed in columns. Aspose.Cells allows you to sort Worksheet data alphabetically or numerically. It works in the same way as MS Excel does to sort data.

The Sort dialog box of MS Excel

 

 

Figure: Sort Dialog Box

 

Aspose.Cells provides DataSorter class used to sort data in Ascending or Descending order. The class has some important members i.e. Properties like Key1....Key3 and Order1....Order3 for .NET version of the component, similarly Methods like setKey1....setKey3 and setOrder1....setOrder3 for JAVA version of the component. These members are used to define sorted keys and specify the sort orders for the keys. You will have to define your desired keys and set orders before you implement data sorting. The class provides Sort method that is used to perform data sorting operation based on a worksheet's cells data. The Sort method accepts the following parameters:

• Aspose.Cells.Cells denotes the cells for underlying worksheet.
• Aspose.Cells.CellArea represents the cellarea range. you have to define your desired cellarea first before applying data sorting.

Example:

This example shows how to sort data using Aspose.Cells API. The example uses a template file "Book1.xls" and sorts data for data range (A1:B14) in the first worksheet:

Take a look at the template file "Book1.xls" created in MS Excel

 

 

Figure: Template Excel file having data

 

Here is the sample code

[C#]

 

 

//Instantiate a new Workbook object.

Workbook workbook = new Workbook();

//Load a template file.

workbook.Open("d:\\test\\Book1.xls");

//Get the workbook datasorter object.

DataSorter sorter = workbook.DataSorter;

//Set the first order for datasorter object.

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

//Define the first key.

sorter.Key1 = 0;

//Set the second order for datasorter object.

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

//Define the second key.

sorter.Key2 = 1;

//Create a cells area (range).

CellArea ca = new CellArea();

//Specify the start row index.

ca.StartRow = 0;

//Specify the start column index.

ca.StartColumn = 0;

//Specify the last row index.

ca.EndRow = 13;

//Specify the last column index.

ca.EndColumn = 1;

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

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

//Save the excel file.

workbook.Save("d:\\test\\outBook.xls");

 

 

[VB]

 

 

'Instantiate a new Workbook object.

Dim workbook As Workbook = New Workbook

'Load a template file.

workbook.Open("d:\test\Book1.xls")

'Get the workbook datasorter object.

Dim sorter As DataSorter = workbook.DataSorter

'Set the first order for datasorter object

sorter.Order1 = Aspose.Cells.SortOrder.Descending

'Define the first key.

sorter.Key1 = 0

'Set the second order for datasorter object.

sorter.Order2 = Aspose.Cells.SortOrder.Ascending

'Define the second key.

sorter.Key2 = 1

'Create a cells area (range).

Dim ca As CellArea = New CellArea

'Specify the start row index.

ca.StartRow = 0

'Specify the start column index.

ca.StartColumn = 0

'Specify the last row index.

ca.EndRow = 13

'Specify the last column index.

ca.EndColumn = 1

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

sorter.Sort(workbook.Worksheets(0).Cells, ca)

'Save the excel file.

workbook.Save("d:\test\outBook.xls")

 

 

[JAVA]

 

 

//Instantiate a new Workbook object.

Workbook workbook = new Workbook();

//Load a template file.

workbook.open("d:\\test\\Book1.xls");

//Get the workbook datasorter object.

DataSorter sorter = workbook.getDataSorter();

//Set the first order for datasorter object.

sorter.setOrder1(SortOrderType.DESCENDING);

//Define the first key.

sorter.setKey1(0);

//Set the second order for datasorter object.

sorter.setOrder2(SortOrderType.ASCENDING);

//Define the second key.

sorter.setKey2(1);

                           

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

sorter.sort(workbook.getWorksheets().getSheet(0).getCells(),new CellArea("A1","B14"));

//Save the excel file.

workbook.save("d:\\test\\outBook.xls");

 

After executing the above code, data is sorted appropriately as you may see the output excel file

 

 

Figure: Output Excel file after sorting data

 

Note: If you want to sort LeftToRight, you can use DataSorter.SortLeftToRight attribute for your requirement.