How to Read Excel Data in AngularJS using Asp.Net Web API in MVC 4 and Show Data in HTML Table

Here I am with another interesting and useful AngularJS example. If integrating Excel data to your AngularJS app is what you are looking, then this is it. I am sharing a simple example here in this post on how to read Excel data in AngularJS using Asp.Net Web API in MVC 4 and show the extracted data in an HTML Table.

Read Excel Data in AngularJS using Asp.Net WebAPI in MVC 4

You can view Excel data by binding it with other elements, such as, the UI-Grid in AngularJS. Here, I am binding the data to an HTML table. The reading and extracting of data from an Excel file is very simple. I have previously submitted a post here on my blog on Multiple FileUpload procedure in AngularJS using $http post and FormData. I am using a similar procedure to upload my Excel file by posting the file to a Web API controller method.

Next, I am using Asp.Net DataTable to load the Excel data in the DataTable object and returning the object to my calling Angular App.

My Excel Data

The sample Excel sheet has four columns and few rows of data.

ID	Name	DOJ	        Address
1	ALPHA	5/17/2015	1 MAIN AVE
2	BRAVO	3/25/2016	B BLOCK NICE STREET
3	CHARLI	9/11/2015	UNIVERSAL NEW AVE
4	DELTA	1/7/2016	SPACE, 1 OF 1
5	ECO	3/9/2014	TUCSON, AZ MSZ
The Web API

My Web API has a Controller, with a function named UploadFiles(). The function returns a DataTable object. It is called from the AngularJS app using HTTP POST method.

Controller “FileUploadController.cs” (C#)
using System;

using System.Net.Http;
using System.Web.Http;

using System.IO;
using System.Data;
using System.Data.OleDb;

namespace FileUpload
{
    public class FileUploadController : ApiController
    {
        [HttpPost()]
        public DataTable UploadFiles()
        {
            // DEFINE THE PATH WHERE WE WANT TO SAVE THE FILES.
            string sPath = "";
            sPath = System.Web.Hosting.HostingEnvironment.MapPath("~/locker/");

            System.Web.HttpFileCollection hfc = System.Web.HttpContext.Current.Request.Files;

            // THE DataTable TO HOLD EXCEL DATA.
            DataTable dt = new DataTable();

            // CHECK THE FILE COUNT.
            for (int iCnt = 0; iCnt <= hfc.Count - 1; iCnt++)
            {
                System.Web.HttpPostedFile hpf = hfc[iCnt];

                if (hpf.ContentLength > 0)
                {
                    // CHECK THE FILE TYPE (YOU CAN CHECK WITH .xls ALSO).
                    if (hpf.FileName.EndsWith("xlsx"))      
                    {
                        // SAVE THE FILES IN THE FOLDER.
                        hpf.SaveAs(sPath + Path.GetFileName(hpf.FileName));

                        // SET A CONNECTION TO THE EXCEL FILE.
                        OleDbConnection myExcelConn = new OleDbConnection
                            ("Provider=Microsoft.ACE.OLEDB.12.0; " +
                                "Data Source=" + sPath + Path.GetFileName(hpf.FileName) +
                                ";Extended Properties=Excel 12.0;");
                        try
                        {
                            myExcelConn.Open();

                            // GET DATA FROM EXCEL SHEET.
                            OleDbCommand objOleDB =
                                new OleDbCommand("SELECT *FROM [Sheet1$]", myExcelConn);

                            // READ THE DATA EXTRACTED FROM THE EXCEL FILE.
                            OleDbDataReader objBulkReader = null;
                            objBulkReader = objOleDB.ExecuteReader();

                            dt.Load(objBulkReader);     // LOAD DATATABLE WITH DATA.

                            // YOU CAN SAVE THE DATA IN DATABASE.
                        }
                        catch (Exception ex)
                        {
                            //
                        }
                        finally
                        {
                            myExcelConn.Close(); myExcelConn = null;        // CLEAR.
                        }
                    }
                }
            }

            return dt;
        }
    }
}
Controller “FileUploadController.vb” (Visual Basic)
Option Explicit On

Imports System.Net.Http
Imports System.Web.Http

Imports System.IO
Imports System.Data.OleDb

Namespace FileUpload
    Public Class FileUploadController
        Inherits ApiController

        <HttpPost()> _
        Public Function UploadFiles() As DataTable

            ' DEFINE THE PATH WHERE WE WANT TO SAVE THE FILES.
            Dim sPath As String = ""
            sPath = System.Web.Hosting.HostingEnvironment.MapPath("~/locker/")

            Dim hfc As System.Web.HttpFileCollection = System.Web.HttpContext.Current.Request.Files

            Dim dt As DataTable = New DataTable

            For iCnt As Integer = 0 To hfc.Count - 1        ' CHECK THE FILE COUNT.
                Dim hpf As HttpPostedFile = hfc(iCnt)
                If hpf.ContentLength > 0 Then
                    
                    ' CHECK THE FILE TYPE (YOU CAN CHECK WITH .xls ALSO).
                    If hpf.FileName.EndsWith(".xlsx") Then
                        
                        ' SAVE THE FILES IN THE FOLDER.
                        hpf.SaveAs(sPath & Path.GetFileName(hpf.FileName))

                        ' SET A CONNECTION TO THE EXCEL FILE.
                        Dim myExcelConn As OleDbConnection = _
                            New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _
                                sPath & "\" & Path.GetFileName(hpf.FileName) & _
                                    ";Extended Properties=Excel 12.0;")
                        Try
                            myExcelConn.Open()

                            ' GET DATA FROM EXCEL SHEET.
                            Dim objOleDB As New OleDbCommand("SELECT *FROM [Sheet1$]", myExcelConn)

                            ' READ THE DATA EXTRACTED FROM THE EXCEL FILE.
                            Dim objBulkReader As OleDbDataReader
                            objBulkReader = objOleDB.ExecuteReader

                            dt.Load(objBulkReader)  ' LOAD DATATABLE WITH DATA.

                            ' YOU CAN SAVE THE DATA IN DATABASE.
                        Catch ex As Exception
                            '
                        Finally
                            myExcelConn.Close() : myExcelConn = Nothing     ' CLEAR.
                        End Try
                    End If
                End If
            Next

            Return dt

        End Function
    End Class
