主题：从不同的表调用数据生成报表

Dim Book As New XLS.Book(ProjectPath & "Attachments\分项工程中期支付汇总表.xlsx")

Dim fl As String = ProjectPath & "Reports\分项工程中期支付汇总表.xlsx"

Dim filter As String = DataTables("中间计量表").loadfilter

filter = IIF(filter > ""," where " & filter,"")

book.AddDataTable("分项工程中期支付汇总表","xlgl","selec t 项目名称,施工合同段,章节,支付编号,细目名称,单位,sum(申报数量) as 申报数量,sum(申报数量 * 单价) as 申报金额 fro m {中间计量表} " & filter & " group by 项目名称,施工合同段,章节,支付编号,细目名称,单位")

Book.Build()

Book.Save(fl)

Dim Proc As New Process

Proc.File = fl

Proc.Start()

Dim Book As New XLS.Book(ProjectPath & "Attachments\分项工程中期支付汇总表.xlsx")

Dim fl As String = ProjectPath & "Reports\分项工程中期支付汇总表.xlsx"

Dim filter As String = DataTables("工程量清单").loadfilter

filter = IIF(filter > ""," where " & filter,"")

book.AddDataTable("分项工程中期支付汇总表","xlgl","selec t 项目名称,施工合同段,章节,支付编号,细目名称,单位,sum(合同数量) as 合同数量,sum(合同数量 * 单价) as 合同金额 fro m {工程量清单} " & filter & " group by 项目名称,施工合同段,章节,支付编号,细目名称,单位")

Book.Build()

Book.Save(fl)

Dim Proc As New Process

Proc.File = fl

Proc.Start()

报表模板：

此主题相关图片如下：66666.png

Dim Book As New XLS.Book(ProjectPath & "Attachments\分项工程中期支付汇总表.xlsx")

Dim fl As String = ProjectPath & "Reports\分项工程中期支付汇总表.xlsx"

Dim filter As String = DataTables("中间计量表").loadfilter

filter = IIF(filter > ""," where " & filter,"")

book.AddDataTable("分项工程中期支付汇总表","xlgl","selec t 项目名称,施工合同段,章节,支付编号,细目名称,单位,sum(申报数量) as 申报数量,sum(申报数量 * 单价) as 申报金额 fro m {中间计量表}, selec t 数量 as 合同数量,数量 * 单价 as 合同金额 fro m {工程量清单}  INNER JOIN {中间计量表}  ON {工程量清单}.项目名称 = {中间计量表}.项目名称 and {工程量清单}.施工合同段 = {中间计量表}.施工合同段 and {工程量清单}.章节 = {中间计量表}.章节 and {工程量清单}.支付编号= {中间计量表}.支付编号 " & filter & " group by 项目名称,施工合同段,章节,支付编号,细目名称,单位")

Book.Build()

Book.Save(fl)

Dim Proc As New Process

Proc.File = fl

Proc.Start()

此主题相关图片如下：7777.png

SELEC T 项目名称,施工合同段,章节,支付编号,细目名称,单位, 合同数量, 合同数量 * 单价, 0.0 as 申报数量, 0.0 as 申报数量 * 单价 FRO M {工程量清单} UNION ALL SELEC T 项目名称,施工合同段,章节,支付编号,细目名称,单位, 0, 0, 申报数量, 申报数量 * 单价  FRO M {中间计量表}