April 19, 2024

PureSourceCode

All technologies, only pure source code

How to Export Data to Excel in Blazor

Enrico019 mins

In this new post, I’m going to show how to export data to Excel in Blazor WebAssembly or Server. Creating and exporting data to Excel file is one of the frequently used feature in web apps.

First, for creating an export in Excel, I will use a free library that is a NuGet package. ClosedXML is a .NET library for reading, manipulating and writing Excel 2007+ (.xlsx, .xlsm) files. It aims to provide an intuitive and user-friendly interface to dealing with the underlying OpenXML API.

So, you can download the full source code of this post from GitHub.

Then, for more documentation, example and components about Blazor, here same links in this blog:

At the end of this post, I can download an export in Excel from a Blazor application. Here the result.

How to Export Data to Excel in Blazor: the result
How to Export Data to Excel in Blazor: the result

Create the solution

First, we have to create a solution and 2 projects. The first one is a Blazor WebAssembly project and the other one is an ASP.NET Core WebAPI project. I showed how to create those kind of projects in few posts and you have the list at the top. If you want or not sure, you can download the source code from GitHub. The result is like the following screenshot.

The solution in Visual Studio - How to Export Data to Excel in Blazor
The solution in Visual Studio

Now, in the API project, I’m going to add the ClosedXML NuGet package or type

Install-Package ClosedXML

This library is free and there are some extensions:

Also, I recommend to look at ClosedXML.Report repository because it allows you to create very nice report with Excel based on an Excel template.

So that, I’m going to create a Models folder in the WebAPI project and then a new class for Author (very original eheh)

public class Author
{
	public int Id { get; set; }
	public string FirstName { get; set; }
	public string LastName { get; set; }
}

Then, in another folder, I’m creating the AuthorData where there is a list of authors.

public static class AuthorData
{
	static List<Author> Authors = new List<Author>
	{
		new Author { Id = 1, FirstName = "Enrico", LastName = "Rossini" },
		new Author { Id = 2, FirstName = "Donal", LastName = "Duck" },
		new Author { Id = 3, FirstName = "Mickey", LastName = "Mouse"}
	};
}

Create Export service

Now, I’m creating a new Services folder and a new ExportService class. The easy way to export our data in Excel is creating an CSV file (comma-separated). Quick and easy. Here the code

using PSCBlazorExportExcel.Api.Models;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace PSCBlazorExportExcel.Api.Services
{
	public class ExportService
	{
		public string GetCSV(IEnumerable<Author> list)
		{
			StringBuilder stringBuilder = new StringBuilder();
			stringBuilder.AppendLine("Id,FirstName,LastName");
			foreach (var author in list)
			{
				stringBuilder.AppendLine($"{author.Id},{author.FirstName},{ author.LastName}");
			}

			return stringBuilder.ToString();
		}
	}
}

So, this is cool but we can do better! Let’s use ClosedXML.

Create a proper Excel file

Now, a workbook in Excel consists of several worksheets. You can create an Excel workbook using the following code.

var workbook = new XLWorkbook();

You can then take advantage of the IXLWorkSheet interface to create and add worksheets to the workbook as shown below.

IXLWorksheet worksheet = workbook.Worksheets.Add("Authors");
worksheet.Cell(1, 1).Value = "Id";
worksheet.Cell(1, 2).Value = "FirstName";
worksheet.Cell(1, 3).Value = "LastName";
for (int index = 1; index <= authors.Count; index++)
{
   worksheet.Cell(index + 1, 1).Value = authors[index - 1].Id;
   worksheet.Cell(index + 1, 2).Value = authors[index - 1].FirstName;
   worksheet.Cell(index + 1, 3).Value = authors[index - 1].LastName;
}

Lastly, you can save the workbook as a memory stream as shown below.

private byte[] ConvertToByte(XLWorkbook workbook)
{
	var stream = new MemoryStream();
	workbook.SaveAs(stream);

	var content = stream.ToArray();
	return content;
}

Now, I can use this function to create an API that returns FileContentResult. We will see that in a moment. So, this is the full code of the ExportService.

