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