Blazor export to excel

Question

I am attempting to add an export to excel button on my blazor server side app. So far after combing the internet this is what I have done.

My button

My method in my .razor page

    public FileResult DownloadExcel(string Region, DateTime StartDate, DateTime EndDate)
    {
        FileResult ExcelFile = searchService.ExportToExcel(Region, StartDate, EndDate);
        return ExcelFile;
    }

And Finally my logic in my service

        public FileResult ExportToExcel(string Region, DateTime StartDate, DateTime EndDate)
        {
            var queryable = context.AuditCardPinrecords.Where(s => Region == s.RegionRecordId)
                .Where(s => s.AuditComplete == true)
                .Where(s => s.DateTime >= StartDate && s.DateTime <= EndDate).AsQueryable();

            var stream = new MemoryStream();

            using (var package = new ExcelPackage(stream))
            {
                var workSheet = package.Workbook.Worksheets.Add("Sheet1");
                workSheet.Cells.LoadFromCollection(queryable, true);
                package.Save();
            }


            string excelName = $"AuditPinRecords-{DateTime.Now.ToString("yyyyMMddHHmmssfff")}.xlsx";

            return File(stream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", excelName); 

        }

My expected result is to download the excel file. Unfortunately nothing happens on button click. Any advice would be greatly appreciated. Thanks!

user_4010106 · Answer

I had a similar need and was able to piece together how to do it via javascript from this project: https://github.com/timplourde/dcidr-blazor

Static Utility Service:

public static class ExcelService
{
    public static byte[] GenerateExcelWorkbook()
    {
        var list = new List()
        {
            new UserInfo { UserName = "catcher", Age = 18 },
            new UserInfo { UserName = "james", Age = 20 },
        };
        var stream = new MemoryStream();

        // ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
        using (var package = new ExcelPackage(stream))
        {
            var workSheet = package.Workbook.Worksheets.Add("Sheet1");

            // simple way
            workSheet.Cells.LoadFromCollection(list, true);

            ////// mutual
            ////workSheet.Row(1).Height = 20;
            ////workSheet.Row(1).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
            ////workSheet.Row(1).Style.Font.Bold = true;
            ////workSheet.Cells[1, 1].Value = "No";
            ////workSheet.Cells[1, 2].Value = "Name";
            ////workSheet.Cells[1, 3].Value = "Age";

            ////int recordIndex = 2;
            ////foreach (var item in list)
            ////{
            ////    workSheet.Cells[recordIndex, 1].Value = (recordIndex - 1).ToString();
            ////    workSheet.Cells[recordIndex, 2].Value = item.UserName;
            ////    workSheet.Cells[recordIndex, 3].Value = item.Age;
            ////    recordIndex++;
            ////}

            return package.GetAsByteArray();
        }
    }
}

public class UserInfo
{
    public string UserName { get; set; }
    public int Age { get; set; }
}

Create a js folder with a site.js file in the wwwroot folder

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

In your _Host.cshtml file add the following script in the body section

In your .razor page that you want to export to excel from

@using YOUR_APP_NAME.Services

@inject IJSRuntime js


    


@code {
    private void DownloadExcelFile()
    {
        var excelBytes = ExcelService.GenerateExcelWorkbook();
        js.InvokeVoidAsync("saveAsFile", $"test_{DateTime.Now.ToString("yyyyMMdd_HHmmss")}.xlsx", Convert.ToBase64String(excelBytes));
    }
}

user_757937 · Answer

A .razor component is not an HTTP endpoint like an MVC view is. Your return FileResult is not going to trigger a download by the browser.

You will need to create an MVC controller action and redirect the user there, or use JavaScript to invoke the a file save action. You would then need to use the JavaScript Interop to invoke the JS function.

window.msSaveBlob = function (payload, filename) {

    const createBlob = data => new Blob([data], { type: "text/csv;charset=utf-8;" });

    const buildDownloadLink = (blob, fileName) => {
        let link = document.createElement("a");
        link.setAttribute("href", URL.createObjectURL(blob));
        link.setAttribute("download", fileName);
        link.style = "visibility:hidden";
        return link;
    };
    const invokeDownload = link => {
        document.body.appendChild(link);
        link.click();
        document.body.removeChild(link);
    };
    const isHtmlDownloadAllowed = document.createElement("a").download !== undefined;
    const isSaveBlobAllowed = navigator.msSaveBlob;

    isSaveBlobAllowed ? navigator.msSaveBlob(createBlob(payload), filename) :
        isHtmlDownloadAllowed ? invokeDownload(buildDownloadLink(createBlob(payload), filename)) :
            console.log("Feature unsupported");

};

Of course there are also commercial libraries to help with these types of things. Telerik UI for Blazor Document Processing

Blazor export to excel

2 Answers