Hi here is a quick rundown on importing excel to a business object use oledb jet provider,
To Import Excel Data to a strongly typed Business Object you first have to create a business object and map to the data source just like any other datasource. Here is a quicky on how to do it.
1) Create and set up your business object project to use oledb and use the connection string as follows. You can then map and build your strong typed business object. You can set whether or not your sheet has header etc in the connection string
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\pathtoyourfile.xls;Extended Properties='Excel 8.0;HDR=YES;IMEX=1';
2) Add an Access Data Source item using the same connection string
DataLayer.DataSources.Add(New MicroFour.StrataFrame.Data.AccessDataSourceItem("ExcelImport", loConnectionString))
3) Fill your business object
Dim LcSheetName as String = "Sheet1"
Bo.FillDataTable(String.Format("Select * from [{0}]", LcSheetName))
Just like any other data source that we generate a business object for and using strong typing we need to know in advance abouth the fields datatypes etc.However sometimes you might want to import an excel sheet that you don't know about at design time.So you can simply use regular ADO and oledb to import to a datatable and then do with it as you want. Here is a really basic example.
Private Function ImportExcel(ByVal ExcelFilename As String, ByVal Header As Boolean) As DataTable
Dim HasHeader As String
'Set Header Value
If Header Then
HasHeader = "YES"
Else
HasHeader = "NO"
End If
Dim loConnectionString As String = String.Format( _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1};IMEX=1'", _
ExcelFilename,HasHeader)
Dim loconn As New OleDbConnection()
Dim locmd As New OleDbCommand()
Dim lodbschema As DataTable
Dim lcSheetName As String
Dim loDataAdapter As OleDbDataAdapter
Dim LoDataTable As New DataTable
'Set Connection String
loconn.ConnectionString = loConnectionString
'Open Connection
loconn.Open()
locmd.Connection = loconn
'Get Schema table and determine if there is more than one sheet
lodbschema = loconn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
If lodbschema.Rows.Count > 1 Then
'call picklist of available sheets
lcSheetName = GetSheetNames(lodbschema)
Else
LcSheetName = lodbschema.Rows(0).Item("Table_Name")
End If
'Create Data Adapter
loDataAdapter = New OleDb.OleDbDataAdapter(String.Format("Select * from [{0}]", lcSheetName), loconn)
'Fill Table
loDataAdapter.Fill(LoDataTable)
'Close the Connection
loconn.Close()
'Dispose
loconn.Dispose()
locmd.Dispose()
lodbschema.Dispose()
loDataAdapter.Dispose()
'Return the Table
Return LoDataTable
Hope that helps and it all makes sense as I was kinda in a hurry and pulling snippets from my application.
Paul