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.

ddanbe commented: Great snip. +15
1,233 Views
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;
        }
    }

nice thanks

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.

Thnk's very much,,, It's really helpful for me...

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