Post
Ask Question

Excel Export In Blazor WASM .NET Core Hosted Using Gembox

Introduction

  • We have created Blazor 3.2 WASM with Add/Edit/Delete capabilities and .NET Core in previous articles.
  • In this article, we will be adding export capabilities on the home screen with the ability to download Employee data in excel format on the Home screen on the click of a button.
Prerequisites
 
Refer to my previous article on creating fully functional Blazor WASM with Add/Edit/Delete capabilities.
Output
 
On a click of Export Button Employee, details are exported in .xlsx Format.
 

Implementation

 
Step 1 - Download and set up Gembox NuGet package 
 
Navigate to Tools-->Nuget Package manager and download the latest version of the Gembox spreadsheet and install it in our .NET Core server project.
 
 
Import Gembox library in startup.cs with the below statement
  1. using GemBox.Spreadsheet;  
Initialize the Spreadsheet info class and set License Key information.
 
This needs to be done in startup.cs of Server project in Configure service method 
  1. public void ConfigureServices(IServiceCollection services) {  
  2.     services.AddDbContext < AppDbContext > (options => options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));  
  3.     services.AddControllersWithViews();  
  4.     services.AddRazorPages();  
  5.     services.AddScoped < IEmployeeRepository, EmployeeRepository > ();  
  6.     SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");  
  7. }  
This completes the set up for Gembox
 
Step 2 - Creating .NET Core API for excel Export
 
Import Gembox library using below syntax
  1. using GemBox.Spreadsheet;   
Create a new Route in Employee Controller.
  1. [Route("Export")]  
  2. [HttpGet]  
  3. public IActionResult Export() {  
  4.     var book = new ExcelFile();  
  5.     var sheet = book.Worksheets.Add("Employee");  
  6.     IList < Employee > data = _employeeRepository.GetAllEmployees().ToList();  
  7.     CellStyle style = sheet.Rows[0].Style;  
  8.     style.Font.Weight = ExcelFont.BoldWeight;  
  9.     style.HorizontalAlignment = HorizontalAlignmentStyle.Center;  
  10.     sheet.Columns[0].Style.HorizontalAlignment = HorizontalAlignmentStyle.Center;  
  11.     sheet.Columns[0].SetWidth(150, LengthUnit.Pixel);  
  12.     sheet.Columns[1].SetWidth(150, LengthUnit.Pixel);  
  13.     sheet.Columns[2].SetWidth(150, LengthUnit.Pixel);  
  14.     sheet.Columns[3].SetWidth(150, LengthUnit.Pixel);  
  15.     sheet.Columns[5].SetWidth(150, LengthUnit.Pixel);  
  16.     sheet.Columns[6].SetWidth(300, LengthUnit.Pixel);  
  17.     sheet.Cells["A1"].Value = "EmployeeId";  
  18.     sheet.Cells["B1"].Value = "First Name";  
  19.     sheet.Cells["C1"].Value = "Last Name";  
  20.     sheet.Cells["D1"].Value = "EmailId";  
  21.     sheet.Cells["E1"].Value = "City";  
  22.     sheet.Cells["F1"].Value = "Comment";  
  23.     for (int r = 1; r <= data.Count; r++) {  
  24.         Employee item = data[r - 1];  
  25.         sheet.Cells[r, 0].Value = item.EmployeeId;  
  26.         sheet.Cells[r, 1].Value = item.FirstName;  
  27.         sheet.Cells[r, 2].Value = item.LastName;  
  28.         sheet.Cells[r, 3].Value = item.Email;  
  29.         sheet.Cells[r, 4].Value = item.City;  
  30.         sheet.Cells[r, 5].Value = item.Comment;  
  31.     }  
  32.     SaveOptions options = GetSaveOptions("XLSX");  
  33.     using(var stream = new MemoryStream()) {  
  34.         book.Save(stream, options);  
  35.         return File(stream.ToArray(), options.ContentType, "EmployeeData." + "XLSX");  
  36.     }  
  37. }  
  38. private static SaveOptions GetSaveOptions(string format) {  
  39.     switch (format.ToUpper()) {  
  40.         case "XLSX":  
  41.             return SaveOptions.XlsxDefault;  
  42.         case "XLS":  
  43.             return SaveOptions.XlsDefault;  
  44.         case "ODS":  
  45.             return SaveOptions.OdsDefault;  
  46.         case "CSV":  
  47.             return SaveOptions.CsvDefault;  
  48.         case "HTML":  
  49.             return SaveOptions.HtmlDefault;  
  50.         case "PDF":  
  51.             return SaveOptions.PdfDefault;  
  52.         case "XPS":  
  53.         case "PNG":  
  54.         case "JPG":  
  55.         case "GIF":  
  56.         case "TIF":  
  57.         case "BMP":  
  58.         case "WMP":  
  59.             throw new InvalidOperationException("To enable saving to XPS or image format, add 'Microsoft.WindowsDesktop.App' framework reference.");  
  60.         default:  
  61.             throw new NotSupportedException();  
  62.     }  
  63. }  
