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 NamespaceThe Markup (The AngularJS View)
<!DOCTYPE html>
<html>
<head>
<script src="https://ajax.googleapis.com/ajax/libs/angularjs/1.5.0/angular.js"></script>
<style>
table, th, td {
border:solid 1px #CCC;
padding:1px 3px;
}
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.