DEV Community

Cover image for C# Excel sortable headers
Karen Payne
Karen Payne

Posted on

C# Excel sortable headers

#csharp #dotnetcore #coding #backend

Introduction

Learn how to create sortable headers by creating a table for the data in an Excel Worksheet in C# using the NuGet package SpreadsheetLight.

Why SpreadSheetLight

Its free, there is a downloadable help file and plenty of sample code.

The package does not provide methods to read WorkSheets but there are packages such as ExcelMapper which is used in the sample code.

Best way to learn

This depends on a developer’s experience and comfort level with C#. Most developers can inspect code, configure the database, and then run the console project. From here, study the code that has useful class and method names.

Projects

The main project is a console project that has the EF Core code and a class project for Excel operations. Separating Excel operations into a class project allows other project types to use the Excel code.

Rather than display the code below, the best way to learn is to first run the console project, then go back and step through the code, which has ample comments.

Preparation

  1. Using SSMS create a database named CustomerDatabase1 under SQLEXPRESS or under any available server. If not under SQLEXPRESS change the connection string in appsettings.json in the console project.
  2. In the console project under the Scripts folder, open Populate.sql in SSMS and execute to populate tables with data.

Source code

Class project source code Console source code

NET Framework

Version 9 is used, but should work with version 8 and will work with version 10.

How to use outside of the code sample

Using classes to populate data that can be flattened into a DataTable or populate a DataTable with data.

In the following example, table variable is a System.Data.DataTable and the DataColumn need to be the first column, SetOrdinal repositions the column to be the first cell/first row in the WorkSheet.

table.Columns["Identifier"].SetOrdinal(0);
Enter fullscreen mode Exit fullscreen mode

Next (optional) rename DataColumn's names, for instance to show First Name rather than ContactFirstName

table.Columns["ContactFirstName"].ColumnName = "First Name";
Enter fullscreen mode Exit fullscreen mode

To create the Excel table

Add the following method to the class to perform Excel Operations.

No WorkSheet is selected as coded there is only one WorkSheet. If this code was to use a different WorkSheet in an existing file the following code shows how to create a new WorkSheet.

public bool AddNewSheet(string fileName, string sheetName)
{
    using SLDocument document = new(fileName);

    if (!(document.GetSheetNames(false)
            .Any((workSheetName) => 
                string.Equals(workSheetName, sheetName, StringComparison.CurrentCultureIgnoreCase))))
    {
        document.AddWorksheet(sheetName);
        document.Save();
        return true;
    }
    else
    {
        return false;
    }
}
Enter fullscreen mode Exit fullscreen mode

Call the code directly before saving the Excel file (see source code)

public static void CreateSortableHeader(SLDocument document)
{

    SLWorksheetStatistics? statistics = document.GetWorksheetStatistics();
    var lastColIndex = statistics.EndColumnIndex;

    string lastColLetter = SLConvert.ToColumnName(lastColIndex);
    List<string> columnNames = [];

    var headers = Headers(document, columnNames);

    // Set headers
    for (var index = 0; index < headers.Length; index++)
        document.SetCellValue(1, index + 1, headers[index]);

    var table = document.CreateTable("A1", $"{lastColLetter}{statistics.EndRowIndex + 1}");

    table.HasTotalRow = true;

    document.InsertTable(table);

}
Enter fullscreen mode Exit fullscreen mode

Adjust column orders

Logging

Serilog NuGet package is used to log any runtime exceptions to a file under, in this case the console project.

Steps involved

  1. Read data from a SQL Server database using EF Core
  2. Convert the data into a DataTable using the NuGet package FastMember
  3. Before creating the Excel file, ensure the file is not open
  4. Create the Excel
    1. Reorder columns in DataTable
    2. Change column names in DataTable
    3. Import data into the Excel file
    4. Set first row header style
    5. Rename WorkSheet name
    6. Freeze first/header row
    7. Auto-size WorkSheet columns
    8. Create table for data which provides filtering and sorting
    9. Save the file

To validate data in Excel the NuGet package ExcelMapper is used to read back the Worksheet data.

EF Core code

The following code reads the data to export into Excel. The class CustomerReportView flattens the data from the three tables.

public static class DataOperations
{
    public static async Task<(List<CustomerReportView> Data, bool IsSuccess)> GetCustomerReportData()
    {
        try
        {
            await using var context = new Context();
            var data = await context.Customers
                .Include(c => c.ContactTypeIdentifierNavigation)
                .Include(c => c.GenderIdentifierNavigation)
                .Select(c => new CustomerReportView
                {
                    Identifier = c.Identifier,
                    ContactFirstName = c.ContactFirstName,
                    ContactLastName = c.ContactLastName,
                    GenderType = c.GenderIdentifierNavigation.GenderType,
                    ContactType = c.ContactTypeIdentifierNavigation.ContactType
                })
                .ToListAsync();

            return (data, true);
        }
        catch (Exception ex)
        {
            Log.Error(ex, "Error fetching customer report data");
            return ([], false);
        }
    }
}
Enter fullscreen mode Exit fullscreen mode

The connection string is stored in appsettings.json. The following code reads the connection string in the DbContext.

private static string GetMainConnectionString()
    => Config.Configuration.JsonRoot()
        .GetSection(nameof(ConnectionStrings))
        .Get<ConnectionStrings>()
        .MainConnection;
Enter fullscreen mode Exit fullscreen mode

JsonRoot, ConnectionStrings and MainConnection come from NuGet package ConsoleConfigurationLibrary.

Excel code

Basic code to export a DataTable

  1. Create an instance of SpreadSheetLight document class SLDocument
  2. Use ImportDataTable to add data
  3. Save the file. 
public static void ExportToExcel(DataTable table, string fileName, bool includeHeader, string sheetName, int row)
{
    using var document = new SLDocument();

    document.ImportDataTable(row, SLConvert.ToColumnIndex("A"), table, includeHeader);

    // give sheet a useful name
    document.RenameWorksheet(SLDocument.DefaultFirstSheetName, sheetName);

    document.SaveAs(fileName);
}
Enter fullscreen mode Exit fullscreen mode

Exporting with Dates

Without proper formatting, dates will not display correctly. In the following code, the DateTime column is in position 6. The variable dateStyle creates a style to format the date column.

See the following on formatting numbers and dates.

public static void ExportToExcel(DataTable table, string fileName, bool includeHeader, string sheetName, int row)
{
    using var document = new SLDocument();


    document.ImportDataTable(row, SLConvert.ToColumnIndex("A"), table, includeHeader);

    // give sheet a useful name
    document.RenameWorksheet(SLDocument.DefaultFirstSheetName, sheetName);

    SLStyle dateStyle = document.CreateStyle();
    dateStyle.FormatCode = "mm-dd-yyyy";
    // format a specific column using above style
    int dateColumnIndex = 6;
    document.SetColumnStyle(dateColumnIndex, dateStyle);

    document.SaveAs(fileName);
}
Enter fullscreen mode Exit fullscreen mode

Sumary

By following the above instructions and walking through the source code provided, a developer can import data into an Excel Worksheet that provides filtering and sorting.

See also

Top comments (0)