End Namespace
The Markup (The AngularJS View)
<!DOCTYPE html>
<html>
<head>
    <script src="http://ajax.googleapis.com/ajax/libs/angularjs/1.5.0/angular.js"></script>

    <style>
        table, th, td {
            border:solid 1px #CCC;
            padding:1px 3px;
            font:15px Verdana;
        }
        th {
            font-weight:bold;
        }
    </style>
</head>
<body>
    <div ng-app="myApp" 
        ng-controller="myController">

        <input type="file" id="file1" name="file" multiple
            ng-files="getTheFiles($files)" />

        <input type="button" ng-click="uploadFiles()" value="Upload" />

        <table>
            <tr>
                <th>ID</th>
                    <th>Employee Name</th>
                        <th>Date of Joining</th>
                            <th>Address</th>
            </tr>
            <tr ng-repeat="emps in empArray | orderBy : 'ID'">

                <td>{{emps.EmpID}}</td>
                    <td>{{emps.EmpName}}</td>
                        <td>{{emps.Doj}}</td>
                            <td>{{emps.Address}}</td>
            </tr>
        </table>
    </div>
</body>

I am using ng-repeat directive to bind an array of data to the <table>. Here’s another useful example on how to implement a Search Filter on HTML table in AngularJS.

The AngularJS Controller (the Script)

<script>
    angular.module('myApp')
        .directive('ngFiles', ['$parse', function ($parse) {

            function fn_link(scope, element, attrs) {
                var onChange = $parse(attrs.ngFiles);
                element.on('change', function (event) {
                    onChange(scope, { $files: event.target.files });
                });
            };

            return {
                link: fn_link
            }
        } ])
        .controller('myController', function ($scope, $http) {
            var formdata = new FormData();

            $scope.getTheFiles = function ($files) {
                angular.forEach($files, function (value, key) {
                    formdata.append(key, value);
                });
            };

            // UPLOAD THE FILES USING POST METHOD.
            $scope.uploadFiles = function () {
                var request = {
                    method: 'POST',
                    url: '/api/fileupload/',
                    data: formdata,
                    headers: {
                        'Content-Type': undefined
                    },
                    transformRequest: angular.identity
                };

                $scope.empArray = new Array;

                // SEND THE FILES TO THE WEB API.
                $http(request)
                    .success(function (data) {
                        var i = 0;

                        // LOOP THROUGH EACH DATA.
                        angular.forEach(data, function () {
                            var b = {
                                EmpID: data[i].ID,
                                EmpName: data[i].Name,
                                Doj: data[i].DOJ,
                                Address: data[i].Address
                            };

                            $scope.empArray.push(b);    // ADD DATA TO THE ARRAY.
                            i += 1;
                        });
                    })
                    .error(function () { });
            }
        });
</script>

I am using AngularJS $http service to post data to the Web API. Once the API successfully processes the request, it will return a list of data (rows). We’ll loop through each row using AngularJS .forEach() method and push the data array to a property called empArray.

That’s it. I have tried to keep the example simple, as it should be. Similarly, you can bind the data to a UI-Grid in AngularJS. I’ll share the example in my next post.

Thanks for reading.

Previous - How to Implement or Add Filtering to AngularJS UI-GridNext - Populate AngularJS UI-Grid with Excel Data using Asp.Net Web API in MVC 4



Like this Article? Subscribe now, and get all the latest articles and tips, right in your inbox.

Enter your email id

Delivered by FeedBurner
Tweet this article Facebook Google+

Related Posts:

Join our Google Plus Community and be a part of a discussion!