余额报表按钮
Dim sql As String = "select 业务员,客户名称 from (select 业务员,客户名称,sum(iif(数量 is null,0,数量)*iif(单价 is null,0,单价))- sum(iif(已收账款 is null,0,已收账款)) as 余额 from {客户往来明细表} where " & IIF(Tables("客户往来明细表").Filter > "",Tables("客户往来明细表").Filter,"1=1") & " group by 业务员,客户名称) as a where 余额 = 0"
Dim dt As DataTable
Dim cmd As new SQLCommand
cmd.CommandText = sql
dt = cmd.ExecuteReader()
For Each dr As DataRow In dt.DataRows
DataTables("客户往来明细表").RemoveFor(" 业务员 = '" & dr("业务员") & "' and 客户名称 = '" & dr("客户名称") & "' ")
Next
Dim Book As New XLS.Book(ProjectPath & "Attachments\客户余额表.xls") '打开模板
Dim fl As String = ProjectPath & "Reports\客户余额表.xls"
Dim Sheet As XLS.Sheet = Book.Sheets(0)
Sheet(2,2).Value = "<客户往来明细表>" '写入表名
Book.Build() '生成报表
Book.Save(fl)
Dim Proc As New Process
Proc.File = fl
Proc.Start()
DataTables("客户往来明细表").LoadFilter = ""
DataTables("客户往来明细表").Load
e.Form.Controls("Button1").PerformClick