Import Data from Excel to DataGridView in C#

First need to add the reference "Microsoft ADO Ext. 2.8". You can easily add it from COM components.

Add an open Dialog box control on form

Put the following code on Browser button click events…..

        private void button1_Click_1(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.

This Excel file can contains more than one Sheet. You need to add another form to 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 even of this form

        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>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;

        }

 


Following function return a dataset so that you can bind it from Data Grid View  easily.

        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];

        }

 

 

To see the complete source code you can download the zip file.