DEV Community

Karen Payne
Karen Payne

Posted on

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.

Admin Razor Page

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();

}


Enter fullscreen mode Exit fullscreen mode

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();

}


Enter fullscreen mode Exit fullscreen mode

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


Enter fullscreen mode Exit fullscreen mode

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);

    <span class="k">return</span> <span class="n">table</span><span class="p">;</span>
<span class="p">}</span>

<span class="k">public</span> <span class="k">static</span> <span class="kt">bool</span> <span class="n">ExportToExcel</span><span class="p">&lt;</span><span class="n">T</span><span class="p">&gt;(</span><span class="n">IReadOnlyList</span><span class="p">&lt;</span><span class="n">T</span><span class="p">&gt;</span> <span class="n">list</span><span class="p">)</span>
<span class="p">{</span>
    <span class="n">DataTable</span> <span class="n">table</span> <span class="p">=</span> <span class="n">ToDataTable</span><span class="p">&lt;</span><span class="n">T</span><span class="p">&gt;(</span><span class="n">list</span><span class="p">);</span>

    <span class="k">try</span>
    <span class="p">{</span>
        <span class="c1">// split column names e.g. FirstName to First Name</span>
        <span class="k">for</span> <span class="p">(</span><span class="kt">int</span> <span class="n">index</span> <span class="p">=</span> <span class="m">0</span><span class="p">;</span> <span class="n">index</span> <span class="p">&lt;</span> <span class="n">table</span><span class="p">.</span><span class="n">Columns</span><span class="p">.</span><span class="n">Count</span><span class="p">;</span> <span class="n">index</span><span class="p">++)</span>
        <span class="p">{</span>
            <span class="n">table</span><span class="p">.</span><span class="n">Columns</span><span class="p">[</span><span class="n">index</span><span class="p">].</span><span class="n">ColumnName</span> <span class="p">=</span> <span class="n">table</span><span class="p">.</span><span class="n">Columns</span><span class="p">[</span><span class="n">index</span><span class="p">].</span><span class="n">ColumnName</span><span class="p">.</span><span class="nf">SplitCamelCase</span><span class="p">();</span>
        <span class="p">}</span>

        <span class="k">using</span> <span class="nn">var</span> <span class="n">document</span> <span class="p">=</span> <span class="k">new</span> <span class="nf">SLDocument</span><span class="p">();</span>

        <span class="n">document</span><span class="p">.</span><span class="n">DocumentProperties</span><span class="p">.</span><span class="n">Creator</span> <span class="p">=</span> <span class="s">"Karen Payne"</span><span class="p">;</span>
        <span class="n">document</span><span class="p">.</span><span class="n">DocumentProperties</span><span class="p">.</span><span class="n">Title</span> <span class="p">=</span> <span class="s">"Deleted reports"</span><span class="p">;</span>
        <span class="n">document</span><span class="p">.</span><span class="n">DocumentProperties</span><span class="p">.</span><span class="n">Category</span> <span class="p">=</span> <span class="s">"ABC Contacts"</span><span class="p">;</span>

        <span class="c1">// define first row style</span>
        <span class="kt">var</span> <span class="n">headerStyle</span> <span class="p">=</span> <span class="nf">HeaderStye</span><span class="p">(</span><span class="n">document</span><span class="p">);</span>

        <span class="cm">/*
         * Import DataTable starting at A1, include column headers
         */</span>
        <span class="n">document</span><span class="p">.</span><span class="nf">ImportDataTable</span><span class="p">(</span><span class="m">1</span><span class="p">,</span> <span class="n">SLConvert</span><span class="p">.</span><span class="nf">ToColumnIndex</span><span class="p">(</span><span class="s">"A"</span><span class="p">),</span> <span class="n">table</span><span class="p">,</span> <span class="k">true</span><span class="p">);</span>

        <span class="c1">// provides a meaningful sheetname</span>
        <span class="n">document</span><span class="p">.</span><span class="nf">RenameWorksheet</span><span class="p">(</span><span class="n">SLDocument</span><span class="p">.</span><span class="n">DefaultFirstSheetName</span><span class="p">,</span> <span class="s">"Deleted report for contacts"</span><span class="p">);</span>

        <span class="c1">// fpr setting style, auto sizing columns</span>
        <span class="kt">var</span> <span class="n">columnCount</span> <span class="p">=</span> <span class="n">table</span><span class="p">.</span><span class="n">Columns</span><span class="p">.</span><span class="n">Count</span><span class="p">;</span>
        <span class="n">document</span><span class="p">.</span><span class="nf">SetCellStyle</span><span class="p">(</span><span class="m">1</span><span class="p">,</span> <span class="m">1</span><span class="p">,</span> <span class="m">1</span><span class="p">,</span> <span class="n">columnCount</span><span class="p">,</span> <span class="n">headerStyle</span><span class="p">);</span>

        <span class="k">for</span> <span class="p">(</span><span class="kt">int</span> <span class="n">columnIndex</span> <span class="p">=</span> <span class="m">1</span><span class="p">;</span> <span class="n">columnIndex</span> <span class="p">&lt;</span> <span class="n">columnCount</span> <span class="p">+</span> <span class="m">1</span><span class="p">;</span> <span class="n">columnIndex</span><span class="p">++)</span>
        <span class="p">{</span>
            <span class="n">document</span><span class="p">.</span><span class="nf">AutoFitColumn</span><span class="p">(</span><span class="n">columnIndex</span><span class="p">);</span>
        <span class="p">}</span>

        <span class="kt">string</span> <span class="n">fileName</span> <span class="p">=</span> <span class="n">Path</span><span class="p">.</span><span class="nf">Combine</span><span class="p">(</span><span class="n">Environment</span><span class="p">.</span><span class="nf">GetFolderPath</span><span class="p">(</span><span class="n">Environment</span><span class="p">.</span><span class="n">SpecialFolder</span><span class="p">.</span><span class="n">MyDocuments</span><span class="p">),</span> 
            <span class="s">$"DeletedReport</span><span class="p">{</span><span class="n">DateTime</span><span class="p">.</span><span class="n">Now</span><span class="p">:</span><span class="n">yyyy</span><span class="p">-</span><span class="n">MM</span><span class="p">-</span><span class="n">dd</span> <span class="n">HH</span><span class="p">-</span><span class="n">mm</span><span class="p">-</span><span class="n">ss</span><span class="p">}</span><span class="s">.xlsx"</span><span class="p">);</span>

        <span class="n">document</span><span class="p">.</span><span class="nf">SetActiveCell</span><span class="p">(</span><span class="s">"A2"</span><span class="p">);</span>

        <span class="n">document</span><span class="p">.</span><span class="nf">SaveAs</span><span class="p">(</span><span class="n">fileName</span><span class="p">);</span>
        <span class="k">return</span> <span class="k">true</span><span class="p">;</span>
    <span class="p">}</span>
    <span class="k">catch</span> 
    <span class="p">{</span>
        <span class="cm">/*
         * 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
         */</span>
        <span class="k">return</span> <span class="k">false</span><span class="p">;</span>
    <span class="p">}</span>
<span class="p">}</span>
<span class="c1">/// &lt;summary&gt;</span>
<span class="c1">/// Style for first row in the Excel file</span>
<span class="c1">/// &lt;/summary&gt;</span>
<span class="k">public</span> <span class="k">static</span> <span class="n">SLStyle</span> <span class="nf">HeaderStye</span><span class="p">(</span><span class="n">SLDocument</span> <span class="n">document</span><span class="p">)</span>
<span class="p">{</span>

    <span class="n">SLStyle</span> <span class="n">headerStyle</span> <span class="p">=</span> <span class="n">document</span><span class="p">.</span><span class="nf">CreateStyle</span><span class="p">();</span>

    <span class="n">headerStyle</span><span class="p">.</span><span class="n">Font</span><span class="p">.</span><span class="n">Bold</span> <span class="p">=</span> <span class="k">true</span><span class="p">;</span>
    <span class="n">headerStyle</span><span class="p">.</span><span class="n">Font</span><span class="p">.</span><span class="n">FontColor</span> <span class="p">=</span> <span class="n">Color</span><span class="p">.</span><span class="n">White</span><span class="p">;</span>
    <span class="n">headerStyle</span><span class="p">.</span><span class="n">Fill</span><span class="p">.</span><span class="nf">SetPattern</span><span class="p">(</span>
        <span class="n">PatternValues</span><span class="p">.</span><span class="n">LightGray</span><span class="p">,</span>
        <span class="n">SLThemeColorIndexValues</span><span class="p">.</span><span class="n">Accent1Color</span><span class="p">,</span>
        <span class="n">SLThemeColorIndexValues</span><span class="p">.</span><span class="n">Accent5Color</span><span class="p">);</span>

    <span class="k">return</span> <span class="n">headerStyle</span><span class="p">;</span>
<span class="p">}</span>
Enter fullscreen mode Exit fullscreen mode

}

Enter fullscreen mode Exit fullscreen mode




Finished worksheet

Finished Excel file

Alert the user with a dialog

Finished 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.

See also

Learn the basics on immutability 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

Top comments (0)