Display Excel data in a DataGridView

In this post, I will show you how to use ADO.NET to load data from an Excel file into a DataGridView.

The principle is pretty straightforward, the first thing we will do is ask the user to select an Excel file. Once selected, we will connect to the file using an OleDbConnection object and once connected we will then use the GetSchema method of the OleDbConnection object to load all Worksheets (tables in ADO.NET speak) into a combo box. When the user selects a worksheet name from the combo box we will then select all data from that sheet and display it in a DataGridView by using standard SQL syntax.

So, create a new Windows Form project with any name you like and add the following controls to your form so that it resembles the screen shot below:

Control TypeProperties
LabelName: excelFileLabel
Text: Excel File
Text BoxName: excelFileTextBox
Anchor: Top, Left, Right
ButtonName: openFileButton
Text: ...
Anchor: Top, Right
LabelName: worksheetsLabel
Text: Worksheets
Combo BoxName: worksheetsComboBox
Anchor: Top, Left, Right
Data Grid ViewName: Name: excelDataGridView
Anchor: Top, Left, Right, Bottom
ButtonName: closeButton
Text: Close
Anchor: Bottom, Right

excelDataGridView

Now add the following code to your form:

You can also download a version I made earlier.

Leave a Reply

Your email address will not be published. Required fields are marked *