| 以文本方式查看主题 - Foxtable(狐表) (http://foxtable.net/bbs/index.asp) -- 专家坐堂 (http://foxtable.net/bbs/list.asp?boardid=2) ---- [求助]列名有差异时怎么导入excel数据 (http://foxtable.net/bbs/dispbbs.asp?boardid=2&id=114740) |
| -- 作者：hrlong -- 发布时间：2018/2/19 22:59:00 -- [求助]列名有差异时怎么导入excel数据 我现在通过下面的代码导入EXCEL文件 但是数据库表格和原excel表格的2列列名有差异，所以这两列导入不进去，请问下面的代码该怎么修改才能导入这两列数据？ 数据库表格 ： 收件人姓名 和 收件人电话 原excel文件： 收件人姓名* 和 收件人电话/手机 ---------------------------------------------------------------- Dim dlg As New OpenFileDialog dlg.Filter = "Excel文件|*.xls;*.xlsx" If dlg.ShowDialog =DialogResult.OK Then Dim t As Table = Tables("订单") t.ResumeRedraw() t.StopRedraw() Dim App As New MSExcel.Application try Dim Wb As MSExcel.WorkBook = App.WorkBooks.Open(dlg.FileName) Dim Ws As MSExcel.WorkSheet = Wb.WorkSheets(1) Dim Rg As MSExcel.Range = Ws.UsedRange Dim arg = rg.value For n As Integer = 2 To rg.Rows.Count Dim r As DataRow = t.DataTable.Find("商品编码 = #" & arg(n, 3) & "# and 系统单号 = \'" & arg(n, 10) & "\' and 商品规格 = \'" & arg(n, 11) & "\'") If r Is Nothing Then r = t.DataTable.AddNew() For i As Integer = 1 To rg.Columns.count Dim cname As String = arg(1,i) If cname > "" AndAlso t.Cols.Contains(cname) Then If t.Cols(cname).IsDate AndAlso arg(n,i).Gettype.name = "double" Then r(cname) = DateTime.FromOADate(arg(n,i)) Else r(cname) = arg(n, i) End If End If Next Next catch ex As exception msgbox(ex.message) MessageBox.Show("导入失败!","恭喜!") finally t.ResumeRedraw() app.quit End try End If ------------------------------------------------------------------------- |
| -- 作者：有点甜 -- 发布时间：2018/2/21 18:06:00 -- 可以单独判断，如
Dim cname As String = arg(1,i)
If cname > "" AndAlso t.Cols.Contains(cname) Then
If t.Cols(cname).IsDate AndAlso arg(n,i).Gettype.name = "double" Then
r(cname) = DateTime.FromOADate(arg(n,i))
Else
r(cname) = arg(n, i)
End If
ElseIf cname = "收件人姓名*"
r("收件人姓名") = arg(n, i)
End If
|