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(string customers)
{
DataTable dt = new DataTable();
JavaScriptSerializer serializer = new JavaScriptSerializer();
object[] objCustomers = (object[])serializer.DeserializeObject(customers);
var columnNames = ((Dictionary<string, object>)objCustomers[0]).Select(x => x.Key).ToList();
for (int i = 0; i < columnNames.Count; i++)
{
dt.Columns.Add(columnNames[i]);
}
for (int i = 0; i < objCustomers.Length; i++)
{
Dictionary<string, object> keyValue = (Dictionary<string, object>)objCustomers[i];
DataRow dr = dt.NewRow();
foreach (KeyValuePair<string, object> item in keyValue)
{
for (int k = 0; k < columnNames.Count(); k++)
{
if (item.Key == columnNames[k].ToString())
{
dr[columnNames[k]] = item.Value;
break;
}
}
}
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(objCustomers.Count().ToString());
}
}
View
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<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: JSON.stringify(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>
Screenshot
The Excel File
The Form
Record After Insert in Database
Add the below line under system.web section in Web.Config file.
<httpRuntime maxUrlLength="10240" maxQueryStringLength="2097100" />
Add the below line under configuration section in Web.Config file.
<system.web.extensions>
<scripting>
<webServices>
<jsonSerialization maxJsonLength="819200000" />
</webServices>
</scripting>
</system.web.extensions>