Office中国论坛/Access中国论坛

标题: 用代码操作Excel，实现分类汇总、页面设置 [打印本页]

作者: guotianxin 时间: 2004-11-26 19:17
标题: 用代码操作Excel，实现分类汇总、页面设置
这篇文章是《将窗体内容导出到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

作者: tmtony 时间: 2004-11-28 18:27
非常精彩，值得一读。

作者: 竹笛 时间: 2004-11-28 20:48
It was wonderful.

欢迎光临 Office中国论坛/Access中国论坛 (http://www.office-cn.net/)