Display Excel data in a DataGridView

djjeavons 2 2K Views

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 
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;
        }
    }

Dani AI

Generated

Nice pattern, . A couple of gotchas readers still hit: the ACE OLE DB provider must match your app bitness. If you see "The 'Microsoft.ACE.OLEDB.12.0' provider is not registered", either target x86/x64 to match Office, or install the 16.0 Access Database Engine and use Provider=Microsoft.ACE.OLEDB.16.0. Also add IMEX=1 when columns mix numbers and text; without it you can get blanks in those cells. For .xls use Excel 8.0; for .xlsx use Excel 12.0 Xml. And wrap OleDbConnection/OleDbDataAdapter in using blocks so exceptions do not keep the workbook locked.

The "junk characters" are just the provider’s formatting: worksheets come back as Sheet1$ or 'My Sheet$'. Show a clean display name but keep the raw TABLE_NAME for querying (and consider keeping only names that end with $ to skip named ranges). A tiny helper:

private static string ToDisplayName(string tableName)
{
    if (string.IsNullOrEmpty(tableName)) return tableName;
    var name = tableName.Trim().Trim('\'');   // strip outer quotes
    return name.EndsWith("$") ? name.Substring(0, name.Length - 1) : name;
}

Bind ComboBox.DisplayMember to the cleaned name and ValueMember to the original schema name so your SELECT uses the raw value.

Yes, the post is C#. Here is a minimal VB.NET read-and-bind snippet; reuse your raw TABLE_NAME (with the $) for sheetTableName:

Dim cs = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1'", excelFileTextBox.Text)
Using da As New OleDbDataAdapter(String.Format("SELECT * FROM [{0}]", sheetTableName), cs)
    Dim dt As New DataTable()
    da.Fill(dt)
    excelDataGridView.DataSource = dt
End Using

If you want to avoid ACE and bitness headaches entirely, a lightweight alternative is ExcelDataReader, which reads .xls/.xlsx directly and fills a DataTable you can bind to the grid.

Member Avatar for grarhakim
grarhakim 0 Newbie Poster

nice thanks

Member Avatar for naz1234
naz1234 0 Newbie Poster

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

Member Avatar for J.C. SolvoTerra
J.C. SolvoTerra 109 Eat, Sleep, Code, Repeat
Member Avatar for du_1
du_1 0 Newbie Poster

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.

Hopefully, this will be of help.

Edited by du_1
Member Avatar for Saqib_2
Saqib_2 0 Newbie Poster

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

Member Avatar for vivek_sharma
vivek_sharma 0 Newbie Poster

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

Edit Preview