Read Excel File using C# Best Practice With Example

Introduction
In this article we will learn how bind multiple dropdown list from a single method using c# or by using single binding method bind multiple dropdowns or use a shortest way to bind multiple dropdowns.

Previous Updates
In previous articles we have learnt Showing Bind multiple dropdown list using single method call using c#. Chart With Database In Asp.Net Using C#. Insert Only Numeric Values In Asp TextBox Using Regex . Get TextBox , Dropdown, CheckBox  control Values In Aspx.cs Page From User Control Using C#

Read Excel In DataGrid From Memory

Many times in our development field we face this situation when we read the data from excel file or write some data on excel file. Because excel is the best way to maintain the records like database tables. you always interact with excel and psd files for bulk updation or insertion.
Today here i will explain how to read the data from excel and display it on datagrid and later in new update i will tell you about how to write the data in Excel using c#.
So here first of all you just need a Excel file with the Data. Like this

After doing this give name the file and save. I hope you already have your excel whom you want to read.
Now i am sharing the Button Click of Read Excel Button where you can upload the file and read it.

private void btnReadExcel_Click(object sender, EventArgs e) {     string excelFilePath = string.Empty;     string fileExtension = string.Empty;     OpenFileDialog file = new OpenFileDialog();     if (file.ShowDialog() == System.Windows.Forms.DialogResult.OK)       {       excelFilePath = file.FileName;       fileExtension = System.IO.Path.GetExtension(excelFilePath);       if (fileExtension.CompareTo(".xls") == 0 || fileExtension.CompareTo(".xlsx") == 0)         {             try             {                 DataTable dt = new DataTable();                 dt = ReadMyExcel(excelFilePath, fileExtension); //read excel file                   dataGridExcel.Visible = true;                 dataGridExcel.Visible = true;                 dataGridExcel.DataSource = dt;             }             catch (Exception ex)             {                 MessageBox.Show(ex.Message.ToString());             }         }   else   {    MessageBox.Show("Please choose .xls or .xlsx file only.",     "Warning", MessageBoxButtons.OK, MessageBoxIcon.Error);   }  } }

In the above code i read the .xls or .xlsx file using ReadMyExcel method which i pasted the code below for this and after reading i bind that file with my datagrid control. 

public DataTable ReadMyExcel(string fileName, string fileExtension) {   string conn = string.Empty;   DataTable dtexcel = new DataTable();   if (fileExtension.CompareTo(".xls") == 0)      conn = @"provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";       Extended Properties='Excel 8.0;HRD=Yes;IMEX=1';"; //.xls for below excel 2007     else      conn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";         Extended Properties='Excel 12.0;HDR=NO';"; //this is for above excel 2007        using (OleDbConnection con = new OleDbConnection(conn))      {       try        {         OleDbDataAdapter oleAdpt = new OleDbDataAdapter("select * from [Sheet1$]", con);         oleAdpt.Fill(dtexcel);        }         catch { }       }         return dtexcel;  }

The above given code is for ReadExcel file from Harddrive or your specified location where you saved you excel sheet. Using Oledb we can read the Excel file. For using OleDbDataAdapter and OleDbConnection you have to use System.Data.OleDb Namespace.