:EXCEL与ACCESS之间数据导入与导出的有关问题

求助:EXCEL与ACCESS之间数据导入与导出的问题
在ACCESS中有一个表TEST,有165个字段,需要导出成EXCEL,修改部分数据后,再导回到数据库的原表中,请问大家用VB.NET怎么实现.附上我的代码:

VB.NET code

Dim conn As New ADODB.Connection        Dim successout As Boolean = False        LogChance()        If LogFileName <> "" Then            Dim ExcelPath As String = LogFileName            If ExtenName = ".xls" Then                conn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & ExcelPath & "';Extended Properties=Excel 8.0;")            End If            Dim strSQL As String            strSQL = "Insert Into [;database=" & DataPath & "]." & TableName & " Select * FROM [" & TableName & "$]"            Try                conn.Execute(strSQL)                successout = True                MsgBox("数据导入完毕", MsgBoxStyle.Information, "提示信息")            Catch ex As Exception                MsgBox(ex.Message, MsgBoxStyle.Exclamation, "提示信息")                successout = False                conn = Nothing                Exit Sub            Finally                conn = Nothing            End Try        Else            successout = False            MsgBox("请选择文件", MsgBoxStyle.Exclamation, "提示信息")            conn = Nothing            Exit Sub        End If

存在的一个问题是,在EXCEL中修改了数据以后.传回数据库中,个别数据进不去,我觉得可能是数据格式的问题,如果要是把每个字段都列出来的话,太多了,请教大家有没有什么好的方法.

[解决办法]
EXCEL，是有行数限制的，以前的好象是65536行，EXCEL2007可以达到行1048576
打开EXCEL，二维数组赋值到EXCEL
修改DATASET再写回
Private Function XlsToDataTable(strpath As [String]) As DataTable
Dim strConn As String
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Convert.ToString(strpath) & ";" & "Extended Properties=Excel 8.0;"
Dim conn As New OleDbConnection(strConn)
Dim myCommand As New OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn)
Dim dt As New DataTable()
myCommand.Fill(dt)
Return dt
End Function

查看更多 下一篇