12 December 2024

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

blazor export xls

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 :

template.AddVariable("name", "christophe");

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

5 thoughts on “Blazor – Create or export your data to Excel

  1. My data is in SQL Server DB.
    I retrieve data and store in string variables (one variable for each column).

    Your sample code gets data from json.
    forecasts = await Http.GetFromJsonAsync(“sample-data/weather.json”);

    How does it work if your data is an array of string variables.
    Do you have any sample code?

    Thank you

    1. The “weather.json” is a text file located in the sample-data folder.
      To get database version, you still use the WeatherForecast model and load the records into forecasts variable.

      Your db table would have matching fields Date, TemperatureC, Summary, TemperatureF or create aliases for the fields.

      Connect to database and read all the records.
      //sample code not tested
      forecasts = await context.View_weather.ToList();

    2. I don’t know if I am replying on time, hope you have found a solution. It’s very simple if your data is coming in Datatable you can first iterate through all column names and make the first row of excel and then iterate to all rows and create rows. In Array of string use the same method above using the foreach(string….) loop.

  2. Hello

    I tried your template based excel sheet code and its working fine. I have below question
    In my excel, I have 2 sheets one is main sheet and other sheet I am using to write the data on click of button using template. But when this sheet is hidden, its not writing the data in that sheet.
    How can we write the data to hidden sheet ?
    In the main sheet, I am binding the data from other sheet as a dropdown using Data Validation

  3. Hello,
    How to improve the speed of writing the date Use an existing excel file. Its taking too much time to write the data in existing excel file (approx. more than 30-40 seconds).
    Any help in this really appreciated

    Thanks

Leave a Reply