从Excel 2003导入数据到dataTable [英] import data from excel 2003 to dataTable
本文介绍了从Excel 2003导入数据到dataTable的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在使用以下代码将excel工作表导入到datagridview:
I am importing excel worksheets to a datagridview using the following code:
Private Sub Browsimportbtn_Click(sender As Object, e As EventArgs) Handles Browsimportbtn.Click
Dim textpath As String
Dim textpath1 As String
Dim opf As New OpenFileDialog
If opf.ShowDialog = 1 Then
textpath = opf.FileName
textpath1 = opf.SafeFileName
textpath1 = textpath1.Remove(textpath1.Length -4,4)
Dim cnexcell As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & textpath & "; Extended Properties = ""Excel 12.0 Xml;HDR=YES"";")
Dim cmdE As New OleDbCommand("SELECT * FROM Feuil1", cnexcell)
Try
Dim daoledb As New OleDbDataAdapter
Dim dset As New DataSet
daoledb.SelectCommand = cmdE
daoledb.Fill(dset, "Feuil1")
DGVmodele.DataSource = dset.Tables("Feuil1")
Catch ex As Exception
MsgBox(ex.Message)
End Try
End If
End Sub
上面的代码与<$ c $配合良好c> .XLSX 文件(2007年,2010年办公室...),但没有使用 .XLS
,我不知道问题出在哪里。
The code above is working well with .XLSX
files (office 2007,2010...) but do not with .XLS
and i don't know where is the issue.
有什么建议吗?
推荐答案
问题是
您要在连接字符串中指定 Excel 12
作为版本,与2007年或更高版本相关。尝试使用此函数来构建连接字符串
You are specifying the Excel 12
as a version in the connection string which is related to 2007 or higher. Try using this function to build the connectionstring
Public Function BuildConnectionString(Byval m_strExcelPath as String) As String
If m_strExcelPath.Substring(m_strExcelPath.LastIndexOf(".")).ToLower = ".xlsx" Then
Return "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & m_strExcelPath & ";Excel 12.0;HDR=YES;IMEX=1"
Else
Return "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & m_strExcelPath & ";Excel 8.0;HDR=YES;IMEX=1"
End If
End Function
这篇关于从Excel 2003导入数据到dataTable的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文