VBA制作Excel报表

xiaozhi7616

浏览: 196567 次
性别:
来自: 北京

最近访客更多访客>>

javabang

wjiayun

nicholaskong

ymgjava

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

博客分类：

Excel & VBA

VBA Excel 数据结构 Office VB

项目中使用Excel生成报表相信很多人都使用过，现在各种各样的专门实现报表的工具包也越来越多，很多基于Flash实现的都非常漂亮。Excel 报表与其相比自然是有优点也有缺点，缺点是样式不够美观（大多数人还是停留在Office 2003阶段），数据量大的时候表现不清晰，太多的sheet会让人很难找到目标，优点是Excel可以更加灵活的展示数据之间的结构，和揭示深层次的数据间的关系，为统计分析很好的提供服务。究竟选择什么样的报表工具关键还是看用户需求，选择最适合的才是正确的。

如题，VBA制作Excel报表和Java没有关系，内置VBA的Excel好像Java是不能直接生成的，我项目中需求是这样：在服务器端对原始数据进行条件计算，然后生成数据并展现到Web端，再拷贝这些数据到Excel里面作为源数据，通过VBA生成各种各样的报表。（这种方式实在是感觉不好，如果有朋友有更好的方法，望不吝赐教，谢谢）

下面介绍一下在项目中使用的过程，以及如何使用VBA制作报表，数据是一些简单的模拟数据，在生成的报表中多为数据透视表和数据透视图。

数据透视表参考：

现在开始动手了，拷贝源数据到Excel中，我从Office Online中找了一个Pivot table（数据透视表）的例子来做演示（代码在附件中），源数据中含有以下5个字段：

国家
销售人员名称
订单日期
订单号
订单金额

Excel技巧

冻结窗口：从上图可以看到不论我们滚动到第几行，第一行标题总是固定停留在那里的，只需要将光标放到A2单元格，点击 window - Freeze Panes，冻结窗口的效果就可以实现了，如果要撤销的话，还是点击 window - Unfreeze Panes 就可以了。

注意

源数据必须具有特定的业务含义，并且粒度要细，因为之后所产生的报表都是基于这些源数据的，源数据的准确性是一定要保证的，如果源数据不准确，后面所生成的报表也就不能真实的反映实际情况。
个人认为还有一点要注意的就是因为源数据是在服务端生成的，如果需要进行计算，那么最好是在服务器端处理好，这样可以减轻Excel处理数据的压力。

有了源数据，接下来就要对源数据进行一些处理，以便得到我们想要的结果。这时就该VBA出马了，首先要打开Excel的代码编辑窗口，通常有两种方式

使用快捷键 Alt+F11
右击 sheet 的标题栏，选择 “View Code”，如图所示

这样就可以看到VBA的代码编辑窗口了

编辑器的每一个部分的作用和操作细节就不一一介绍了，用到哪儿说到哪儿吧。

有了源数据，该以怎样的数据结构来操作这些源数据呢，我是搞Java的，当然第一想法就是面向对象了，最好能封装一个源数据的对象出来，这样我们要用什么直接从对象中取出来就好了，可是很不幸，VB这门语言是不支持面向对象的，那就只好假装一下了，用OO的思想来吧（我对VB不太了解，要是有什么不对的地方，希望朋友能帮忙提出来）。

新建一个类模块，在我们的工程VBAProject 上右击 Insert - Class Module，

选中新建的类模块，可以在下面的 Properties 工具栏中修改它的 Name 属性，改为 dataObj。

好了，现在在右边的代码区域来把它的属性放到这个新建的类模块中，属性就是源数据中的列名，每一行数据都可以封装成一个dataObj对象，这样我们在用的时候就可以方便的取出来用了。

Public country As String
Public salePerson As String
Public orderDate As String
Public orderID As String
Public orderAmount As Double

接下来，我们新建一个模块，在 VBAProject 上新建模块，命名 “getData”，

