24
- July
2020
Posted By : Rahul
How to Export Data to Excel in Blazor – Asp.Net Core

Creating and exporting data to Excel file is one of the frequently used feature in web apps. We will learn about how to export data to excel in Blazor WebAssembly or Server project in this post. but I will use Blazor WebAssembly in this article.

Why I came up with this post? This is because few days back, someone posted his problem about exporting data to excel from Blazor using Asp.Net Core API in one of the groups of Blazor community and he was trying to solve the problem from last few days, but no success even after so much googling. I saw his post and connected with him just to help him with the same. So more or less, I will try to explain here writing a short article with the solution about exporting excel in Blazor what I provided to him and I’m glad 🤩, he was happy 😀 with solution.

Table of Contents

  1. Demo
  2. Download Source Code
  3. Creating a Blazor Project
  4. Add Code to BlazorDownload.Server Project
  5. Add Code to BlazorDownload.Client Project
  6. Run the application

Demo

export data to excel in blazor

Download Source Code

You can download the source code from GitHub. You can also support me giving a star ⭐ on GitHub, if you found this post or source code helpful. 😊

Creating a Blazor Project

Using Visual Studio

For creating a Blazor WebAssembly project with Asp.Net Core hosted solution using Visual Studio 2019 following below steps –

  • Create a project by selecting File -> New -> Project
  • In the next window, Search “Blazor” in “Search for templates” box or press Alt+S
  • Select Blazor App from the list and press the Next button
  • In the next window, type your project name “BlazorDownload” in my case and select your project location to save
  • Give the solution name or check “Place solution and project in same directory” and click on Create button
  • In the next window, Select Blazor WebAssembly app and click on “ASP.NET Core hosted” checkbox in right and click on Create button
export data to excel in blazor - create blazor project

Once project is created, your project structure will look like below with three projects.

How to Export Data to Excel in Blazor - blazor project structure

Using Command-line tool

If you don’t have Visual Studio 2019 IDE installed, you can create it using .NET CLI. Check out my previous article “Getting Started with Blazor” to create Blazor WebAssembly project using dotnet command line tool.

For more information about .NET CLI, you can check this link.

Add Code to BlazorDownload.Server Project

Create a Controller

Once project is created, now create an API controller named FilesController in the Controllers folder of BlazorDownload.Server project.

Right on Controllers folder -> Add -> Controller then Select API Controller – Empty from the list and click Add button.

Once FilesController is created, Replace all code in the file with the following code –

using Microsoft.AspNetCore.Mvc;

namespace BlazorDownload.Server.Controllers
{
    [Route("api/[controller]/[action]")]
    [ApiController]
    public class FileController : ControllerBase
    {
	private const string XlsxContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
    }
}

Create an API Action to the Controller

Create an action to the controller named DownloadExcel and add the following code to the action method –

[HttpGet]
public IActionResult DownloadExcel()
{
     byte[] reportBytes;

     using(var package = Utils.createExcelPackage(_hostingEnvironment))
     {
          reportBytes = package.GetAsByteArray();
      }

      return File(reportBytes, XlsxContentType, $"MyReport{DateTime.Now.ToString("yyyy-MM-dd", CultureInfo.InvariantCulture)}.xlsx");
}

Add Nuget Package

Now add a Nuget package called EPPlus via Manage Nuget Packages options or via Package Manager Console option.

add nuget package for excel export in blazor

👉 This is not required step if you are not going to create an excel file dynamically or you are using other library to create your excel file.

EPPlus is a library that helps to create an excel-sheet dynamically available for .NET Core as well as .NET Framework both. However this is not required to install this particular library if you are using any other library for creating an excel file.

Create Utils Class

Create a new class named “Utils.cs” and add the following code to the file –

public class Utils
{
        public static ExcelPackage createExcelPackage()
        {
            var package = new ExcelPackage();
            package.Workbook.Properties.Title = "Test Report";
            package.Workbook.Properties.Author = "rahul2306";
            package.Workbook.Properties.Subject = "Test Report";
            package.Workbook.Properties.Keywords = "Testing";


            var worksheet = package.Workbook.Worksheets.Add("Employee");

            //First add the headers
            worksheet.Cells[1, 1].Value = "ID";
            worksheet.Cells[1, 2].Value = "Name";
            worksheet.Cells[1, 3].Value = "Gender";
            worksheet.Cells[1, 4].Value = "Salary (in $)";

            //Add values

            var numberformat = "#,##0";
            var dataCellStyleName = "TableNumber";
            var numStyle = package.Workbook.Styles.CreateNamedStyle(dataCellStyleName);
            numStyle.Style.Numberformat.Format = numberformat;

            worksheet.Cells[2, 1].Value = 1;
            worksheet.Cells[2, 2].Value = "Rahul";
            worksheet.Cells[2, 3].Value = "M";
            worksheet.Cells[2, 4].Value = 50000;
            worksheet.Cells[2, 4].Style.Numberformat.Format = numberformat;

            worksheet.Cells[3, 1].Value = 2;
            worksheet.Cells[3, 2].Value = "Duy";
            worksheet.Cells[3, 3].Value = "M";
            worksheet.Cells[3, 4].Value = 50000;
            worksheet.Cells[3, 4].Style.Numberformat.Format = numberformat;

            worksheet.Cells[4, 1].Value = 3;
            worksheet.Cells[4, 2].Value = "Steve";
            worksheet.Cells[4, 3].Value = "M";
            worksheet.Cells[4, 4].Value = 45000;
            worksheet.Cells[4, 4].Style.Numberformat.Format = numberformat;

            // Add to table / Add summary row
            var tbl = worksheet.Tables.Add(new ExcelAddressBase(fromRow: 1, fromCol: 1, toRow: 4, toColumn: 4), "Data");
            tbl.ShowHeader = true;
            tbl.TableStyle = TableStyles.Dark9;
            tbl.ShowTotal = true;
            tbl.Columns[3].DataCellStyleName = dataCellStyleName;
            tbl.Columns[3].TotalsRowFunction = RowFunctions.Sum;
            worksheet.Cells[5, 4].Style.Numberformat.Format = numberformat;

            // AutoFitColumns
            worksheet.Cells[1, 1, 4, 4].AutoFitColumns();

            return package;
        }
}

