Data Sorting

Data sorting is one of Microsoft Excel’s many useful features. It allows users to order data to make it easier to scan. Aspose.Cells for Node.js via C++ lets developers sort worksheet data alphabetically or numerically which works in the same way as Microsoft Excel does to sort data.

Sorting Data in Microsoft Excel

To sort data in Microsoft Excel:

Select Data from the Sort menu. The Sort dialog will be 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.

Sorting Data with Aspose.Cells

Aspose.Cells for Node.js via C++ provides the DataSorter class used to sort data in ascending or descending order. The class has some important members, for example, properties like Key1 … Key3 and Order1 … Order3. 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 DataSorter.sort method used to perform data sorting based on the cell data in a worksheet.

The DataSorter.sort method accepts the following parameters:

Cells, the cells for the underlying worksheet.
CellArea, the range of cells. Define the cell area before applying data sorting.

This example uses the template file “Book1.xls” created in Microsoft Excel. After executing the code below, data is sorted appropriately.

	//For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
	const AsposeCells = require("aspose.cells.node");

	// Instantiate a new Workbook object.
	// Load a template file.
	var workbook = new AsposeCells.Workbook("book1.xls");
	// Get the workbook datasorter object.
	var sorter = workbook.getDataSorter();
	// Set the first order for datasorter object.
	sorter.setOrder1(AsposeCells.SortOrder.Descending);
	// Define the first key.
	sorter.setKey1(0);
	// Set the second order for datasorter object.
	sorter.setOrder2(AsposeCells.SortOrder.Ascending);
	// Define the second key.
	sorter.setKey2(1);
	// Create a cells area (range).
	var ca = new AsposeCells.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.getWorksheets().get(0).getCells(), ca);

	// Save the excel file.
	workbook.save("output.out.xls");

view raw Cells-Data-DataSorting-1.js hosted with ❤ by GitHub

If you want to sort LeftToRight, use the DataSorter.setSortLeftToRight attribute.

Sorting data with background color

Excel provides features to sort data based on the background color. The same feature is provided using Aspose.Cells for Node.js via C++ using DataSorter where SortOnType.CellColor can be used in DataSorter.addKey to sort data based on the background color. All the cells which contain specified color in the DataSorter.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

	//For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
	const AsposeCells = require("aspose.cells.node");

	// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
	// Create a workbook object and load template file
	var workbook = new AsposeCells.Workbook("CellsNet46500.xlsx");

	// Instantiate data sorter object
	var sorter = workbook.getDataSorter();

	// Add key for second column for red color
	sorter.addKey(1, AsposeCells.SortOnType.CellColor, AsposeCells.SortOrder.Descending, AsposeCells.Color.Red);

	// Sort the data based on the key
	sorter.sort(workbook.getWorksheets().get(0).getCells(), AsposeCells.CellArea.createCellArea("A2", "C6"));
	// Save the output file
	workbook.save("outputSortData_CustomSortList.xlsx");

view raw Cells-Data-SortDataInColumnWithBackgroundColor-1.js hosted with ❤ by GitHub

Advance topics

Data Validation Get Cell String Value with and without Formatting