在这个模块中建立一个 getRowData 方法来把源数据中每一行数据都封装成一个 dataObj 对象

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' 读取 Source Data 表，封装 dataObj 对象
'
'   ByRef - By Reference, 按内存地址传递参数
'   rowDataObj - 是一个数组
'
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Sub getRowData(ByRef rowDataObj() As dataObj)

    ' 声明变量用来指定行数
    Dim rowCount As Integer
    
    ' 源数据sheet中取定从 第50000行第1列 向上方向 的单元格
    ' -1 是除去 标题栏 行的数据
    rowCount = Worksheets("Source Data").Cells(50000, 1).End(xlUp).Row - 1
    
    ' 定义数组长度
    ReDim rowDataObj(rowCount)
    Dim index As Integer

    ' 遍历 SourceData 表
    For index = 1 To rowCount
        
        ' 创建数组元素为 dataObj 类型
        Set rowDataObj(index) = New dataObj
        
        ' 为 dataObj 赋值
        rowDataObj(index).country = Trim(CStr(Worksheets("Source Data").Cells(index + 1, 1)))
        rowDataObj(index).salePerson = Trim(CStr(Worksheets("Source Data").Cells(index + 1, 2)))
        rowDataObj(index).orderDate = Trim(CStr(Worksheets("Source Data").Cells(index + 1, 3)))
        rowDataObj(index).orderID = Trim(CStr(Worksheets("Source Data").Cells(index + 1, 4)))
        rowDataObj(index).orderAmount = Trim(CStr(Worksheets("Source Data").Cells(index + 1, 5)))
        
    Next index

End Sub

通过这个方法，Source Data sheet上的源数据就被以 dataObj 对象的形式放到 rowDataObj() 数组里面了。在使用过程中，仅有这样的一个数组用起来不是很方便的，而且效率也不高，如果要找到一个特定的对象，那么就需要遍历整个数组，这样就非常得不偿失了，这里引入 Dictionary （字典）对象。

Dictionary 的好处在于它里面可以存放任何类型的数据和字典对象，而且在增加数据成员、在字典中进行迭代搜索和删除数据成员操作上的速度优势也非常明显。 Dictionary是以“键值对”的形式存放数据，非常类似 HashTable 的一种数据结构（使用参考：http://www.officefans.net/cdb/viewthread.php?tid=47628&highlight，这里就不详细的解释了）。

由于 Dictionary 并不是VBA具体存在的部分，而是存在于Microsoft Scripting Runtime Library（SCRRUN.DLL）中的一个对象。如果要使用 Dictionary 对象，就需要首先引入相关的动态链接库，在代码编辑窗口中，点击 Tools - References，在弹出对话框中选中 Microsoft Scripting Runtime 选项，确定，这样才可以使用 Dictionary 对象。

再新建一个模块，用来封装以国家为键的 Dictionary对象，命名“initDic”

Sub initCountryDic(ByRef dic As Dictionary, rowDataObj() As dataObj)

    Dim i As Integer
    Dim tempDic As Dictionary
    
    Set dic = New Dictionary
    
    ' 遍历数组
    For i = 1 To UBound(rowDataObj)
        
        ' 建立以 country 为键的 Dictionary
        If Not dic.Exists(rowDataObj(i).country) Then
            
            Set tempDic = New Dictionary
            dic.Add rowDataObj(i).country, tempDic
            
        End If
        
    Next i

End Sub

好了，有了这样的数据结构为下面创建我们所想要的数据透视表就打好了基础，我们可以通过源数据，来创建各种各样的不同结果的分析报表了。下面就动手做一张数据透视表吧，新建一个report模块：

