How to Load Excel Sheet Names into DropDownList using C#

Introduction

Today, one of my projects I want to bind or load Excel sheets names in to DropDownList to use by users and perform calculation. I have done that using ADO.NET with C#. I would like share with you here.

Implementation

Let’s create a simple excel sheet with four sheet for this demonstration. I could not share original excel sheet of the customer here. Excel book like below, 

In above book have four sheets. So let’s load these four sheet names in dropdownList to select by users. I have used the OldDbConnection to connect to excel sheet and get the Scheme Tables from the book and then extract names from the source.

To use the OleDbConnection , you have to add the following namespace.

C# Code

using System.Data.OleDb;

Now write code to extract sheet names and add dropdownlist.

C# Code

using System;
using System.Data;
using System.Data.OleDb;
using System.Windows.Forms;

namespace GetSheetNames
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            LoadSheetsName(@"\Sample Sheet.xlsx");
        }

        private void LoadSheetsName(string fileName)
        {
            try
            {
                DataTable tablesNames;
                cmbNames.Items.Clear();
                string connectionstring = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + fileName + ";Extended Properties=Excel 8.0;";
                using (OleDbConnection oleDbConn = new OleDbConnection(connectionstring))
                {
                    oleDbConn.Open();
                    tablesNames = oleDbConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                    if (tablesNames == null)
                    {
                        cmbNames.Items.Add("-Select-");
                    }
                    else
                    {
                        cmbNames.Items.Add("-Select-");
                        for (int i = 0; i < tablesNames.Rows.Count; i++)
                        {
                            string tableName = tablesNames.Rows[i]["TABLE_NAME"].ToString().Replace("$", "");
                            tableName = tableName.Replace("'", "");
                            cmbNames.Items.Add(tableName.Trim());
                        }
                    }
                }


            }
            catch (Exception)
            {

                throw;
            }
        }       
    }
}

In above code I had connected to excel using OleDbConnection and then open connection. After that Call GetOleDbSchemaTable() method with Tables for Get Schema of the data source. In this demonstration DataSource is excel sheet and scheme table will be every sheets.

Next when you are get the sheet names from the Tables Names object, sheet name has $ and ' .I have replaced those with empty.

string tableName = tablesNames.Rows[i]["TABLE_NAME"].ToString().Replace("$", "");
 tableName = tableName.Replace("'", "");

That's all. Now run application and see output will be followings,

Output 

Download Sample Project 

Download source files -44 kb