In this article I will explain with an example, how to import data from
Excel file to Windows Forms (WinForms) Application using C# and VB.Net.
The uploaded
Excel file data will be read using
OLEDB library and the read data will be displayed in DataGridView.
Download System.Data.OleDb DLL
There are two ways you can download the System.Data.OleDb DLL.
MSI Installer:
Nuget:
Connection String for Excel 2003 and Excel 2007 or higher formats
The Connection Strings for the Excel files of both 2003 and 2007 or higher formats have been specified in the App.Config file.
<connectionStrings>
<add name="Excel03ConString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'" />
<add name="Excel07ConString" connectionString="Provider=Microsoft.ACE.OLEDB.16.0;Data Source={0};Extended Properties='Excel 16.0;HDR={1}'" />
</connectionStrings>
Form Controls
The Form consists of following controls:
DataGridView – For displaying imported Excel data.
RadioButton – For capturing user input for specifying whether Excel file has header or not hence two RadioButtons will be used.
OpenFileDialog – For selecting Excel file.
The OpenFileDialog has been assigned with a FileOk event handler.
![Import data from Excel file to Windows Forms DataGridView using C# and VB.Net]()
Button – For importing Excel file.
The Button has been assigned with a Click event handler.
![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;
using System.Configuration;
using System.ComponentModel;
VB.Net
Imports System.IO
Imports System.Data
Imports System.Data.OleDb
Imports System.Configuration
Imports System.ComponentModel
Selecting the Excel File using C# and VB.Net
When the Select File Button is clicked, the Open File Dialog Box will be opened where the Excel file can be selected for importing.
C#
private void OnSelect(object sender, EventArgs e)
{
openFileDialog1.ShowDialog();
}
VB.Net
Private Sub OnSelect(sender As Object, e As EventArgs) Handles btnSelect.Click
OpenFileDialog1.ShowDialog()
End Sub
Importing the Excel File’s Sheet to DataGridView control using C# and VB.Net
When Excel file is selected and OK Button is clicked in OpenFileDialog, the file name and extension of the selected file is determined and the header value is also determined based on selection of the RadioButton.
Then, using the switch case statement and based one Excel file extension the connection string is built.
After that, file path and headers are replaced with the placeholders.
A connection is established with the Excel file using
OLEDB classes and the name of the first sheet is determined and the first sheet data is read into a DataTable.
Finally, the DataTable is assigned to the DataGridView and the DataGridView is populated.
C#
private void openFileDialog1_FileOk(object sender, CancelEventArgs e)
{
string filePath = openFileDialog1.FileName;
string extension = Path.GetExtension(filePath);
string header = rbHeaderYes.Checked ? "YES" : "NO";
string constr, sheetName;
constr = "";
switch (extension)
{
case ".xls"://Excel 97-03
constr = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
break;
case ".xlsx"://Excel 07
constr = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;
break;
}
DataTable dt = new DataTable();
constr = string.Format(constr, filePath, header);
//Read Data from the First Sheet.
using (OleDbConnection connExcel = new OleDbConnection(constr))
{
using (OleDbCommand cmdExcel = new OleDbCommand())
{
using (OleDbDataAdapter odaExcel = new OleDbDataAdapter())
{
cmdExcel.Connection = connExcel;
//Get the name of First Sheet.
connExcel.Open();
DataTable dtExcelSchema;
dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
sheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
connExcel.Close();
//Read Data from First Sheet.
connExcel.Open();
cmdExcel.CommandText = "SELECT * From [" + sheetName + "]";
odaExcel.SelectCommand = cmdExcel;
odaExcel.Fill(dt);
connExcel.Close();
//Populate DataGridView.
dataGridView1.DataSource = dt;
}
}
}
}
VB.Net
Private Sub openFileDialog1_FileOk(ByVal sender As Object, ByVal e As 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, sheetName As String
constr = String.Empty
Select Case extension
Case ".xls"
constr = ConfigurationManager.ConnectionStrings("Excel03ConString").ConnectionString
Case ".xlsx"
constr = ConfigurationManager.ConnectionStrings("Excel07ConString").ConnectionString
End Select
Dim dt As DataTable = New DataTable()
constr = String.Format(constr, filePath, header)
Using connExcel As OleDbConnection = New OleDbConnection(constr)
Using cmdExcel As OleDbCommand = New OleDbCommand()
Using odaExcel As OleDbDataAdapter = New OleDbDataAdapter()
cmdExcel.Connection = connExcel
connExcel.Open()
Dim dtExcelSchema As DataTable
dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
sheetName = dtExcelSchema.Rows(0)("TABLE_NAME").ToString()
connExcel.Close()
connExcel.Open()
cmdExcel.CommandText = "SELECT * From [" & sheetName & "]"
odaExcel.SelectCommand = cmdExcel
odaExcel.Fill(dt)
connExcel.Close()
dataGridView1.DataSource = dt
End Using
End Using
End Using
End Sub
Screenshot
Excel File
![Import data from Excel file to Windows Forms DataGridView using C# and VB.Net]()
Importing Excel data to DataGridView
![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