Sub CreateReport(ByRef rowDataObj() As dataObj)

    Dim resDic As Dictionary
    
    ' 初始化 Dictionary 对象， 键为 country
    initDic.initCountryDic resDic, rowDataObj()
    
    Dim i As Integer
    Dim tempDic As Dictionary
    
    ' 遍历给定数组
    For i = 1 To UBound(rowDataObj)
        
        ' 将键为 country 的值赋给 tempDic
        Set tempDic = resDic(rowDataObj(i).country)
        
        ' 为 tempDic 赋值
        If Not tempDic.Exists(rowDataObj(i).salePerson) Then
            
            Set tempDic = New Dictionary
            tempDic.Add "salePerson", rowDataObj(i).salePerson
            tempDic.Add "orderDate", rowDataObj(i).orderDate
            tempDic.Add "orderID", rowDataObj(i).orderID
            tempDic.Add "orderAmount", rowDataObj(i).orderAmount
        
        End If
        
    Next i
    
    '''''''''''''''''''''''''''''''''''''''''''''''''
    '创建工作表 "Order Amounts"
    '
    '   分析每个 sales 的订单总额
    '
    '''''''''''''''''''''''''''''''''''''''''''''''''
    Dim ws As Worksheet
    Dim rowCount As Integer
    Dim columnCount As Integer
    
    ' 创建工作表，如果不存在，新建一个并命名，如果存在，需要删除已存在的工作表,再新建一个,而不能简单清空这个工作表
    If Not Utils.WorksheetExists(ThisWorkbook, "Order Amounts") Then
        Set ws = Sheets.Add
        ws.Name = "Order Amounts"
    Else
        Set ws = Worksheets("Order Amounts")
        Application.DisplayAlerts = False
        ws.Delete
        Set ws = Sheets.Add
        ws.Name = "Order Amounts"
        Application.DisplayAlerts = True
    End If
    
    ' 将工作表的标签设置成红色
    ActiveWorkbook.Sheets("Order Amounts").Tab.ColorIndex = 3
    
    ' 取行数和列数
    rowCount = Worksheets("Source Data").Cells(50000, 1).End(xlUp).Row
    columnCount = Worksheets("Source Data").Cells(rowCount, 100).End(xlToLeft).Column
    
    
    Dim pivotName As String
    pivotName = "pivot_orderAmounts"
    
    '''''''''''''''''''''''''''''''''''''''''''''''''
    ' 创建数据透视表
    '
    '  SourceData:     "Source Data" 源数据表
    '  R1C1:            第1行第1列
    '  DefaultVersion:  数据透视表版本
    '
    '''''''''''''''''''''''''''''''''''''''''''''''''
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "'Source Data'!R1C1:R" + CStr(rowCount) + "C" + CStr(columnCount)).CreatePivotTable TableDestination:= _
        "'[" + ThisWorkbook.Name + "]Order Amounts'!R3C1", TableName:= _
        pivotName, DefaultVersion:=xlPivotTableVersion10
    
    ' 显示数据透视表列表字段
    ActiveWorkbook.ShowPivotTableFieldList = True
    
    ' 添加数据透视表 行 字段
    With ActiveSheet.PivotTables(pivotName).PivotFields("Salesperson")
        .Orientation = xlRowField
        .Position = 1
    End With
    
    ' 添加数据透视表 数据 字段
    With ActiveSheet.PivotTables(pivotName).PivotFields("Order Amount")
        .Orientation = xlDataField
        .NumberFormat = "$#,##0.00"
    End With   
    
    ' 实际这个时候只是把源数据中的字段放到数据透视表里面，数据透视表值负责数据的展示，并没有改变任何数据
    
End Sub

好了数据透视表就建好了，那么怎么才能触发之前定义的这些方法来看到创建的数据透视表呢？这需要在Excel中建立一个按钮，在点击按钮的时候响应这些事件

创建按钮，先调出控制工具箱，

这就是控制工具箱啦，它和工具栏上其他的工具一样，使用起来很方便，从这个工具箱里我们可以创建一个命令按钮，单击Command Button，这时光标会变成十字形状，在工作表里面空白的地方拖拽就可以画出一个命令按钮了，调整到合适的大小

注意

看图中处于激活状态下的按钮 “Design Mode”，是用来控制我们创建的命令按钮的，只有处于设计模式下，才可以对创建的按钮进行修改操作，也就是说如果我们要修改按钮，首先需要进入设计模式下才可以进行操作

调整按钮上的文字，右击Buttoon - 命令按钮Object - 编辑，输入Create Report，再用鼠标点击空白处就OK了。

一个 Button 就出来了，接下来要让点击Button响应之前定义的事件，右击按钮 - View Code

跳到代码编辑器，

给一个按钮响应的事件

Sub onclick_generate()
    
    Dim rowDataObj() As dataObj
    
    getData.getRowData rowDataObj()
    
    report.CreateReport rowDataObj()
    
    
End Sub

来点下按钮试试吧，这样一个最最最最简单的数据透视表就建成了，不过知道原理和过程,要进行复杂一点的操作就容易多了。

看一下生成的数据透视图

注意

如果本地语言是中文的话，那么格式化后的Order Amount结果会以“￥”开头，没有关系，可以在区域和语言选项中调整成英文

