ASP.NET : Read Excel File Spreadsheet Into DataSet In Using C#

The following program illustrates how to open an existing Excel spreadsheet in C#.

Step 1: Please download  ‘AccessDatabaseEngine_X64’  file from here

This download will install a set of components that can be used to facilitate transfer of data between Microsoft Office System files and non-Microsoft Office applications such as Microsoft SQL Server and it also installed ODBC and OLEDB drivers that helps developers to use in developing their application.

Step 2: you have to add the Microsoft Excel 15.0 Object Library to you project.

Step 3:If still you facing following issue as shown in screen shot then you have to set the configuration management as shown in screen shot below this issue.

Note: Rebuild your application and run following code, if you face same problem again then close your application and open it again. After all this I hope this code of .cs file will read your excel file.

Step 4: Copy following code to you console application. It will read your excel file.

using System.Data;

using System.Data.OleDb;

namespace ReadingExcelFile

{

    class Program

    {

        static void Main(string[] args)

        {

            string[] tableName = {

                                    "EMPLOYEE",

                                    "DEPT"

                                 };

            for (int i = 0; i < tableName.Length; i++)

            {

               fnReadExcelFile(tableName[i]);               

            }

        }

        protected static void fnReadExcelFile(string TableName)

        {

       string path = @"D:\Projects\ReadingCSVFile\ReadingCSVFile\" + TableName + ".xlsx";

            //Create connection string to Excel work book

            string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;Persist Security Info=False";

            //Create Connection to Excel work book

            OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);

            //Create OleDbCommand to fetch data from Excel

            string _sql = "Select * from [Employees$]";

            OleDbCommand cmd = new OleDbCommand(_sql, excelConnection);

            excelConnection.Open();

            OleDbDataAdapter dReader = new OleDbDataAdapter(_sql, excelConnection);

            DataSet ds = new DataSet();

            dReader.Fill(ds);

            fnInsertData(ds.Tables[0], TableName);

        }

        private static void fnInsertData(DataTable dt, string TableName)

        {

            switch (TableName)

            {

                case "EMPLOYEES":

                    {

                        for (int i = 0; i < dt.Rows.Count; i++)

                        {

                            //Your function to insert record to database

                            //fnInsEMPLOYEES(dt.Rows[i]);

                        }

                        break;

                    }

                case "DEPT":

                    {

                        for (int i = 0; i < dt.Rows.Count; i++)

                        {

                            //Your function to insert record to database

                            //fnInsDEPT(dt.Rows[i]);

                        }

                        break;

                    }

            }

        }

    }

}