Data Sorting

Sorting Data in Microsoft Excel

To sort data in Microsoft Excel:

  1. Select Data from the Sort menu. The Sort dialog is displayed.
  2. 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

todo:image_alt_text

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 setKey1 … setKey2 and setOrder1 … 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.

The 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

todo:image_alt_text

After executing the code below, data is sorted appropriately as you can see from the output Excel file.

Output Excel file after sorting data

todo:image_alt_text

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(DataSorting.class) + "data/";
// Instantiate a new Workbook object.
Workbook workbook = new Workbook(dataDir + "book1.xls");
// Get the workbook datasorter object.
DataSorter sorter = workbook.getDataSorter();
// Set the first order for datasorter object.
sorter.setOrder1(SortOrder.DESCENDING);
// Define the first key.
sorter.setKey1(0);
// Set the second order for datasorter object.
sorter.setOrder2(SortOrder.ASCENDING);
// Define the second key.
sorter.setKey2(1);
// Sort data in the specified data range (CellArea range: A1:B14)
CellArea cellArea = new CellArea();
cellArea.StartRow = 0;
cellArea.StartColumn = 0;
cellArea.EndRow = 13;
cellArea.EndColumn = 1;
sorter.sort(workbook.getWorksheets().get(0).getCells(), cellArea);
// Save the excel file.
workbook.save(dataDir + "DataSorting_out.xls");
// Print message
System.out.println("Sorting Done Successfully");

Sorting data with background color

Excel provides the feature to sort data based on the background color. The same feature is provided using Aspose.Cells using DataSorter where SortOnType.CELL_COLOR can be used in addKey() to sort data based on the 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:

sampleBackGroundFile.xlsx

outputsampleBackGroundFile.xlsx

Sample Code

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// Load the Excel file.
Workbook wb = new Workbook(srcDir + "sampleInlineCharts.xlsx");
// Access the sheet
Worksheet ws = wb.getWorksheets().get(0);
// Set the print area.
ws.getPageSetup().setPrintArea("D2:M20");
// Initialize HtmlSaveOptions
HtmlSaveOptions options = new HtmlSaveOptions();
// Set flag to export print area only
options.setExportPrintAreaOnly(true);
//Save to HTML format
wb.save(outDir + "outputInlineCharts.html",options);

Advance topics