Blazor WASM: Exporting and Importing Data to Excel and PDF

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

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

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 uploadedList = await ExcelService.Read(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(IEnumerable 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 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.