-
Dec 6th, 2011, 03:40 PM
#1
Thread Starter
Frenzied Member
Import Excel into a Dataset
Anybody happen to have a semi-current link on how to do this? Don't imagine it is too tough but have never tried to before. Thanks.
Sean
Sean
Some days when I think about the next 30 years or so of my life I am going to spend writing code, I happily contemplate stepping off a curb in front of a fast moving bus.
-
Dec 6th, 2011, 04:07 PM
#2
Frenzied Member
Re: Import Excel into a Dataset
You can read an excel file using ADO, like any other database and import to a DataSet
look at www.connectionstrings.com for the correct connection string
This might help you also:
http://www.shiningstar.net/aspnet_ar...tProject5.aspx
-
Dec 6th, 2011, 10:38 PM
#3
Re: Import Excel into a Dataset
Reading a Excel 2007 sheet into a DataTable, all columns, all rows that have data. HDR=No in Extended Properties means the first row has data no column names. HDR=Yes treats the first row as column names.
Code:
Using cn As New System.Data.OleDb.OleDbConnection
Using cmd As OleDbCommand = New OleDbCommand With _
{ _
.Connection = cn, _
.CommandText = "SELECT * FROM [Sheet1$]" _
}
Dim FileName As String = IO.Path.Combine( _
Application.StartupPath, "SomeFile.xlsx")
Dim Builder As New OleDbConnectionStringBuilder With _
{ _
.DataSource = FileName, _
.Provider = "Microsoft.ACE.OLEDB.12.0" _
}
Builder.Add("Extended Properties", "Excel 12.0; HDR=No;")
cn.ConnectionString = Builder.ConnectionString
cn.Open()
Dim dt As New DataTable
dt.Load(cmd.ExecuteReader)
End Using
End Using
Read Excel pre 2007, first row of the two sheets same as above, no column header, instead data.
Code:
Dim dtSheet1 As New DataTable
Dim dtSheet2 As New DataTable
Dim Builder As New OleDbConnectionStringBuilder _
With _
{ _
.DataSource = IO.Path.Combine(Application.StartupPath, "SomeFile.xls"), _
.Provider = "Microsoft.Jet.OLEDB.4.0" _
}
Using cn As New System.Data.OleDb.OleDbConnection
Builder.Add("Extended Properties", "Excel 8.0; IMEX=1;HDR=No;")
cn.ConnectionString = Builder.ConnectionString
Using cmd As OleDbCommand = New OleDbCommand With {.Connection = cn}
cn.Open()
cmd.CommandText = "SELECT F1, F2, F3 FROM [Sheet1$]"
dtSheet1.Load(cmd.ExecuteReader)
cmd.CommandText = "SELECT F1, F2, F3 FROM [Sheet2$]"
dtSheet2.Load(cmd.ExecuteReader)
End Using
End Using
Let's say there are no headers and you know column 1 contains FirstName and column 2 is LastName you can alias the columns
Code:
Dim dtSheet1 As New DataTable
Dim Builder As New OleDbConnectionStringBuilder _
With _
{ _
.DataSource = IO.Path.Combine(Application.StartupPath, "SomeFile.xls"), _
.Provider = "Microsoft.Jet.OLEDB.4.0" _
}
Using cn As New System.Data.OleDb.OleDbConnection
Builder.Add("Extended Properties", "Excel 8.0; IMEX=1;HDR=No;")
cn.ConnectionString = Builder.ConnectionString
Using cmd As OleDbCommand = New OleDbCommand With {.Connection = cn}
cn.Open()
cmd.CommandText = "SELECT F1 As FirstName, F2 As LastName FROM [Sheet1$]"
dtSheet1.Load(cmd.ExecuteReader)
End Using
End Using
Not sure if the file is XLS or XLSX
Code:
Private Sub cmdSelectExcelFile_Click( _
ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles cmdSelectExcelFile.Click
If OpenFileDialog1.ShowDialog = Windows.Forms.DialogResult.OK Then
Dim FileName As String = OpenFileDialog1.FileName.ToUpper
Dim Builder As New OleDbConnectionStringBuilder With {.DataSource = FileName}
TextBox1.Text = OpenFileDialog1.FileName
If IO.Path.GetExtension(FileName) = ".XLSX" Then
Builder.Provider = "Microsoft.ACE.OLEDB.12.0"
Builder.Add("Extended Properties", "Excel 12.0;HDR=No;")
Else
Builder.Provider = "Microsoft.Jet.OLEDB.4.0"
Builder.Add("Extended Properties", "Excel 8.0;HDR=No;")
End If
Using cn As New OleDbConnection With {.ConnectionString = Builder.ConnectionString}
' Beings Excel gives Fn column names when a sheet/range has no column names in
' the first row we provide our own names.
Dim cmd As New OleDbCommand With _
{ _
.CommandText = "SELECT F1 As Column1, F2 As Column2, F3 As Column3 FROM [Sheet1$]", _
.Connection = cn _
}
cn.Open()
Dim dt As New DataTable
dt.Load(cmd.ExecuteReader)
End Using
End If
End Sub
-
Dec 7th, 2011, 09:53 AM
#4
Thread Starter
Frenzied Member
Re: Import Excel into a Dataset
Sean
Some days when I think about the next 30 years or so of my life I am going to spend writing code, I happily contemplate stepping off a curb in front of a fast moving bus.
-
Dec 12th, 2011, 10:31 AM
#5
Thread Starter
Frenzied Member
Re: Import Excel into a Dataset
It doesn't recognize my worksheet for some reason, and thoughts on what might cause that?
"The Microsoft Access database engine could not find the object 'Sheet1$'. Make sure the object exists and that you spell its name and the path name correctly. If 'Sheet1$' is not a local object, check your network connection or contact the server administrator."
Sean
Some days when I think about the next 30 years or so of my life I am going to spend writing code, I happily contemplate stepping off a curb in front of a fast moving bus.
-
Dec 12th, 2011, 12:02 PM
#6
Re: Import Excel into a Dataset
The only reason for this message is that in the workbook you are working with is that Sheet1 does not exists.
What are the names of the worksheets in the workbook you are working with? One way to learn the names of sheets in a workbook besides opening the file is using the code below which is hard coded to a workbook which you change to point to your Excel 2007 file. If you are looking at a pre-2007 file than change the connection string using the example I gave before.
Code:
Using cn As New System.Data.OleDb.OleDbConnection
Using cmd As OleDbCommand = New OleDbCommand With {.Connection = cn}
Dim Builder As New OleDbConnectionStringBuilder With _
{ _
.DataSource = IO.Path.Combine(Application.StartupPath, "book1.xlsx"), _
.Provider = "Microsoft.ACE.OLEDB.12.0" _
}
Builder.Add("Extended Properties", "Excel 12.0; HDR=No;")
cn.ConnectionString = Builder.ConnectionString
cn.Open()
Dim SheetNames = _
( _
From T In cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, _
New Object() {Nothing, Nothing, Nothing, "TABLE"}).AsEnumerable _
Where T.Field(Of String)("Table_Name").Contains("$") _
Select T.Field(Of String)("Table_name") _
).ToList
For Each SheetName In SheetNames
Console.WriteLine(SheetName)
Next
End Using
End Using
-
Dec 12th, 2011, 01:42 PM
#7
Thread Starter
Frenzied Member
Re: Import Excel into a Dataset
Huh it shows no worksheets at all.
Edit: Eh never mind I got it, wasn't using the full path. Wonder why it doesn't except though
Last edited by SeanGrebey; Dec 12th, 2011 at 01:49 PM.
Sean
Some days when I think about the next 30 years or so of my life I am going to spend writing code, I happily contemplate stepping off a curb in front of a fast moving bus.
-
Dec 12th, 2011, 01:57 PM
#8
Re: Import Excel into a Dataset
Originally Posted by SeanGrebey
Huh it shows no worksheets at all.
Then to OleDb there are no worksheets.
For a double check if you had OpenXML SDK installed the following gets worksheet names for Office 2007 and up versions of Excel.
The code below is taken from my code bank post on the basics of OpenXML in regards to working with Excel http://www.vbforums.com/showthread.php?t=664975
Code:
Public Function GetSheets(ByVal fileName As String) As DataTable
Using dtSheetData As New DataTable()
dtSheetData.Columns.AddRange(New DataColumn() _
{ _
New DataColumn("OrdinalPosition", GetType(System.Int32)), _
New DataColumn("SortedPosition", GetType(System.Int32)), _
New DataColumn("RelationshipId", GetType(System.String)), _
New DataColumn("SheetName", GetType(System.String)) _
} _
)
Dim CanOpen As Boolean = True
Try
Using spreadSheetDocument As SpreadsheetDocument = spreadSheetDocument.Open(fileName, False)
Application.DoEvents()
End Using
Catch ex As Exception
CanOpen = False
End Try
If Not CanOpen Then
Return dtSheetData
End If
Using document As SpreadsheetDocument = SpreadsheetDocument.Open(fileName, False)
Dim WorkBookPart As WorkbookPart = document.WorkbookPart
Dim Sheets As IEnumerable(Of Sheet) = WorkBookPart.Workbook.Descendants(Of Sheet)()
For Each Sheet In Sheets
dtSheetData.Rows.Add(New Object() _
{ _
CInt(Regex.Replace(Sheet.Id.ToString, "[^0-9]", "")), _
CInt(Sheet.SheetId.ToString), _
Sheet.Id.ToString, _
Sheet.Name _
})
Next
End Using
Return dtSheetData
End Using
End Function
-
Jul 22nd, 2014, 03:55 PM
#9
New Member
Re: Import Excel into a Dataset
"The Microsoft Access database engine could not find the object 'Sheet1$'. Make sure the object exists and that you spell its name and the path name correctly. If 'Sheet1$' is not a local object, check your network connection or contact the server administrator."
Originally Posted by kevininstructor
The only reason for this message is that in the workbook you are working with is that Sheet1 does not exists.
That happens not to be the case. I am looking right at this workbook and the sheet is right there. Not only that it worked on the developement machine. It did not give this error until it was deployed to the server. The workbook is contained within the web site. First I got the error
The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.
The I ran the install of the Microsoft Access Database Engine 2010 Redistributable 64 bit installer which it was reported needed to be run on machine before it could see "The 'Microsoft.ACE.OLEDB.12.0' provider" Then after that I got the error message
The Microsoft Access database engine could not find the object 'Sheet1$'. Make sure the object exists and that you spell its name and the path name correctly.
The path is being set using Server.MapPath(filename.xlsx) The file is under the root of the web app's directory structure. As stated before the app saw the file and the sheet when they were run in IIS Express on the developer machine, and responded perfectly.
The only difference is that the developer machine has MS Office 2010 installed on it and the server does not. Is there something incorrect about the engine. But everything I found says it is the proper engine.
-
Jul 22nd, 2014, 04:27 PM
#10
Re: Import Excel into a Dataset
I don't have a web server to duplicate your environment so I have nothing else to suggest.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|