Read Excel Sheet in C# and ASP.Net using Generic Data Access in ADO.Net 2.0

Most often, we will get some data in microsoft excel sheet which needs to be inserted to our application database. To do this, we can use the Microsoft Oledb provider with the ADO.Net classes to read the excel sheet data and do whatever we need to accomplish with data.

The below code can be used to fetch data from excel sheet using ADO.Net 2.0.

I have used Generic data access code to achieve this.

Connect to Excel

public DataTable FillDtFromExcel()

        {

            string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=NaturalEmail.xls;Extended Properties=""Excel 8.0;HDR=YES;""";

            string query = "Select * from [AllEmail$]";

            DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");

            DbConnection connection = factory.CreateConnection();

            connection.ConnectionString = connectionString;

             DbCommand selectCommand = factory.CreateCommand();

            selectCommand.CommandText = query;

            selectCommand.Connection = connection;

            DbDataAdapter adapter = factory.CreateDataAdapter();

            adapter.SelectCommand = selectCommand;

            DataTable dt = new DataTable();

            adapter.Fill(dt);

            return dt;

        }

Replace the Excel file name in the connection string with path to use the above code! The worksheet name should be given inside the [] brackets with a $ symbol concatenated in the query([AllEmail$]).

Since, the above code uses generic data access it will work only in .Netframework 2.0 and above.