This code snippet demonstrates how to use ADO.NET to read the contents of a Microsoft Excel Worksheet. This snippet also demonstrates the use of the GetSchema method to determine what worksheets are available within an Excel workbook allowing the end user the ability to select a specific worksheet in order to view the data.

To start, create a Windows Forms application and add the following controls so that it resembles the screenshot below:

excelDataGridView.JPG

  • Label (Name: excelFileLabel, Text: Excel File)
  • Text Box (Name: excelFileTextBox, Anchor: Top, Left, Right)
  • Button (Name: openFileButton, Text: ..., Anchor: Top, Right)
  • Label (Name: worksheetsLabel, Text: Worksheets)
  • Combo Box (Name: worksheetsComboBox, Anchor: Top, Left, Right)
  • Data Grid View (Name: excelDataGridView, Anchor: Top, Left, Right, Bottom)
  • Button (Name: closeButton, Text: Close, Anchor: Bottom, Right)

The first piece of code will go into the openFileButton_Click event and is responsible for asking the user to select an Excel file. Once the file has been selected, an OleDbConnection will be used to connect to the Excel file and once connected will then use the GetSchema method to get all table names (Worksheets) that belong to the selected Excel file.

private void openFileButton_Click(object sender, EventArgs e)
{
OpenFileDialog openDialog = new OpenFileDialog();
openDialog.Filter = "Excel|*.xlsx";

if (openDialog.ShowDialog() == DialogResult.OK)
    {
        excelFileTextBox.Text = openDialog.FileName;

        //Get all worksheet names from the Excel file selected using GetSchema of an OleDbConnection
        string sourceConnectionString = String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0 Xml;HDR=YES'",excelFileTextBox.Text);
        OleDbConnection connection = new OleDbConnection(sourceConnectionString);

        connection.Open();
        DataTable tables = connection.GetSchema("Tables", new String[] { null, null, null, "TABLE" });
        connection.Dispose();

        //Add each table name to the combo box
        if (tables != null && tables.Rows.Count > 0)
        {
            worksheetsComboBox.Items.Clear();
            foreach (DataRow row in tables.Rows)
            {
                worksheetsComboBox.Items.Add(row["TABLE_NAME"].ToString());
            }
        }
    }
}

The next piece of code will go into the SelectedIndexChanged event of the worksheetsComboBox and will simply run an SQL SELECT statement against the chosen Worksheet and populate the DataGridView.

private void worksheetsComboBox_SelectedIndexChanged(object sender, EventArgs e)
{
    //Display the data from the selected Worksheet
    string sourceConnectionString = String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0 Xml;HDR=YES'", excelFileTextBox.Text);

    OleDbDataAdapter adapter = new OleDbDataAdapter(String.Format("SELECT * FROM [{0}]", worksheetsComboBox.SelectedItem.ToString()), sourceConnectionString);
    DataTable currentSheet = new DataTable();
    adapter.Fill(currentSheet);
    adapter.Dispose();

    excelDataGridView.DataSource = currentSheet;
}

And to finish off, the close button code:

private void closeButton_Click(object sender, EventArgs e)
{
    Application.Exit();
}

I have tested this using Excel files created with Office 2013, but there is no reason why this would not work for previous versions of Office. You may however need to adjust the connection string and if this is the case, please see Excel connection strings for an appropriate one.

Edited 1 Year Ago by djjeavons

Comments
Great snip.
public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void closeButton_Click(object sender, EventArgs e)
        {
            Application.Exit();
        }

        private void openFileButton_Click(object sender, EventArgs e)
        {
            OpenFileDialog openDialog = new OpenFileDialog();
            openDialog.Filter = "Excel|*.xlsx";

            if (openDialog.ShowDialog() == DialogResult.OK)
            {
                excelFileTextBox.Text = openDialog.FileName;

                //Get all worksheet names from the Excel file selected using GetSchema of an OleDbConnection
                string sourceConnectionString = String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0 Xml;HDR=YES'",excelFileTextBox.Text);
                OleDbConnection connection = new OleDbConnection(sourceConnectionString);

                connection.Open();
                DataTable tables = connection.GetSchema("Tables", new String[] { null, null, null, "TABLE" });
                connection.Dispose();

                //Add each table name to the combo box
                if (tables != null && tables.Rows.Count > 0)
                {
                    worksheetsComboBox.Items.Clear();
                    foreach (DataRow row in tables.Rows)
                    {
                        worksheetsComboBox.Items.Add(row["TABLE_NAME"].ToString());
                    }
                }

            }
        }

        private void worksheetsComboBox_SelectedIndexChanged(object sender, EventArgs e)
        {
            //Display the data from the selected Worksheet
            string sourceConnectionString = String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0 Xml;HDR=YES'", excelFileTextBox.Text);

            OleDbDataAdapter adapter = new OleDbDataAdapter(String.Format("SELECT * FROM [{0}]", worksheetsComboBox.SelectedItem.ToString()), sourceConnectionString);
            DataTable currentSheet = new DataTable();
            adapter.Fill(currentSheet);
            adapter.Dispose();

            excelDataGridView.DataSource = currentSheet;
        }
    }

I am a highly motivated individual and pride myself in my technical and people skills. I am a team player and enjoy motivating individuals to achieve both their goals and the goals of the business.

I have over 17 years of software development experience and have managed a number of teams during this time. I am passionate about software development, tools and processes that can aid development teams to become as efficient and effective as possible. I enjoy creating and delivering new products that are customer focused and solve real business problems.

Hi,

I really interested about this article. But I cant run this code. I'm using VB.NEt. Is that a c# in your code and how to convert it?

Sorry I'm still in basic

Thanks for the information. I have been searching this for a couple of days, and I also found following artcile very useful in displying Excel data in datatable. It provides both C# and VB.NET code.

Import data from Excel to Datatable in C#, VB.NET

Hopefully, this will be of help.

Edited 1 Year Ago by du_1

i tried doing the same , but my worksheet names in the combo box are getting populated with junk characters. Although its working fine.
Please help me over this issue

The article starter is a financial contributor. Sponsored articles offer a bounty for quality replies.