using ClosedXML.Excel;
using PSCBlazorExportExcel.Api.Data;
using PSCBlazorExportExcel.Api.Models;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace PSCBlazorExportExcel.Api.Services
{
	public class ExportService
	{
		#region CSV
		public string GetCSV(IEnumerable<Author> list)
		{
			StringBuilder stringBuilder = new StringBuilder();
			stringBuilder.AppendLine("Id,FirstName,LastName");
			foreach (var author in list)
			{
				stringBuilder.AppendLine($"{author.Id},{author.FirstName},{ author.LastName}");
			}

			return stringBuilder.ToString();
		}
		#endregion

		#region Excel
		private byte[] ConvertToByte(XLWorkbook workbook)
		{
			var stream = new MemoryStream();
			workbook.SaveAs(stream);

			var content = stream.ToArray();
			return content;
		}

		public byte[] CreateAuthorExport()
		{
			var workbook = new XLWorkbook();
			workbook.Properties.Title = "Export from authors";
			workbook.Properties.Author = "Enrico Rossini";
			workbook.Properties.Subject = "Export from authors";
			workbook.Properties.Keywords = "authors, puresourcecode, blazor";

			CreateAuthorWorksheet(workbook);

			return ConvertToByte(workbook);
		}

		public byte[] CreateFullExport()
		{
			var workbook = new XLWorkbook();
			workbook.Properties.Title = "Full Export";
			workbook.Properties.Author = "Enrico Rossini";
			workbook.Properties.Subject = "Full Export";
			workbook.Properties.Keywords = "authors, puresourcecode, blazor";

			CreateAuthorWorksheet(workbook);
			CreateOtherWorksheet(workbook);

			return ConvertToByte(workbook);
		}

		public void CreateAuthorWorksheet(XLWorkbook package)
		{
			var worksheet = package.Worksheets.Add("Authors");

			worksheet.Cell(1, 1).Value = "Id";
			worksheet.Cell(1, 2).Value = "FirstName";
			worksheet.Cell(1, 3).Value = "LastName";
			for (int index = 1; index <= AuthorData.Authors.Count; index++)
			{
				worksheet.Cell(index + 1, 1).Value = AuthorData.Authors[index - 1].Id;
				worksheet.Cell(index + 1, 2).Value = AuthorData.Authors[index - 1].FirstName;
				worksheet.Cell(index + 1, 3).Value = AuthorData.Authors[index - 1].LastName;
			}
		}

		public void CreateOtherWorksheet(XLWorkbook package)
		{
		}
		#endregion
	}
}

Now, I created CreateAuthorWorksheet that is creating a new worksheet in the XLWorkbook. Also, you see 2 very identical functions: CreateAuthorExport and CreateFullExport. Why? If you have a lot of data to export, probably you want to have an export for each model but also a full export of all the data. So, I created the CreateFullExport where I call all the worksheet creation. This approach gives you the flexibility to create the combination of export you want. Each create worksheet function adds a new worksheet in the main workbook.

API time

Now, I have to create the API. Quite straight forward. First, in the Startup.cs add the ExportService in the dependency injection. So, I can use it in the controller.

public void ConfigureServices(IServiceCollection services)
{
	services.AddTransient<ExportService>();

	services.AddControllers();
	services.AddSwaggerGen(c =>
	{
		c.SwaggerDoc("v1", new OpenApiInfo { Title = "PSCBlazorExportExcel.Api", Version = "v1" });
	});
}

Then, create a new ExportController. The code for the ExportController is the folllowing:

using Microsoft.AspNetCore.Mvc;
using PSCBlazorExportExcel.Api.Services;

namespace PSCBlazorExportExcel.Api.Controllers
{
	[ApiController]
	[Route("api/[controller]")]
	public class ExportController : Controller
	{
		private ExportService _service;

		private string contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
		private string fileName = "authors.xlsx";

		public ExportController(ExportService service)
		{
			_service = service;
		}

		[HttpGet]
		public IActionResult DownloadAuthorsExport()
		{
			return File(_service.CreateAuthorExport(), contentType, fileName);
		}
	}
}

