I need some help in my asp.net MVC web-application, how can I Upload an excel file and extract or save(import) excel file data into my MS SQL server database using asp.net MVC and C#?
Any reference link or code will help, as I want to perform both uploading a file and saving its data into database, any combined solution will work
thanks
I have done similar thing in the past, but as you haven't mentioned other details like Excel file columns, let me give you my code example
Suppose you need to get Name
and Email
from two columns of excel sheet, then your C# code to get these data from Excel file would be something like this
[HttpPost]
public ActionResult Index(HttpPostedFileBase file)
{
DataSet ds = new DataSet();
//Check for file data, you can add more here to check if there is even file etc
if (Request.Files["file"].ContentLength > 0)
{
string fileExtension = System.IO.Path.GetExtension(Request.Files["file"].FileName);
if (fileExtension == ".xls" || fileExtension == ".xlsx")
{
string fileLocation = Server.MapPath("~/Content/") + Request.Files["file"].FileName;
if (System.IO.File.Exists(fileLocation))
{
System.IO.File.Delete(fileLocation);
}
Request.Files["file"].SaveAs(fileLocation);
string excelConnectionString = string.Empty;
excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
//connection String for xls file format.
if (fileExtension == ".xls")
{
excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
}
//connection String for xlsx file format.
else if (fileExtension == ".xlsx")
{
excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
}
//Create Connection to Excel work book and add oledb namespace
OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
excelConnection.Open();
DataTable dt = new DataTable();
dt = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
if (dt == null)
{
return null;
}
String[] excelSheets = new String[dt.Rows.Count];
int t = 0;
//save excel data as temp file
foreach (DataRow row in dt.Rows)
{
excelSheets[t] = row["tbl_Name"].ToString();
t++;
}
OleDbConnection excelConnection1 = new OleDbConnection(excelConnectionString);
string query = string.Format("Select * from [{0}]", excelSheets[0]);
using (OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, excelConnection1))
{
dataAdapter.Fill(ds);
}
}
//Check if file if of .xml type & save it
if (fileExtension.ToString().ToLower().Equals(".xml"))
{
string fileLocation = Server.MapPath("~/Content/") + Request.Files["FileUpload"].FileName;
if (System.IO.File.Exists(fileLocation))
{
System.IO.File.Delete(fileLocation);
}
Request.Files["UploadedFile"].SaveAs(fileLocation);
XmlTextReader xmlreader = new XmlTextReader(fileLocation);
// DataSet ds = new DataSet();
ds.ReadXml(xmlreader);
xmlreader.Close();
}
//loop all the rows of the Excel file and save it in database
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
string conn = ConfigurationManager.ConnectionStrings["dbconnection"].ConnectionString;
SqlConnection con = new SqlConnection(conn);
string query = "Insert into Person(Name,Email) Values('" + ds.Tables[0].Rows[i][0].ToString() + "','" + ds.Tables[0].Rows[i][1].ToString() + "')";
con.Open();
SqlCommand cmd = new SqlCommand(query, con);
cmd.ExecuteNonQuery();
con.Close();
}
}
return View();
}
The above code, assumes that we are actually posting a file, which has contents
If the above answer isn't helpful, I have explained the how to import excel into database using C# in ASP.NET MVC
You can take a look at the above article, you can also download sample project.
Subscribe to our weekly Newsletter & Keep getting latest article/questions in your inbox weekly