Import Excel data to Database using AngularJS C# and VB.Net in ASP.Net

Last Reply 10 days ago By dharmendr

Posted 10 days ago

How to import excel to SQL server Database using AngularJS in asp.net

Posted 10 days ago

Hi rani,

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

For reading file i have used below article.

Read Excel File using AngularJS and HTML5 File API

After reading record passed the rows to WebMethod using jQuery Ajax and converted the rows to DataTable.

Then using the SqlBulkCopy from the below article, write the DataTable to Database.

SqlBulkCopy: Bulk Copy data from DataTable (DataSet) to SQL Server Table using C# and VB.Net

HTML

<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>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" />
<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/angularjs/1.3.9/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) {
                $scope.SaveData(excelRows);
            }
        };

        $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.");
            }
        }

        // Save excel data to database.
        $scope.SaveData = function (excelData) {
            $http({
                method: "POST",
                url: "Default.aspx/SaveData",
                data: { customers: excelData },
                headers: { 'Content-Type': 'application/json' }
            }).then(function (response) {
                if (response.data.d > 0) {
                    $scope.Message = response.data.d + " record inserted.";
                }
                else {
                    $scope.Message = "Record Insert failed.";
                }
            }, function (response) {
                $scope.Message = response.responceText;
            })
        }
    } ]);
</script>

Namespaces

C#

using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web.Services;

VB.Net

Imports System.Collections.Generic
Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient
Imports System.Linq
Imports System.Web.Services

Code

C#

[WebMethod]
public static string SaveData(List<object> customers)
{
    DataTable dt = new DataTable();
    var columnNames = ((Dictionary<string, object>)customers[0]).Select(x => x.Key).ToList();
    for (int i = 0; i < columnNames.Count(); i++)
    {
        dt.Columns.Add(columnNames[i]);
    }
    foreach (Dictionary<string, object> customer in customers)
    {
        DataRow dr = dt.NewRow();
        for (int i = 0; i < columnNames.Count(); i++)
        {
            dr[columnNames[i]] = customer[columnNames[i]];
        }
        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 customers.Count().ToString();
}

VB.Net

<WebMethod()>
Public Shared Function SaveData(ByVal customers As List(Of Object)) As String
    Dim dt As DataTable = New DataTable()
    Dim columnNames = (CType(customers(0), Dictionary(Of String, Object))).[Select](Function(x) x.Key).ToList()
    For i As Integer = 0 To columnNames.Count() - 1
        dt.Columns.Add(columnNames(i))
    Next
    For Each customer As Dictionary(Of String, Object) In customers
        Dim dr As DataRow = dt.NewRow()
        For i As Integer = 0 To columnNames.Count() - 1
            dr(columnNames(i)) = customer(columnNames(i))
        Next
        dt.Rows.Add(dr)
    Next

    If dt.Rows.Count > 0 Then
        Dim consString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
        Using con As SqlConnection = New SqlConnection(consString)
            Using sqlBulkCopy As SqlBulkCopy = New SqlBulkCopy(con)
                sqlBulkCopy.DestinationTableName = "dbo.Customers"
                con.Open()
                sqlBulkCopy.WriteToServer(dt)
                con.Close()
            End Using
        End Using
    End If

    Return customers.Count().ToString()
End Function

Screenshots

The Excel File

The Form

Record After Insert in Database