ASP.NET Core/Razor Pages export to Excel

Learn how to export data from, in this case a SQL-Server database table using SpreadSheetLight library from their NuGet package.

SpreadSheetLight library is relatively unknown while other libraries like those in the Top 20 NuGet excel Packages yet there are 1.9M downloads.

Getting to know SpreadSheetLight can take a little time yet well worth time spent. I recommend downloading their help file and reading through the documentation.

Export data to Excel

Take an existing (which is important as your customers may ask for this in an existing project) Razor Pages project using EF Core created originally for demonstrating EF Core shadow properties and query filtering and present a page that shows all records in a table where some are set as soft deletes as shown below which existed prior to adding code to export data to Excel. Note, the button did not exists in the original code.

All other pages in the project use EF Core except this page. To get all records the following method is used to return a read-only list of a model named Report. Since there is no reason to edit the data a read-only list protects the data.

public static async Task<IReadOnlyList<Report>> Reports()
{
    string command =
        """
        SELECT ContactId,
               FirstName,
               LastName,
               LastUser,
               CreatedBy,
               FORMAT(CreatedAt, 'MM/dd/yyyy') AS CreatedAt,
               FORMAT(LastUpdated, 'MM/dd/yyyy') AS LastUpdated,
               IIF(isDeleted = 'TRUE' , 'Y','N') AS Deleted
        FROM dbo.Contact1;
        """;

    await using SqlConnection cn = new(ConnectionString);
    await using SqlCommand cmd = new() { Connection = cn, CommandText = command };

    await cn.OpenAsync();

    var reader = await cmd.ExecuteReaderAsync();

    var list = new List<Report>();

    while (reader.Read())
    {
        list.Add(new Report
        {
            ContactId = reader.GetInt32(0),
            FirstName = reader.GetString(1),
            LastName = reader.GetString(2),
            LastUser = reader.GetString(3),
            CreatedBy = reader.GetString(4),
            CreatedAt = reader.GetString(5),
            LastUpdated = reader.GetString(6),
            Deleted = reader.GetString(7)
        });
    }

    return list.ToImmutableList();

}

The method above is executed from a OnPost event. Note StatusMessage is a property on the page marked as TempData which is used to show a dialog for success or failure for the create and populate an Excel file.

What could go wrong? more likely this would be a permission issue while an error thrown from SpreadSheetLight will point to a developer slip-up.

public async Task<PageResult> OnPostExportButton()
{

    Reports = await DataOperations.Reports();

    StatusMessage = ExcelOperations.ExportToExcel(Reports) ? 
        "Report created <strong>successfully</strong>" : 
        "<strong>Failed</strong> to create report";


    return Page();

}

ExcelOperations.ExportToExcel

Takes the readonly list and converts the list to a DataTable as this is what SpreadSheetLight expects. To do this NuGet package FastMember.NetCore is used as it might be overkill here but when there are thousands of records FastMember is going to be faster than home cooked code.

For an extra touch, iterate the DataColumn collection and change column names from FirstName to First Name for when importing into Excel.

Once there is a DataTable, create an instance of SLDocument which is the main class for interacting with Excel for SpreadSheetLight library.

Next setup styling for the header row using the following code.

Using SLDocument ImportDataTable method, import the DataTable

Parameters

Start at the first row
Column A to start
The DataTable to import
True to include headers

Note if there a need to append data that is possible too by changing the first to parameters and setting the last parameter to false. SpreadSheetLight has the following to get the last used row to help.

SLDocument.GetWorksheetStatistics().EndRowIndex

Next, best not to use Sheet1 for the sheetname so we use RenameWorksheet method to give a meaningful name to the sheet.

Next, set each column width to auto fix the data. Then set the active cell.

Create a unique file name followed by saving the data to a new Excel file.

Full code

using System.Data;
using DocumentFormat.OpenXml.Spreadsheet;
using FastMember;
using SpreadsheetLight;
using Color = System.Drawing.Color;

namespace ShadowProperties.Classes;
public class ExcelOperations
{
    /// <summary>
    /// Create a DataTable from <see>
    ///     <cref>{T}</cref>
    /// </see>
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="sender"></param>
    /// <returns></returns>
    public static DataTable ToDataTable<T>(IReadOnlyList<T> sender)
    {
        DataTable table = new();
        using var reader = ObjectReader.Create(sender);
        table.Load(reader);

        return table;
    }

    public static bool ExportToExcel<T>(IReadOnlyList<T> list)
    {
        DataTable table = ToDataTable<T>(list);

        try
        {
            // split column names e.g. FirstName to First Name
            for (int index = 0; index < table.Columns.Count; index++)
            {
                table.Columns[index].ColumnName = table.Columns[index].ColumnName.SplitCamelCase();
            }

            using var document = new SLDocument();

            document.DocumentProperties.Creator = "Karen Payne";
            document.DocumentProperties.Title = "Deleted reports";
            document.DocumentProperties.Category = "ABC Contacts";

            // define first row style
            var headerStyle = HeaderStye(document);

            /*
             * Import DataTable starting at A1, include column headers
             */
            document.ImportDataTable(1, SLConvert.ToColumnIndex("A"), table, true);

            // provides a meaningful sheetname
            document.RenameWorksheet(SLDocument.DefaultFirstSheetName, "Deleted report for contacts");

            // fpr setting style, auto sizing columns
            var columnCount = table.Columns.Count;
            document.SetCellStyle(1, 1, 1, columnCount, headerStyle);

            for (int columnIndex = 1; columnIndex < columnCount + 1; columnIndex++)
            {
                document.AutoFitColumn(columnIndex);
            }

            string fileName = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments), 
                $"DeletedReport{DateTime.Now:yyyy-MM-dd HH-mm-ss}.xlsx");

            document.SetActiveCell("A2");

            document.SaveAs(fileName);
            return true;
        }
        catch 
        {
            /*
             * Basic reason for failure
             * 1. Developer error
             * 2. User has file open in Excel but here that is not possible because of the file name
             */
            return false;
        }
    }
    /// <summary>
    /// Style for first row in the Excel file
    /// </summary>
    public static SLStyle HeaderStye(SLDocument document)
    {

        SLStyle headerStyle = document.CreateStyle();

        headerStyle.Font.Bold = true;
        headerStyle.Font.FontColor = Color.White;
        headerStyle.Fill.SetPattern(
            PatternValues.LightGray,
            SLThemeColorIndexValues.Accent1Color,
            SLThemeColorIndexValues.Accent5Color);

        return headerStyle;
    }
}

Finished worksheet

Alert the user with a dialog

This is done using sweetalert2 library in tangent with checking a Page property marked as TempData.

Extras

See SheetHelpers.cs under Classes folder in the project ShadowProperties for several helper methods for SpreadSheetLight.

Summary

Presented, how to import data to Excel with styling. A developer can, if no styling is needed get away with three lines of code for a raw import.

Source code

Clone the following GitHub repository with code using EF Core 7, SQL-Server database.

Prepare for running

Run the script under the Scripts folder in the project ShadowProperties named script.sql.

New to EF Core filtering and shadow properties?

Take time to read the main readme file and the Microsoft docs.

In closing

The author actively uses SpreadSheetLight and GemBox.Spreadsheet

Blog