Import data from excel to DataGridView in C#

In this article we will discuss that how to import data from any excel file into datagridview control in c#.
– Start a new project in your visual studio in C#.

– Add reference of Microsoft ADO Ext. 2.8 from .net COM component. We will use this library to extract all Excel Sheet from Excel file.

Import data from excel to DataGridView

– Add a windows form in your project, add a Datagridview control and use the following code snippets.

– Use Open Dialog box control So that we can browse and excel file. Set Open Dialog control Filter = “Excel Sheet(*.xls)|*.xls|All Files(*.*)|*.*”, so that it will filter browse only Excel files.

Put the following Code in Browser Button Click Event.

   private void btnBrowser_Click(object sender, EventArgs e)
        {
 
            OpenFileDialog fdlg = new OpenFileDialog();
            fdlg.Title = "Select file";
            fdlg.InitialDirectory = @"c:\";
            fdlg.FileName = txtFileName.Text;
            fdlg.Filter = "Excel Sheet(*.xls)|*.xls|All Files(*.*)|*.*";
            fdlg.FilterIndex = 1;
            fdlg.RestoreDirectory = true;
            if (fdlg.ShowDialog() == DialogResult.OK)
            {
                txtFileName.Text = fdlg.FileName;
                Import();
                Application.DoEvents();        
     }
        }

This will filter only Excel file from your Machine.
Browse any Excel file and Click OK.

This Excel file can contain more than one Sheet. You need to add another form to show all excel sheets name so that user can select any one excel sheet which he want to import.

Write the following code on Page Load event of  that from on which you want to show all excel sheet list

        private void Select_Tables_Load(object sender, EventArgs e)
        {
            if (!DataTables)             {
                if (Tables != null)
                {
                    for (int tables = 0; tables < Tables.Length; tables++)
                    {
                        try
                        {
                            ListViewItem lv = new ListViewItem();
                            lv.Text = Tables[tables].ToString();
                            lv.Tag = tables;
                            lstViewTables.Items.Add(lv);
                        }
                        catch (Exception ex)
                        { }
                    }
                }
            }
            else
            {
                if (dtTable.Rows.Count&gt;0)
                {
                    for (int tables = 0; tables< dtTable.Rows.Count; tables++)
                    {
                        try
                        {
                            ListViewItem lv = new ListViewItem();
                            lv.Text = dtTable.Rows[tables][0].ToString();
                            lv.Tag = dtTable.Rows[tables][0];
                            lstViewTables.Items.Add(lv);
                        }
                        catch (Exception ex)
                        { }
                    }
                }
            }
        } 

By the following function we can find the total sheets in Excel file.

  
      public static string[] GetTableExcel(string strFileName)
        {
            string[] strTables = new string[100];
            Catalog oCatlog = new Catalog();
            ADOX.Table oTable = new ADOX.Table();
            ADODB.Connection oConn = new ADODB.Connection();
            oConn.Open("Provider=Microsoft.Jet.OleDb.4.0; Data Source = " + strFileName + "; Extended Properties = \"Excel 8.0;HDR=Yes;IMEX=1\";", "", "", 0);
            oCatlog.ActiveConnection = oConn;
            if (oCatlog.Tables.Count>0)
            {
                int item = 0;
                foreach (ADOX.Table tab in oCatlog.Tables)
                {
                    if (tab.Type == "TABLE")
                    {
                        strTables[item] = tab.Name;
                        item++;
                    }
                }
            }
            return strTables;
        }

Select any sheet from above list and click on Import button.

Call following function:
The following function return a dataset.

     public static DataTable GetDataTableExcel(string strFileName, string Table)
 
        {
            System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0; Data Source = " + strFileName + "; Extended Properties = \"Excel 8.0;HDR=Yes;IMEX=1\";");
            conn.Open();
            string strQuery = "SELECT * FROM [" + Table + "]";
            System.Data.OleDb.OleDbDataAdapter adapter = new System.Data.OleDb.OleDbDataAdapter(strQuery, conn);
            System.Data.DataSet ds = new System.Data.DataSet();
            adapter.Fill(ds);
            return ds.Tables[0];
        }