在 .NET 中读取 Excel - 某些列被读为 null
我正在尝试从 Excel 中读取一些数据：
If (FileUpload1.PostedFile.ContentType = "application/vnd.ms-excel") Then
Dim filename As String = Path.GetFileName(FileUpload1.FileName)
'Session("userid") & "-" & Date.Now()
filepath = "\excel\" & Session("userid") & "_" & Now.Date().ToString("Mdy") & "_" & filename
FileUpload1.SaveAs(Server.MapPath("~/") & filepath)
ReadExcel(filepath)
Else
StatusLabel.Text = "Only Excel file types are accepted"
End If
一切似乎都很好，除了其中一列有时读为 NULL 时。似乎如果它是不同类型的话就会发生这种情况。 我不知道那是什么。请有人帮助我......
Sub ReadExcel(ByVal filepath As String)
Dim MyConnection As System.Data.OleDb.OleDbConnection
Dim DtSet As System.Data.DataSet
Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
MyConnection = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & Server.MapPath("~/") & filepath & "';Extended Properties=Excel 8.0;")
DtSet = New System.Data.DataSet
Try
MyCommand.Fill(DtSet)
'gwResults.DataSource = DtSet.Tables(0)
LoopSources(DtSet)
'gwResults.DataBind()
MyConnection.Close()
Catch ex As Exception
StatusLabel.Text = "Import Excel status: The file could not be loaded. The following error occured: <br>" + ex.Message
End Try
End Sub
i am trying to read in some data from an excel:
If (FileUpload1.PostedFile.ContentType = "application/vnd.ms-excel") Then
Dim filename As String = Path.GetFileName(FileUpload1.FileName)
'Session("userid") & "-" & Date.Now()
filepath = "\excel\" & Session("userid") & "_" & Now.Date().ToString("Mdy") & "_" & filename
FileUpload1.SaveAs(Server.MapPath("~/") & filepath)
ReadExcel(filepath)
Else
StatusLabel.Text = "Only Excel file types are accepted"
End If
everything seems to be fine, except when one of the columns sometimes reads in as NULL. seems like that happens if it's of a different type.
i can't figure out what it is. someone help me please....
Sub ReadExcel(ByVal filepath As String)
Dim MyConnection As System.Data.OleDb.OleDbConnection
Dim DtSet As System.Data.DataSet
Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
MyConnection = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & Server.MapPath("~/") & filepath & "';Extended Properties=Excel 8.0;")
DtSet = New System.Data.DataSet
Try
MyCommand.Fill(DtSet)
'gwResults.DataSource = DtSet.Tables(0)
LoopSources(DtSet)
'gwResults.DataBind()
MyConnection.Close()
Catch ex As Exception
StatusLabel.Text = "Import Excel status: The file could not be loaded. The following error occured: <br>" + ex.Message
End Try
End Sub
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您需要在连接字符串中包含
Extended Properties="Excel 8.0;IMEX=1;"。 Excel 尝试通过读取前几行来确定列的数据类型。一旦它认为自己知道数据类型，任何不符合该数据类型的内容都将被强制为 NULL。很烦人。IMEX=1告诉它以混合方式读取您的数据，并且应该可以解决您的问题。如果您有标题行，还应该考虑包含HDR=YES。请参阅此链接：http://www.connectionstrings.com/excel 了解更多信息。You need to include
Extended Properties="Excel 8.0;IMEX=1;"in your connection string. Excel tries to determine the data type of your columns by reading the first few rows. Once it thinks it knows the data type, anything that doesn't conform to that is coerced to NULL. Very annoying.IMEX=1tells it to read your data as intermixed and should resolve your problem. If you have a header row, you should also consider includingHDR=YES. See this link: http://www.connectionstrings.com/excel for more info.可能列的类型确定不正确。默认情况下，Jet 提供程序读取前 8 行来确定列的类型。如果这 8 行包含错误的内容，您就会遇到问题。
这可以通过在注册表中设置 TypeGuessRows 设置来更改。
Probably the type of column is determined incorrectly. By default Jet provider reads 8 first rows to determine the type of column. And if these 8 lines contain something wrong you'll get a problem.
This can be changed by setting TypeGuessRows setting in registry.