ConnectionStringEx
Several extensions to assist with setting a connection string for an OleDb.Connection to an Excel file for Excel 97-2003 or Excel 2007 and above.
Source
Module ExcelConnectionHelpers
Public Enum ImportExportMode
Normal = 0
''' <summary>
''' Read "intermixed" (numbers, dates, strings etc) data columns as text.
''' </summary>
''' <remarks></remarks>
AsText = 1
End Enum
Public Enum UseHeader
''' <summary>
''' Indicates that the first row contains columnnames, no data
''' </summary>
''' <remarks></remarks>
Yes
''' <summary>
''' Indicates that the first row does not contain columnnames
''' </summary>
''' <remarks></remarks>
No
End Enum
Public Enum ExcelProvider
''' <summary>
''' Microsoft.Jet.OLEDB.4.0
''' </summary>
''' <remarks></remarks>
XLS
''' <summary>
''' Microsoft.ACE.OLEDB.12.0
''' </summary>
''' <remarks></remarks>
XLSX
End Enum
Private OleExelVersion As String = "8.0"
Private Provider As String = "Microsoft.Jet.OLEDB.4.0"
Private Sub SetVersions(ByVal TheProvider As ExcelProvider)
If TheProvider = ExcelProvider.XLSX Then
OleExelVersion = "12.0"
Provider = "Microsoft.ACE.OLEDB.12.0"
Else
OleExelVersion = "8.0"
Provider = "Microsoft.Jet.OLEDB.4.0"
End If
End Sub
''' <summary>
'''
''' </summary>
''' <param name="sender"></param>
''' <param name="DataSource">File name to open including path if needed</param>
''' <param name="TheProvider"></param>
''' <remarks></remarks>
<System.Diagnostics.DebuggerStepThrough()> _
<Runtime.CompilerServices.Extension()> _
Public Sub ConnectionStringEx(ByVal sender As OleDb.OleDbConnection, _
ByVal DataSource As String, _
ByVal TheProvider As ExcelProvider)
SetVersions(TheProvider)
sender.ConnectionString = _
<Connection>
provider=<%= Provider %>;
data source='<%= DataSource %>';
Extended Properties=Excel <%= OleExelVersion %>;
</Connection>.Value
End Sub
''' <summary>
'''
''' </summary>
''' <param name="sender"></param>
''' <param name="DataSource">File name to open including path if needed</param>
''' <param name="TheProvider"></param>
''' <param name="MixType"></param>
''' <remarks></remarks>
<System.Diagnostics.DebuggerStepThrough()> _
<Runtime.CompilerServices.Extension()> _
Public Sub ConnectionStringEx(ByVal sender As OleDb.OleDbConnection, _
ByVal DataSource As String, _
ByVal TheProvider As ExcelProvider, _
ByVal MixType As ImportExportMode)
SetVersions(TheProvider)
sender.ConnectionString = _
<Connection>
provider=<%= Provider %>;
data source='<%= DataSource %>';
Extended Properties="Excel <%= OleExelVersion %>; IMEX=<%= CInt(MixType) %>;"
</Connection>.Value
End Sub
''' <summary>
'''
''' </summary>
''' <param name="sender"></param>
''' <param name="DataSource">File name to open including path if needed</param>
''' <param name="TheProvider"></param>
''' <param name="MixType"></param>
''' <param name="Header"></param>
''' <remarks></remarks>
<System.Diagnostics.DebuggerStepThrough()> _
<Runtime.CompilerServices.Extension()> _
Public Sub ConnectionStringEx(ByVal sender As OleDb.OleDbConnection, _
ByVal DataSource As String, _
ByVal TheProvider As ExcelProvider, _
ByVal MixType As ImportExportMode, _
ByVal Header As UseHeader)
SetVersions(TheProvider)
sender.ConnectionString = _
<Connection>
provider=<%= Provider %>;
data source='<%= DataSource %>';
Extended Properties="Excel <%= OleExelVersion %>; IMEX=<%= CInt(MixType) %>;HDR=<%= Header.ToString %>;"
</Connection>.Value
Console.WriteLine(sender.ConnectionString)
Console.WriteLine()
End Sub
<System.Diagnostics.DebuggerStepThrough()> _
<Runtime.CompilerServices.Extension()> _
Public Sub ConnectionStringExUpdatable(ByVal sender As OleDb.OleDbConnection, _
ByVal DataSource As String, _
ByVal TheProvider As ExcelProvider, _
ByVal Header As UseHeader)
SetVersions(TheProvider)
sender.ConnectionString = _
<Connection>
provider=<%= Provider %>;
data source='<%= DataSource %>';
Extended Properties="Excel <%= OleExelVersion %>; IMEX=<%= 2 %>"
</Connection>.Value
Console.WriteLine(sender.ConnectionString)
Console.WriteLine()
End Sub
End Module
Example
Private FileName As String = "MyExcel.xls"
. . .
If IO.File.Exists(FileName) Then
Dim cn As New System.Data.OleDb.OleDbConnection()
cn.ConnectionStringEx(FileName, ExcelProvider.XLS, ImportExportMode.AsText, UseHeader.No)
cn.Open()
Dim cmd As OleDbCommand = New OleDbCommand("SELECT * FROM [Sheet1$]", cn)
Private FileName As String = "MyExcel.xlsx"
. . .
If IO.File.Exists(FileName) Then
Dim cn As New System.Data.OleDb.OleDbConnection()
cn.ConnectionStringEx(FileName, ExcelProvider.XLSX, ImportExportMode.AsText, UseHeader.Yes)
cn.Open()
Dim cmd As OleDbCommand = New OleDbCommand("SELECT * FROM [Sheet1$]", cn)
Author: Kevin Gallagher
Submitted on: 25 mei 2011
Language: VB
Type: System.Data.Common.DbConnection
Views: 5015