DataTables("开具piao据").DataRows.Clear
Dim f1 As String = "收购piao据\收购piao据报表.xls"
Dim Book As New XLS.Book(ProjectPath & "Attachments\fa piao报表.xls")
Book.Marks.Add("用户名",User.Name)
Book.Marks.Add("日期",vars("adate"))
Book.Marks.Add("单位名称",vars("gg"))
Dim Sheet1 As XLS.Sheet = Book.Sheets("报表")
For Each r1 As Row In Tables("收购记录")
If r1("收购凭证号") <> "" Then
Dim r2 As Row = Tables("开具piao据").AddNew
r2("开piao日期") = r1("日期")
r2("收购凭证号") = r1("收购凭证号")
End If
Next
For Each r1 As Row In Tables("作废收购凭证号")
If r1("开piao日期") = vars("adate") Then
Dim r2 As Row = Tables("开具piao据").AddNew
r2("开piao日期") = r1("开piao日期")
r2("收购凭证号") = r1("作废收购凭证号")
r2("作废") = True
End If
Next
Tables("开具piao据").sort() = "收购凭证号"
Dim a As Integer = 0
For i As Integer = 0 To Tables("开具piao据").Rows.Count-1
Dim r1 As Integer
Dim a1 As Integer
r1 = 1
a1 = 1
If Tables("开具piao据").Rows(i)("作废") = True Then
Sheet1(6+a1,r1+5-a1*5).Value = Tables("开具piao据").Rows(i)("收购凭证号")
r1 = r1 + 1
If r1 > 5*a1 Then
a1=a1+1
Sheet1.Rows.Insert(a1+6)
Dim Style As XLS.Style = Book.NewStyle() '定义新样式
Style.ForeColor = Color.black '设置样式的字体颜色
Sheet1.Rows(a+5).Height = 30 '设置行高
Book.DefaultFont = New Font("黑体",10,FontStyle.Bold) '设置默认字体
Style.AlignHorz = XLS.AlignHorzEnum.Center
Style.AlignVert = XLS.AlignVertEnum.Center
Style.BorderTop = XLS.LineStyleEnum.Thin
Style.BorderBottom = XLS.LineStyleEnum.Thin
Style.BorderLeft = XLS.LineStyleEnum.Thin
Style.BorderRight = XLS.LineStyleEnum.Thin
For c1 As Integer = 1 To 7
Sheet1(a1+6,c1).Style = Style
Next
End If
End If
If i = 0 Then
Sheet1(5+a,1).Value = Tables("开具piao据").Rows(i)("收购凭证号")
Else If i = Tables("开具piao据").Rows.Count-1 Then
Sheet1(5+a,2).Value = Tables("开具piao据").Rows(i)("收购凭证号")
Sheet1(5+a,3).Value = DataTables("开具piao据").Compute("count([收购凭证号])", "收购凭证号 >= " & Sheet1(5,1).Value & " and 收购凭证号 <= " & Sheet1(5,2).Value & " and 作废 = false")
Sheet1(5+a,4).Value = DataTables("开具piao据").Compute("count([收购凭证号])", "收购凭证号 >= " & Sheet1(5,1).Value & " and 收购凭证号 <= " & Sheet1(5,2).Value & " and 作废 = true")
Else If (Tables("开具piao据").Rows(i)("收购凭证号") + 1) <> Tables("开具piao据").Rows(i+1)("收购凭证号")
Sheet1(5+a,2).Value = Tables("开具piao据").Rows(i)("收购凭证号")
Sheet1(5+a,3).Value = DataTables("开具piao据").Compute("count([收购凭证号])", "收购凭证号 >= " & Sheet1(5,1).Value & " and 收购凭证号 <= " & Sheet1(5,2).Value & " and 作废 = false")
Sheet1(5+a,4).Value = DataTables("开具piao据").Compute("count([收购凭证号])", "收购凭证号 >= " & Sheet1(5,1).Value & " and 收购凭证号 <= " & Sheet1(5,2).Value & " and 作废 = true")
Sheet1.Rows.Insert(a+5)
Dim Style As XLS.Style = Book.NewStyle() '定义新样式
Style.ForeColor = Color.black '设置样式的字体颜色
Sheet1.Rows(a+5).Height = 30 '设置行高
Book.DefaultFont = New Font("黑体",10,FontStyle.Bold) '设置默认字体
Style.AlignHorz = XLS.AlignHorzEnum.Center
Style.AlignVert = XLS.AlignVertEnum.Center
Style.BorderTop = XLS.LineStyleEnum.Thin
Style.BorderBottom = XLS.LineStyleEnum.Thin
Style.BorderLeft = XLS.LineStyleEnum.Thin
Style.BorderRight = XLS.LineStyleEnum.Thin
For c1 As Integer = 1 To 7
Sheet1(a+5,c1).Style = Style
Next
a = a + 1
Sheet1(5+a,1).Value = Tables("开具piao据").Rows(i+1)("收购凭证号")
End If
Next
Book.Build() '生成细节区
Book.Save(f1) '保存工作簿
Dim Proc As New Process '打开工作簿
Proc.File = f1
Proc.Start()