Display Excel data in a DataGridView

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:

• 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.