How to sort columns with the first row in .NET Excel Library

17 Aug 20264 minutes to read

This following code samples demonstrate how to sort a column by including first row using C# (Cross-platform and Windows-specific) and VB.NET.

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

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

    //This includes the first column for sorting range
    sorter.HasHeader = false;

    //Range to sort
    sorter.SortRange = worksheet.Range["D1:D26"];

    //Adds a sort field: then by values in column D in descending order
    ISortField sortField = sorter.SortFields.Add(3, SortOn.Values, OrderBy.Descending);

    //Setting the algorithm to sort the values
    sorter.Algorithm = SortingAlgorithms.QuickSort;
    sorter.Sort();
    #endregion

    #region Save
    //Saving the workbook
    workbook.SaveAs(Path.GetFullPath("Output/Output.xlsx"));
    #endregion

}
using (ExcelEngine excelEngine = new ExcelEngine())
{
    #region Workbook initialization
    IApplication application = excelEngine.Excel;
    application.DefaultVersion = ExcelVersion.Xlsx;
    IWorkbook workbook = application.Workbooks.Open("InputTemplate.xlsx");
    IWorksheet worksheet = workbook.Worksheets[0];
    #endregion

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

    //This includes the first column for sorting range
    sorter.HasHeader = false;

    //Range to sort
    sorter.SortRange = worksheet.Range["D1:D26"];

    //Adds a sort field: then by values in column D in descending order
    ISortField sortField = sorter.SortFields.Add(3, SortOn.Values, OrderBy.Descending);

    //Setting the algorithm to sort the values
    sorter.Algorithm = SortingAlgorithms.QuickSort;
    sorter.Sort();
    #endregion

    #region Save
    //Saving the workbook
    workbook.SaveAs("Output.xlsx");
    #endregion
}
Using excelEngine As New ExcelEngine()
    Dim application As IApplication = excelEngine.Excel
    application.DefaultVersion = ExcelVersion.Xlsx
    Dim workbook As IWorkbook = application.Workbooks.Open("InputTemplate.xlsx")
    Dim worksheet As IWorksheet = workbook.Worksheets(0)

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

    'This includes the first column for sorting range
    sorter.HasHeader = False

    'Range to sort
    sorter.SortRange = worksheet.Range("D1:D26")

    'Adds a sort field: then by values in column D in descending order
    Dim sortField As ISortField = sorter.SortFields.Add(3, SortOn.Values, OrderBy.Descending)

    'Setting the algorithm to sort the values
    sorter.Algorithm = SortingAlgorithms.QuickSort
    sorter.Sort()
    'Saving the workbook
    workbook.SaveAs("Output.xlsx")
End Using

A complete working example in C# is present on this GitHub page.