Above code create an small excel sheet and fill it with demo data with the help of classes provided by EPPlus Nuget package. This code create a very small excel file with 3-4 rows data for purpose only. You can write your own code to create an excel file and format it as you want.

You can check out the complete sample project here on GitHub for creating excel-sheet and formatting it in a different way.

This is what you have to code for BlazorDownload.Server project. Now let’s proceed to BlazorDownload.Client project.

Add Code to BlazorDownload.Client Project

Create a JavaScript File

A Blazor (.razor) component is not an HTTP endpoint like an MVC view, so like MVC if i try to return FileResult from component, is not going to trigger the download by the browser.

That’s the reason I need to create and use JavaScript to invoke the a file save action and i’ll invoke JavaScript function using JSInterop feature.

In the BlazorDownload.Client project, Create a JS folder and create a JavaScript file named download.js and add the following code –

window.saveAsFile = function (fileName, byteBase64) {
    var link = this.document.createElement('a');
    link.download = fileName;
    link.href = "data:application/octet-stream;base64," + byteBase64;
    this.document.body.appendChild(link);
    link.click();
    this.document.body.removeChild(link);
}

Add JavaScript file

Now add this JavaScript file in index.html file (in case of Blazor WebAssembly project) or to the _Host.cshtml file (in case of Blazor server project) just before the body closing tag like below –

how to export data to excel file in blazor - register JS file

Add a Component

Add a Razor component named “DownloadExcel.razor” to the Pages folder and add the following code –

@page "/download-excel"
@inject IJSRuntime JSRuntime
@inject HttpClient _httpClient

<h3>Click Button to download Excel</h3>

<button @onclick="@(async() => await DownloadFile())">Download</button>
<br />
@if (IsDownloadStarted == 1)
{
    @*<p>Downloading...Please wait</p>*@
    <img src="/images/download.gif" />
}
else if (IsDownloadStarted == 2)
{
    <p>Download complete.</p>
}

@code {
    public int IsDownloadStarted { get; set; } = 0;

    protected async Task DownloadFile()
    {
        if (await JSRuntime.InvokeAsync<bool>("confirm", $"Do you want to Export?"))
        {
            IsDownloadStarted = 1;
            StateHasChanged();
            var response = await _httpClient.GetAsync("/api/files/DownloadExcel");
            response.EnsureSuccessStatusCode();
            var fileBytes = await response.Content.ReadAsByteArrayAsync();
            var fileName = $"MyReport{DateTime.Now.ToString("yyyy-MM-dd", System.Globalization.CultureInfo.InvariantCulture)}.xlsx";
            await JSRuntime.InvokeAsync<object>("saveAsFile", fileName, Convert.ToBase64String(fileBytes));
            IsDownloadStarted = 2;
        }
    }
}

In above code, I have –

  • Injected IJSRuntime and IHttpClient classes in the component where IJSRuntime is used here to call saveAsFile JavaScript function we just added to the file and IHttpClient is used here to invoke DownloadExcel API which we just created in BlazorDownload.Server project.
  • Added a button with @onclick event – When you will click this button, DownloadExcel API will be invoked and then we will invoke a JavaScript saveAsFile function to download excel file.

What is @page directive?

@page directive is used to define a route URL for the current component or making a routable component in Blazor you can say. 😀

What is @inject directive?

@inject directive is used to inject a dependency in razor component. In this article, I have used it to inject IHttpClient as well as IJSRuntime dependencies.

What is IJSRuntime?

A Blazor app can invoke JavaScript functions from .NET methods using IJSRuntime abstraction.

What is IHttpClient?

IHttpClient is used to make the external HTTP calls. In this article, I am using to invoke DownloadExcel HTTP API from the BlazorDownload.Server project.

Add a NavLink

Add a NavLink inside NavMenu.razor file under Shared folder pasting the following code –

<li class="nav-item px-3">
      <NavLink class="nav-link" href="download-excel">
           <span class="oi oi-plus" aria-hidden="true"></span> Download Excel
      </NavLink>
</li>

Run the application

Now run the application via F5 or pressing Ctrl + F5 (for starting without debugging) and navigate to Download Excel menu. Click the download button and your file will be download in a moment. 😊

export data to excel in blazor

Summary

In this post, we have learned about how we can create and format an excel file dynamically and how we can download it in a Blazor WebAssembly project.

Please share this post if you find it helpful. Thank you 🙏 for reading this article. Keep Reading, Keep Growing, Keep Sharing 😊

If you found this article helpful,

BMC logoBuy me a coffee

Comments

Leave a Reply