专家坐堂-导入EXCEL数据Foxtable(狐表) - 新一代数据库软件，完美融合Access、Foxpro、Excel、vb.net之优势，人人都能掌握的快速软件开发工具!

以文本方式查看主题

-  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=106983)

--  作者：zoyong
--  发布时间：2017/9/19 16:03:00
--  导入EXCEL数据

Dim dlg As New OpenFileDialog
dlg.MultiSelect = True
dlg.Filter= "Excel文件|*.xls;*.xlsx"
If dlg.ShowDialog = DialogResult.OK Then
For Each file As String In dlg.FileNames
Dim Book As New XLS.Book(file)
Dim Sheet As XLS.Sheet = Book.Sheets(0)
Tables("cw_yhlsb").StopRedraw()
\'注意以下数组中列名称的顺序,必须和Excel表中的列顺序一致
Dim nms() As String = {"交易日期","交易网点","对方账号","收入","支出","账户余额","摘要"}
\'注意下面的循环变量从2开始,而不是从0开始,因为Excel表的第一行是标题
For n As Integer = 1 To Sheet.Rows.Count -1
Dim bh As String = sheet(n,0).Text
Dim dr As DataRow = DataTables("cw_yhlsb").Find("交易日期 = \'" & bh & "\'")

If dr Is Nothing Then \'如果不存在同编号的订单
dr = DataTables("cw_yhlsb").AddNew()
End If
For m As Integer = 0 To nms.Length - 1
dr(nms(m)) = Sheet(n,m).Value
Next
Next
Tables("cw_yhlsb").ResumeRedraw()
Next
End If

代码可用，但是导入的数据不全
以 "交易日期","交易网点","对方账号","收入","支出","账户余额","摘要" 为导入，导入的数据与表格式相同

--  作者：有点甜
--  发布时间：2017/9/19 16:12:00
--

你说的数据不全，指什么？

你根据交易日期进行查询比较的，同一个交易日期，只会导入一行记录的。

--  作者：zoyong
--  发布时间：2017/9/19 16:23:00
--
我想导入全部数据，现在只有一条记录

--  作者：zoyong
--  发布时间：2017/9/19 17:40:00
--

For n As Integer = 1 To Sheet.Rows.Count -1
Dim bh1 As String = sheet(n,1).Text
Dim bh2 As String = sheet(n,2).Text
Dim bh3 As String = sheet(n,3).Text
Dim bh4 As String = sheet(n,4).Text
Dim bh5 As String = sheet(n,5).Text
Dim bh6 As String = sheet(n,6).Text
Dim bh0 As String = sheet(n,0).Text
Dim dr As DataRow = DataTables("cw_yhlsb").Find("交易日期 = \'" & bh0 & "\' and 交易网点 = \'" & bh2 & "\'and 对方账号 = \'" & bh3 & "\'" )


搞定了

--  作者：zoyong
--  发布时间：2017/9/19 17:46:00
--

Dim dlg As New OpenFileDialog
dlg.MultiSelect = True
dlg.Filter= "Excel文件|*.xls;*.xlsx"
If dlg.ShowDialog = DialogResult.OK Then
For Each file As String In dlg.FileNames
Dim Book As New XLS.Book(file)
Dim Sheet As XLS.Sheet = Book.Sheets(0)
Tables("cw_yhlsb").StopRedraw()
\'注意以下数组中列名称的顺序,必须和Excel表中的列顺序一致
Dim nms() As String = {"交易日期","交易网点","对方账号","收入","支出","账户余额","摘要"}
\'注意下面的循环变量从2开始,而不是从0开始,因为Excel表的第一行是标题
For n As Integer = 1 To Sheet.Rows.Count -1
Dim bh0 As String = sheet(n,0).Text
Dim bh1 As String = sheet(n,1).Text
Dim bh2 As String = sheet(n,2).Text
Dim bh3 As String = sheet(n,3).Text
Dim bh4 As String = sheet(n,4).Text
Dim bh5 As String = sheet(n,5).Text
Dim bh6 As String = sheet(n,6).Text
Dim dr As DataRow = DataTables("cw_yhlsb").Find("交易日期 = \'" & bh0 & "\' and 交易网点 = \'" & bh2 & "\'and 对方账号 = \'" & bh3 & "\'and 收入 = \'" & bh4 & "\'and 支出 = \'" & bh4 & "\'and 账户余额 = \'" & bh5 & "\'and 摘要 = \'" & bh6 & "\'" )
If dr Is Nothing Then \'如果不存在同编号的订单
dr = DataTables("cw_yhlsb").AddNew()
End If
For m As Integer = 0 To nms.Length - 1
dr(nms(m)) = Sheet(n,m).Value
Next
Next
\'-----合计导入--------------
Dim cnt As Integer
cnt = DataTables("cw_yhlsb").SQLReplaceFor("交易网点", True)
MessageBox.Show("合计导入了 " & cnt & " 条记录!","提示")

Tables("cw_yhlsb").ResumeRedraw()
Next
End If

上面代码可用了现在问题是怎样跳过重复值