In the code you see a constant for the content type contentType to use in the DownloadAuthorsExport and the file name. You can add another function for downloading the full report, same code different name. Now, I can run the project and play with Swagger. For more information about Swagger, please read this post.

So, press the Execute button and you can download the file.

Download file from Swagger documentation - How to Export Data to Excel in Blazor
Download file from Swagger documentation

The file is a normal Excel and you see the worksheet has the name I defined in the ExportService.

The export from the WebAPI - How to Export Data to Excel in Blazor
The export from the WebAPI

This is cool! The title of this post is “How to Export Data to Excel in Blazor”. I haven’t spoke about Blazor yet. Now that the API are ready I take care of Blazor.

Spoiler alert: a simple call to the DownloadAuthorsExport is not enough in Blazor to start to download the file

CORS

There is one important configuration to do in the WebAPI Startup.cs about CORS. Cross-Origin Resource Sharing (CORS) is an HTTP-header based mechanism that allows a server to indicate any other origins (domain, scheme, or port) than its own from which a browser should permit loading of resources. CORS also relies on a mechanism by which browsers make a “preflight” request to the server hosting the cross-origin resource, in order to check that the server will permit the actual request. In that preflight, the browser sends headers that indicate the HTTP method and headers that will be used in the actual request.

Without the right configuration in the WebAPI project, the Blazor application won’t be allow to use the api. To add CORS in the Startup.cs under ConfigureServices add this code:

readonly string MyAllowSpecificOrigins = "_myAllowSpecificOrigins";

public void ConfigureServices(IServiceCollection services)
{
	services.AddCors(options =>
	{
		options.AddPolicy(name: MyAllowSpecificOrigins,
			builder =>
			{
				builder.AllowAnyOrigin()
					   .AllowAnyMethod()
					   .AllowAnyHeader();
			});
	});
}

So, this code allows the WebAPI to replies to every call from any origin, any method and header. In a real application, you could be changed those settings for security reason.

Then, in the Configure method, add

app.UseCors(MyAllowSpecificOrigins);

Download a file from Blazor

Now, there is an important thing to have in mind. A Blazor 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.

So, that’s the reason I need to create and use JavaScript to invoke the file save action and I’ll invoke JavaScript function using JSInterop feature.

Then, in the Blazor 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

Add download.js in the index.html
Add download.js in the index.html

Create a Razor component

Add a Razor component named DownloadFile.razor to the Pages folder and add the following code

@page "/downloadexcel"
@inject IJSRuntime JSRuntime
@inject HttpClient _httpClient

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

<button @onclick="@(async() => await DownloadFile())">Download</button>

@if (IsDownloadStarted == 1)
{
	<p>Downloading...Please wait</p>
}
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/export");
			response.EnsureSuccessStatusCode();

			var fileBytes = await response.Content.ReadAsByteArrayAsync();
			var fileName = $"Authors{DateTime.Now.ToString("yyyy-MM-dd", System.Globalization.CultureInfo.InvariantCulture)}.xlsx";
			await JSRuntime.InvokeAsync<object>("saveAsFile", fileName, Convert.ToBase64String(fileBytes));

			IsDownloadStarted = 2;
		}
	}
}

Here, the component asks to the user if he wants to download the file because the button invokes DownloadFile(). If the user confirms, a call is made to the api. When the api replies, the code reads the content in a byte array and then invokes saveAsFile via IJSRuntime. saveAsFile is the function in the download.js

One important thing to do is to configure HttpClient with the right URI. For that, in the Program.cs add before builder.Build()

builder.Services.AddScoped(sp => new HttpClient { 
	BaseAddress = new Uri("https://localhost:44348") 
});

await builder.Build().RunAsync();

The URI is related to the local WebAPI project. In a real world, we have to use the public URL.

Conclusion

Finally, we understand how to Export Data to Excel in Blazor and set the right configuration in the WebAPI project. The source code of this post is on GitHub. If you have any question, please use the comment at the bottom of tis page or our forum.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Related News