Create a new Excel worksheet object with the name 'Employee'.This is the name of our worksheet which would be downloaded and save on our local machine.
 
The _employeeRepository.GetAllEmployees() will get a list of all employees of Employee Type.
 
Here we can call any other repository function and pass data of our choice.
 
The type of data model needs to be defined in shared library and imported on this page.  
   
Employee Class
  1. public partial class Employee {  
  2.     public int EmployeeId {  
  3.         get;  
  4.         set;  
  5.     }  
  6.     [Required]  
  7.     [StringLength(50, ErrorMessage = "First name is too long.")]  
  8.     public string FirstName {  
  9.         get;  
  10.         set;  
  11.     }  
  12.     [Required]  
  13.     [StringLength(50, ErrorMessage = "Last name is too long.")]  
  14.     public string LastName {  
  15.         get;  
  16.         set;  
  17.     }  
  18.     [Required]  
  19.     [EmailAddress]  
  20.     public string Email {  
  21.         get;  
  22.         set;  
  23.     }  
  24.     public string Street {  
  25.         get;  
  26.         set;  
  27.     }  
  28.     public string Zip {  
  29.         get;  
  30.         set;  
  31.     }  
  32.     public string City {  
  33.         get;  
  34.         set;  
  35.     }  
  36.     public string PhoneNumber {  
  37.         get;  
  38.         set;  
  39.     }  
  40.     [StringLength(1000, ErrorMessage = "Comment length can't exceed 1000 characters.")]  
  41.     public string Comment {  
  42.         get;  
  43.         set;  
  44.     }  
  45. }  
We would be exporting 6 properties of Employee class to excel.
 
We would need to define column formatting and labels for these properties.
 
GetSaveOptions method accepts a file type. We have created this method with multiple file types. This article is restricted to use of XLSX export.
 
The file is outputted as a memory stream and can be downloaded and saved on our local machine. 
 
We can also specify the workbook name over here 
  1. using (var stream = new MemoryStream())  
  2. {  
  3.    book.Save(stream, options);  
  4.    return File(stream.ToArray(), options.ContentType, "EmployeeData."+ "XLSX");  
  5. }  
EmployeeData is the sheet name.
 
The .NET Core API is now ready to be consumed in the Blazor Client app.
 
Step 3 - Calling .NET Core on Export Export Click and Download File 
 
Add a new Export button on Employeepage.Razor 
  1. <a href="@($"api/employee/export")" class="btn btn-primary-details table-btn">  
  2.    <i class="fas fa-info-circle"></i>  
  3.    Export  
  4. </a>  
On click of Export button, the Export API will be called and the Employee excel file would be downloaded with the name EmployeeData
 
 
Gembox provides a lot of capabilities for excel formatting which can be used as per our requirement.
 

Summary

 
Through this article, we have learned how to export data in excel format in Blazor WASM using the Gembox spreadsheet library.
 
Thanks a lot for reading. I hope you liked this article. Please share your valuable suggestions and feedback. Write in the comment box in case you have any questions. Have a good day!


Similar Articles