从这个数据透视图可以直接看到每个 SalesPerson 全部的 OrderAmount，很直观吧，把光标放到数据透视图中，可以看到数据透视图字段列表，里面加粗的字段是已经反应在数据透视表里面的字段，普通字体的是还没有放到透视表中的字段

介绍一下数据透视表中的几个区域

行字段区
列字段区：
数据字段区：放置进行统计的目标数据，比如销售额，销售量
Page Fields：通常用来放置过滤条件

把Country字段放到Page Fields中选择UK，就可以看到所有英国区域的销售额了

再来一个例子，在上面 report 模块代码的后面加上

    pivotName = "pivot_orderAmounts2"
    
    '''''''''''''''''''''''''''''''''''''''''''''''''
    ' 创建数据透视表
    '
    '  SourceData:     "Source Data" 源数据表
    '  R1C1:            第1行第1列
    '  DefaultVersion:  数据透视表版本
    '
    '''''''''''''''''''''''''''''''''''''''''''''''''
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "'Source Data'!R1C1:R" + CStr(rowCount) + "C" + CStr(columnCount)).CreatePivotTable TableDestination:= _
        "'[" + ThisWorkbook.Name + "]Order Amounts2'!R3C1", TableName:= _
        pivotName, DefaultVersion:=xlPivotTableVersion10
    
    ' 显示数据透视表列表字段
    ActiveWorkbook.ShowPivotTableFieldList = True
    
    ' 添加数据透视表 行 字段
    With ActiveSheet.PivotTables(pivotName).PivotFields("Salesperson")
        .Orientation = xlRowField
        .Position = 1
    End With
    
    ' 添加过滤字段
    With ActiveSheet.PivotTables(pivotName).PivotFields("Country")
        .Orientation = xlPageField
        .Position = 1
    End With
    With ActiveSheet.PivotTables(pivotName).PivotFields("Order Date")
        .Orientation = xlPageField
        .Position = 2
    End With
    
    ' 添加数据透视表 数据 字段
    With ActiveSheet.PivotTables(pivotName).PivotFields("Order Amount")
        .Orientation = xlDataField
        .NumberFormat = "$#,##0.00"
    End With
    
    ' 设置透视表样式
    ActiveSheet.PivotTables(pivotName).Format xlTable2
    
    ' 创建数据透视图
    Charts.Add

    ActiveChart.SetSourceData Source:=Sheets("Order Amounts2") _
        .PivotTables(pivotName).TableRange2

    ActiveChart.Location Where:=xlLocationAsNewSheet
    ActiveChart.Name = "Order Amount2 Chart"

上面的例子中添加了两个过滤字段，并且设置了一下透视表的样式

可以通过 ActiveSheet.PivotTables(pivotName).Format xlTable2 语句定义透视表样式，参数xlTable2参数从xlTable1到xlTable10的效果是不同的，大于10的参数小于1的样式只有一种，但不会报错。

这里同时还生成了一张数据透视图

数据透视图和透视表是一样的，它们都是以Source Data为源数据，而展现的方式不同，在透视图中也可以对字段进行拖拽操作和过滤操作，实现起来很简单，有了源数据借助它们就能够进行深层次的分析，得到不同的结果。

这里有一篇通过VBA生成普通Excel的文章，有兴趣的朋友可以看一下

Excel Chart reference：http://peltiertech.com/Excel/ChartsHowTo/QuickChartVBA.html#VBAcharts

总结一下，个人认为透视表仅仅是一种数据分析的工具，建立透视表之前关键在于对源数据的分析，怎样有效的组织源数据来达到深入分析的结果才是关键。本文中介绍的例子是非常简单的，只介绍了这个工具的使用方法，实际工作中根据需求可以创作出各种各样的不同的数据展现方式，必要的时候还有可能借用源数据来计算生成一些中间表帮助我们解决一些复杂的问题。同时，在数据量很大的时候，操作数据要小心，避免一下创建很多的对象在内存中，有时候会出现内存溢出等意想不到的结果。希望分享这些知识能帮助到有希望使用Excel生成报表的朋友。

（附件中有两个Excel文件，SampleSalespersonReports.xls是从Office Online上下载的Pivot的例子，SampleSalespersonReports_01.xls是我的文中例子，有一点小问题没有修正，第一次打开之后点击按钮会生成透视表和透视图，再次点击按钮会报错，因为透视图所在 sheet 已经存在，删除之后再点击按钮就没有问题了）