Import excel sheet in asp.net MVC using AngularJS

Nilesh this is not working I have 1 lakh data in excel sheet I want to directly save in database using angularjs and web api.I have try 10000 to 15000 data save in database that is working properly but when exceed data above 15000 then angular send only null value to action method.

Please read following code and then tell me what is problem.

///Angular code-

$scope.SelectedFileForUpload = null;

$scope.UploadFile = function (files) {

$scope.$apply(function () {

$scope.Message = "";

$scope.SelectedFileForUpload = files[0];

});

}

$scope.ParseExcelDataAndSave = function () {

var file = $scope.SelectedFileForUpload;

if (file) {

var reader = new FileReader();

reader.onload = function (e) {

var data = e.target.result;

var workbook = XLSX.read(data, { type: 'binary' });

var sheetName = workbook.SheetNames[0];

var excelData = XLSX.utils.sheet_to_row_object_array(workbook.Sheets[sheetName]);

if (excelData.length > 0) {

$scope.SaveData(excelData);

}

else

{

$scope.Message = "No data found";

}

reader.onerror = function (ex) {

console.log(ex);

}

reader.readAsBinaryString(file);

}

$scope.SaveData = function (excelData) {

debugger;

$http({

method: "POST",

url: "http://localhost:20890/api/values/Excelsavedata",

data: JSON.stringify(excelData),

headers: {

'Content-Type': 'application/json'

}

}).then(function (data) {

if (data.status) {

$scope.Message = excelData.length + " record inserted";

GetDetails();

}

else {

$scope.Message = "Failed";

}

}, function (error) {

$scope.Message = "Error";

})

}

///Web Api Code

public IHttpActionResult PostExcelsavedata(List students)

{

foreach (var i in students)

{

string cs = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;

using (SqlConnection conn = new SqlConnection(cs))

{

SqlCommand command = new SqlCommand("sp_insert_update", conn);

command.CommandType = CommandType.StoredProcedure;

command.Parameters.AddWithValue("@PIDD",0);

command.Parameters.AddWithValue("@Name", i.name);

command.Parameters.AddWithValue("@UID", i.UID);

command.Parameters.AddWithValue("@CID", i.CID);

command.Parameters.AddWithValue("@TID", i.TID);

command.Parameters.AddWithValue("@LastYearMark", i.lastyearmark);

command.Parameters.AddWithValue("@Maths", i.math);

command.Parameters.AddWithValue("@English",i.english);

command.Parameters.AddWithValue("@Hindi", i.hindi);

command.Parameters.AddWithValue("@Science",i.science);

command.Parameters.AddWithValue("@Sanskrit",i.sanskrit);

conn.Open();

command.ExecuteNonQuery();

}

return Ok("DATA SAVED");

}

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Sandy8y ago
Hi Mohd,

You have to improve code of "PostExcelsavedata " function because it is saving data in sequence that will take too much time.
Either try to insert data using 'Bulk Insert in C# ' or Use table Variable.

Thanks
Mohd Shamim8y ago
Nilesh this is not working I have 1 lakh data in excel sheet I want to directly save in database using angularjs and web api.I have try 10000 to 15000 data save in database that is working properly but when exceed data above 15000 then angular send only null value to action method.

Please read following code and then tell me what is problem.

///Angular code-

$scope.SelectedFileForUpload = null;

$scope.UploadFile = function (files) {

$scope.$apply(function () {

$scope.Message = "";

$scope.SelectedFileForUpload = files[0];

});

}

$scope.ParseExcelDataAndSave = function () {

var file = $scope.SelectedFileForUpload;

if (file) {

var reader = new FileReader();

reader.onload = function (e) {

var data = e.target.result;

var workbook = XLSX.read(data, { type: 'binary' });

var sheetName = workbook.SheetNames[0];

var excelData = XLSX.utils.sheet_to_row_object_array(workbook.Sheets[sheetName]);

if (excelData.length > 0) {

$scope.SaveData(excelData);

}

else

{

$scope.Message = "No data found";

}

}

reader.onerror = function (ex) {

console.log(ex);

}

reader.readAsBinaryString(file);

}

}

$scope.SaveData = function (excelData) {

debugger;

$http({

method: "POST",

url: "http://localhost:20890/api/values/Excelsavedata",

data: JSON.stringify(excelData),

headers: {

'Content-Type': 'application/json'

}

}).then(function (data) {

if (data.status) {

$scope.Message = excelData.length + " record inserted";

GetDetails();

}

else {

$scope.Message = "Failed";

}

}, function (error) {

$scope.Message = "Error";

})

}

///Web Api Code

public IHttpActionResult PostExcelsavedata(List students)

{

foreach (var i in students)

{

string cs = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;

using (SqlConnection conn = new SqlConnection(cs))

{

SqlCommand command = new SqlCommand("sp_insert_update", conn);

command.CommandType = CommandType.StoredProcedure;

command.Parameters.AddWithValue("@PIDD",0);

command.Parameters.AddWithValue("@Name", i.name);

command.Parameters.AddWithValue("@UID", i.UID);

command.Parameters.AddWithValue("@CID", i.CID);

command.Parameters.AddWithValue("@TID", i.TID);

command.Parameters.AddWithValue("@LastYearMark", i.lastyearmark);

command.Parameters.AddWithValue("@Maths", i.math);

command.Parameters.AddWithValue("@English",i.english);

command.Parameters.AddWithValue("@Hindi", i.hindi);

command.Parameters.AddWithValue("@Science",i.science);

command.Parameters.AddWithValue("@Sanskrit",i.sanskrit);

conn.Open();

command.ExecuteNonQuery();

}

}

return Ok("DATA SAVED");

}
Sandy9y ago
Hello Mohd,

try to set maximum upload limit in IIS
      "2147483648" />



if it is already set than try to write error on server end in notepad file or in DB.
so that we can troubleshoot that issue.
Thanks
Nilesh Patil9y ago
Hi,

Refer this

https://www.aspsnippets.com/Articles/How-to-Import-Excel-Sheet-data-into-SQL-Server-using-ASPNet.aspx

https://www.codeproject.com/tips/878730/file-upload-using-angularjs-and-asp-net-mvc