Blazor – Create or export your data to Excel

Here I will show you two examples of how to create an Excel file. The first method is to create a native Excel (.XLSX) file and the second is to use an existing Excel file and place your data in it. I will use a Blazor Webassembly application, but it’s the same with Blazor Server

Method 1 : Creation of a file in XLS format

For these two methods, I’m going to use a Nugets package named ClosedXML which is licensed by MIT. So you can use it freely even if your application is commercial.

The principle is quite simple if you want to create a basic excel file: You create the excel file “XLWorkbook” in which you will add one (or more 🙂 ) tab “Worksheets” and you will move from cell to cell “Cell” to place your data and at the end you save your file by the function “SaveAs”. Simple, isn’t it?

var wb = new XLWorkbook();
wb.Properties.Author = "the Author";
wb.Properties.Title = "the Title";
wb.Properties.Subject = "the Subject";
 
var ws = wb.Worksheets.Add("Weather Forecast");
 
ws.Cell(1, 1).Value = "Temp. (C)";
ws.Cell(1, 2).Value = "Temp. (F)";
ws.Cell(1, 3).Value = "Summary";
 
for (int row = 0; row < data.Length; row++)
{
     ws.Cell(row + 1, 1).Value = data[row].TemperatureC;
     ws.Cell(row + 1, 2).Value = data[row].TemperatureF;
     ws.Cell(row + 1, 3).Value = data[row].Summary;
}
 
MemoryStream XLSStream = new();
wb.SaveAs(XLSStream);

Note that here I save my Excel spreadsheet in a Stream but we can directly write the path and the file name. Being on a web application, I will then use a javacript function to propose to my user to download his report.

You will find the code in my GitHub repository that I have simplified so that you can quickly understand the principle.

Method 2 – Use an existing excel file

For this method, we will use the Nugets ClosedXML.Report package. You can easily find these packages in the Nuguets explorer of Visual Studio.

By using an Excel file as a Template, we can quickly make a nice presentation. It all depends on what you want. Honestly, if it’s just a data export for “non-geek” users, the first method will do the job well. Then yes, you could propose to export in raw formats like JSON, CSV etc… but depending on the audience using your application, it’s not nice.

On the code side, it’s much simpler:

var template = new  XLTemplate(streamTemplate);
 
template.AddVariable("WeatherForecasts", data);
template.Generate();
 
MemoryStream XLSStream = new();
emplate.SaveAs(XLSStream);

That’s it. We give XLTemplate the initial XLS file as a parameter or, as in this case, the Stream of my file. Then we pass it via “AddVariable” a List, Array IEnumerable… and indicate the name of the group of cells! This is where I had difficulties to master the beast. But once found, it’s like anything else; it’s easy 🙂

And here you find the name I gave to my list: “WeatherForecasts“. Note that in the ” double braces you have item followed by the name of my properties. item is imposed by the library. There are 3 of them:

• item – element of the list.
• index – index of an item
• items – the whole set ex: TOTAL RECORDS : {{items.Count()}}

Naming a group is only useful for lists, otherwise it is enough to put a name like {{name}} in a cell and in your C# code to add :

There is quite a lot of documentation on these libraries: https://closedxml.github.io/ClosedXML.Report/docs/en/index

Find here my example on Github : https://github.com/tossnet/Blazor-Excel-export