Sorting Data in Excel Document
26 Aug 202413 minutes to read
A range of cells in Excel worksheet can be sorted based on data in one or more columns. Following types of sorting is supported in XlsIO:
- Cell Values
- Font Color
- Cell Color
NOTE
Currently XlsIO don’t support sorting based on cell icon, parsing and serialization of its sorting details.
Cell Values
The following code example illustrates how to sort a range of cells by values.
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
FileStream fileStream = new FileStream("SortingData.xlsx", FileMode.Open, FileAccess.Read);
IWorkbook workbook = application.Workbooks.Open(fileStream);
IWorksheet worksheet = workbook.Worksheets[0];
//Creates the data sorter
IDataSort sorter = workbook.CreateDataSorter();
//Range to sort
sorter.SortRange = worksheet.Range["D3:D16"];
//Adds the sort field with the column index, sort based on and order by attribute
ISortField sortField = sorter.SortFields.Add(0, SortOn.Values, OrderBy.Ascending);
//Adds another sort field
ISortField sortField2 = sorter.SortFields.Add(1, SortOn.Values, OrderBy.Ascending);
//Sort based on the sort Field attribute
sorter.Sort();
//Saving the workbook as stream
FileStream stream = new FileStream("Sort.xlsx", FileMode.Create, FileAccess.ReadWrite);
workbook.Version = ExcelVersion.Xlsx;
workbook.SaveAs(stream);
stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
IWorksheet sheet = workbook.Worksheets[0];
//Creates the data sorter
IDataSort sorter = workbook.CreateDataSorter();
//Range to sort
sorter.SortRange = sheet.Range["D3:D16"];
//Adds the sort field with the column index, sort based on and order by attribute
ISortField sortField = sorter.SortFields.Add(0, SortOn.Values, OrderBy.Ascending);
//Adds another sort field
ISortField sortField2 = sorter.SortFields.Add(1, SortOn.Values, OrderBy.Ascending);
//Sort based on the sort Field attribute
sorter.Sort();
workbook.Version = ExcelVersion.Xlsx;
workbook.SaveAs("Sort.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
Dim application As IApplication = excelEngine.Excel
Dim workbook As IWorkbook = application.Workbooks.Open("Sample.xlsx")
Dim sheet As IWorksheet = workbook.Worksheets(0)
'Creates the Data sorter
Dim sorter As IDataSort = workbook.CreateDataSorter()
'Specifies the sort range
sorter.SortRange = sheet.Range("D3:D16")
'Adds the sort field with column index, sort based on and order by attribute
Dim sortField As ISortField = sorter.SortFields.Add(0, SortOn.Values, OrderBy.Ascending)
'Adds the second sort field
Dim sortField2 As ISortField = sorter.SortFields.Add(1, SortOn.Values, OrderBy.Ascending)
'Sorts the data with the sort field attribute
sorter.Sort()
workbook.Version = ExcelVersion.Xlsx
workbook.SaveAs("Sort.xlsx")
End Using
A complete working example to sort Excel data based on cell values in C# is present on this GitHub page.
Font Color
The following code example illustrates how to move a range of cells with the specified font color to either top or bottom of the sorting range.
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
FileStream fileStream = new FileStream("Sample.xlsx", FileMode.Open, FileAccess.Read);
IWorkbook workbook = application.Workbooks.Open(fileStream);
IWorksheet worksheet = workbook.Worksheets[0];
//Creates the data sorter
IDataSort sorter = workbook.CreateDataSorter();
//Range to sort
sorter.SortRange = worksheet.Range["A2:D16"];
//Creates the sort field with the column index, sort based on and order by attribute
ISortField sortField1 = sorter.SortFields.Add(2, SortOn.FontColor, OrderBy.OnTop);
//Specifies the color to sort the data
sortField1.Color = Color.Red;
//Creates another sort field with the column index, sort based on and order by attribute
ISortField sortField2 = sorter.SortFields.Add(2, SortOn.FontColor, OrderBy.OnTop);
//Specifies the color to sort the data
sortField2.Color = Color.Green;
//Sort based on the sort Field attribute
sorter.Sort();
//Saving the workbook as stream
FileStream stream = new FileStream("Sort.xlsx", FileMode.Create, FileAccess.ReadWrite);
workbook.Version = ExcelVersion.Xlsx;
workbook.SaveAs(stream);
stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
IWorksheet sheet = workbook.Worksheets[0];
//Creates the data sorter
IDataSort sorter = workbook.CreateDataSorter();
//Range to sort
sorter.SortRange = sheet.Range["A2:D16"];
//Creates the sort field with the column index, sort based on and order by attribute
ISortField sortField1 = sorter.SortFields.Add(2, SortOn.FontColor, OrderBy.OnTop);
//Specifies the color to sort the data
sortField1.Color = Color.Red;
//Creates another sort field with the column index, sort based on and order by attribute
ISortField sortField2 = sorter.SortFields.Add(2, SortOn.FontColor, OrderBy.OnTop);
//Specifies the color to sort the data
sortField2.Color = Color.Green;
//Sort based on the sort Field attribute
sorter.Sort();
workbook.Version = ExcelVersion.Xlsx;
workbook.SaveAs("Sort.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
Dim application As IApplication = excelEngine.Excel
Dim workbook As IWorkbook = application.Workbooks.Open("Sample.xlsx")
Dim sheet As IWorksheet = workbook.Worksheets(0)
'Creates the Data sorter
Dim sorter As IDataSort = workbook.CreateDataSorter()
'Specifies the sort range
sorter.SortRange = sheet.Range("A2:D16")
'Adds the sort field with column index, sort based on and order by attribute
Dim field1 As ISortField = sorter.SortFields.Add(2, SortOn.FontColor, OrderBy.OnTop)
'Sorts the data based on this color
field1.Color = Color.Red
'Adds another sort field with column index, sort based on and order by attribute
Dim field2 As ISortField = sorter.SortFields.Add(2, SortOn.FontColor, OrderBy.OnTop)
'Sorts the data based on this color
field2.Color = Color.Green
'Sorts the data with the sort field attribute
sorter.Sort()
workbook.Version = ExcelVersion.Xlsx
workbook.SaveAs("Sort.xlsx")
End Using
A complete working example to sort Excel data based on font color in C# is present on this GitHub page.
Cell Color
The following code example illustrates how to move a range of cells with the specified cell background color to either top or bottom of the sorting range.
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
FileStream fileStream = new FileStream("Sample.xlsx", FileMode.Open, FileAccess.Read);
IWorkbook workbook = application.Workbooks.Open(fileStream);
IWorksheet worksheet = workbook.Worksheets[0];
//Creates the data sorter
IDataSort sorter = workbook.CreateDataSorter();
//Range to sort
sorter.SortRange = worksheet.Range["A2:D16"];
//Creates the sort field with the column index, sort based on and order by attribute
ISortField sortField1 = sorter.SortFields.Add(2, SortOn.CellColor, OrderBy.OnTop);
//Specifies the color to sort the data
sortField1.Color = Color.Red;
//Creates another sort field with the column index, sort based on and order by attribute
ISortField sortField2 = sorter.SortFields.Add(2, SortOn.CellColor, OrderBy.OnTop);
//Specifies the color to sort the data
sortField2.Color = Color.Green;
//Sort based on the sort Field attribute
sorter.Sort();
//Saving the workbook as stream
FileStream stream = new FileStream("Sort.xlsx", FileMode.Create, FileAccess.ReadWrite);
workbook.Version = ExcelVersion.Xlsx;
workbook.SaveAs(stream);
stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
IWorksheet sheet = workbook.Worksheets[0];
//Creates the data sorter
IDataSort sorter = workbook.CreateDataSorter();
//Range to sort
sorter.SortRange = sheet.Range["A2:D16"];
//Creates the sort field with the column index, sort based on and order by attribute
ISortField sortField1 = sorter.SortFields.Add(2, SortOn.CellColor, OrderBy.OnTop);
//Specifies the color to sort the data
sortField1.Color = Color.Red;
//Creates the sort field with the column index, sort based on and order by attribute
ISortField sortField2 = sorter.SortFields.Add(2, SortOn.CellColor, OrderBy.OnTop);
//Specifies the color to sort the data
sortField2.Color = Color.Green;
//Sort based on the sort field attribute
sorter.Sort();
workbook.Version = ExcelVersion.Xlsx;
workbook.SaveAs("Sort.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
Dim application As IApplication = excelEngine.Excel
Dim workbook As IWorkbook = application.Workbooks.Open("Sample.xlsx")
Dim sheet As IWorksheet = workbook.Worksheets(0)
Dim sorter As IDataSort = workbook.CreateDataSorter()
'Specifies the sort range.
sorter.SortRange = sheet.Range("A2:D16")
'Adds the sort field with column index, sort based on and order by attribute
Dim field1 As ISortField = sorter.SortFields.Add(2, SortOn.CellColor, OrderBy.OnTop)
'Sorts the data based on this color
field1.Color = Color.Red
'Adds the sort field with column index, sort based on and order by attribute
Dim field2 As ISortField = sorter.SortFields.Add(2, SortOn.CellColor, OrderBy.OnTop)
'Sorts the data based on this color
field2.Color = Color.Green
'Sorts the data with the sort field attribute
sorter.Sort()
workbook.Version = ExcelVersion.Xlsx
workbook.SaveAs("Sort.xlsx")
End Using
A complete working example to sort Excel data based on cell color in C# is present on this GitHub page.