In this article I will explain how to import data from Excel file’s sheet and display in DataGridView control in Windows Forms (WinForms) application using C# and VB.Net.
The Excel sheet data will be read into a DataTable which is then used to populate the DataGridView control.
Form Controls
I have added a DataGridView, two Radio Buttons and a Button to the Windows Form. I have also added an OpenFileDialog to the Form which allow us to select the excel file.
![Import data from Excel file to Windows Forms DataGridView using C# and VB.Net]()
Namespaces
You will need to import the following namespaces.
C#
using System.IO;
using System.Data;
using System.Data.OleDb;
VB.Net
Imports System.IO
Imports System.Data
Imports System.Data.OleDb
Connection Strings to the Excel
Excel versions 97-2003 and Excel 2007 (and higher) use different providers I have declared two connection strings of which one uses Microsoft Jet Engine for older versions and the other one uses Microsoft Ace for newer versions.
C#
private string Excel03ConString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'";
private string Excel07ConString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'";
VB.Net
Private Excel03ConString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'"
Private Excel07ConString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'"
Selecting the Excel File
When the Select File button is clicked, it shows the Open File Dialog, using which we need to select the Excel file we want to import to DataGridView control.
C#
private void btnSelect_Click(object sender, EventArgs e)
{
openFileDialog1.ShowDialog();
}
VB.Net
Private Sub btnSelect_Click(sender As Object, e As EventArgs) Handles btnSelect.Click
OpenFileDialog1.ShowDialog()
End Sub
![Import data from Excel file to Windows Forms DataGridView using C# and VB.Net]()
Importing the Excel File’s Sheet to DataGridView control
As soon as the Excel File is selected the following event handler is executed. Here first the path of the Excel file is determined and then its extension.
Using the extension of the Excel file, appropriate connection string variable is chosen and the connection string is built using the path of the file and value of the RadioButtons which determine whether the Excel file has header or not.
Next the name of the first sheet is determined and then the sheet data is read into a DataTable which ultimately is bound to the DataGridView control.
C#
private void openFileDialog1_FileOk(object sender, System.ComponentModel.CancelEventArgs e)
{
string filePath = openFileDialog1.FileName;
string extension = Path.GetExtension(filePath);
string header = rbHeaderYes.Checked ? "YES" : "NO";
string conStr, sheetName;
conStr = string.Empty;
switch (extension)
{
case ".xls": //Excel 97-03
conStr = string.Format(Excel03ConString, filePath, header);
break;
case ".xlsx": //Excel 07
conStr = string.Format(Excel07ConString, filePath, header);
break;
}
//Get the name of the First Sheet.
using (OleDbConnection con = new OleDbConnection(conStr))
{
using (OleDbCommand cmd = new OleDbCommand())
{
cmd.Connection = con;
con.Open();
DataTable dtExcelSchema = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
sheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
con.Close();
}
}
//Read Data from the First Sheet.
using (OleDbConnection con = new OleDbConnection(conStr))
{
using (OleDbCommand cmd = new OleDbCommand())
{
using (OleDbDataAdapter oda = new OleDbDataAdapter())
{
DataTable dt = new DataTable();
cmd.CommandText = "SELECT * From [" + sheetName + "]";
cmd.Connection = con;
con.Open();
oda.SelectCommand = cmd;
oda.Fill(dt);
con.Close();
//Populate DataGridView.
dataGridView1.DataSource = dt;
}
}
}
}
VB.Net
Private Sub openFileDialog1_FileOk(sender As Object, e As System.ComponentModel.CancelEventArgs) Handles OpenFileDialog1.FileOk
Dim filePath As String = OpenFileDialog1.FileName
Dim extension As String = Path.GetExtension(filePath)
Dim header As String = If(rbHeaderYes.Checked, "YES", "NO")
Dim conStr As String, sheetName As String
conStr = String.Empty
Select Case extension
Case ".xls"
'Excel 97-03
conStr = String.Format(Excel03ConString, filePath, header)
Exit Select
Case ".xlsx"
'Excel 07
conStr = String.Format(Excel07ConString, filePath, header)
Exit Select
End Select
'Get the name of the First Sheet.
Using con As New OleDbConnection(conStr)
Using cmd As New OleDbCommand()
cmd.Connection = con
con.Open()
Dim dtExcelSchema As DataTable = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
sheetName = dtExcelSchema.Rows(0)("TABLE_NAME").ToString()
con.Close()
End Using
End Using
'Read Data from the First Sheet.
Using con As New OleDbConnection(conStr)
Using cmd As New OleDbCommand()
Using oda As New OleDbDataAdapter()
Dim dt As New DataTable()
cmd.CommandText = (Convert.ToString("SELECT * From [") & sheetName) + "]"
cmd.Connection = con
con.Open()
oda.SelectCommand = cmd
oda.Fill(dt)
con.Close()
'Populate DataGridView.
dataGridView1.DataSource = dt
End Using
End Using
End Using
End Sub
Screenshots
Excel File
![Import data from Excel file to Windows Forms DataGridView using C# and VB.Net]()
DataGridView with Excel data
![Import data from Excel file to Windows Forms DataGridView using C# and VB.Net]()
Exceptions
It is possible that you might get the following exception while trying to import Excel version 2007 or higher.
![Import data from Excel file to Windows Forms DataGridView using C# and VB.Net]()
Downloads
