ASP.NET Core/Razor Pages export to Excel
Tags: webdev, codenewbie, database, csharp
Published: 2023-09-04 22:00:14

Learn how to export data from, in this case a SQL-Server database table using [SpreadSheetLight library](https://spreadsheetlight.com/) from their [NuGet package](https://www.nuget.org/packages/SpreadsheetLight/3.5.0?_src=template).

SpreadSheetLight library is relatively unknown while other libraries like those in the [Top 20 NuGet excel Packages](https://nugetmusthaves.com/tag/excel) 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.


![Admin Razor Page](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/7jce4lxg1qnnxq9airfg.png)

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](https://github.com/karenpayneoregon/efcore-shadow-properties/blob/master/ShadowProperties/Models/Report.cs). Since there is no reason to edit the data a read-only list protects the data.

```csharp
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.

```csharp
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](https://www.nuget.org/packages/FastMember.NetCore/1.1.0?_src=template) 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](https://github.com/ARLM-Keller/SpreadsheetLight/blob/master/SLDocument.cs) which is the main class for interacting with Excel for SpreadSheetLight library.

Next setup styling for the header row using [the following code](https://github.com/karenpayneoregon/efcore-shadow-properties/blob/master/ShadowProperties/Classes/ExcelOperations.cs#L86C14-L99).

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.

```csharp
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

```csharp
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


![Finished Excel file](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/47n5m1ojx4rtjst7pz2w.png)

### Alert the user with a dialog

![Finished dialog](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/06gif3es2ruijr72qyfc.png)

This is done using [sweetalert2](https://sweetalert2.github.io/) 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](https://github.com/karenpayneoregon/efcore-shadow-properties/tree/master) 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](https://github.com/karenpayneoregon/efcore-shadow-properties/blob/master/readme.md) and the Microsoft docs.

## See also

[Learn the basics on immutability](https://github.com/karenpayneoregon/basic-immutability-csharp) which has been used in the source code for this article. The code is presented in Windows forms but that does not matter, what is presented can be used in any project type.


## In closing

The author actively uses SpreadSheetLight and [GemBox.Spreadsheet](https://www.gemboxsoftware.com/spreadsheet)