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];
        }
  • warren wee

    Hello, your code here the same as another code that I have found on other website. But both of the codes are not working for me. I am unable to find the total sheets in Excel file and show in the second form. Any idea what’s the problem?

    Thank you in advance.

    Warren Wee

  • Ankur

    Hi Warren wee,

    I wrote this artical on Both site.

    Have u add refrence  Microsoft ADO Ext. 2.8 from .net COM component in your application ?
    we use this dll to findout the sheet names from excel file.

    please let me know

    Thanks
    Ankur