Post
Ask Question

Excel Sheet Generation in .NET Core 6 Web API and Angular 15

Introduction

In the dynamic landscape of web application development, data manipulation and reporting are often at the forefront of user requirements. Excel spreadsheets remain a ubiquitous choice for presenting and exchanging data due to their familiarity and versatility. In this tutorial, we will explore a seamless approach to generating and downloading Excel sheets in a web application powered by .NET Core 6 Web API and Angular.

Why Excel Generation and Download Matter?

Imagine a scenario where you need to offer users the ability to export complex data tables, financial reports, or analytical results from your web application. Providing this functionality not only enhances the user experience but also serves as a valuable feature for data-driven applications. With Excel sheet generation and download, users can effortlessly access, analyze, and share data on their own terms.

Prerequisites

  • Basic Knowledge: Familiarity with fundamental web development concepts, including HTML, CSS, JavaScript/TypeScript, and HTTP.
  • Development Environment: A development environment set up for both .NET Core 6 and Angular. You should have these tools installed.
    • .NET 6 SDK
    • Node.js and npm (Node Package Manager)
    • Angular CL
    • Text Editor or IDE: A code editor or integrated development environment of your choice, such as Visual Studio Code or Visual Studio.
    • Basic Understanding of REST: A foundational understanding of RESTful API concepts will be beneficial.

Sever-Side(WebAPI ) Implementation


Creating a New Project in Visual Studio 2022

In the "Create a new project" dialog, select "ASP.NET Core Web API." And install the necessary package.

Install closed XML Library through NuGet Package Manage

The Visual Studio software provides the Nuget Package Manager option to install the package directly to the solution.

In Visual Studio, Select Tools > NuGet Package Manager > Manage NuGet Packages for the solution. The below screenshot shows how to open the Nuget Package Manager.

After installing ClosedXML, your .NET Core application can utilize its functionalities to generate Excel files on the server side.

You can do this by creating Excel workbooks and worksheets and populating them with data using the API provided by ClosedXML.

ClosedXML

Implemented employee add, edit, create, and delete functionalities using the Dapper framework and Microsoft SQL Server.

completeSetup

"In the controller, you can implement the 'Export Excel' functionality by calling the 'GetAllEmployee' function from the 'IEmployeeInformationRepository.cs' interface."

[HttpGet("ExportExcel")]

public async Task<ActionResult> GetExcelfile()
{


}

This statement suggests that within your controller, you can implement a method for exporting data to Excel by utilizing the data retrieval logic provided by the 'GetAllEmployee' function defined in the 'IEmployeeInformationRepository.cs' interface.

codewibndow

"In this implementation, we have a child function called GetEmployeeData responsible for configuring the Excel sheet name, defining column names, setting colors, and managing internal configurations."

codewindow2

using ClosedXML.Excel; // Make sure to include the necessary namespace

[HttpGet("ExportExcel")]
public async Task<ActionResult> GetExcelFile()
{
    // Assuming GetEmployeeData is an asynchronous method that retrieves employee data
    var employeeData = await GetEmployeeData();

    string base64String;

    using (var wb = new XLWorkbook())
    {
        var sheet = wb.AddWorksheet(employeeData, "Employee Records");

        // Apply font color to columns 1 to 5
        sheet.Columns(1, 5).Style.Font.FontColor = XLColor.Black;

        using (var ms = new MemoryStream())
        {
            wb.SaveAs(ms);

            // Convert the Excel workbook to a base64-encoded string
            base64String = Convert.ToBase64String(ms.ToArray());
        }
    }

    // Return a CreatedResult with the base64-encoded Excel data
    return new CreatedResult(string.Empty, new
    {
        Code = 200,
        Status = true,
        Message = "",
        Data = base64String
    });
}

api

Once you fetch the response from the 'ExportExcel' function, you will receive a Base64 string.

swagerUI

In the server-side 'Export Excel' endpoint, the generated Excel file is converted into a Base64 string format and included in the server's response, allowing for seamless retrieval and download of the Excel file by the client.

Angular -Client Side Implementation

If I understand correctly, you are already familiar with Angular and would like to focus on the steps to implement client-side Excel export in an Angular application.

Employee.ts service file add getexportExcel functionalities.

Integrates this url https://localhost:7206/api/Employee/ExportExcel.

in service file ‘GetExportExcel’ function.

vscodewindow

I've created an 'Employee' component and implemented the 'downloadExcel' functionality

downloadExcel

"I've created an 'EmployeeComponent' and implemented an 'onClick' event handler with a function named 'ongenerateExcel.'

<button class="btn btn-success" (click)="ongenerateExcel()">ExportExcel</button>

EmployeeComponent.ts file implemented in download functionalities.

ongenerateExcel() {
  this.empService.GetExportExcel().subscribe((res: any) => {
    var binaryData = atob(res.data);

    const blob = new Blob([new Uint8Array(binaryData.length).map((_, index) => binaryData.charCodeAt(index))], {
      type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
    });

    // Create a URL for the blob
    const url = window.URL.createObjectURL(blob);

    // Create a link element to trigger the download
    const a = document.createElement('a');
    a.href = url;
    a.download = 'EmployeeList.xlsx'; // Set the desired file name
    a.click();

    // Revoke the object URL to free up resources
    window.URL.revokeObjectURL(url);
  });
}

In this function

  • You call the GetExportExcel function from your service to fetch the Excel data.
  • You convert the received Base64 string data into binary data.
  • A Blob object is created from the binary data with the appropriate content type for Excel.
  • A URL is generated for the Blob object.
  • You create a new <a> element, set its href attribute to the Blob object's URL, and specify the filename for the downloaded file.
  • By programmatically triggering a click event on the <a> element, you initiate the download of the Excel file.
  • Finally, you release the URL object to free up system resources.

This function essentially downloads the Excel file generated by your server-side API when you click the associated button in your Angular application.

addemp

Once click the Export Excel Button and download the Excel file in the browser download section.

Download the Excel file. Open.

excel

To access the complete code samples used in this article, please check the below git repository

Conclusion

In this article, we've shown how to easily create and download Excel sheets in your web application. We used .NET Core 6 on the server side to generate Excel files and Angular on the client side to make downloading effortless. With these tools, you can empower your users to export and work with data seamlessly. As you continue to explore, remember the potential for customization and enhancement to suit your specific needs. Excel generation and download are valuable features in modern web development, and we hope this article has made it simple for you to implement them


Similar Articles