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
