Blazor WASM: Exporting and Importing Data to Excel and PDF
5 min readApr 13, 2024
Introduction
In modern web development, the ability to seamlessly export and import data is crucial for enhancing user experience and improving workflow efficiency. In this blog post, we’ll explore how to create a generic service in a Blazor WASM application to export data to Excel files, read data from Excel files into classes, and even create a generic method to export data to PDF format.
Getting Started
Getting started with exporting and importing data in Excel and PDF formats in your Blazor WASM application is simple. We’ll be using the ClosedXML library for Excel and iTextSharp.LGPLv2.Core for PDF functionalities. Follow these steps to get started:
- Installing ClosedXML for Excel Export
dotnet add package ClosedXML --version 0.101.0- Installing iTextSharp.LGPLv2.Core for PDF Export
dotnet add package iTextSharp.LGPLv2.Core --version 3.4.18- Create saveAsFile function in “wwwroot/index.html” file.
<script>
window.saveAsFile = function (filename, data) {
const blob = new Blob([data], { type: 'application/octet-stream' });
const url = window.URL.createObjectURL(blob);
const a = document.createElement('a');
a.href = url;
a.download = filename;
document.body.appendChild(a);
a.click();
document.body.removeChild(a);
window.URL.revokeObjectURL(url);
};
</script>Exporting Data to Excel
Exporting data to Excel is a common requirement in many web applications, especially when dealing with tabular data or reports. Let’s dive into how we can achieve this in a Blazor WASM application.
- Create a new file named as “ExcelService.cs” in services folder and add the following code:
using ClosedXML.Excel;
using ClosedXML.Graphics;
using Microsoft.JSInterop;
using System.Text.RegularExpressions;
namespace APP.Services
{
public class ExcelService
{
private readonly IJSRuntime _jsRuntime;
private readonly HttpClient _httpClient;
public ExcelService(IJSRuntime jsRuntime, HttpClient httpClient)
{
_jsRuntime = jsRuntime;
_httpClient = httpClient;
}
public async Task Export<T>(IEnumerable<T> data, string fileName)
{
using (var workbook = new XLWorkbook())
{
var propertyNames = typeof(T).GetProperties().Select(p => p.Name).ToList();
var worksheet = workbook.Worksheets.Add("Sheet1");
// Write header row
for (int i = 0; i < propertyNames.Count; i++)
{
worksheet.Cell(1, i + 1).Value = propertyNames[i];
}
// Write data rows
var rowData = data.ToList();
for (int rowIndex = 0; rowIndex < rowData.Count; rowIndex++)
{
for (int colIndex = 0; colIndex < propertyNames.Count; colIndex++)
{
var propertyName = propertyNames[colIndex];
var propertyValue = typeof(T).GetProperty(propertyName)?.GetValue(rowData[rowIndex])?.ToString();
worksheet.Cell(rowIndex + 2, colIndex + 1).Value = propertyValue;
}
}
// Create Table
worksheet.RangeUsed().CreateTable();
// Save the workbook to a memory stream
using (var stream = new MemoryStream())
{
workbook.SaveAs(stream);
await _jsRuntime.InvokeVoidAsync("saveAsFile", fileName, stream.ToArray());
}
}
}
}
}- Register the service in “Program.cs” file.
builder.Services.AddScoped<ExcelService>();- Whenever you want to use the service simply inject it and use as follows:
@inject ExcelService ExcelService
@code{
List<Category> categories = new();
protected override async Task OnInitializedAsync(){
// API call to fetch categories
}
private async Task ExportToExcel(){
await ExcelService.Export(categories, "Categories.xlsx");
}
}Importing Data from Excel
To import data from excel add following method to “ExcelService.cs” file.
public async Task<List<T>> Read<T>(MemoryStream ms)
{
List<T> items = new();
// You can use any font that exists in your project's wwwroot/fonts directory. If there no font file just download and add one.
var fallbackFontStream = await _httpClient.GetStreamAsync("fonts/SourceSansPro-Regular.woff");
var loadOptions = new ClosedXML.Excel.LoadOptions
{
// Create a default graphic engine that uses only fallback font and additional fonts passed as streams. It also uses system fonts.
GraphicEngine = DefaultGraphicEngine.CreateWithFontsAndSystemFonts(fallbackFontStream)
};
using (var workbook = new XLWorkbook(ms, loadOptions))
{
var worksheet = workbook.Worksheet(1);
var table = worksheet.Table("Table1");
var headers = table.HeadersRow().Cells().Select(c => c.Value.ToString()).ToList();
foreach (var row in table.DataRange.RowsUsed())
{
T item = Activator.CreateInstance<T>();
for (int i = 1; i <= headers.Count(); i++)
{
var header = headers[i - 1].ToString();
var cellValue = row.Cell(i).Value.ToString();
if (!string.IsNullOrEmpty(cellValue))
{
var property = typeof(T).GetProperty(header);
if (property != null)
{
var targetType = Nullable.GetUnderlyingType(property.PropertyType) ?? property.PropertyType;
if (targetType == typeof(TaxOn))
{
Enum.TryParse(cellValue, out TaxOn value);
property.SetValue(item, value);
}
else if (targetType == typeof(double?))
{
double parsedValue;
if (double.TryParse(cellValue, out parsedValue))
{
property.SetValue(item, parsedValue);
}
else
{
property.SetValue(item, null);
}
}
else if (targetType == typeof(DateTime))
{
DateTime parsedValue;
if (DateTime.TryParse(cellValue, out parsedValue))
{
property.SetValue(item, parsedValue);
}
else
{
property.SetValue(item, null);
}
}
else
{
var value = Convert.ChangeType(cellValue, targetType);
property.SetValue(item, value);
}
}
}
}
items.Add(item);
}
}
return items;
}
}- Use it wherever you want. I’m using it with a file input.
@inject ExcelService ExcelService
<InputFile class="form-control w-100" accept=".xlsx" OnChange="@FileUploaded" />
@code{
private async Task FileUploaded(InputFileChangeEventArgs e)
{
using (MemoryStream ms = new MemoryStream())
{
await e.GetMultipleFiles(1).First().OpenReadStream().CopyToAsync(ms);
ms.Position = 0;
List<Category> uploadedList = await ExcelService.Read<Category>(ms);
}
}
}Exporting Data to PDF
- Create a new file named “PdfService.cs” in services folder and add following code:
using iTextSharp.text.pdf;
using iTextSharp.text;
using Microsoft.JSInterop;
namespace APP.Services
{
public class PdfService
{
private readonly IJSRuntime _jsRuntime;
private readonly Common.Common _common;
public PdfService(IJSRuntime jsRuntime, Common.Common common)
{
_jsRuntime = jsRuntime;
_common = common;
}
public async Task Export<T>(IEnumerable<T> data, string fileName, bool landscape = true)
{
var pageSize = landscape ? PageSize.A4.Rotate() : PageSize.A4;
Document doc = new(pageSize, 10, 10, 10, 10);
var stream = new MemoryStream();
PdfWriter.GetInstance(doc, stream);
doc.Open();
Font titleFont = FontFactory.GetFont(FontFactory.HELVETICA_BOLD, 16);
Paragraph title = new(fileName + "\n", titleFont)
{
Alignment = Element.ALIGN_CENTER
};
doc.Add(title);
doc.Add(new Paragraph("\n"));
var propertyNames = typeof(T).GetProperties().Select(p => p.Name).ToList();
PdfPTable table = new(propertyNames.Count)
{
WidthPercentage = 100
};
foreach (var propertyName in propertyNames)
{
PdfPCell headerCell = new(new Phrase(propertyName.SplitCamelCase(), new Font(Font.HELVETICA, 11f)))
{
VerticalAlignment = Element.ALIGN_MIDDLE,
ExtraParagraphSpace = 2
};
table.AddCell(headerCell);
}
foreach (var item in data)
{
foreach (var propertyName in propertyNames)
{
var property = typeof(T).GetProperty(propertyName);
if (property != null)
{
var value = property.GetValue(item);
PdfPCell dataCell = new(new Phrase(value == null ? "" : value.ToString(), new Font(Font.HELVETICA, 11f)))
{
VerticalAlignment = Element.ALIGN_MIDDLE,
ExtraParagraphSpace = 2
};
table.AddCell(dataCell);
}
}
}
doc.Add(table);
doc.Close();
await _jsRuntime.InvokeVoidAsync("saveAsFile", $"{fileName}.pdf", stream.ToArray());
}
}
}- Register the service in “Program.cs” file.
builder.Services.AddScoped<PdfService>();- Simply inject and use it when you need it.
@inject PdfService PdfService
@code{
List<Category> categories = new();
protected override async Task OnInitializedAsync(){
// API call to fetch categories
}
private async Task ExportToPdf(){
await PdfService.Export(categories, "Categories", false);
}
}Conclusion
By implementing a generic service for exporting and importing data in a Blazor WASM application, we can streamline data handling processes and enhance user productivity. Whether it’s generating reports, analyzing datasets, or importing external data, these functionalities empower developers to create more efficient web applications. Experiment with these methods in your own projects to unlock the full potential of data management in Blazor WASM.
