How to export data to excel using AngularJS

How to export data to excel using AngularJS?

You'll need an endpoint that generates the Excel file and sends it as a response, then from your AngularJS client, you'll call this API, receive the file, and trigger a download.

First, You need to create an API method that allows you to export your data to an excel file. You should change data source to fetch data from your database.

In your C# Web API, Ensure you have the Closed library installed via NuGet (Install-Package ClosedXML).

In this example, I'm using the respository pattern to retrieve invoices from day to day, then use the ClosedXML.Excel library to export data to excel file in ASP.NET Web API.

You can download and install ClosedXML.Excel from the Nuget Package Manager in your Visual Studio.

The ClosedXML is a .NET library for reading, manipulating, and writing Excel 2007+ files (.xlsx, .xlsm). It provides an intuitive and user-friendly interface for handling the basic OpenXML API.

To export data to an Excel file using AngularJS and a C# Web API, you can follow these general steps:

Develop a Web API endpoint using C# that receives a request from the AngularJS application and generates the Excel file like this:

[HttpGet]
public HttpResponseMessage Export(DateTime fromDate, DateTime toDate)
{
    List<Core.Entities.InvoiceInfo> invoices = _repository.GetInvoiceFromDateToDate(fromDate, toDate, Guid.Parse(User.Identity.GetUserId()));
    if (invoices != null)
    {
        DataTable dt = new DataTable("Invoices");
        dt.Columns.AddRange(new DataColumn[10]
        {
             new DataColumn("InvoiceNumber"),
             new DataColumn("BuyerName"),
             new DataColumn("BuyerAddress"),
             new DataColumn("BuyerEmail"),
             new DataColumn("Total"),
             new DataColumn("InvoiceDate")
        });
        foreach (var item in invoices)
            dt.Rows.Add(item.RowNumber, item.InvoiceNumber, item.BuyerName, item.BuyerAddress, item.BuyerEmail, item.Total, item.InvoiceDate);
        XLWorkbook wb = new XLWorkbook();
        {
            wb.Worksheets.Add(dt);
            MemoryStream ms = new MemoryStream();
            {
                wb.SaveAs(ms);
                ms.Position = 0;
                HttpResponseMessage result = Request.CreateResponse(HttpStatusCode.OK);
                result.Content = new StreamContent(ms);
                result.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment");
                result.Content.Headers.ContentType = new MediaTypeHeaderValue("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
                result.Content.Headers.ContentDisposition.FileName = "Invoices.xlsx";
                return result;
            }
        }
    }
    return Request.CreateResponse(HttpStatusCode.NoContent);
}

This API allows you to export data to an excel file and download it through the web api. To download a file using the AngularJS, you should create a service layer and controller layer to handle your download file.

Create an AngularJS application where you can fetch the data that needs to be exported to Excel, then Implement functionality in your AngularJS application to trigger the export/download request to the Web API endpoint and handle the response to initiate the file download.

(function () {
    'use strict';

    angular
        .module('app')
        .service('reportService', reportService);

    reportService.$inject = ['$http'];

    function reportService($http) {      
        this.export = function (fromDate, toDate) {
            return $http({
                url: 'api/Report/Export',
                method: "GET",
                responseType: "arraybuffer",
                params: { fromDate: fromDate, toDate: toDate }
            });
        };
    }
})();

Don't forget add the responseType: "arraybuffer" to your export or download file in services layer.

As you can see, the services layer handles retrieval of your data through the web api using $http

(function () {
    'use strict';

    angular
        .module('app')
        .controller('reportController', reportController);

    reportController.$inject = ['$scope', 'toaster', 'reportService'];

    function reportController($scope, toaster, reportService) {
        $scope.invoiceList;        

        $scope.export = function () {
            var filter = {
                FromDate: moment($scope.FromDate, "DD/MM/YYYY").format("MM/DD/YYYY"),
                ToDate: moment($scope.ToDate, "DD/MM/YYYY").format("MM/DD/YYYY")
            };
            reportService.export(filter.FromDate, filter.ToDate).then(function (response) 
            {
                var blob = new Blob([response.data], {
                    type: "application/octet-stream"
                });
                saveAs(blob, 'Invoices.xlsx');
            }, function (error) {
                toaster.error("Error", error.message);
            });
        }
    }
})();

The controller layer handles download your file using the services layer. We will convert data to Blob, then you can download it by using saveAs(blob, 'Invoice.xlsx') method of FileSaver.js

FileSaver implements the FileSaver interface in browsers that don't natively support it. FileSaver is the solution for client-side file storage and is perfect for web applications that need to create files or to save sensitive information that shouldn't be sent to an external server.

In your HTML view, add a button that calls this method. To call the export method from your controller you can use ng-click from views layer

<div><i class="fa fa fa-download"></i><a href="#" ng-click="export()"> Export excel</a></div>

Follow these steps, you can easily export data to Excel in an AngularJS application using a C# Web API.