vb.net 使用oledb连接按每个单元格检索excel数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18010234/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
retrieve excel data by each cell using oledb connection
提问by xana
i have a code for retrieve each data from excel to update dbf file. i use Microsoft.Office.Interop.Excel for read the data each range of column. now i want to change the connection excel using oledb.. how can i retrieve data of each column and set it into a variable? here is my code for Microsoft.Office.Interop.Excel.
我有一个代码，用于从 excel 中检索每个数据以更新 dbf 文件。我使用 Microsoft.Office.Interop.Excel 读取每个列范围的数据。现在我想使用 oledb 更改连接 excel .. 如何检索每列的数据并将其设置为变量？这是我的 Microsoft.Office.Interop.Excel 代码。
xlApp = New Excel.Application
xlWorkBook = xlApp.Workbooks.Open(xlsName)
xlWorkSheet = xlWorkBook.Worksheets("sheet1")
xlRange = xlWorkSheet.UsedRange
endrow = xlRange.Rows.Count
For rCnt = 3 To endrow
Empno = xlRange.Cells(rCnt, 1).Value
totalhrs = xlRange.Cells(rCnt, 3).Value
latehr = xlRange.Cells(rCnt, 4).Value
Next
xlWorkBook.Close()
xlApp.Quit()
this is my oledb connection. after the connection,i really dont have an idea what should i do to get the data. please help me...i hope someone in stackoverflow can help me.
这是我的 oledb 连接。连接后，我真的不知道我该怎么做才能获取数据。请帮助我...我希望 stackoverflow 中的某个人可以帮助我。
Dim xlsConnect As System.Data.OleDb.OleDbConnection
Dim DtSet As System.Data.DataSet
Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
Try
If (Path.GetExtension(xlsName) = ".xls") Then
xlsConnect = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & xlsName & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=2""")
ElseIf (Path.GetExtension(xlsName) = ".xlsx") Then
xlsConnect = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & xlsName & ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1;';")
End If
MyCommand = New System.Data.OleDb.OleDbDataAdapter("Select * from [Sheet1$]", xlsConnect)
'looping data should be here
xlsConnect.Close()
Catch ex As Exception
MsgBox(ex.ToString)
End Try
回答by VB.NET LEARNER
Assuming you will display your details in a datagridview.You can try this piece of coding :
假设您将在 datagridview 中显示您的详细信息。您可以尝试这段代码：
MyCommand.TableMappings.Add("Table", "TestTable")
DtSet = New System.Data.DataSet
MyCommand.Fill(DtSet)
DataGridView1.DataSource = DtSet.Tables(0)
MyConnection.Close()
If you want to use variables you have to loop through your dataset:
如果要使用变量，则必须遍历数据集：
dim i as integer = 0
for i = 0 to dtset.tables(0).rows.count-1
'Add values to your variable
next
