以文本方式查看主题 - Foxtable(狐表) (http://foxtable.com/bbs/index.asp) -- 专家坐堂 (http://foxtable.com/bbs/list.asp?boardid=2) ---- excel取指定值填入新增行 (http://foxtable.com/bbs/dispbbs.asp?boardid=2&id=113612) |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
-- 作者:zoyong -- 发布时间:2018/1/19 14:11:00 -- excel取指定值填入新增行
上表导入表后,excel表取定值A4数据填充到新增行的批次号列上,E4数据填充到新增行的日期列上 现在下面的代码可以导入,但是没有导入批次号和日期 求大神帮忙修改一下代码 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_批量打款民泰").StopRedraw() \'注意以下数组中列名称的顺序,必须和Excel表中的列顺序一致 Dim nms() As String = {"序号","收款人账号","收款人姓名","接收行行名","交易金额","转账方式","交易结果","备注"} \'注意下面的循环变量从6开始,而不是从0开始,因为Excel表的第一行是标题 For n As Integer = 5 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 bh7 As String = sheet(n,7).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 If bh2 > "" Then filter &= " and 收款人姓名 = \'" & bh2 & "\'" Else filter &= " And 收款人姓名 Is null" End If If bh3 > "" Then filter &= " and 接收行行名 = \'" & bh3 & "\'" Else filter &= " And 接收行行名 Is null" End If If bh4 > "" Then filter &= " and 交易金额 = \'" & bh4 & "\'" Else filter &= " And 交易金额 Is null" End If If bh5 > "" Then filter &= " and 转账方式 = \'" & bh5 & "\'" Else filter &= " And 转账方式 Is null" End If If bh6 > "" Then filter &= " and 交易结果 = \'" & bh6 & "\'" Else filter &= " And 交易结果 Is null" End If If bh7 > "" Then filter &= " and 备注 = \'" & bh7 & "\'" Else filter &= " And 备注 Is null" End If Dim dr As DataRow = DataTables("cw_批量打款民泰").Find(filter) If dr Is Nothing Then \'如果不存在同编号的订单 dr = DataTables("cw_批量打款民泰").AddNew() End If For m As Integer = 0 To nms.Length - 1 dr(nms(m)) = Sheet(n,m).Value Next Next Tables("cw_批量打款民泰").ResumeRedraw() CurrentTable.Save Next End If |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
-- 作者:有点甜 -- 发布时间:2018/1/19 14:24:00 -- 加上红色代码
For m As Integer = 0 To nms.Length - 1
dr(nms(m)) = Sheet(n,m).Value
Next
dr("批次号") = sheet(0, 3).Text dr("日期") = sheet(4, 3).Text [此贴子已经被作者于2018/1/19 14:24:50编辑过]
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
-- 作者:zoyong -- 发布时间:2018/1/19 14:40:00 -- 还有一事请教 跨年度查询会出错,同一年度没有问题 .NET Framework 版本:2.0.50727.8825 Foxtable 版本:2017.12.31.1 错误所在事件:窗口,民泰流水表,条件加载,SelectedIndexChanged 详细错误信息: 年、月和日参数描述无法表示的 DateTime。 代码 ElseIf e.Form.Controls("条件加载").text = "上一月" Then Dim y As Integer = Date.Today.Year Dim m As Integer = Date.Today.Month Dim d As Integer = Date.Today.day If m>=2 Dim dt1 As New Date(y, m-1, 1) Dim dt2 As New Date(y, m-1, Date.DaysInMonth(y, m-1)) \'获取本月的最后一天 Dim Filter As String Filter = "账务日期 >= \'" & dt1 & "\' And 账务日期 <= \'" & dt2 & "\'" DataTables("cw_lsbzzs").LoadFilter = Filter DataTables("cw_lsbzzs").Load() ElseIf m=1 Then Dim dt1 As New Date(y, m-1, 1) Dim dt2 As New Date(y-1, m+11, Date.DaysInMonth(y-1, m+11)) \'获取本月的最后一天 Dim Filter As String Filter = "账务日期 >= \'" & dt1 & "\' And 账务日期 <= \'" & dt2 & "\'" DataTables("cw_lsbzzs").LoadFilter = Filter DataTables("cw_lsbzzs").Load() End If |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
-- 作者:有点甜 -- 发布时间:2018/1/19 15:10:00 -- 上一个月,应该这样处理
ElseIf e.Form.Controls("条件加载").text = "上一月" Then |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
-- 作者:zoyong -- 发布时间:2018/2/9 15:49: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_批量打款民泰").StopRedraw() \'注意以下数组中列名称的顺序,必须和Excel表中的列顺序一致 Dim nms() As String = {"序号","收款人账号","收款人姓名","接收行行名","交易金额","转账方式","交易结果","备注"} \'注意下面的循环变量从6开始,而不是从0开始,因为Excel表的第一行是标题 For n As Integer = 5 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 bh7 As String = sheet(n,7).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 If bh2 > "" Then filter &= " and 收款人姓名 = \'" & bh2 & "\'" Else filter &= " And 收款人姓名 Is null" End If If bh3 > "" Then filter &= " and 接收行行名 = \'" & bh3 & "\'" Else filter &= " And 接收行行名 Is null" End If If bh4 > "" Then filter &= " and 交易金额 = \'" & bh4 & "\'" Else filter &= " And 交易金额 Is null" End If If bh5 > "" Then filter &= " and 转账方式 = \'" & bh5 & "\'" Else filter &= " And 转账方式 Is null" End If If bh6 > "" Then filter &= " and 交易结果 = \'" & bh6 & "\'" Else filter &= " And 交易结果 Is null" End If If bh7 > "" Then filter &= " and 备注 = \'" & bh7 & "\'" Else filter &= " And 备注 Is null" End If Dim dr As DataRow = DataTables("cw_批量打款民泰").Find(filter) If dr Is Nothing Then \'如果不存在同编号的订单 dr = DataTables("cw_批量打款民泰").AddNew() End If For m As Integer = 0 To nms.Length - 1 dr(nms(m)) = Sheet(n,m).Value Next dr("批次号") = sheet(0,3).Text \'从excel表中取批定单元格数据填入新增行 dr("交易日期") = sheet(4,3).Text Next Tables("cw_批量打款民泰").ResumeRedraw() CurrentTable.Save MessageBox.Show("导入成功!","提示",MessageBoxButtons.OK,MessageBoxIcon.Warning) Next End If 加了红色代码导入会错误 没有红色能够正常导入 另想在 MessageBox.Show("导入成功!","提示",MessageBoxButtons.OK,MessageBoxIcon.Warning) 中加入导入的条数提示
[此贴子已经被作者于2018/2/9 15:51:08编辑过]
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
-- 作者:有点甜 -- 发布时间:2018/2/9 16:06:00 -- 1、红色代码包什么错?output.show(sheet(4,3).text)是否你需要的值。
2、导入条数,你用变量记录。
dim count as integer = 0 \'...... count += 1 \'...... msgbox(count) |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
-- 作者:zoyong -- 发布时间:2018/2/9 17:05:00 -- .NET Framework 版本:2.0.50727.8825 Foxtable 版本:2017.12.31.1 错误所在事件:窗口,批量打款民泰,导入批量打款数据,Click 详细错误信息: 从字符串“接收行行名”到类型“Date”的转换无效。 |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
-- 作者:有点甜 -- 发布时间:2018/2/9 17:07:00 -- 说明你 sheet(4,3).text 的值不正确。算好坐标位置,可能是 3,2 (从0开始算) |