Free Support Forum - aspose.com

Excel sorting using column headers

Hi,

I have a worksheet with several columns and I need to perform sorting based upon 3 columns . The column index of these 3 columns are not fixed it varies , say if my column names are 'employeeid', 'salary' and 'age' then based on these 3 column names how can i perform sorting using aspose?

Thanks

Hi,


Thanks for your query.

Please see the document on how to do data sorting (upon data) with example code for your complete reference:
http://www.aspose.com/docs/display/cellsnet/Data+Sorting

Thank you.

How can I add key if I do not know column index and only column header is available?


Hi Richa,


You can use the Aspose.Cells’ Find/Search Data feature to find the column index containing any specific text. Please check the following piece of code and attached spreadsheet for your reference.

C#

var document = new Workbook(“D:/book1.xlsx”);
var cells = document.Worksheets[0].Cells;
var opts = new FindOptions();
opts.LookInType = LookInType.Values;
opts.LookAtType = LookAtType.EntireContent;

//Find the cell with string Employee ID
var cell = cells.Find(“Employee ID”, null, opts);

//Find column containing the cell with string Employee ID
var columnIndex = cell.Column;

Console.WriteLine(columnIndex);

Hi,


I have refined sample code shared by Babar to implement your scenario/ case precisely. I have used a sample Excel file (attached) which contains some data set as per your scenario. I have written the following sample code to accomplish your task a bit. Please refer to my sample code and you may add and write your own sample code accordingly for your data in your spreadsheet for your needs. I have also attached the output Excel file for your reference.
e.g
Sample code:

Workbook workbook = new Workbook(“e:\test2\Bk_sort1.xlsx”);
//Get the first worksheet.
Worksheet worksheet = workbook.Worksheets[0];
//Get the cells.
Cells cells = worksheet.Cells;
//Identify keys for the columns based on headers’ titles
//We need to find the header/title cells and then evaluate the column index for data sorting.
Aspose.Cells.Cell prevcell = null;
Aspose.Cells.Cell foundCell = null;
int Empindex = 0;
string stringToFind = “Employeeid”; //You can change the string as required, also you may find other titles accordingly too.
FindOptions opts = new FindOptions();
//Create a Cells Area
CellArea ca = new CellArea();
ca.StartRow = 0;
ca.StartColumn = 0;
ca.EndRow = cells.MaxDataRow;
ca.EndColumn = cells.MaxDataColumn;

//Set cells area for find options
opts.SetRange(ca);
opts.LookAtType = LookAtType.Contains; //use as per your needs
opts.LookInType = LookInType.Values; //change if you want to search in formulas

foundCell = worksheet.Cells.Find(stringToFind, prevcell, opts);
if (foundCell != null)
{
Empindex = foundCell.Column;
}
DataSorter dataSorter = workbook.DataSorter;
dataSorter.Key1 = Empindex;
dataSorter.Order1 = Aspose.Cells.SortOrder.Ascending;
//Similarly you may find out other titles and get the column indexes accordingly.


CellArea area = new CellArea();
area.StartRow = foundCell.Row+1;
area.EndRow = cells.MaxDataRow;
area.StartColumn = Empindex;
area.EndColumn = cells.MaxDataColumn;
dataSorter.Sort(workbook.Worksheets[0].Cells, area);

workbook.Save(“e:\test2\out1sort1_new.xlsx”);


Hope, this helps a bit.

Thank you.

Hi,

Thanks for the above responses. I have written below code for sorting:

Workbook wk = new Workbook("…\Before_sort.xlsx");

//Get the workbook datasorter object.
DataSorter sorter = wk.DataSorter;

//Set the first order for datasorter object.
sorter.Order1 = Aspose.Cells.SortOrder.Ascending;

//Define the first key.
sorter.Key1 = 0;
//Create a cells area (range).
CellArea ca = new CellArea();

//Specify the start row index.
ca.StartRow = 1;

//Specify the start column index.
ca.StartColumn = 0;

//Specify the last row index.
ca.EndRow = 14;

//Specify the last column index.
ca.EndColumn = 1;

//Sort data in the specified data range (A1:B14)
sorter.Sort(wk.Worksheets[0].Cells, ca);

wk.Save("…\AfterSort.xlsx");

The above code does not perform sorting correctly.
If suppose I have a column ‘EmployeeData(index=0)’ for sorting and it contains below as input records:
A-AAOC
C-AAOD
C-AAOD
C-AAOD
C-AAOE
C-AAOF
C-AAOH
A-AAOD
C-AAOC
C-AAOD
C-AAOD
C-AAOE
C-AAOF

and after performing sort the above code generates below result:
A-AAOC
A-AAOD
C-AAOD
C-AAOD
C-AAOD
C-AAOE
C-AAOF
C-AAOH
C-AAOC
C-AAOD
C-AAOD
C-AAOE
C-AAOF

You can see sort order for value'c-' are not in correct order. Please suggest what might be the issue in the code?

Hi,


Please post your temp;late “Before_sort.xlsx” file which contains your mentioned dataset, we will check it soon.

Thank you.

Somehow I am not able to upload the file, getting some error.

The before_sort.xlsx file contains only 1 column ‘EmployeeData’ with below cell data
A-AAOC
C-AAOD
C-AAOD
C-AAOD
C-AAOE
C-AAOF
C-AAOH
A-AAOD
C-AAOC
C-AAOD
C-AAOD
C-AAOE
C-AAOF

Hi,


I have tested your scenario/ case using a sample Excel file (attached) as per your dataset in the first column in the first worksheet of the workbook with the following sample code using Aspose.Cells for .NET v8.5.2.3 (attached), it works fine. I have also attached the output file for your reference here.
e.g
Sample code:

Workbook wk = new Workbook(“e:\test2\Before_sort.xlsx”);

//Get the workbook datasorter object.
DataSorter sorter = wk.DataSorter;

//Set the first order for datasorter object.
sorter.Order1 = Aspose.Cells.SortOrder.Ascending;

//Define the first key.
sorter.Key1 = 0;
//Create a cells area (range).
CellArea ca = new CellArea();

//Specify the start row index.
ca.StartRow = 1;

//Specify the start column index.
ca.StartColumn = 0;

//Specify the last row index.
ca.EndRow = 14;

//Specify the last column index.
ca.EndColumn = 1;

//Sort data in the specified data range (A1:B14)
sorter.Sort(wk.Worksheets[0].Cells, ca);

wk.Save(“e:\test2\AfterSort.xlsx”);

Please try the attached latest version/fix: Aspose.Cells for .NET v8.5.3.2.

Let us know if you still have any issue.

Thank you.