The attached VS2012 solution contains a project which has a component tray designed to assist those who have not done much with creating connection strings via OleDb data provider to work with Excel. The component tray if found useful could be placed into a class project, compiled then added to the IDE toolbox.
Please note that the component works for many xlsx and xls file but there may be some it does not until IMEX is setup correctly.
MSDN_ExcelHelper.zip
The main form has two of these components to examine that are setup while the third is not setup. To setup the component single click on ExcelHelperEmpty, select properties then select FileName which shows a button. Pressing the button you are prompted for a xlsx file, change the filter for selecting a xlx file. Once a file has been selected set the property Headers. Yes indicates sheets that you want to read the first row represents field names while No indicates the first row has data. If there is nothing special about the sheets to read leave Mode at Normal, Mode indicates the IMEX setting for the connection.
In the demo project's main form the first two lines show the connection strings for two of the components, next IsReady determines if it is safe to get sheet names for the ExcelHelper1 file and place the names into a ComboBox used later to read one of the sheets.
Code:
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
Console.WriteLine(ExcelHelper1.ConnectionString)
Console.WriteLine(ExcelHelper2.ConnectionString)
'
' Lets make sure the file exists
'
If ExcelHelper1.IsReady Then
cboSheetNames.DataSource = ExcelHelper1.SheetNames
txtConnectionString.Text = ExcelHelper1.ConnectionString
Else
cmdDataInformation.Enabled = False
cmdLoad.Enabled = False
MessageBox.Show("Excel file not available.")
End If
End Sub
Select a sheet then pressing the load button reads the sheet data into a DataGridView.
Code:
Private Sub cmdLoad_Click(sender As Object, e As EventArgs) Handles cmdLoad.Click
Dim dt As New DataTable
Using cn As New OleDb.OleDbConnection With
{
.ConnectionString = ExcelHelper1.ConnectionString
}
Using cmd As New OleDb.OleDbCommand With
{
.Connection = cn,
.CommandText = "SELECT * FROM [" & cboSheetNames.Text & "]"}
cn.Open()
dt.Load(cmd.ExecuteReader)
End Using
End Using
DataGridView1.DataSource = dt
End Sub
Properties of the component