How to avoid header row while sorting Excel data?

29 Nov 20246 minutes to read

Syncfusion® XlsIO considers first row in the sort range as header. In order to disable this behavior and consider the first row in sorting, the HasHeader property of IDataSort interface should be disabled. Please find the code snippet below.

C# [Cross-platform]

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Load data
  worksheet["A2"].Text = "banana";
  worksheet["A3"].Text = "Cherry";
  worksheet["A4"].Text = "Banana";
  worksheet["A5"].Text = "Apple";
  worksheet["A6"].Text = "cherry";
  worksheet["A7"].Text = "apple";

  worksheet["B2"].Number = 744;
  worksheet["B3"].Number = 5079;
  worksheet["B4"].Number = 1267;
  worksheet["B5"].Number = 1418;
  worksheet["B6"].Number = 4728;
  worksheet["B7"].Number = 943;

  worksheet["C2"].Number = 162;
  worksheet["C3"].Number = 1249;
  worksheet["C4"].Number = 1062;
  worksheet["C5"].Number = 756;
  worksheet["C6"].Number = 4547;
  worksheet["C7"].Number = 349;

  IDataSort sorter = workbook.CreateDataSorter();
  sorter.SortRange = worksheet["A2:C7"];
  ISortField sortField = sorter.SortFields.Add(0, SortOn.Values, OrderBy.Ascending);

  //Disable HasHeader
  sorter.HasHeader = false;

  sorter.Sort();
  worksheet.UsedRange.AutofitColumns();

  FileStream stream = new FileStream("Output.xlsx", FileMode.Create, FileAccess.ReadWrite);
  workbook.SaveAs(stream);
  stream.Dispose();
}

C# [Windows-specific]

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Load data
  worksheet["A2"].Text = "banana";
  worksheet["A3"].Text = "Cherry";
  worksheet["A4"].Text = "Banana";
  worksheet["A5"].Text = "Apple";
  worksheet["A6"].Text = "cherry";
  worksheet["A7"].Text = "apple";

  worksheet["B2"].Number = 744;
  worksheet["B3"].Number = 5079;
  worksheet["B4"].Number = 1267;
  worksheet["B5"].Number = 1418;
  worksheet["B6"].Number = 4728;
  worksheet["B7"].Number = 943;

  worksheet["C2"].Number = 162;
  worksheet["C3"].Number = 1249;
  worksheet["C4"].Number = 1062;
  worksheet["C5"].Number = 756;
  worksheet["C6"].Number = 4547;
  worksheet["C7"].Number = 349;

  IDataSort sorter = workbook.CreateDataSorter();
  sorter.SortRange = worksheet["A2:C7"];
  ISortField sortField = sorter.SortFields.Add(0, SortOn.Values, OrderBy.Ascending);

  //Disable HasHeader
  sorter.HasHeader = false;

  sorter.Sort();
  worksheet.UsedRange.AutofitColumns();
  workbook.SaveAs("Output.xlsx");
}

VB.NET [Windows-specific]

Using excelEngine As ExcelEngine = New ExcelEngine
  Dim application As IApplication = excelEngine.Excel
  Dim workbook As IWorkbook = application.Workbooks.Create(1)
  Dim worksheet As IWorksheet = workbook.Worksheets(0)

  'Load data
  worksheet("A2").Text = "banana"
  worksheet("A3").Text = "Cherry"
  worksheet("A4").Text = "Banana"
  worksheet("A5").Text = "Apple"
  worksheet("A6").Text = "cherry"
  worksheet("A7").Text = "apple"

  worksheet("B2").Number = 744
  worksheet("B3").Number = 5079
  worksheet("B4").Number = 1267
  worksheet("B5").Number = 1418
  worksheet("B6").Number = 4728
  worksheet("B7").Number = 943

  worksheet("C2").Number = 162
  worksheet("C3").Number = 1249
  worksheet("C4").Number = 1062
  worksheet("C5").Number = 756
  worksheet("C6").Number = 4547
  worksheet("C7").Number = 349

  Dim sorter As IDataSort = workbook.CreateDataSorter
  sorter.SortRange = worksheet("A2:C7")
  Dim sortField As ISortField = sorter.SortFields.Add(0, SortOn.Values, OrderBy.Ascending)

  'Disable HasHeader
  sorter.HasHeader = False

  sorter.Sort()
  worksheet.UsedRange.AutofitColumns()

  workbook.SaveAs("Output.xlsx")
End Using