Data Sorting with C++

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 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 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 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 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.

#include <iostream>
#include "Aspose.Cells.h"
using namespace Aspose::Cells;

int main()
{
    Aspose::Cells::Startup();

    // Source directory path
    U16String srcDir(u"..\\Data\\01_SourceDirectory\\");

    // Output directory path
    U16String outDir(u"..\\Data\\02_OutputDirectory\\");

    // Path of input Excel file
    U16String inputFilePath = srcDir + u"book1.xls";

    // Path of output Excel file
    U16String outputFilePath = outDir + u"output.out.xls";

    // Create workbook
    Workbook workbook(inputFilePath);

    // Get the workbook DataSorter object
    DataSorter sorter = workbook.GetDataSorter();

    // Set the first order for the DataSorter object
    sorter.SetOrder1(SortOrder::Descending);

    // Define the first key
    sorter.SetKey1(0);

    // Set the second order for the DataSorter object
    sorter.SetOrder2(SortOrder::Ascending);

    // Define the second key
    sorter.SetKey2(1);

    // Create a cells area (range)
    CellArea ca = CellArea::CreateCellArea(0, 0, 13, 1);

    // Sort data in the specified data range (A1:B14)
    sorter.Sort(workbook.GetWorksheets().Get(0).GetCells(), ca);

    // Save the Excel file
    workbook.Save(outputFilePath);

    std::cout << "Data sorted successfully!" << std::endl;

    Aspose::Cells::Cleanup();
}

If you want to sort LeftToRight, use the DataSorter::GetSortLeftToRight() method.

Sorting Data with Background Color

Excel provides a feature to sort data based on the background color. The same feature is available in Aspose.Cells through DataSorter, where SortOnType::CellColor can be used in AddKey() to sort data based on the background color. All the cells that contain the specified color in AddKey() are placed at the top or bottom according to the SortOrder setting, and the order of the remaining cells is not changed.

The following sample files can be downloaded to test this feature:

sampleBackGroundFile.xlsx

outputsampleBackGroundFile.xlsx

#include <iostream>
#include "Aspose.Cells.h"
using namespace Aspose::Cells;

int main()
{
    Aspose::Cells::Startup();

    // Source directory path
    U16String srcDir(u"..\\Data\\01_SourceDirectory\\");

    // Output directory path
    U16String outDir(u"..\\Data\\02_OutputDirectory\\");

    // Path of input Excel file
    U16String inputFilePath = srcDir + u"CellsNet46500.xlsx";

    // Path of output Excel file
    U16String outputFilePath = outDir + u"outputSortData_CustomSortList.xlsx";

    // Create a workbook object and load template file
    Workbook workbook(inputFilePath);

    // Instantiate a DataSorter object
    DataSorter sorter = workbook.GetDataSorter();

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

    // Sort the data based on the key
    sorter.Sort(workbook.GetWorksheets().Get(0).GetCells(), CellArea::CreateCellArea(u"A2", u"C6"));

    // Save the output file
    workbook.Save(outputFilePath);

    std::cout << "Data sorted successfully!" << std::endl;

    Aspose::Cells::Cleanup();
}

Advanced Topics

Data Validation with C++ Get Cell String Value with and without Formatting with C++