--  作者：有点甜
--  发布时间：2017/9/19 18:49:00
--

这样写查询条件

Dim bh0 As String = sheet(n,0).Text
Dim bh1 As String = sheet(n,1).Text
Dim bh2 As String = sheet(n,2).Text
Dim bh3 As String = sheet(n,3).Text
Dim bh4 As String = sheet(n,4).Text
Dim bh5 As String = sheet(n,5).Text
Dim bh6 As String = sheet(n,6).Text
Dim filter As String = "1=1"
If bh0 > "" Then
    filter &= " and 交易日期 = \'" & bh0 & "\'"
Else
    filter &= " And 交易日期 Is null"
End If
If bh1 > "" Then
    filter &= " and 交易网点 = \'" & bh1 & "\'”
else
    filter &= " and 交易网点 is null”
end if
Dim dr As DataRow = DataTables("cw_yhlsb").Find(filter)

--  作者：zoyong
--  发布时间：2017/9/30 17:20:00
--

.NET Framework 版本：2.0.50727.8689
Foxtable 版本：2017.8.19.1
错误所在事件：窗口,流水表,导入,Click
详细错误信息：
无法在 System.DateTime 和 System.String 上执行“=”操作。

此主题相关图片如下：搜狗截图20170930171853.png
导入的日期格式 15:26:54



[此贴子已经被作者于2017/9/30 17:23:12编辑过]

--  作者：有点蓝
--  发布时间：2017/9/30 17:33:00
--
filter &= " and 交易日期 = #" & bh0 & "#"

--  作者：zoyong
--  发布时间：2018/1/2 15:08:00
--

Dim dlg As New OpenFileDialog
dlg.MultiSelect = True
dlg.Filter= "Excel文件|*.xls;*.xlsx"
If dlg.ShowDialog = DialogResult.OK Then
For Each file As String In dlg.FileNames
Dim Book As New XLS.Book(file)
Dim Sheet As XLS.Sheet = Book.Sheets(0)
Tables("cw_lsbjh").StopRedraw()
\'注意以下数组中列名称的顺序,必须和Excel表中的列顺序一致
Dim nms() As String = {"记账日","交易日期","交易时间","交易地点","支出","收入","账户余额","对方账号","对方户名","币种","摘要"}
\'注意下面的循环变量从7开始,而不是从0开始,因为Excel表的第一行是标题
For n As Integer = 6 To Sheet.Rows.Count -1
Dim bh0 As String = sheet(n,0).Text
Dim bh1 As String = sheet(n,1).Text
Dim filter As String = "1=1"
If bh0 > "" Then
filter &= " and 交易时间 = \'" & bh0 & "\'"
Else
filter &= " And 交易时间 Is null"
End If
If bh1 > "" Then
filter &= " and 交易日期 = \'" & bh1 & "\'"
Else
filter &= " And 交易日期 Is null"
End If

Dim dr As DataRow = DataTables("cw_lsbjh").Find(filter)
If dr Is Nothing Then \'如果不存在同编号的订单
dr = DataTables("cw_lsbjh").AddNew()
End If
For m As Integer = 0 To nms.Length - 1
dr(nms(m)) = Sheet(n,m).Value
Next
Next

Tables("cw_lsbjh").ResumeRedraw()
CurrentTable.Save
Next
\'\'-----合计导入--------------
\'Dim cnt As Integer
\'cnt = DataTables("cw_lsbjh").SQLReplaceFor("登记号")
\'MessageBox.Show("合计导入了 " & cnt & " 条记录!","提示")
End If

代码可用，但是有问题
1.重复导入的数据不会覆盖
2.最后一行的内容不想导入：以上数据仅供参考|具体内容请以柜台为准
请老师帮忙修改一下

用这语句代码无效
Dim dr As DataRow = DataTables("cw_lsbjh").Find("交易日期 = \'" & bh1 & "\' and 交易时间 = \'" & bh0 & "\'“）

[此贴子已经被作者于2018/1/2 15:15:44编辑过]

--  作者：有点甜
--  发布时间：2018/1/2 15:20:00
--

试试

For n As Integer = 6 To Sheet.Rows.Count -2

Dim bh0 As String = sheet(n,0).Text

Dim bh1 As String = sheet(n,1).Text

Dim filter As String = "1=1"

If bh0 > "" Then

filter &= " and 交易时间 = #" & bh0 & "#"

Else

filter &= " And 交易时间 Is null"

End If

If bh1 > "" Then

filter &= " and 交易日期 = #" & bh1 & "#"

Else

filter &= " And 交易日期 Is null"

End If

  msgbox(filter)

Dim dr As DataRow = DataTables("cw_lsbjh").Find(filter)

If dr Is Nothing Then \'如果不存在同编号的订单

dr = DataTables("cw_lsbjh").AddNew()

End If

For m As Integer = 0 To nms.Length - 1

dr(nms(m)) = Sheet(n,m).Value

Next

Next



Tables("cw_lsbjh").ResumeRedraw()

CurrentTable.Save

Next