vb.net Visual Basic 将 Excel 工作表导入到 DataTable
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12451849/
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
Visual Basic Import Excel Sheet to DataTable
提问by MiBol
I'm currently working with a huge amount of data in Excel, and I want uploaded into a DataTable in VB.Net (70,000 rows per 30 columns) with mixed datatypes.
我目前正在 Excel 中处理大量数据，我想将混合数据类型上传到 VB.Net 中的 DataTable（每 30 列 70,000 行）。
I'm using the next code to import the information:
我正在使用下一个代码来导入信息：
Public Function mc_ExcelTableToDataTable(ByRef ExcelApp As Excel.Application, _
ByVal SheetName As String, _
ByVal TableName As String, _
Optional ByVal FilePath As String = "", _
Optional ByVal SQLsentence As String = "") As DataTable
Dim vPath As String
Dim vCloseExcelWorkbook As Boolean = False
If ExcelApp.ActiveWorkbook IsNot Nothing Then
vPath = IIf(FilePath = "", ExcelApp.ActiveWorkbook.FullName, FilePath)
Else
vPath = FilePath
End If
If SQLsentence = "" And ExcelApp.ActiveWorkbook Is Nothing Then
vCloseExcelWorkbook = True
ExcelApp.Workbooks.Open(vPath)
End If
Dim vRange As String = ExcelApp.Sheets(SheetName).ListObjects(TableName).Range.AddressLocal
vRange = vRange.Replace("$", "")
Dim vCNNstring As String = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source= " & vPath & ";" & _
"Extended Properties=""Excel 8.0;HDR=YES;IMEX=1"""
Dim ExcelCNN As System.Data.OleDb.OleDbConnection = New System.Data.OleDb.OleDbConnection(vCNNstring)
Dim vSQL As String = IIf(SQLsentence = "", _
"SELECT * FROM [" + SheetName + "$" & vRange & "]", _
SQLsentence)
Dim ExcelCMD As System.Data.OleDb.OleDbDataAdapter = New System.Data.OleDb.OleDbDataAdapter(vSQL, ExcelCNN)
ExcelCNN.Open()
Dim ExcelDS As System.Data.DataSet = New DataSet
ExcelCMD.Fill(ExcelDS)
mc_ExcelTableToDataTable = ExcelDS.Tables(0).Copy
ExcelCMD = Nothing
ExcelCNN.Close()
ExcelDS.mc_Dispose()
If vCloseExcelWorkbook Then ExcelApp.ActiveWorkbook.Close(False)
GCcleaner()
End Function
But, VB.Net give me the following error:
但是，VB.Net 给我以下错误：
Somebody knows what is the maximum capacity of the provider ACE.OLEDB.12.0? Or how fix this issue?
有人知道提供者ACE.OLEDB.12.0的最大容量是多少？或者如何解决这个问题？
FYI, this is the SQL select sentence:
仅供参考，这是 SQL 选择语句：
SELECT * FROM [Workflow data$A1:AC70276]
It's important mention that if I limit the rows to 20000 (SELECT * FROM [Workflow data$A1:AC20000]), the process works fine!
重要的是，如果我将行数限制为 20000（SELECT * FROM [Workflow data$A1:AC20000]），则该过程正常！
采纳答案by MiBol
seems I found a solution of the problem.
似乎我找到了问题的解决方案。
Basically I removed the range. In the currently select sentence I used:
基本上我删除了范围。在我使用的当前选择句子中：
SELECT * FROM [Workflow data$A1:AC70276]
I replaced that, by the next sentence:
我用下一句话代替了它：
SELECT * FROM [Workflow data$]
回答by David W
Hmmm....have heard that there may, in some cases, be an old, artifact-style bug in the ACE.OLEDB.12.0 driver such that queries to Excel spreadsheets in excess of 60000 rows can generate strange errors like this...and while I cannot attest to this myself, I can't help but wonder what would happen if you modified your range size to the ol' 65536 row limit in old Excel, or something +/- say 5%, if the error would go away. Its particularly curious that you tried it with 20000 rows and it apparently worked.
嗯....听说在某些情况下，ACE.OLEDB.12.0 驱动程序中可能存在一个旧的、人工样式的错误，这样对超过 60000 行的 Excel 电子表格的查询可能会产生这样的奇怪错误.. .虽然我自己无法证明这一点，但我不禁想知道如果您将范围大小修改为旧 Excel 中的 ol' 65536 行限制，或者 +/- 说 5%，如果错误会发生离开。特别好奇的是，您尝试了 20000 行并且显然有效。
EDITI have thrown together some comparable C# code to test this scenario, and can only offer that queries approaching this size are creating inconsistent errors, ranging from "System resource exceeded" to hangs to "OutOfMemoryExceptions." While I can't say I've seen your precise error, I would say this behavior is starting to tax the importing process such that undesirable if not unpredictable behavior may ensue. I'd suggest breaking the data up into smaller chunks if at all possible.
编辑我已经将一些类似的 C# 代码放在一起来测试这个场景，并且只能提供接近这个大小的查询会产生不一致的错误，从“超出系统资源”到挂起到“OutOfMemoryExceptions”。虽然我不能说我已经看到了您的确切错误，但我会说这种行为正在开始对进口过程征税，因此可能会出现不良甚至不可预测的行为。如果可能的话，我建议将数据分成更小的块。
Good luck.
祝你好运。
