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

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

2 Replies

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

Viswanatha Swamy6y ago
Accepted answer
Hi Bhavya,
Good evening. Are you looking at the steps/process? OR the entire code? Please advise.
Vishvjit Shinde6y ago
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