vb.net 从excel导入到数据表vb.net
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/49188924/
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
Import from excel to data table vb.net
提问by Vengat
I have imported the excel sheet to data table by using the below code but the few cell value could not be loaded properly. instead of the cell value it is displayed as null.
Please refer the attached the image of the excel and data table.
我已使用以下代码将 Excel 工作表导入数据表，但无法正确加载少数单元格值。它显示为空值而不是单元格值。
请参考随附的excel和数据表的图像。
Dim conStr As String = ""
conStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=Yes'"
conStr = String.Format(conStr, Path)
Dim connExcel As New OleDbConnection(conStr)
Dim cmdExcel As New OleDbCommand()
Dim oda As New OleDbDataAdapter()
cmdExcel.Connection = connExcel
'Get the name of First Sheet
connExcel.Open()
dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
Dim SheetName As String = ""
If dtExcelSchema.Rows.Count > 0 Then
SheetName = dtExcelSchema.Rows(dtExcelSchema.Rows.Count - 1)("TABLE_NAME").ToString()
End If
connExcel.Close()
'Read Data from First Sheet
connExcel.Open()
cmdExcel.CommandText = "SELECT * From [" & SheetName & "]"
oda.SelectCommand = cmdExcel
oda.Fill(dt)
dt.TableName = SheetName.ToString().Replace("$", "")
connExcel.Close()
Return dt
回答by Hadi
You are facing this issue because your column contains mixed data types, so the OLEDB provider will take the dominant data type and convert all other datatype values to NULL.
您面临此问题是因为您的列包含混合数据类型，因此 OLEDB 提供程序将采用主要数据类型并将所有其他数据类型值转换为 NULL。
Try adding IMEX=1to the extended properties in the connection String:
尝试添加IMEX=1到连接字符串中的扩展属性：
"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'"
If it doesn't work, try adding a dummy row after the Header, in this row add a string value ex xyzand in the code delete this row after reading the data.
如果它不起作用，请尝试在Header后面添加一个虚拟行，在该行中添加一个字符串值ex，xyz并在读取数据后在代码中删除该行。
