Sorting Data in Microsoft Excel
To sort data in Microsoft Excel:
- Select Data from the Sort menu.
The Sort dialog is displayed.
- Select a sorting option.
Generally, sorting is performed on a list - defined as a contiguous group of data where the data is displayed in columns.
The Sort dialog box in Microsoft Excel
Sorting Data with Aspose.Cells
Aspose.Cells provides the
DataSorter class used to sort data in ascending or descending order. The class has some important members, for example, methods like
setOrder2. These members are used to define sorted keys and specify the key sort order.
You have to define keys and set the sort order before implementing data sorting. The class provides the
sort method used to perform data sorting based on the cell data in a worksheet.
sort method accepts the following parameters:
Cells, the cells of the worksheet.
CellArea, the range of cells. Define the cell area before applying data sorting.
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:
This example uses the template file "Book1.xls" created in Microsoft Excel.
Template Excel file complete with data
After executing the code below, data is sorted appropriately as you can see from the output Excel file.
Output Excel file after sorting data
Sorting data with background colour
Excel provides feature to sort data based on background colour. Same feature is provided with using Aspose.Cells using DataSorter where SortOnType.CellColor can be used in addKey() to sort data based on background color. All the cells which contain specified color in the addKey(), function are placed on top or bottom according to the SortOrder setting and order of the rest of the cells is not changed at all.
Following are the sample files which can be downloaded for testing this feature:
// Load the source Excel file
Workbook workbook = new Workbook(srcDir + "sampleBackgroundFile.xlsx");
// Instantiate data sorter object
DataSorter sorter = workbook.getDataSorter();
// Add key for Column B, Sort it in descending order with background color red
sorter.addKey(1, SortOnType.CELL_COLOR, SortOrder.DESCENDING, Color.getRed());
// Sort the data based on the key
sorter.sort(workbook.getWorksheets().get(0).getCells(), CellArea.createCellArea("A2", "C6"));
// Save the output file
workbook.save(outDir + "outputSampleBackgroundFile.xlsx");