Blazor - Downloading Excel File

Working on a project at work I have a requirnment for creating and downloading an excel file.

This has proven to be confusing but in the end with the help of our friend google I managed to find and implement the code needed using Javascript in blazor to do this so adding here for my memory and just in case i tmay help anyone else in the future.

First the Javascript file method:

function saveAsFile(filename, bytesBase64) {
    var link = document.createElement('a');
    link.download = filename;
    link.href = "data:application/octet-stream;base64," + bytesBase64;
    document.body.appendChild(link); // Needed for Firefox
    link.click();
    document.body.removeChild(link);
}

Then in the blazor file or class file you would call as

await JSRuntime.InvokeAsync<object>("saveAsFile", fileName, Convert.ToBase64String(content));

It needs (if I have understood this correctly) the content to be byte format so for my excel file I used ClosedXML to create the excel file then at the end exported so it donwloaded to the users selected download location.

What I essentially have in my project is a:

• Razor page which has a table of requests with an export button which calls a method from a custom excel class. This custom excel class is injected into this razor page.

• The custom excel class uses ClosedXML to create the excel file as required with data, formatting etc.

• In the startup the custom excel class is injected as a service so the razor page can inject it.

Custom Class Code:

using System.Threading.Tasks;
using System.IO;
using Microsoft.JSInterop;


namespace SACOMaintenance.Blazor.Server.Services
{
    public class MaintenanceRequestsExcel
    {

        private readonly IJSRuntime JSRuntime;

        public MaintenanceRequestsExcel(IJSRuntime jSRuntime)
        {
            JSRuntime = jSRuntime;
        }

            using (var workbook = new XLWorkbook())
            {
                var worksheet = workbook.Worksheets.Add("Maint Requests All");

                worksheet.Cell("A1").Value = "All Maintenance Requests";
                worksheet.Cell("A1").Style.Font.Bold = true;
                worksheet.Cell("A1").Style.Font.FontSize = 20;
                worksheet.Range("A1", "E1").Merge(true);

                worksheet.Cell("A2").Value = "Id";
                worksheet.Cell("A2").Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
                worksheet.Cell("A2").Style.Font.Bold = true;

                worksheet.Cell("B2").Value = "Details";
                worksheet.Cell("B2").Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
                worksheet.Cell("B2").Style.Font.Bold = true;

                worksheet.Cell("C2").Value = "Date Raised";
                worksheet.Cell("C2").Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
                worksheet.Cell("C2").Style.Font.Bold = true;

                worksheet.Cell("D2").Value = "Equipment Name";
                worksheet.Cell("D2").Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
                worksheet.Cell("D2").Style.Font.Bold = true;

                worksheet.Cell("E2").Value = "Status";
                worksheet.Cell("E2").Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
                worksheet.Cell("E2").Style.Font.Bold = true;

                worksheet.Column(1).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
                worksheet.Column(3).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
                worksheet.Column(4).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
                worksheet.Column(5).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);

                worksheet.Range("A2", "E2").SetAutoFilter();

                //loop through the collection and put the items in columns then increment to the next row
                var i = 3; //declare the row number to start

                //loop through the items in the list
                foreach(var item in exportList)
                {
                    worksheet.Cell("A" + i).Value = item.Id;
                    worksheet.Cell("B" + i).Value = item.RequestDetails;
                    worksheet.Cell("C" + i).Value = item.DateRaised;
                    worksheet.Cell("D" + i).Value = item.Equipment.Name;
                    worksheet.Cell("E" + i).Value = item.Status.StatusName;
                    i++;
                }

                worksheet.Columns("A", "E").AdjustToContents();

                //Download the excel file to the users download default lcoation
                using (var stream = new MemoryStream())
                {
                    workbook.SaveAs(stream);
                    var content = stream.ToArray();

                    var fileName = "Maintenance Request Export " + DateTime.Now.ToString("dd-MM-yyyy") + " .xlsx"; //TODO: see if this format can be changed in the app settings?
                    await JSRuntime.InvokeAsync<object>("saveAsFile", fileName, Convert.ToBase64String(content));
                } 
            }
        }
    }  
}

So the Javascript is add from a using statment so it can call the relevant javascript file from this line

using Microsoft.JSInterop;

Then the IJSRuntime is injected into the class and constructor.

 private readonly IJSRuntime JSRuntime;

        public MaintenanceRequestsExcel(IJSRuntime jSRuntime)
        {
            JSRuntime = jSRuntime;
        }

The part of the method that creates the excel and download with the Javascript function is

using (var stream = new MemoryStream())
{
       workbook.SaveAs(stream);
        var content = stream.ToArray();

        var fileName = "Maintenance Request Export " + DateTime.Now.ToString("dd-MM-yyyy") + " .xlsx"; //TODO: see if this format can be changed in the app settings?
        await JSRuntime.InvokeAsync<object>("saveAsFile", fileName, Convert.ToBase64String(content));
}

In the startup class in blazor the excel class is added as a service

services.AddScoped<MaintenanceRequestsExcel>();

In the razor page file I call the excel class method to run the export function and download the file. The method is tied to an onclick event of a button.

public async Task ExportExcelFile()
 {
        await ExcelExport.ExportListToExcel(maintReqListViewModel.requests);
 }

Here is a quick video of it in action where it is exporting the table shown into an excel file.