Import excel sheet in asp.net MVC using AngularJS

Question

why we can not send more than lakh data of excel from angular http services in asp.net mvc.I getting null exception error in web api action method.

Sandy · Answer

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 Shamim · Answer

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

Sandy · Answer

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 Patil · Answer

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

Import excel sheet in asp.net MVC using AngularJS

Insert Link

Embed YouTube Video

Table Options

Insert Image

Answers (4)