how to read data from excel file using Web Api in C#

Question

Read the data from excel file by calling web api controller method and store the data in database using web api.

Viswanatha Swamy · Answer

Hi Bhavya, Good evening. Are you looking at the steps/process? OR the entire code? Please advise.

Vishvjit Shinde · Answer

Hi, This is Web Api for Reading Excel file and store in Database. [Route( "UploadFile" )] public ActionResult UploadFile( int ID) { try { Response res = new Response(); SqlConnection con = null ; string Connectionstring = _config.GetValue string >( "ConnectionString" ); con = new SqlConnection(Connectionstring); var file = Request.Form.Files[0]; string FileName = Request.Form.Files[0].FileName.ToString(); string folderName = "UploadedFiles\" ; string webRootPath = _hostingEnvironment.ContentRootPath; string newPath = Path.Combine(webRootPath, folderName); FileInfo files = new FileInfo(Path.Combine(newPath, FileName)); if (!Directory.Exists(newPath)) { Directory.CreateDirectory(newPath); } if (file.Length > 0) { string fileName = ContentDispositionHeaderValue.Parse(file.ContentDisposition).FileName.Trim( '"' ); string fullPath = Path.Combine(newPath, fileName); using (var stream = new FileStream(fullPath, FileMode.Open,FileAccess.ReadWrite,FileShare.ReadWrite)) { file.CopyTo(stream); stream.Flush(); stream.Dispose(); stream.Close(); } } string webRootPaths = _hostingEnvironment.WebRootPath; if ( string .IsNullOrWhiteSpace(_hostingEnvironment.WebRootPath)) { string WebRootPath = Path.Combine(Directory.GetCurrentDirectory(), "wwwroot" ); } ExcelEngine excelEngine = new ExcelEngine(); IApplication application = excelEngine.Excel; application.DefaultVersion = ExcelVersion.Excel2010; string basePath = files.FullName.ToString(); FileStream sampleFile = new FileStream(basePath, FileMode.Open); IWorkbook workbook = application.Workbooks.Open(sampleFile); IWorksheet worksheet = workbook.Worksheets[0]; DataTable dataTable = worksheet.ExportDataTable(worksheet.UsedRange, ExcelExportDataTableOptions.ColumnNames); SqlCommand sqlcmd = new SqlCommand( "SP_Name" , con); con.Open(); sqlcmd.CommandType = CommandType.StoredProcedure; foreach (DataRow row in dataTable.Rows) { sqlcmd.Parameters.Clear(); sqlcmd.Parameters.AddWithValue( "@Variable" , Convert.ToString(row[ "User_Name" ])); sqlcmd.Parameters.AddWithValue( "@Variable" , Convert.ToString(row[ "Password" ])); sqlcmd.Parameters.AddWithValue( "@Variable" , Convert.ToString(row[ "Group" ])); sqlcmd.Parameters.AddWithValue( "@Variable" , false ); sqlcmd.Parameters.AddWithValue( "@Variable" , false ); sqlcmd.Parameters.AddWithValue( "@Variable" , ID); sqlcmd.ExecuteNonQuery(); } con.Close(); sampleFile.Close(); return Json( "Upload Successful." ); } catch (System.Exception ex) { return Json( "Upload Failed: " + ex.Message); } } You have to Hit this Service from From UI With File Uploader