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

Last Reply 2 months ago By dharmendr

Posted 2 months 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 2 months ago Modified on 2 months 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