|
这篇文章是《将窗体内容导出到Excel并对其进行格式化》http://www.office-cn.net/forum.php?mod=viewthread&tid=22539
的高级话题。
当你将窗体的数据导出到Excel后,可能会碰到需要对报表数据进行排序、分类汇总、页面设置等操作,下面我将我曾经写过的代码公开给大家,与大家分享,希望能对你有所帮助:
'排序
MyXL.Worksheets(1).UsedRange.Select
SortByCol "F6" '按F6所在的列排序
'分类统计
Dim sTotalCol()
Dim aTotalCol(10) As Integer
Dim n As Integer
'分类统计的序号
i = 1
For i = 0 To lstTarget.ListCount - 1
If lstTarget.ItemData(i) = Me.cboCat Then
Exit For
End If
Next i
'分类统计的列数及列号
n = 1
For n = 1 To 10
aTotalCol(n) = 0
Next n
n = 1
For j = 0 To iCols - 1
Select Case rstTmp.Fields(j).Type
Case 4 '数值型
aTotalCol(n) = j + 1
n = n + 1
Case 5 '货币型
aTotalCol(n) = j + 1
n = n + 1
End Select
Next j
For n = 0 To 10
If aTotalCol(n + 1) = 0 Then
Exit For
End If
Next n
Select Case n
Case 1
sTotalCol = Array(aTotalCol(1))
Case 2
sTotalCol = Array(aTotalCol(1), aTotalCol(2))
Case 3
sTotalCol = Array(aTotalCol(1), aTotalCol(2), aTotalCol(3))
Case 4
sTotalCol = Array(aTotalCol(1), aTotalCol(2), aTotalCol(3), aTotalCol(4))
Case 5
sTotalCol = Array(aTotalCol(1), aTotalCol(2), aTotalCol(3), aTotalCol(4), aTotalCol(5))
Case 6
sTotalCol = Array(aTotalCol(1), aTotalCol(2), aTotalCol(3), aTotalCol(4), aTotalCol(5), aTotalCol(6))
Case 7
sTotalCol = Array(aTotalCol(1), aTotalCol(2), aTotalCol(3), aTotalCol(4), aTotalCol(5), aTotalCol(6), aTotalCol(7))
Case 8
sTotalCol = Array(aTotalCol(1), aTotalCol(2), aTotalCol(3), aTotalCol(4), aTotalCol(5), aTotalCol(6), aTotalCol(7), aTotalCol(8))
Case 9
sTotalCol = Array(aTotalCol(1), aTotalCol(2), aTotalCol(3), aTotalCol(4), aTotalCol(5), aTotalCol(6), aTotalCol(7), aTotalCol(8), aTotalCol(9))
Case 10
sTotalCol = Array(aTotalCol(1), aTotalCol(2), aTotalCol(3), aTotalCol(4), aTotalCol(5), aTotalCol(6), aTotalCol(7), aTotalCol(8), aTotalCol(9), aTotalCol(10))
Case Else
sTotalCol = Array(aTotalCol(1))
End Select
TotalByCat Trim(Str(i + 1)), sTotalCol
'按指定列排序
Sub SortByCol(sCol As String)
MyXL.Application.Selection.Sort Key1:=Range(sCol), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, SortMethod _
:=xlPinYin
End Sub
'分类汇总
Sub TotalByCat(iGroupBy As Integer, aTotalList As Variant)
MyXL.Application.Selection.Subtotal GroupBy:=iGroupBy, Function:=xlSum, TotalList:=aTotalList _
, Replace:=True, PageBreaks:=False, SummaryBelowData:=True
End Sub
'页面设置
Sub PageSetup(iLastCol As Integer)
On Error Resume Next
With MyXL.Application.ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = "&10打印日期&""Times New Roman,常规"":&D"
.CenterFooter = "&10第&""Times New Roman,常规""&&""宋体,常规""页&""Times New Roman,常规"" &""宋体,常规""共&""Times New Roman,常规""&N&""宋体,常规""页"
.RightFooter = "&10艳阳天客户管理系统&""Times New Roman,常规""V3.0 Tel:13714680826 www.szyyt.com"
If iLastCol > 10 |
|