In this article I will explain with an example, how to display
Excel Sheet in 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.
Excel 97 – 2003 format which uses Microsoft Jet driver and the Excel version is set to 8.0 and Header is set to YES i.e. first row in Excel sheet will be Header Row.
<add name="Excel03ConString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'" />
Excel 2007 and higher format which uses Microsoft Ace driver and the Excel version is set to 12.0 and Header is set to YES i.e. first row in Excel sheet will be Header Row.
<add name="Excel07ConString" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR={1}'" />
Queries
The following SQL query selects records from Excel file.
SELECT * From [" + sheetName + "]
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.
![Display Excel Sheet in Windows Forms using C# and VB.Net]()
Button – For importing Excel file.
The Button has been assigned with a Click event handler.
![Display Excel Sheet in Windows Forms 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
Screenshots
Excel File
![Display Excel Sheet in Windows Forms using C# and VB.Net]()
Importing Excel data to DataGridView
![Display Excel Sheet in Windows Forms 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.
![Display Excel Sheet in Windows Forms using C# and VB.Net]()
Downloads
