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 NamespaceThe Markup (The AngularJS View)
<!DOCTYPE html>
<html>
<head>
<script src="https://ajax.googleapis.com/ajax/libs/angularjs/1.5.0/angular.js"></script>
<script src="https://ui-grid.info/release/ui-grid.js"></script>
<link rel="stylesheet" href="https://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.