主题：[求助]交叉统计日期分组漏日期

1.模板改为:[01日],[02日]...[31日]

2.

Dim Filter As String = "[姓名]<>''"

Dim Y As Integer = Forms("选择月份").Controls("TextBox1").Value

Dim M As Integer = Forms("选择月份").Controls("TextBox2").Value

Dim SD As Date = New Date(Y,M,1)

Dim Days As Integer = Date.DaysInMonth(Y,M)

Dim ED As Date = New Date(Y,M,Days)

Filter = Filter & "And [日期] >= #" & SD & "# And [日期]<= #" & ED & "#"

Dim s1,s2,sql  As String

Dim Ls As New List(Of String)

Dim cmd As New SQLCommand

Dim dt As DataTable

sql = "select Right('0' & day(日期),2) as 日 From{计件表} where " &  Filter

cmd.CommandText = sql

dt = cmd.ExecuteReader()

Ls = dt.GetUniqueValues("", "日")

For Each s1 In Ls

    s2+ =",iif(Right('0' & day(日期),2)='" & s1 & "',[工价]*[数量] ,Null) as " & s1 & "日"

Next

sql= "Select 部门,姓名" & s2 & ",[工价]*[数量] as 合计 From{计件表} where " &  Filter

Dim g As New GroupTableBuilder("计件工资",sql)

g.Groups.AddDef("部门")

g.Totals.AddDef("合计")

g.Build 

DataTables("计件工资").Fill(sql, True)

 'Tables("计件工资")汇总及设置小数位自行设计

Dim Book As New XLS.Book(ProjectPath & "Attachments\计件工资模板.xls")

Dim fl As String = ProjectPath & "Reports\计件工资" & Y & "-" & M & ".xls"

Dim Sheet As XLS.Sheet = Book.Sheets(0)

Book.Build() '生成细节区

Book.Save(fl) '保存工作簿

Dim Proc As New Process '打开工作簿

Proc.File = fl

Proc.Start()

Forms("选择月份").Close