how to save Excel data into database using MVC C#?


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


Asked by:- Vinnu
1
: 387 At:- 9/21/2017 10:15:56 AM
asp.net save-excel-data-in-database sql-server c#






2 Answers
profileImage Answered by:- bhanu

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

2
At:- 9/21/2017 3:02:58 PM
thanks, the above solution worked for me, after doing a few changes according to my Excel file and Class Model :) 0
By : Vinnu - at :- 9/27/2017 8:02:43 AM


profileImage Answered by:- vikas_jk

If the above answer isn't helpful, you can refer to these links also

  1. https://www.codeproject.com/Tips/752981/Import-Data-from-Excel-File-to-Database-Table-in-A
  2. http://www.code-sample.com/2014/07/aspnet-c-mvc-4-import-data-from-excel.html (This example uses Kendo UI to import data from Excel to  SQL server database)
0
At:- 9/26/2017 4:32:52 AM





Login/Register to answer
Or
Register directly by posting answer/details

Full Name *

Email *




By posting your answer you agree on privacy policy & terms of use