Import Excel in Asp.net MVC using OLE DB

OLE DB

OLE DB is a Microsoft technology for access to data . It actually works with Microsoft Access Engine to read excel or csv files.

So, for reading excel data from xlsx file, we will follow these steps.

1. First of all create a new asp.net mvc application and add a view with Html Form and add one input type file control on it along with button to save the file

@using (Html.BeginForm("ImportExcelFile", "Home", FormMethod.Post, new { @enctype = "multipart/form-data" }))
{
    <div class="container mt-5" style="margin-top:10px;">
        <input type="file" name="file" />
        <br />
        <input type="submit" value="save" />
    </div>
}

So, we have here added fileupload control on the view and a button for saving the file which we will read data from.

Now after adding the form we will go to controller and add code for saving the selected file and than reading the data from data excel file.

So, now take one controller with name "Home" as we have mentioned that in the beginform. And add a action named "ImportExcelFile". You can copy that from here

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Data.OleDb;
using System.Data;
using System.IO;
namespace Export.Controllers
{
    public class HomeController : Controller
    {
        public ActionResult Index()
        {
            
            return View();
        }

        public ActionResult ImportExcelFile(HttpPostedFileBase file)
        {
            string lsconnStr = string.Empty;
            DataTable dt = new DataTable();
            OleDbCommand command = null;
            var lsFilePath = Server.MapPath("/Content/TestFile"+DateTime.Now.ToString("ss")+".xlsx");
            file.SaveAs(lsFilePath);
            string lsFileExt = Path.GetExtension(lsFilePath);
           
            string errorMessage = "";
            string hdr = "Yes";
            int n_rows = 0;

            try
            {

                if (lsFileExt == ".xlsx")

                    lsconnStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + lsFilePath + ";Extended Properties='Excel 12.0 Xml;HDR=" + hdr + ";IMEX=1;MAXSCANROWS=0'";
                else if (lsFileExt == ".xls")
                    lsconnStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + lsFilePath + ";Extended Properties='Excel 12.0 Xml;HDR=" + hdr + ";IMEX=1;MAXSCANROWS=0'";
                else
                {
                    errorMessage = "Invalid file for import data. Allow only .xlsx.";
                }
                string s_excel_sql = string.Empty;
                OleDbConnection conn = new OleDbConnection(lsconnStr);
                conn.Open();
                DataTable excelSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

                string s_table = string.Empty;
                foreach (DataRow row in excelSchema.Rows)
                {
                    if (!row["TABLE_NAME"].ToString().Contains("FilterDatabase"))
                    {
                        if (!string.IsNullOrEmpty(s_table))
                        {
                            errorMessage = "Excel File contains multiple sheets. Please Upload Excel File with single Sheet.";

                        }
                        s_table = row["TABLE_NAME"].ToString();

                    }
                }

                if (n_rows > 0)
                {


                    s_excel_sql = String.Format(" SELECT TOP {0} * FROM [{1}] ", n_rows, System.IO.Path.GetFileNameWithoutExtension(s_table));
                }
                else
                {
                    s_excel_sql = String.Format(" SELECT * FROM [{0}] ", System.IO.Path.GetFileNameWithoutExtension(s_table));
                }

                command = new OleDbCommand(s_excel_sql, conn);
                OleDbDataAdapter da = new OleDbDataAdapter(command);
                DataTable exceldatatable = new DataTable();
                da.Fill(dt); //You will get the data in this dt datatable
            }
            catch (Exception ex)
            {

            }
            finally
            {
                if (command != null)
                {
                    command.Connection.Close();
                    command.Dispose();
                }
            }
            return RedirectToAction("Index");
        }

       
    }
}

So, you can copy the action from here and there we will use OLEDbCommand for creating a connection to the file and than we will use OLEDbDataAdapter for reading the data from the excel file which is provided in the connection.

You will see the data in the dt like this image

Issue The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.

If you get any error saying The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine while using OLE DB than you will have to fix that error first and you can take reference from Solved 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine. 

Just follow the steps from the article and you will be able to solve your error and than you will be able to use OLE DB.

So, this is how you can Import Excel in Asp.net MVC using OLE DB . There is one other way also to import excel file in asp.net and that is by using ExcelDataReader which you can check below

Import Excel using Excel Data Reader

If you face issue while working with OLE DB you can try working with Excel Data Reader. You can get all the steps of using ExcelDataReader from our article Import data from Excel in Asp.Net using ExcelDataReader . 

Let us know if you face any issue and comment if issue doesn't solve for you. You can copy the code samples also.