Populate AngularJS UI-Grid with Excel Data using Asp.Net Web API in MVC 4

Yesterday, I have shared an article with example explaining how to read data from Excel in AngularJS. Using a similar method, I’ll show you how to populate the AngularJS UI-Grid with Excel data using Asp.Net Web API.

Populete AngularJS UI-Grid with Excel Data using Asp.Net Web API in MVC 4

The method is very simple.

The application will choose the Excel file using the File Input element.
Send the Excel file to my Web API controller function using AngularJS $http service.
The Web API function will identify the file, extract data from the Excel sheet, and load it to a DataTable.
The API will return the DataTable to the requesting AngularJS service. Finally, the data will be bound to the UI-Grid.

You can do tons of operations using the UI-Grid in AngularJS. Here are few examples in UI-Grid that I am sure will be useful for web developers.

1) How to Implement Filtering in AngularJS UI-Grid
2) How to Make UI-Grid Editable
3) Implementing Pagination to AngularJS UI-Grid
4) Create a Data Grid in AngularJS using UI-Grid and Asp.Net Web API MVC 4

Data in My Excel File

I have a list of birds in my Excel sheet, which I want to read and show in my AngularJS.

ID	Name	                Type	    Scientific_Name
001	Eurasian Collared-Dove	Dove	    Streptopelia
002	Bald Eagle              Hawk	    Haliaeetus leucocephalus
003	Cooper's Hawk	        Hawk	    Accipiter cooperii
004	Bell's Sparrow	        Sparrow	    Artemisiospiza belli
005	Mourning Dove	        Dove	    Zenaida macroura
The Web API

My Web API has a Controller, with a function named UploadFiles(). The function returns a DataTable object to the requesting app.

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.
            System.Web.HttpPostedFile hpf = hfc[0];

            if (hpf.ContentLength > 0)
            {
                if (hpf.FileName.EndsWith("xlsx"))      // CHECK THE FILE TYPE (YOU CAN CHECK WITH .xls ALSO).
                {
                    // 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.
                    }
                    catch (Exception ex)
                    {
                        //
                    }
                    finally
                    {
                        myExcelConn.Close(); myExcelConn = null;        // CLEAR.
                    }
                }
            }

            return dt;          // RETURN DataTable TO THE CALLING APP.
        }
    }
}
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 ' THE DataTable TO HOLD EXCEL DATA.

            Dim hpf As HttpPostedFile = hfc(0)
            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.
                    Catch ex As Exception
                        '
                    Finally
                        myExcelConn.Close() : myExcelConn = Nothing     ' CLEAR.
                    End Try
                End If
            End If

            Return dt       ' RETURN DataTable TO THE CALLING APP.
        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>
    <script src="http://ui-grid.info/release/ui-grid.js"></script>
    <link rel="stylesheet" href="http://ui-grid.info/release/ui-grid.css" type="text/css">

    <style>
        .uiGrd {
            width: 550px;
            height: 300px;
        }
    </style>
</head>
<body>
    <div ng-app="myApp" 
        ng-controller="myController">

        <input type="file" id="file1" name="file" ng-files="getTheFiles($files)" />
        <input type="button" ng-click="uploadFiles()" value="Upload" />

        <div class="uiGrd" id="grd" ui-grid="gridData"></div>
    </div>
</body>
The AngularJS Controller (the Script)
<script>
    angular.module('myApp', ['ui.grid'])
        .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);
                });
            };

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

                $scope.arr = new Array;

                // SEND THE FILES.
                $http(request)
                    .success(function (data) {
                        var i = 0;      // JUST A COUNTER.

                        // LOOP THROUGH EACH DATA.
                        angular.forEach(data, function () {
                            var b = {
                                ID: data[i].ID,
                                BirdName: data[i].Name,
                                Type: data[i].Type,
                                Scientific_Name: data[i].Scientific_Name
                            };

                            $scope.arr.push(b);    // ADD DATA TO THE ARRAY.
                            i += 1;
                        });

                    })
                    .error(function () { });
            }
            $scope.gridData = { data: 'arr' };      // BIND ARRAY (WITH DATA) TO THE GRID.
        });
</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 in an Array.

That’s it .Thanks for reading.

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



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!