How to Export data to excel using AngularJS

By FoxLearn 5/20/2024 8:41:02 AM   136
This post shows you how to use angularjs to export data to excel file or download excel file using Web API in C# Code.

 

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.

Ensure you have the Closed library installed via NuGet (Install-Package ClosedXML). This code assumes you've set up a basic AngularJS application and a C# Web API project.

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.

ClosedXML.Excel

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.

[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);
}

The code above 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.

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>