Sorting Data in Excel Worksheet

30 Jul 202615 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 OrderBy enum supports Ascending and Descending for value-based sorting.

The following code example illustrates how to sort a range of cells by values.

using (ExcelEngine excelEngine = new ExcelEngine())
{
	IApplication application = excelEngine.Excel;
	application.DefaultVersion = ExcelVersion.Xlsx;
	IWorkbook workbook = application.Workbooks.Open(Path.GetFullPath(@"Data/InputTemplate.xlsx"));
	IWorksheet worksheet = workbook.Worksheets[0];

	#region Sort On Cell Values
	//Creates the data sorter
	IDataSort sorter = workbook.CreateDataSorter();

	//Range to sort
	sorter.SortRange = worksheet.Range["A1:B11"];

	//Adds a sort field: sort by values in column A in ascending order
	sorter.SortFields.Add(0, SortOn.Values, OrderBy.Ascending);

	//Adds a sort field: then by values in column B in descending order
	sorter.SortFields.Add(1, SortOn.Values, OrderBy.Descending);

	//Sort based on the sort Field attribute
	sorter.Sort();

	//A new data sorter is required because the previous sort has been applied
	sorter = workbook.CreateDataSorter();

	//Range to sort
	sorter.SortRange = worksheet.Range["C1:C11"];

	//Adds a sort field: sort by values in column C in descending order
	sorter.SortFields.Add(2, SortOn.Values, OrderBy.Descending);

	//Sort based on the sort Field attribute
	sorter.Sort();
	#endregion

	#region Save
	//Saving the workbook
	workbook.SaveAs(Path.GetFullPath("Output/SortOnValues.xlsx"));
	#endregion
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
  IWorksheet worksheet = workbook.Worksheets[0];

  //Creates the data sorter
  IDataSort sorter = workbook.CreateDataSorter();

  //Range to sort
  sorter.SortRange = worksheet.Range["A1:B11"];

  //Adds a sort field: sort by values in column A in ascending order
  sorter.SortFields.Add(0, SortOn.Values, OrderBy.Ascending);

  //Adds a sort field: then by values in column B in descending order
  sorter.SortFields.Add(1, SortOn.Values, OrderBy.Descending);

  //Sort based on the sort Field attribute
  sorter.Sort();

  //Creates the data sorter
  sorter = workbook.CreateDataSorter();

  //Range to sort
  sorter.SortRange = worksheet.Range["C1:C11"];

  //Adds a sort field: sort by values in column C in descending order
  sorter.SortFields.Add(2, SortOn.Values, OrderBy.Descending);

  //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 worksheet As IWorksheet = workbook.Worksheets(0)

  'Creates the data sorter
  Dim sorter As IDataSort = workbook.CreateDataSorter()

  'Range to sort 
  sorter.SortRange = worksheet.Range("A1:B11")

  'Adds a sort field: sort by values in column A in ascending order
  sorter.SortFields.Add(0, SortOn.Values, OrderBy.Ascending)

  'Adds a sort field: then by values in column B in descending order
  sorter.SortFields.Add(1, SortOn.Values, OrderBy.Descending)

  'Sort based on the sort Field attribute
  sorter.Sort()

  'Creates the data sorter
  sorter = workbook.CreateDataSorter()

  'Range to sort 
  sorter.SortRange = worksheet.Range("C1:C11")

  'Add sort field: sort by values in column C in descending order
  sorter.SortFields.Add(2, SortOn.Values, OrderBy.Descending)

  'Sort based on 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 OrderBy enum supports OnTop and OnBottom for color-based sorting. When multiple sort fields are added on the same column index with different colors, the first color takes priority over the second, and so on. Set the color to sort on through the ISortField.Color property.

The following code example illustrates how to sort a range of cells so that the cells with the specified font color appear at the top or bottom of the sorting range.

using (ExcelEngine excelEngine = new ExcelEngine())
{
	IApplication application = excelEngine.Excel;
	application.DefaultVersion = ExcelVersion.Xlsx;
	IWorkbook workbook = application.Workbooks.Open(Path.GetFullPath("Data/InputTemplate.xlsx"));
	IWorksheet worksheet = workbook.Worksheets[0];

	#region Sort on Font Color
	//Creates the data sorter
	IDataSort sorter = workbook.CreateDataSorter();

	//Range to sort
	sorter.SortRange = worksheet.Range["A1:A11"];

	//Creates the sort field with the column index, sort based on and order by attribute
	ISortField sortField = sorter.SortFields.Add(0, SortOn.FontColor, OrderBy.OnTop);

	//Specifies the color to sort the data
	sortField.Color = Syncfusion.Drawing.Color.Red;

	//Sort based on the sort Field attribute
	sorter.Sort();

	//A new data sorter is required because the previous sort has been applied
	sorter = workbook.CreateDataSorter();

	//Range to sort
	sorter.SortRange = worksheet.Range["B1:B11"];

	//Creates another sort field with the column index, sort based on and order by attribute
	sortField = sorter.SortFields.Add(1, SortOn.FontColor, OrderBy.OnBottom);

	//Specifies the color to sort the data
	sortField.Color = Syncfusion.Drawing.Color.Red;

	//Sort based on the sort Field attribute
	sorter.Sort();
	#endregion

	#region Save
	//Saving the workbook
	workbook.SaveAs(Path.GetFullPath("Output/SortOnFontColor.xlsx"));
	#endregion
}
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
  //First color takes priority over the second for the same column
  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, based on and order by attribute
  'First color takes priority over the second for the same column
  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, 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 sort a range of cells so that the cells with the specified cell background color appear at the top or bottom of the sorting range.

using (ExcelEngine excelEngine = new ExcelEngine())
{
	IApplication application = excelEngine.Excel;
	application.DefaultVersion = ExcelVersion.Xlsx;
	IWorkbook workbook = application.Workbooks.Open(Path.GetFullPath(@"Data/InputTemplate.xlsx"));
	IWorksheet worksheet = workbook.Worksheets[0];

	#region Sort on Cell Color
	//Creates the data sorter
	IDataSort sorter = workbook.CreateDataSorter();

	//Range to sort
	sorter.SortRange = worksheet.Range["A1:A11"];

	//Creates the sort field with the column index, sort based on and order by attribute
	ISortField sortField = sorter.SortFields.Add(0, SortOn.CellColor, OrderBy.OnTop);

	//Specifies the color to sort the data
	sortField.Color = Syncfusion.Drawing.Color.Yellow;

	//Sort based on the sort Field attribute
	sorter.Sort();

	//A new data sorter is required because the previous sort has been applied
	sorter = workbook.CreateDataSorter();

	//Range to sort
	sorter.SortRange = worksheet.Range["B1:B11"];

	//Creates another sort field with the column index, sort based on and order by attribute
	sortField = sorter.SortFields.Add(1, SortOn.CellColor, OrderBy.OnBottom);

	//Specifies the color to sort the data
	sortField.Color = Syncfusion.Drawing.Color.Yellow;

	//Sort based on the sort Field attribute
	sorter.Sort();
	#endregion

	#region Save
	//Saving the workbook
	workbook.SaveAs(Path.GetFullPath("Output/SortOnCellColor.xlsx"));
	#endregion
}
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
  //First color takes priority over the second for the same column
  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, based on and order by attribute
  'First color takes priority over the second for the same column
  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, 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.