1 100801新版后系统可以自动为分组的分页来补空行了,所以代码也不同了:更新如下,请自己放入按钮中吧
2 应用了e-png老弟的强制换行代码, 解决了分页小计在分组合计行的后面的问题(见三楼图那样的尴尬样子)
Dim cp As String = DataTables("订单").GetComboListString("产品")
Dim Book As New XLS.Book(ProjectPath & "Attachments\分组统计1.xls")
Dim Sheet As XLS.Sheet
Sheet = Book.Sheets("sheet1")
Book.Build()
Book.Save(ProjectPath & "reports\分组统计1.xls")
'************************************************************************
Dim x,xc,xy,xh As Integer
Dim Book1 As New XLS.Book(ProjectPath & "reports\分组统计1.xls")
Dim Sheet1 As XLS.Sheet
Sheet1 = Book1.Sheets("sheet1")
For Each c As String In cp.split("|")
Dim drs As List(Of DataRow)
drs = DataTables("订单").Select("[产品] = '" & c & "'")
Dim ys1 As Integer = drs.count'************************100801版本后新增::::::::below:
If ys1 Mod 10 > 0
ys1 = ys1 + 10-(ys1 Mod 10)
End If
'************************100801版本后新增::::::above:::
Dim yn As Integer = 10
For y As Integer = 1 To ys1/10
x = x + 3
sheet1(y*yn+x+xc+xh-12+y-1,6).value = c & "第:" & y & " 页"
sheet1(y*yn+x+xc+xh-12+y-1,7).value = c & "共:" & ys1/10 & " 页"
Next
xc = xc +ys1
xh = xh +1 +ys1/10
Next
Book1.Save(ProjectPath & "reports\分组统计1.xls")
'************************100801版本后新增::::below:::强制换行:::::::
Dim App As New MSExcel.Application
Dim Wb As MSExcel.WorkBook = App.WorkBooks.Open(ProjectPath & "Reports\分组统计1.xls")
Dim Ws As MSExcel.WorkSheet = Wb.WorkSheets(1)
Dim n As Integer
Dim Rg0 As MSExcel.Range = Ws.UsedRange
For i As Integer = 1 To Rg0.Cells(Rg0.Count).Row+5-1
If i > n
If Ws.Cells(i,1).value Like "*合计"
Dim Rg As MSExcel.Range = Ws.Range("A" & i+1)
Rg.EntireRow.Insert(MSExcel.XlInsertShiftDirection.xlShiftDown)
Dim Rg1 As MSExcel.Range = Ws.Range("A" & i & ":" & "H" & i)
Rg1.Cut(Ws.Range("A" & i+1))
Rg1 = Ws.Range("A" & i+2 & ":" & "H" & i+2)
Rg1.Cut(Ws.Range("A" & i))
Rg1= Ws.Range("A" & i+2)
Rg1.Entirerow.Delete(MSExcel.XlDirection.xlUp)
n = i+2
End If
End If
Next
Wb.Save
App.quit '*********Visible = True 用这个就不要下面的三行代码了,只是打开Excel而已~~'************************100801版本后新增::::above:::::
Dim Proc As New Process
Proc.File = ProjectPath & "reports\分组统计1.xls"
Proc.Start()
[此贴子已经被作者于2010-8-4 14:23:18编辑过]
