| 以文本方式查看主题 - Foxtable(狐表) (http://foxtable.com/bbs/index.asp) -- 专家坐堂 (http://foxtable.com/bbs/list.asp?boardid=2) ---- 求助自定义函数 (http://foxtable.com/bbs/dispbbs.asp?boardid=2&id=150513) |
| -- 作者：czy66ds -- 发布时间：2020/6/1 8:36:00 -- 求助自定义函数 关于内部函数至今还是似懂非懂。 比如下一段代码是将excel差旅费审批记录表导入到狐表“差旅费记录表1”，在窗口表T1进行操作。 希望将此代码还用于其它类似Excel表的导入，所以想将这段代码写成内部函数。 代码中红色部分因不同Excel表而异，如何将其表达为参数就懵懂了，故求助。 Dim cm As WinForm.ComboBox = e.Form.Controls("ComboBox2") Dim T1 As Table = e.Form.Controls("Table1").Table Dim cmd As new SQLCommand cmd.C Dim r As Row If cm.Value = "差旅费" Then
Dim i As Integer = 0
Dim dlg As New OpenFileDialog \'定义一个新的OpenFileDialog
dlg.Filter= "Excel2007文件|*.xlsx"
If dlg.ShowDialog = DialogResult.Ok Then Dim Book As New XLS.Book(dlg.FileName) Dim Sheet As XLS.Sheet = Book.Sheets(0) For n As Integer = 1 To Sheet.Rows.Count
-1 If sheet(n,0).Text <> ""
Then If sheet(n,25).Text.trim()
<> "已通过" Then MessageBox.Show("差旅费列表中存在没有审批通过的记录,请处理!") \'Exit For Return End If Else Exit For End If Next Dim
nms() As String = {"审批编号","提交时间","申请人","申请人部门","申请人账号","部门","出差人员","出差任务","出发时间","出发地","到达时间","到达地","当日返回","车费","油补","过道费","餐补","宿费","其他费用","费用小计","详情说明","总费用小计","预借金额","预借金额大写","报销总额","当前审批状态","审批人","抄送人","审批流程","已打印","已领取","已报销","备注"} For n As Integer = 1 To
Sheet.Rows.Count -1 Dim spbh As String = sheet(n,0).Text If spbh.Length < 2 Then Exit For End If cmd.CommandText = "select
count(*) f rom{差旅费记录表1} where 审批编号= \'" &
spbh & "\'" If cmd.ExecuteScalar = 0 Then r = T1.AddNew For m As Integer = 0 To nms.Length
- 1 If m = 29 Or m = 30 Or m = 31 Then \'处理逻辑列 r(nms(m)) = False ElseIf 13<= m <=19 Or
m = 21 Or m = 22 Or m = 24 Then \'处理诸费用列 r(nms(m)) = Sheet(n,m).Value.split("元")(0) Else r(nms(m)) = Sheet(n,m).Value End If Next i = i + 1 End
If Next End If MessageBox.Show("审批表添加完毕,共添加" &
i & "行") End If |
| -- 作者：有点蓝 -- 发布时间：2020/6/1 9:04:00 -- 函数 Dim T1 As Table = args(0) Dim nms() As string = args(1) Dim tn As string = args(2) Dim cmd As new SQLCommand cmd.C Dim r As Row Dim i As Integer = 0 Dim dlg As New OpenFileDialog \'定义一个新的OpenFileDialog dlg.Filter= "Excel2007文件|*.xlsx" If dlg.ShowDialog = DialogResult.Ok Then Dim Book As New XLS.Book(dlg.FileName) Dim Sheet As XLS.Sheet = Book.Sheets(0) For n As Integer = 1 To Sheet.Rows.Count -1 If sheet(n,0).Text <> "" Then If sheet(n,25).Text.trim() <> "已通过" Then MessageBox.Show("差旅费列表中存在没有审批通过的记录,请处理!") \'Exit For Return -1 End If Else Exit For End If Next For n As Integer = 1 To Sheet.Rows.Count -1 Dim spbh As String = sheet(n,0).Text If spbh.Length < 2 Then Exit For End If cmd.CommandText = "select count(*) f rom {" & tn & "} where 审批编号= \'" & spbh & "\'" If cmd.ExecuteScalar = 0 Then r = T1.AddNew For m As Integer = 0 To nms.Length - 1 If r(nms(m)).IsBoolean Then \'处理逻辑列 r(nms(m)) = False ElseIf r(nms(m)).IsNumeric andalso Sheet(n,m).Value like "*元*" Then \'处理诸费用列 r(nms(m)) = Sheet(n,m).Value.split("元")(0) Else r(nms(m)) = Sheet(n,m).Value End If Next i = i + 1 End If Next End If MessageBox.Show("审批表添加完毕,共添加" & i & "行") End If 调用 Dim cm As WinForm.ComboBox = e.Form.Controls("ComboBox2") Dim T1 As Table = e.Form.Controls("Table1").Table If cm.Value = "差旅费" Then Dim nms() As String = {"审批编号","提交时间","申请人","申请人部门","申请人账号","部门","出差人员","出差任务","出发时间","出发地","到达时间","到达地","当日返回","车费","油补","过道费","餐补","宿费","其他费用","费用小计","详情说明","总费用小计","预借金额","预借金额大写","报销总额","当前审批状态","审批人","抄送人","审批流程","已打印","已领取","已报销","备注"} Dim tn As String = "差旅费记录表1" Functions.Execute("xx函数",T1,nms,tn ) End If
|
| -- 作者：czy66ds -- 发布时间：2020/6/1 10:29:00 -- 收到回复代码，测试通过。回复太快了。 收到回复代码，如获珍宝，爱不释手，一定仔细玩味，争取更上一层楼。真心感恩狐表，使我这个菜鸟能逐渐上手，开发出解决企业实际需求的应用项目。 真心感谢有点蓝，不厌其烦地使出真本事帮助菜鸟们。没有你们的帮助，不知道还要在黑暗中摸索多久。
|