Read Data From Excel File And Insert Into Database In ASP.NET MVC

I will show you how to connect to a Microsoft Excel workbook using the OLEDB.NET data provider, extract data and then insert it into the database table.

To begin with, we will create an ExcelToDatabase in Home Controller which returns a View. This method will return a View from where we have to upload the excel file. Now we will create another method ExcelToDatabase.  Now if we make a get request then ExcelToDatabase will be called and for post request, ExcelToDatabase will be called. The following is the code to read excel files.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Data;
using System.Data.OleDb;
using System.IO;

namespace ExcelReadData
{
    public class HomeController : Controller
    {
        // GET: ClonePanel
        public ActionResult ExcelToDatabase()
        {
            return View();
        }

        [HttpPost]
        public ActionResult ExcelToDatabase()
        {
            bool result = false;
            ViewBag.data = null;
                  
            if (Request.Files["FileUpload1"].ContentLength > 0)
            {
                string extension = System.IO.Path.GetExtension(Request.Files["FileUpload1"].FileName).ToLower();
                string query = null;
                string connString = "";

                string[] validFileTypes = { ".xls", ".xlsx" };

                string path1 = string.Format("{0}/{1}", Server.MapPath("~/Content/Uploads"), Request.Files["FileUpload1"].FileName);
                if (!Directory.Exists(path1))
                {
                    Directory.CreateDirectory(Server.MapPath("~/Content/Uploads"));
                }
                if (validFileTypes.Contains(extension))
                {
                    if (System.IO.File.Exists(path1))
                    { System.IO.File.Delete(path1); }
                    Request.Files["FileUpload1"].SaveAs(path1);

                    //Connection String to Excel Workbook  
                    if (extension.Trim() == ".xls")
                    {
                        connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path1 + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
                        result = Service.ImportExceltoDatabase(path1, connString, userId);
                    }
                    else if (extension.Trim() == ".xlsx")
                    {
                        connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path1 + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
                        result = Service.ImportExceltoDatabase(path1, connString,userId);
                    }
                }
                else
                {
                    ViewBag.Error = "Please Upload Files in .xls, .xlsx or .csv format";
                }
            }
            if (result)
            {
                ViewBag.data = "Data Import Successfully from excel to database.";
            }
            else
            {
                ViewBag.data = "there is some issue while importing the Data.";
            }
            return View();
        } 
    }
}

Here I have created a class Service that contains 1 method ConvertXSLXtoDataTable. The following is the code for the service class.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.OleDb;
using System.Data;

namespace ExcelReadData
{
    public class Service : IDisposable
    {
        #region **Private Variables**

        private TestDatabaseEntities _dbContext;
        
        #region **Constructor**

        public ImportExceltoDatabase()
        {
            _dbContext = new TestDatabaseEntities();
        }
        #endregion

        public bool ImportExceltoDatabase(string strFilePath, string connString)
        {
            bool result = false;
            OleDbConnection oledbConn = new OleDbConnection(connString);
            DataTable dt = new DataTable();
            try
            {
                oledbConn.Open();
                using (OleDbCommand cmd = new OleDbCommand("SELECT * FROM [Sheet1$]", oledbConn))
                {
                    OleDbDataAdapter oleda = new OleDbDataAdapter();
                    oleda.SelectCommand = cmd;
                    DataSet ds = new DataSet();
                    oleda.Fill(ds);

                    dt = ds.Tables[0];

                    if (dt.Rows.Count > 0)
                    {
                        table tblObj = new table();
                        foreach (DataRow row in dt.Rows)
                        {
                            tblObj.Name = row["Name"].ToString();
                            tblObj.Name = row["Address"].ToString();
                            tblObj.Salary = (int)row["Salary"];
                            tblObj.Age = (int)row["Age"];
                        }
                    }
                }
            }
            catch(Exception ex)
            {
                result = false;
            }
            finally
            {
                oledbConn.Close();
            }
            return result;
        }
    }
}

Now we have to create a view that contains file upload control and a button. When a request for ExcelToDatabase of Home Controller is made, it will show file upload control with button control. When the user selects a file and presses the button it will make a post request to Home Controller and the ExcelToDatabase method will be called. The following is the Razor View for both requests.

@using (Html.BeginForm("ImportExcel", "ExcelToDB", FormMethod.Post, new { enctype = "multipart/form-data" }))
{
    <table>
        <tr><td>Excel file</td><td><input type="file" id="FileUpload1" name="FileUpload1" /></td></tr>
        <tr><td></td><td><input type="submit" id="Submit" name="Submit" value="Submit" /></td></tr>
        <tr><td></td><td><lable>@(viewbag.data)</lable></td></tr>
    </table>
}