Search code examples
javasortingasposeaspose-cells

Aspose Sort Cells based on Row as Key in java

I have an Excel File with Some Columns merged as shown in the Screen Shot, I want to Sort the TOP Columns in ascending order and their Corresponding values, and then I'll sort the COLUMN1, COLUMN2, Column3, But Before I want to Sort the TOP Column Headers, For Eg: TOP COLUMN A should Come first and the Corresponding Sub column and the values of those Six TOP columns.

Content area I was able to sort, but how will I be able to achieve Sorting for the TOP Cols Headers?

BEFORE

AFTER

Solution

  • Since, you want to sort data from left to right, you need to set the following property to true.

    Workbook.getDataSorter().setSortLeftToRight(true);

    Please see the following code. It sorts data from left to right by row 3. You can add more levels too.

    Java

    // Load your Excel file
Workbook wb = new Workbook(dirPath + "sort.xlsx");

// For the first key, we want to sort by assending order
// And we want to sort by row not by column
// using SortLeftToRight property
wb.getDataSorter().setOrder1(SortOrder.ASCENDING);
wb.getDataSorter().setSortLeftToRight(true);

// Access first worksheet
Worksheet ws = wb.getWorksheets().get(0);

// Specify the range of cells
CellArea ca = CellArea.createCellArea("A1", "I7");

// We want to sort by row 3, since index starts from 0
// So 2 means 3
wb.getDataSorter().setKey1(2);

// Sort the workbook data
wb.getDataSorter().sort(ws.getCells(), ca);

// Save the output Excel file
wb.save(dirPath + "output.xlsx");

    Note: I am working as Developer Evangelist at Aspose