Import Excel data to Database using AngularJS in ASP.Net MVC

Last Reply 14 days ago By dharmendr

Posted 14 days ago

Hi,

How to import excel data to sql in Angularjs in asp.net mvc

For import excel data to sql i am using below code.

<html xmlns="http://www.w3.org/1999/xhtml">
<head>
    <title>Index</title>
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/3.3.7/css/bootstrap.min.css" />
    <script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/jquery/1.10.2/jquery.min.js"></script>
    <script type="text/javascript" src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
    <script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/angular.js/1.6.8/angular.min.js"></script>
    <script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/danialfarid-angular-file-upload/12.2.13/ng-file-upload.min.js"></script>
    <script type="text/javascript">
        var app = angular.module("myApp", ['ngFileUpload']);
        app.controller("myCntrl", ['$scope', '$http', 'myService', function ($scope, $http, myService) {
            $scope.UploadFiles = function (files) {
                $scope.SelectedFiles = files;
            };
 
            $scope.AddUpdateEmployee = function () {
                var formData = new FormData();
                var files = $scope.SelectedFiles;
                if (files != undefined) {
                    for (var i = 0; i < files.length; i++) {
                        formData.append(files[i].name, files[i]);
                    }
                }
                var details = {};
                details.Name = $scope.Name;
                formData.append("details", JSON.stringify(details));
                var getData = myService.AddSt(formData);
                getData.then(function (tc) {
                    alert(tc);
                });
            }
        } ]);
        app.service("myService", function ($http) {
            this.AddSt = function (employee) {
                var response = $.ajax({
                    url: '/Home/AddState',
                    type: "POST",
                    contentType: false,
                    processData: false,
                    data: employee
                });
                return response;
            }
        });
    </script>
</head>
<body ng-app="myApp" ng-controller="myCntrl">
    <div class="container">
        <div>
            <div id="wrapper" class="clearfix">
                <form name="userForm">
                <div class="form-horizontal">
                    <div class="form-row">
                        <div class="col-md-4">
                            <label for="COI_Name">
                                Upload a file
                            </label>
                            <input type="file" ngf-select="UploadFiles($files)" />
                        </div>
                    </div>
                    <div>
                    </div>
                    <div class="form-group">
                    </div>
                    <div class="form-group" style="width: 120%; text-align: center; padding: 10px;">
                        <div class="col-md-offset-2 col-md-5">
                            <p>
                                <button ng-model="IsVisible" ng-click="AddUpdateEmployee()">
                                    <span class="glyphicon glyphicon-ok"></span>Submitt
                                </button>
                            </p>
                        </div>
                    </div>
                </div>
                </form>
            </div>
        </div>
    </div>
</body>
</html>

I don't know what to do to save excel records to sql

could you please help me

 

Posted 14 days ago Modified on 13 days ago

Hi mahesh213,

Check this example. Now please take its reference and correct your code.

Namespaces

using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web.Mvc;
using System.Web.Script.Serialization;

Controller

public class HomeController : Controller
{
    // GET: /Home/
    public ActionResult Index()
    {
        return View();
    }

    [HttpPost]
    public JsonResult Save(List<object> customers)
    {
        DataTable dt = new DataTable();
        JavaScriptSerializer serializer = new JavaScriptSerializer();
        var columnNames = ((Dictionary<string, object>)serializer.DeserializeObject(customers[0].ToString())).Select(x => x.Key).ToList();
        for (int i = 0; i < columnNames.Count; i++)
        {
            dt.Columns.Add(columnNames[i]);
        }
        foreach (string customer in customers)
        {
            DataRow dr = dt.NewRow();
            for (int i = 0; i < columnNames.Count(); i++)
            {
                Dictionary<string, object> keyValue = ((Dictionary<string, object>)serializer.DeserializeObject(customer));
                foreach (KeyValuePair<string, object> item in keyValue)
                {
                    if (item.Key == columnNames[i].ToString())
                    {
                        dr[columnNames[i]] = item.Value;
                    }
                }
            }
            dt.Rows.Add(dr);
        }
        if (dt.Rows.Count > 0)
        {
            string consString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
            using (SqlConnection con = new SqlConnection(consString))
            {
                using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
                {
                    //Set the database table name.
                    sqlBulkCopy.DestinationTableName = "dbo.Customers";
                    con.Open();
                    sqlBulkCopy.WriteToServer(dt);
                    con.Close();
                }
            }
        }

        return Json(customers.Count().ToString());
    }
}

View

<html xmlns="http://www.w3.org/1999/xhtml">
<head>
    <title>Index</title>   
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" />
    <script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/angular.js/1.7.8/angular.min.js"></script>
    <script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/danialfarid-angular-file-upload/12.2.13/ng-file-upload.min.js"></script>
    <script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.13.5/xlsx.full.min.js"></script>
    <script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.13.5/jszip.js"></script>
    <script type="text/javascript">
        var app = angular.module('MyApp', ['ngFileUpload']);
        app.controller('MyController', ['$scope', '$http', '$window', function ($scope, $http, $window) {
            $scope.SelectFile = function (file) {
                $scope.SelectedFile = file;
            };

            $scope.ProcessExcel = function (data) {
                //Read the Excel File data.
                var workbook = XLSX.read(data, {
                    type: 'binary'
                });

                //Fetch the name of First Sheet.
                var firstSheet = workbook.SheetNames[0];

                //Read all rows from First Sheet into an JSON array.
                var excelRows = XLSX.utils.sheet_to_row_object_array(workbook.Sheets[firstSheet]);

                if (excelRows.length > 0) {
                    // Save excel data to database.
                    $http({
                        method: "POST",
                        url: "Home/Save/",
                        params: { customers: excelRows }
                    }).then(function (response) {
                        if (response.data > 0) {
                            $scope.Message = response.data + " record inserted.";
                        }
                        else {
                            $scope.Message = "Record Insert failed.";
                        }
                    }, function (response) {
                        $scope.Message = response.responceText;
                    })
                }
            };

            $scope.Import = function () {
                var regex = /^([a-zA-Z0-9\s_\\.\-:])+(.xls|.xlsx)$/;
                if (regex.test($scope.SelectedFile.name.toLowerCase())) {
                    if (typeof (FileReader) != "undefined") {
                        var reader = new FileReader();
                        //For Browsers other than IE.
                        if (reader.readAsBinaryString) {
                            reader.onload = function (e) {
                                $scope.ProcessExcel(e.target.result);
                            };
                            reader.readAsBinaryString($scope.SelectedFile);
                        } else {
                            //For IE Browser.
                            reader.onload = function (e) {
                                var data = "";
                                var bytes = new Uint8Array(e.target.result);
                                for (var i = 0; i < bytes.byteLength; i++) {
                                    data += String.fromCharCode(bytes[i]);
                                }
                                $scope.ProcessExcel(data);
                            };
                            reader.readAsArrayBuffer($scope.SelectedFile);
                        }
                    } else {
                        $window.alert("This browser does not support HTML5.");
                    }
                } else {
                    $window.alert("Please upload a valid Excel file.");
                }
            }
        } ]);
    </script>
</head>
<body>
    <div ng-app="MyApp" ng-controller="MyController">
        <input type="file" name="file" ngf-select="SelectFile($file)" />
        <input type="button" value="Import" class="btn btn-success" ng-click="Import()" />
        <br />
        <span style="color: green">{{Message}} </span>
    </div>
</body>
</html>

Screenshots

The Excel File

The Form

Record After Insert in Database