This is one of the most popular topics on the Internet as far as manipulating data from Excel into a dataset and vice versa. In this article, we will mostly talk about the ability to import an Excel sheet into a dataset.
Getting the Excel sheet name
This process requires to know the Excel sheet name of the one you wish to import. Basically, there is just no way of detecting that easily other than to use OLE automation or to purchase a 3rd party library. So, despite that this class provides a property cSheet where the object can initialize to it, it still does require the developer to mention what it is or simply go with the default which is Schema.
As many servers would not have Excel installed on it, the OLE automation approach is not applicable. However, I have heard some there could be some 3rd party libraries that can do this job.
This is a very simple class.
Public Class Import
Private oApp As Framework.App
Private oConnection As OleDbConnection
Private oProcess As Framework.LXProcess
Public cSource As String = ""
Public cWorkbook As String = "Sheet1"
Public nCount As Integer = 0
Public oDataAdapter As OleDbDataAdapter
Public oDataSet As DataSet
' This is when we access the data provider in desktop and Web service mode
Sub New(ByVal toApplication As Framework.App)
oApp = toApplication
' This is when we access the data provider in a Web mode
Public Sub New(ByVal toProcess As Framework.LXProcess)
oProcess = toProcess
oApp = oProcess.oApp
' Import from Excel
Public Function ImportFromExcel() As Boolean
Dim lcConnection As String = ""
lcConnection = "provider=Microsoft.Jet.OLEDB.4.0; " + _
"data source='" + cSource + "'; Extended Properties=Excel 8.0;"
' Assign the source directory
oConnection = New OleDbConnection(lcConnection)
' Select the data from Sheet1 of the workbook
oDataAdapter = New OleDbDataAdapter("Select * From [" + cWorkbook + "$]", oConnection)
oDataSet = New DataSet
' Record count
nCount = oDataSet.Tables(0).Rows.Count
Properties and methods
Here is the method definition:
Here is the property definition:
How to use it?
The use of this class is very simple. Here is an example:
Dim loImport As Framework.Framework.Import = New Framework.Framework.Import(oProcess)
' Assign the Excel file
loImport.cSource = cFile
' Assign the workbook
loImport.cWorkbook = "Schema"
' Import into a dataset
If Not loImport.ImportFromExcel() Then
' Import all the rows
For lnCounter = 0 To loImport.nCount - 1
loRow = loImport.oDataSet.Tables(0).Rows(lnCounter)
This is a very simple class to import an Excel sheet into a dataset. It is also very easy to adapt it to fit your own needs.
Note that the object instantiation of this class requires the availability of the oProcess or oApp application objects. You may adjust to fit your own needs.