Get All Sheets From Excel And Fill Data Of Selected Excel Sheet In DataGridView

Step 1: Create new widows form application.

Step 2: Create windows form with button, label, combobox and datagridview as below.

 

Step 3: Write following code.

using System;

using System.Data;
using System.Data.OleDb;

using System.Windows.Forms;

namespace ExcelDemo

{

    public partial class Form1: Form

    {

        OleDbConnection OleDbcon;

        public Form1()

        {

            InitializeComponent();

        }

        private void button1_Click(object sender, EventArgs e)

        {

            OpenFileDialog openFileDialog = new OpenFileDialog();

            openFileDialog.Filter = "Excel Files|*.xls;*.xlsx";

            openFileDialog.ShowDialog();

            if (!string.IsNullOrEmpty(openFileDialog.FileName))

            {

                OleDbcon = new OleDbConnection(@ "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + openFileDialog.FileName + ";Extended Properties=Excel 12.0;");

                OleDbcon.Open();

                DataTable dt = OleDbcon.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

                OleDbcon.Close();

                comboBox1.Items.Clear();

                for (int i = 0; i < dt.Rows.Count; i++)

                {

                    String sheetName = dt.Rows[i]["TABLE_NAME"].ToString();

                    sheetName = sheetName.Substring(0, sheetName.Length - 1);

                    comboBox1.Items.Add(sheetName);

                }

            }

        }

        private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)

        {

            OleDbDataAdapter oledbDa = new OleDbDataAdapter("Select * from [" + comboBox1.Text + "$]", OleDbcon);

            DataTable dt = new DataTable();

            oledbDa.Fill(dt);

            dataGridView1.DataSource = dt;

        }

        private void button2_Click(object sender, EventArgs e)

        {

            OpenFileDialog OpenFileDialog = new OpenFileDialog();

            OpenFileDialog.ShowDialog();

            string path = OpenFileDialog.FileName;

        }

    }

}

Step 4: Run application. And click on browse button.

 

Step 5: From open file dialog select excel file. When file is selected, all sheet names in file are filled in combobox.

 

Step 6: Select any sheet name from combobox. Then data from that selected sheet is filled in datagridview. The first row of excel sheet is considered as column header.