ASPOSE ROW HEADERS SORT in JAVA

[aymarzapak]

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?
ExcelFile Sample.PNG (37.0 KB)

[shakeel.faiz]

@arshad3641

Please provide us your Excel file as you have shown in your image. Also provide us your Expected Excel file after sorting. Please create Expected Excel file manually using Microsoft Excel. Also let us know the steps you performed to in Microsoft Excel to achieve your expected results. We will look into it and achieve the same thing using Aspose.Cells API and share the sample code with you. Thanks for your cooperation in this regard and have a good day.

[aymarzapak]

Hi Shakeel, Im trying to Sort left to right for level 1 row and next level row 4 for columns starting from TOPCOLA, Have attached a sample for before and after excelsample.zip (13.7 KB)

[aymarzapak]

I did the following steps as shown in the pic
save1.PNG (107.4 KB)

[shakeel.faiz]

@arshad3641

You want to sort by row and not by column. If you want to sort by row then set the following property to true.

Workbook.getDataSorter().setSortLeftToRight(true);

Please check the source Excel file used in the code and output Excel file generated by the code for your reference.

Download Link:
source-and-output-Excel-files.zip (13.1 KB)

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");

[aymarzapak]

Hi Shakeel Thanks , It working,Can ypou help me how to sort, with first by row1, then by row 2 , then by row 3, like consecutive succesfull 3 rows

[aymarzapak]

how to add levels, I’m trying to do sorter.setKey2(3); but its not reflecting

[shakeel.faiz]

@arshad3641

Please see the following sample code and its output Excel file. It sorts with Row 3 and then Row 5 as shown in this screenshot.

Download Output Excel File
output-excel.zip (6.8 KB)

Screenshot
sc.png (65.9 KB)

I have added just two lines inside the code which are these

// -----------------------------------------------------
// Do it for second key
// We are now sorting by row 5
wb.getDataSorter().setOrder2(SortOrder.ASCENDING);
wb.getDataSorter().setKey2(4);
// -----------------------------------------------------

Here is the full sample code for your reference.

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);

// -----------------------------------------------------
// Do it for second key
// We are now sorting by row 5
wb.getDataSorter().setOrder2(SortOrder.ASCENDING);
wb.getDataSorter().setKey2(4);
// -----------------------------------------------------

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

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

[aymarzapak]

Thanks a lot Shakeel

[aymarzapak]

Hi, Shakeel, Can you update me how to sort Month Row in Aspose, It gets sorted according to alphabets, is there anyway to sort the same

[shakeel.faiz]

@arshad3641

Please let us know how do you do it in Microsoft Excel. It will give us the idea how to do it in Aspose.Cells. If the feature is available in Microsoft Excel but not present in Aspose.Cells, we will log a New Feature request in our database to support your needs.

[aymarzapak]

In Microsoft Excel we sort it by Sleecting orber by by Custom List, which has this month order.added a sample Screen shortsort_custom_add6.png (9.3 KB)

[shakeel.faiz]

@arshad3641

Aspose.Cells supports the feature of sorting by custom list. Please see the following sample code in C# and Java. Please also see the sample Excel file used inside the code and the output Excel file generated by it for a reference.

Download Link:
sample and outputSortedCustomList.zip (12.6 KB)

C#

//Load sample workbook
Workbook wb = new Workbook("sampleSortedCustomList.xlsx");

//Access first worksheet
Worksheet ws = wb.Worksheets[0];

//Cell area to be sorted
CellArea ca = CellArea.CreateCellArea("A1", "A100");

//Add key to sort with custom list
wb.DataSorter.AddKey(0, SortOrder.Ascending, "Car,Pear,Mango,Apple,Banana,Cycle");

//Sort the data
wb.DataSorter.Sort(ws.Cells, ca);

//Save the workbook
wb.Save("outputSortedCustomList.xlsx");

Java

//Load sample workbook
Workbook wb = new Workbook(dirPath + "sampleSortedCustomList.xlsx");

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

//Cell area to be sorted
CellArea ca = CellArea.createCellArea("A1", "A100");

//Add key to sort with custom list
wb.getDataSorter().addKey(0, SortOrder.ASCENDING, "Car,Pear,Mango,Apple,Banana,Cycle");

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

//Save the workbook
wb.save(dirPath + "outputSortedCustomList.xlsx");

[aymarzapak]

thanks much. Ill reply if I have any query

[aymarzapak]

Hi Shakeel,

How to Sort the dates col in Aspose I have added a samle Excel of Date col and the Screen Shot of How can It be done in Excel.
image.png (6.8 KB)

I Want to replicate the similar in aspose , I’m unable to sort it a normal way.SampleDateCol.zip (7.7 KB)

[aymarzapak]

Moreover can you say me how to use multiple sorters in a single worksheet, say some cols i want to sort left To Right and then want to Sort Top to Bottom as usual

[shakeel.faiz]

@arshad3641

Workbook.getDataSorter() will provide you 3 keys, if you need more keys, then add them using Workbook.getDataSorter().addKey(key, order) method.

The three keys are like this

Workbook.getDataSorter().getKey1();
Workbook.getDataSorter().getKey2();
Workbook.getDataSorter().getKey3();

and there ascending and descending orders are decided like this

Workbook.getDataSorter().setOrder1();
Workbook.getDataSorter().setOrder2();
Workbook.getDataSorter().setOrder3();

The following code sets the order of second key to descending order

Workbook.getDataSorter().setOrder2(SortOrder.DESCENDING);

You don’t need to do anything relating to your image saying “Sort numbers and numbers stored…” because this is the default behavior of Aspose.Cells. But if you want to change it, then set it true

Workbook.getDataSorter().setSortAsNumber(true);