Import Excel Sheet Data in MS SQL server Database using AngularJS

Sunday, January 22, 2017 Sourav Mondal AngularJS | import

Introduction

In one of my previous article, we have seen how we can import data from Excel file data to MS SQL Server database in asp.net application. Today in this article, I will explain how to Import Excel Sheet Data in MS SQL server Database using AngularJS

Here in this example, we will use the js-xlsx javascript library which is a Parser and writer for various spreadsheet formats.

Ok, let's start importing Excel Sheet Data in MS SQL server Database using AngularJS.

Follow the following steps in order to implement "Import Excel Sheet Data in MS SQL server Database using AngularJS".

Here In this article, I have used Visual Studio 2015

Step - 1: Create New Project.

Go to File > New > Project > ASP.NET Web Application (under web) > Enter enter application name > select your project location > and then click on add button > It will brings up a new dialog window for select template > here I will select Empty template > checked MVC checkbox from Add folder and core referances for: > and then click on ok button.

Step-2: Add a Database.

Now I will create a database for our application. As this is a tutorial project, I will add a database in our applications here in the app_data folder.

Go to Solution Explorer > Right Click on App_Data folder > Add > New item > Select SQL Server Database Under Data > Enter Database name > Add.

Step-3: Create a table in our database.

In this example, I have added the table for store Employee information from Excel file.

double click on the database under app_data folder for open the database in server explorer > expand the database and Right click on Tables node > click on Add New Table > here we will write schema of the table for the table we want to create > now click on Update button for create the table and then again click on Update Database button.

Step-4: Add Entity Data Model.

Go to Solution Explorer > Right Click on Project name form Solution Explorer > Add > New item > Select ADO.net Entity Data Model under data > Enter model name > Add.
A popup window will come (Entity Data Model Wizard) > Select Generate from database > Next >
Chose your data connection > select your database > next > Select tables > enter Model Namespace > Finish.

Step-5: Add a javascript file, where we will write AngularJS code for creating an angular module and an angular controller.

In our application, I will add a javascript file into Scripts folder.
Go to solution explorer > Right click on "Scripts" folder > Add > new Item > Select Javascrip file under Scripts > Enter file name (here in my application it is "app.js") > and then click on Add button.

Here in this example we will write angular code for upload Excel file and parse excel sheet data.

See: How to upload files with AngularJS and ASP.NET MVC application

var app = angular.module('MyApp', []);
app.controller('MyController', ['$scope', '$http', function ($scope, $http) {
    $scope.SelectedFileForUpload = null;

    $scope.UploadFile = function (files) {
        $scope.$apply(function () { //I have used $scope.$apply because I will call this function from File input type control which is not supported 2 way binding
            $scope.Message = "";
            $scope.SelectedFileForUpload = files[0];
        })
    }

    //Parse Excel Data 
    $scope.ParseExcelDataAndSave = function () {
        var file = $scope.SelectedFileForUpload;
        if (file) {
            var reader = new FileReader();
            reader.onload = function (e) {
                var data = e.target.result;
                //XLSX from js-xlsx library , which I will add in page view page
                var workbook = XLSX.read(data, { type: 'binary' });
                var sheetName = workbook.SheetNames[0];
                var excelData = XLSX.utils.sheet_to_row_object_array(workbook.Sheets[sheetName]);
                if (excelData.length > 0) {
                    //Save data 
                    $scope.SaveData(excelData);
                }
                else {
                    $scope.Message = "No data found";
                }
            }
            reader.onerror = function (ex) {
                console.log(ex);
            }

            reader.readAsBinaryString(file);
        }
    }

    // Save excel data to our database
    $scope.SaveData = function (excelData) {
        $http({
            method: "POST",
            url: "/home/SaveData",
            data: JSON.stringify(excelData),
            headers: {
                'Content-Type' : 'application/json'
            }
        }).then(function (data) {
            if (data.status) {
                $scope.Message = excelData.length + " record inserted";
            }
            else {
                $scope.Message = "Failed";
            }
        }, function (error) {
            $scope.Message = "Error";
        })
    }
}])

Step-6: Create a Controller.

Go to Solution Explorer > Right Click on Controllers folder form Solution Explorer > Add > Controller > Enter Controller name > Select Templete "empty MVC Controller"> Add.

Here I have created a controller "HomeController"

Now we will add a view for Index action (which is already added in the home controller) where we will show data in Datatables.

Step-7: Add view for Index action.

Right Click on Action Method (here right click on Index action) > Add View... > Enter View Name > Select "Empty" under Template dropdown > Uncheck use a layout page > > Add.

HTML Code

@{
    Layout = null;
}
<!DOCTYPE html>
<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>Index</title>
    <link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css"  rel="stylesheet"/>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.8.1/xlsx.full.min.js"></script>
    <script src="http://oss.sheetjs.com/js-xlsx/jszip.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/angular.js/1.6.1/angular.min.js"></script>
    <script src="~/Scripts/app.js"></script>
</head>
<body ng-app="MyApp">
    <div ng-controller="MyController" class="container" style="margin-top:50px;"> 
        <div class="form-inline">
            <input type="file" name="file" class="form-control"
                   onchange="angular.element(this).scope().UploadFile(this.files)"/>
            <input type="button" value="Import" class="btn btn-success"  ng-disabled="!SelectedFileForUpload"
                   ng-click="ParseExcelDataAndSave()" />
            <br/>
            <span style="color:red">
                {{Message}}
            </span>
        </div>
    </div>
</body>
</html>

Step-8: Add a new method in HomeController.

Here I have added a method SaveData for import employees data from Excel file to SQL Server database.

[HttpPost]
public ActionResult SaveData(List<Employee> employees)
{
    bool status = false;
    if (ModelState.IsValid)
    {
        using (MyDatabaseEntities dc = new MyDatabaseEntities())
        {
            foreach (var i in employees)
            {
                dc.Employees.Add(i);
            }
            dc.SaveChanges();
            status = true;
        } 
    }
    return new JsonResult { Data = new { status = status } };
}

Step-9: Run Application.

We have done all the steps. Now it's time to run the application.

Live Demo & Download