译者说明：欢迎访问我的Blog: http://blog.csdn.net/daidaoke2001/
译文中的错误或不当之处望不吝指出，这也是我坚持翻译工作的最大动力。
译文中标注了多个问号的地方，是我也不明白的地方，如果你知道如何翻译，请告诉我，
让我们一起提高。
我的Email：tangtaike@163.com
如需转载，请事先通知。
第三章 图 表 组 件
至此我们已经熟悉了电子表格组件，现在让我们来研究图表组件的功能和编程模型。和前一章中一样，在介绍了一个特定功能后，我会讲述该功能的一些有趣用途。本书第二部分中，您会看到大部分功能在实际中的运用。
在本章中也会演示一些使用您自己的代码来为图表组件添加功能的巧妙方法。刚开始的时候您可能会觉得图表组件要比电子表格和透视组件复杂一些，但它具有一些可供您在解决方案中使用的强大功能。此外，它还提供了许多“配置选项”，您可以通过调节这些选项来根据您自己的爱好定制图表。
第一节 图 表 组 件 基 础
图表组件是一个COM控件，它就是由开发Microsoft Excel2000中的图表生成功能的小组开发的。它以位于界面的COM控件形式，以及位于内存中的GIF生成引擎形式来提供基本的商业图表生成功能。在现在这个第一个版本中，图表控件支持所有能在Excel中找到的二维图表类型（等高线图类型除外），另外还支持 极坐标图，堆积饼图，以及填充散布图表类型。当前这个版本不包括任何三维图表类型或效果。
本章中将会讨论的一个新特性是图表控件能够在一个组件的整个图表空间中显示多个图表。图表控件实际上是由一个包含一个或多个图表的完整图表空间组成的，这些图表都共享同一组相同的类别。（我会在本节后部详细解释术语“类别”）通常，在一个时刻，你只会在控件中包含一个图表；然而，在同一个图表空间中包含多个图表，使得可以很容易的快速比较相似信息。在本章结尾处我会详细讲述“图表空间”。
和电子表格组件一样，图表组件具有许多基本特性和一些独特的术语。在我们讨论更高级的主题之前，您应该熟悉这些特性和术语。
图表生成的术语
在开发Excel2000图表生成功能的一个新特性时，OWC小组组织了大量的可用性测试，来检查是否我们的设计易于使用。如果您从来没有听说过这种测试，我可以解释一下，测试是这样的，我们从整个西雅图地区的公司和家庭中找来一些真正的顾客，向他们展示一个新特性的原型。我们要求这些人执行各种各样的任务，观察他们，以了解他们如何解决问题，以及是否我们的设计能够有效的帮助他们完成任务。我们常常发现现实情况与我们的期望相去甚远――这意味着必须回头重新设计这个特性。
在一个测试中，我们向测试人员展示我们在Excel中创建的各种图表，并将图表中的某部分圈起来。我们要求他们告诉我们他们认为这些被圈起来的部分应该叫什么。从逻辑上说，您会期望我们找到一些共性，发现那个大多数人已经将它和某一特定元素关联起来的名称。然后我们就可以在我们的文档，编程模型，用户界面等地方使用这个名称。但是结果使我们十分沮丧，我们发现完全不能给这些图表元素提供一个标准名称。您可能认为人们应该知道哪个是x轴，哪个是y轴，但是许多人并不会记得多少数学课上学到的东西，而常常将它们搞混。
注释
如果您正努力回忆哪个轴是X轴，哪个轴是Y轴，让我告诉您，X轴是水平方向的，Y轴是垂直方向的。当然，大多数图表都有被称为类别轴和数值轴的轴，而它们的方向是依赖于特定图表类型的。散布图和气泡图具有X和Y轴，因此这些图表上的各点之间要比较两个(或三个)值。在雷达图或极坐标图这样的图表类型中，轴实际上是从图表的中心延伸出去，因此X和Y轴就没有意义了。
因为看来并不存在描述一个图表的元素的通用语言，所以讨论创建和操作图表就十分困难。为了获得任何对于图表组件的特性和编程模型的理解，我们都必须首先定义一系列用于组件的关键术语，并确定它们代表什么元素。许多术语在不同的图表类型中具有特殊的含义，因此，如果你在下面讨论中遇到一个您不熟悉的图表类型，可参考“支持的图表类型”一节，查看该图表类型相对应的屏幕截图和介绍。
系列
系列是图表组件中最重要的结构之一。实际上，这个组件中的大部分内部结构都是围绕着系列的，图3-1中标记出了一个柱状图中的系列。请注意每个系列与图表的图例中的一项相对应。
图3-1。一个包含两个系列的柱形图。
一个系列代表了希望以某种方式显示的多个数据点的一个序列。人们通常认为是图表具有一个独特的类型（例如线形图，条形图，或者饼图）。但是在图表控件中，具有独特类型的是系列――这意味着您可以通过设置一个系列为线形，而另一个系列为柱型来创建一个组合图表。一个系列中的所有数据点通常具有相同的颜色（但您可以改变这个颜色，我们后面会提到）。另外，例如趋势线和错误条这样的元素是和特定的系列相关的。
缺省情况下，图例上的一项代表一个系列，但是如果需要，您可以隐藏图例中的某项。（后面将解释具体怎样做）
类别
类别要比系列难解释一些。图3-2标记出了一个柱形图中包含的类别标签。
图3-2。柱形图中的类别。
所有图表都有类别的概念，但并不是所有图表都有类别轴。在图3-2中，销售代表的名字是类别，每个系列包含各个类别上的一个单一数据点。大多数图表中，类别和系列的交叉点创建一个数据点。不过要注意的是，有些特别的系列和其它系列不同，它可能并不包含包含某个类别的数据点。例如，图表控件从所有系列中合并所有的类别，但在系列－类别的交集处不绘制数据点（？？？）。
类别轴在三个重要的方面不同于数值轴。
u 各类别没有内在的顺序。
u 没有最小和最大的类别。
u 一个数据点仅分配给一个特定的类别。
如果销售人员是您的类别轴，特定的销售数量就很自然与特定的销售人员相对应，在销售人员之间不存在数据点。而一个值轴有一个确定的最小值和最大值，在轴上的空间被平均分割为多个单元，当从最小值移向最大值时单元对应的值逐渐增加。因此不管位于轴的何处，每个数据点都能被标记。
散布图和气泡图没有类别轴，因为它的数据点是由X坐标和Y坐标定义的，气泡图表的数据点还具有一个代表气泡尺寸的值。虽然这些图表类型没有类型轴，数据点仍然可以属于某个特定的类别，并且你也可以获得一个给定数据点的类别名。将额外的信息编码到散布图或气泡图中的数据点的机制是很有用的，它允许您在用户将鼠标停留在一个数据点上时显示这个信息。
注释
显然，在真实的世界中，销售额可能常常属于多个销售代表。但大部分销售信息系统都将每个销售代表对销售的贡献分配合存储在一个数值中，这个数值会被标注在图表中。数据在类别中整齐分布的目的是使得可以使用连续的数值来比较离散的类别。例如，数值1.4567454可以在一个范围在0到2之间的数值轴中被标注，但是这个数值并不与类别在轴上的离散分布的容器相对应？？？
数值，数值，数值
在大多数较简单的图表类型中，您只需要考虑一组数值。表3-3显示了以销售人员和年来标注的销售量。这个图表中每个数据点只包含一个数值――对于大部分简单图表类型来说都是这样的。
图3-3。柱形图中的数值。
另一方面，散布图和气泡图表，则要求每个数据点必须包括两个或三个数值。在一个散布图表中，每个数据点有一个X值和Y值，这两个值的组合定义了一个二维空间，也就是笛卡儿空间中的点（X,Y）。气泡图表增加了第三个数值：一个决定了气泡以（X,Y）为中心的半径的气泡尺寸值。可以选择使用气泡的尺寸值代替半径来代表气泡的面积。
当使用盘高－盘低－收盘图时，情况会更复杂一些，因为在这个图中又是三个值确定一个数据点，不过它们现在被称为盘高值，盘低值，和收盘值。一个开盘－盘高－盘低－收盘(OHLC)图表中每个数据点关联4个数值，当然，额外的那个数值被称为开盘值。（这些类型的图表常常被称为股票图表，因为它们通常主要是被用来显示股票数据。）
当处理一个极坐标图时，您需要提供另一组数值：R值和Theta值。R值定义了数据点距图表中心的距离，Theta值代表了与通过原点的水平线之间的角度。
轴
您可能非常熟悉“轴”这个术语。在图表组件中，轴的含义和它在几何学上的含义基本上是相同的。不过，图表控件给轴添加了另一层含义，即将轴指定为类别轴或数值轴。在表3－4中，标明了类别轴和数值轴。
表3－4。 柱形图中的类别和数值轴。
一个类别轴被分割成多个相等的段――每个代表一个单独的类别。而数据点被标注在每个类别的中间。对于类别轴来说，不存在最小值和最大值的概念。而另一方面，数值轴因为是一个连续的轴，所以它具有最小值和最大值。在数值轴上，数据点在最小值和最大值之间被标注在它们应该位于的地方。
因为类别轴和数值轴的位置依赖于不同的图表类型，所以“类别”和“数值”只是逻辑的名称，而不能简单的直接映射为X轴和Y轴。例如，柱形图中的类别轴是沿底部(X)水平伸展的，而数值轴(Y)则是位于左方垂直上升的。但是在条形图中，位置正好相反，类别轴位于左边，而数值轴位于底部。可以参考“支持的图表类型”以了解各类型图表的更多信息。
缩放比例
虽然您一定听说过术语“轴”，但您可能以前并没有听说过术语“缩放比例”。虽然您通常只在数值轴上使用缩放比例，但是其实每个轴都具有缩放比例，缩放比例定义了它对应的轴的度量刻度，决定了轴的最小和最大值。缩放比例也决定了轴上的间隔是线性的，还是对数的。线性尺度将轴分割成从最小值线性增加到最大值的平均的各段（例如，20，40，60，80，100）。对数尺度也将轴分割成平均的各段；但从一个段到下一个段是以对数，而不是线性来递增的（例如，1，10，100）。
当本章后面部分讨论分割轴时，以及第6章中，我们会更加详细的讨论轴的缩放比例，在第6章中，我们还会看到如何通过“放大”来更加详细的显示您的数据中的一部分。
趋势线
图表组件支持在您的图表中为每个系列创建一个趋势线。和在Excel中一样，一个趋势线常被用来显示各系列中数据的趋势。如果历史中的各值一直保持递增或递减，那么如果您需要预测将来会出现哪个值，就可以使用趋势线来进行趋势分析和预测。和在Excel中一样，图表控件提供几种不同的趋势计算方法，包括线性，对数，多项式，指数，以及权。然而，图表组件不提供Excel中的移动平均数趋势线类型。图3－5显示了一个趋势图的例子。
误差线
图3－6显示了一个误差线，误差线是从数据点延伸出去的短线段，它指出了您的数据的某种不确定性，也就是误差值。每个数据点可以显示一个误差线，它表明数据点的真实值可能是误差范围中的任何一点。
和趋势线一样，误差线是和系列相关的。系列中的每个数据点都会显示一个误差线，而这个系列的误差线的集合可以用来显示一个正的误差总量，一个负的误差总量，或者同时显示这两个。误差总量可以以百分比的形式（例如+10%, -10%, 或 +/-10%），相对值的形式(例如 +2, -2,或 +/-2)，或者自定义误差的形式(例如, 对于数据点10来说，上届是12，下届是8)来表示。误差线也可以进行数据绑定，如果进行了数据绑定，则图表控件将各结果列中的值看作是每个数据点的自定义误差值。虽然Excel图表也为误差总量提供了标准误差和标准偏差选项，但图表控件本身并不支持这些功能。当然，您可以自己计算这些数值，并在图表中使用自定义的误差总量来显示它们。
为什么要定义一个单独的缩放比例对象？
您可能疑惑于为什么缩放比例的概念不只是轴的一部分。毕竟，似乎是缩放比例决定了一个轴的最小和最大值。但是，缩放比例的最小和最大值决定了被称为绘图区的可视区域的尺寸。轴显示标记和标签，但是是缩放比例为绘图区的尺度确定了准确的点/值比例。通过将缩放比例从轴上分离出去，图表控件就能够支持没有可视轴的图表类型。
数据标签
数据标签是位于数据点附近的一小段文字，您可以通过设置它来显示数据点的值、在系列中所占的百分比、类型名、系列名、或气泡的尺寸。（参见图3－7的例子）您可以在一个数据标签中显示这些信息片断的任意组合。此外，你还可以控制字体，颜色，边框格式等属性――甚至可以设置在每个信息片断之间使用哪种间隔字符。
图3－7。 柱形图中的数据标签。
显然，当需要在您的数据点附近显示数字，尤其是需要比较那些可能值非常接近的数据点时，数据标签是十分有用的。在需要显示一个数据点在整个系列中所占的百分比时（例如在饼图中），使用数据标签显示百分比当然也是很有用的。在没有类别轴的图表（例如散布图和气泡图）中使用数据标签显示数据点的类别名也是一个很好的显示额外的类别信息的方法。
和趋势线和误差线一样，数据标签是和系列相关联的。您不能对一个单独的数据点进行格式化，隐藏或显示的操作。所有针对数据标签的操作都会影响系列中的所有数据点。
支持的图表类型
当我在介绍图表组件时，通常我从一个开发者的口中听到的第一个问题就是：“它支持那些图表类型？”图表类型是图表生成功能的基础，因此多多益善。在我的书架上有一本包含各种图表类型，图表元素和信息图形学技巧的参考书，它几乎有450页！（这本书是：《信息图形：全面图解指南[操作图形，1997]》，和其它任何Edward Tufte的书一样，对任何从事信息图形学的人来说，它都是一个极好的资源。）
图形组件的第一个版本包括Excel2000中所有二维图表类型的集合（不包括等高线类型），另外，还包括极坐标图，堆积饼图，以及填充散布图类型。在这个版本的图表控件中不包含任何三维图表类型或效果，也不支持Excel提供的奇特的填充效果。
让我们看一些图表组件支持的图表类型的例子，并讨论可以使用它们来显示哪种数据。
柱形图和条形图
柱形图和条形图是在商业中最常用的图表类型，图表为每个数据点显示一个从数值轴上的零点延伸到数据点的被填充的条。
大多数人并不在意柱形图和条形图之间的区别――毕竟，它们基本上是相同的，只是伸展的方法不同。图形组件使用术语“柱”来描述沿屏幕的上下方向扩展的垂直列，使用术语“条”来描述横跨屏幕延伸的水平条。表3－8显示了一个柱形图和一个条形图的例子。
图3－8。 柱形图（上面）和条形图。
这些图表类型常用于显示那些包含不需要以某种特定次序显示的类别的数据。与线图不同，柱形图和条形图并不描述次序或级别的概念。
和许多图表类型一样，柱形图和条形图有一些图表子类型。缺省的子类型，叫做簇状柱形图或簇状条形图，在每个类别中不同系列的标记条紧挨在一起。（图3－8显示了一个簇状柱形图和一个簇状条形图。）当不同的系列之间完全无关或它们不应在界面上被合并时，簇状子系统是最有用的子系统。例如，如果您在一个系列中标记预算值，而在另一个系列中标注实际值，那么您不会需要合并这两种数值。而是会需要将这两种柱形或条形并排放在一起，以便于比较。
堆积柱形图和堆积条形图以一个堆叠一个的形式显示各系列。在这种图表中，柱或条的长度代表了类别对应的所有数据点的总和。表3－9显示了一个堆积柱形图的例子。
图3－9。 一个堆积柱形图。
在显示那些可以，并应该被合并起来，以便在视觉效果上描述每个类别总和的数据时，堆积柱形图和堆积条形图是很有用的。例如，如果您以国家和产品来标记销售量信息，您应该需要使用堆积柱形图来显示每个国家（类别）所有产品的总销售量，条仍然根据每个产品的销售量的准确值来进行分段――即高销售量的产品会拥有较长的段，而销售情况不好的产品会拥有较短的段。当不必考虑相对关系，而需要考虑每个类别的总和时堆积图表十分有用。
最后，百分比堆积图表子类型有点象饼图：贯穿绘画区画一个条，或柱，然后将条，或柱根据每个系列的数据点所代表的百分比分割为多个段。这种子图表类型和堆积子图表类型的关键区别在于：各段的长度是数据点占类别中各数据点总和的百分比，而不是数据点的具体值。因为所有条的长度都相同（100％），因此这样的图表不能用来比较不同类别的总和。不过，这种类型的图表在查看饼图显示的相同类型的信息时很有用，但与饼图不同的是，它能同时查看来自多个类别和系列的信息。
这里描述的大部分其它图表类型都具有同样的子类型集合――簇状，堆积，和百分比堆积。在后面的介绍中，我不再重复定义这三种子类型，不过需要的时候，我会作简单的说明。请回头参考本节，以了解这些子类型的介绍，以及哪种类型的数据适合于使用这些子类型显示。
饼图，堆积饼图，和圆环图
饼图在商业图表中也很常见，它们只能显示最低密度的有用信息，并且也是最低效的，这几乎是一个极大的缺点。但是，它们的简洁也使它们非常易于理解，极具说服力。例如，当显示市场占有率的分类信息时，极大或极小的一块所带来的视觉效果确实是强有力的。图3－10显示了一个典型的饼图。
图3－10。一个饼图。
饼图最重要的独特之处，是图例显示类别值，而不是系列值。大部分图表在图例中显示各系列，但因为饼图只显示一个系列，所以图例被用来显示对应各颜色饼“块”的类别的标记。
显然饼图在显示百分比占有率或总量的细目分类信息时是很有用的。饼图之所以只显示一维数据的原因刚才我们已经提到了，那就是它只能显示一个系列的数据点。
不过，堆积饼图和圆环图类型可以同时显示多个系列，这与百分比堆积柱形图可以同时显示多个系列和类别的数据很相似。堆积饼图和圆环图之间真正的唯一区别就是圆环图在中心有一个洞（如果您愿意，可以称为“圆环洞”）。图3－11描述了这样的信息，首先是一个堆积饼图，其后是一个圆环图。
图3－11。一个堆积饼图和一个圆环图。
我承认这两个图表类型有一些奇怪；实际上，我建议只有在没有其它图表类型能够满足需要的特殊环境中才使用它们（例如，在环绕爆炸地点的同心环中显示土壤中化学组成的百分比）。同心环可能会误导人们，因为它们的相对大小和顺序其实是抽象手锯，并不是基于某种数值的。
折线，平滑折线，以及面积图表
折线图表和面积图表属于较为简单的图表类型，似乎它们应该常被使用，但遗憾的是，并不是这样的。图3－12显示了一个典型的折线图和面积图的样子。
折线图和面积图在显示那些类别具有实际意义的次序的数据时十分有用，比如说一系列的日期或时间。例如，对于在一系列的日期上标记销售量或在一系列小时上标记股票价格的情况，使用折线图来显示比使用柱形图来显示更有效。这是因为在图表中的各数据点之间画上连线使得很容易说明哪儿有一个上升的趋势，哪儿有一个下降的趋势。
折线图和面积图之间真正的唯一区别是，在面积图中，类别轴和折线之间的部分被系列的颜色所填充。对于一个系列的值比另一个系列的值大时发生的覆盖情况，面积图表处理起来比较困难――除非您使用的是我们前面提到的堆积子类型。因为系列是按照顺序依次绘制的，最后的系列会覆盖前面绘制的任何系列。因此只有在您确认一个系列的值一直都比后续的各系列的值高时，才应该使用不堆积的面积图。
图3－12。一个折线图和一个面积图。
和柱形图、条形图一样，折线图和面积图也包括簇状、堆积和百分比堆积子类型。不过，术语“簇状”通常不用来描述缺省的子类型，这些缺省子类型只是简单的被称为折线图和面积图，而不加任何特别的限定。
折线图具有一个柱形图和条形图没有的子类型。折线图中的折线可以画成“笔直的”或者“平滑的”。当然，使用平滑线来绘制的图表被称为平滑折线图。在这种图表类型中，图表控件使用有弧度的线条进行绘制，以消除参差不齐的峰谷。
散布图和气泡图
有人认为，散布图能够成为比前面所介绍的较简单的图表类型更强有力的分析工具，但是不幸的是，它在商业图表中很少用到。虽然散布图具有系列和类别，但它也是通过两个值（而不是一个）来确定一个数据点的位置的。散布图中的每个数据点都具有一个X值和一个Y值，这两个值组合起来确定数据点在绘图区中的位置。
散布图和气泡图的关键区别是气泡图中的数据点是具有动态大小的圆形。气泡图中的数据点包括名为气泡尺寸的第三个值，它决定了气泡的半径或面积。图3－13显示了典型的散布图和气泡图。
图3－13。一个散布图和一个气泡图。
在比较两个不同的值以揭示相互关系或分配结构时散布图和气泡图十分有用。例如，使用散布图绘制一个部门的士气高低与部门收益之间的关系可以揭示一个明显的相关性：即高昂的士气通常会增加部门的收益。（至少，大部分雇员愿意相信它。）（？？）
散布图包含一些独特的子类型。缺省的子类型叫做标记散布图，它使用标记点（例如菱形或正方形这样的小几何形状）来代表数据点。不同的形状常用来代表不同的系列。您也可以选择使用平滑或者笔直的线段来连接每个系列中的标记点。还有，您还可以选择完全没有任何标记点的线段。图表组件包括一个Excel中完全没有包含的子类型。这个子类型允许您填充由数据点和连接线组成的多边形，这样就形成了“填充散布图”。
填充散布图的乐趣
您可以尝试一下随书光盘中一个相当有创造性的填充散布图的演示例子。打开文件夹Chap03夏的DrawWithChart.htm文件，点击图表界面，以创建一个填充多边形的各点。然后双击鼠标完成这个多边形。这个演示例子是由Jeff Couckuyt编写的，他是一位极为优秀的图表组件开发者，这个例子仅仅使用了图表控件和一个填充散布图表类型。
记住气泡图表有和面积图表相同的覆盖的问题。一个大的气泡会隐藏任何它下方的数据点，因此只有在您确定覆盖的可能性很小的时候才使用气泡图表，否则，请考虑将气泡填充为透明的。
雷达图表
雷达图似乎在美国并不怎么使用，但我知道在亚洲国家常用它来描绘例如食品中的营养成分这样的数据。图3－14显示了这个有趣和有用的图表类型的样子。
图3－14。一个雷达图。
雷达图和其它简单图表类型一样，具有类别，系列和数值。但是，雷达图在一个环绕图表的圆环中绘制类别名称，并包括从图表的中心延伸导每个类别名称的轮辐。每个轮辐就是一个数值轴。每个类别的各数据点在相应轮辐上的相应点上，以相应的系列颜色进行绘制。
然后图表将各系列中的各数据点用线段连接起来，还可以选择将线段和原点之间的区域使用系列的颜色进行填充。填充雷达图表具有面积图和气泡图具有的相同的老问题，因此要小心使用填充子类型，除非您确定您的数据不会造成覆盖（或者您根本不关心这个问题）。
雷达图也支持我们前面提到的平滑线子类型。另外，您还可以选择在平滑线和直线子类型上绘制数据点的标记。
盘高－盘低－收盘图和开盘－盘高－盘低－收盘图
需要显示股票和金融证券信息的人会对这两种图表类型感兴趣。盘高－盘低－收盘图类型(HLC图表)为每个类别显示一个线段。每个线段从盘低值延伸到盘高值，并且专门通过一个小核对符号来指示收盘值。图3－15显示了一个HLC图表的例子。
图3－15。一个盘高－盘低－收盘图。
在任何时候，如果您的数据中每个给定时段包含一列数据，并且在这列数据中有一个特别的数值需要被标记，那么您应该考虑使用这个图表类型。例如，您可以使用HLC图表来显示一段时间内的温度读数。
开盘－盘高－盘低－收盘图（OHLC图）在HLC图的数据上添加了一段额外的信息：开盘值。该图表使用一个在开盘值和收盘值之间填充的矩形来显示开盘值，如图3－16所示。
填充的矩形既可以是系列的颜色，也可以是黑色，这依赖与收盘值和开盘值之间的差值是正还是负。正的差值会显示正常的系列颜色，而负的差值会显示黑色。这就向观察者显示了某段时间内值发生了升高还是降低。这样他或她就能确定数值是变好还是变坏了，以及变好和变坏的程度任何。
图3－16。开盘－盘高－盘低－收盘图。
HLC和OHLC图表类型依然保留了系列的概念。但是，在HLC图表类型中，图表组件在同一水平位置上放置不同的系列。换句话说，控件会在同一个地方重复的绘制各个系列。通常，这个图表类型在数据点只有一个系列时非常有用。另一方面，OHLC图表能够管理多个系列，并将多个系列的条紧挨着进行绘制。
极坐标图
极坐标图形类型是Chart组件中出现的一个新图表类型，Excel图表功能中并不包含它。极坐标图，这个微软的Office用户一直以来都在要求的图表，在显示角度和距离之间的关系时十分有用。极坐标图表常用于音频领域和无线电领域中，例如，可以用来显示麦克风的拾音器的强度和方向。图3－17描述了一个典型的极坐标图表。
图3－17。极坐标图。
极坐标图表包括平滑线子类型，并且您可以选择是否在线上显示数据点的标记。
组合图表
在本章开始处我们提到过，图表生成最大的秘密之一就是一个图表实际上并不包括一个图表类型。而是每个单独的系列包含一个图表类型。如果一个图表中的所有系列碰巧包含的都是相同的类型，那么Chart对象的Type属性会返回这个类型。但是，您可以使用这个特性来创建更加复杂的组合图表，在其中，您可以将一些系列绘制成柱形或条形，而将另一些系列绘制成线性。
不是所有的图表类型都能够被组合，在这个版本的Office中，图表组件只允许您对柱形，折线形，和面积图表类型进行组合。显示商业数据时最常用的组合图表是柱形图和折线图的组合。
装载数据
您已经了解了各种图表元素的名称，以及图表控件能够显示那些图表类型，现在您需要学习如何将数据装载到图表中。和大多数Offic Web组件一样，图表控件可以从多种数据源中装载数据，并且装载的过程可以在设计环境中使用图表向导来完成，或通过编写代码来完成。图表组件可以绑定到所有其它的Office Web组件上――电子表格组件，透视表组件，以及数据源组件――还有所有实现了IDataSource接口（这个一个微软IE和VB中的数据源控件所具有的标准接口，在微软开发者网络图书馆和OLE DB SDK中有关于它的文档）的其它控件，ADO的Recordset对象，甚至是文本数据的数组或使用分隔符分隔的一系列数据组成的字符串。
将数据装载到图表控件中的常用方法是：告诉图表应该从哪里获得数据，以及数据源的哪部分应该用于当前图表类型所需要的系列，类别和值。在编程模型中，图表控件将这些可以绑定到数据源的某部分的图表元素称为dimensions。当绑定到一个电子表格时，您指定的数据源的“部分”就是一个range的引用，例如A1:C1。如果绑定到一个OLE DB数据源时，您就应指定使用结果集中的哪一个列名或者列的下标。而对于透视控件，您应指定使用哪一个透视轴。（我们将在第4章中进一步讨论这个问题），而对于文本数据来说，只有一种“部分”存在：数组或具有分隔符的字符串本身。
图表向导通过为您提供一个简单的指定这种绑定信息的用户界面，帮您完成了大部分的绑定工作。但实际上图表向导只是调用了图表控件的公共编程模型，因此如果通过编写代码，您能够完成任何图表向导的功能，并能完成图表向导所不具备的功能。图表向导的使用是相当明白的，它的在线帮助也包含了大部分的使用方法。因为本书主要着眼于使用Office Web组件开发定制的解决方案，所以我不会在这里详细介绍如何使用图表向导。而会深入到那些当通过编程将数据装载到图表中时您必须编写的代码中去。
使用文本数据装载图表
下面的子过程，取自随书光盘中Chap03文件夹下的LoadFromLiteral.htm文件，它显示了如何将文本数据装载到图表控件中：
'------------------------------------------------------------------------
' LoadChartWithLiteral()
' 目的: 将文本数据装载到图表中
' In: cspace ChartSpace对象的引用
' vSeries 变量数组或以tab分割的字符串，代表各系列名。
' vCategories 变量数组或以tab分割的字符串，代表各类别名。
' avValues 变量二维数组或以tab分割的字符串，代表各数值；
' 外围数组中的一项代表一个系列。
'
Sub LoadChartWithLiteral(cspace, vSeries, vCategories, avValues)
' 局部变量
Dim cht ' 我们将在图表空间中创建的Chart对象。
Dim ser ' 临时系列
' 获得常量对象以便我们可以在脚本中使用常量名称。注意：只是在VBScript中需要这样做，
' 而在VBA中并不需要。
Set c = cspace.Constants
' 清除图表空间中。
cspace.Clear
' 在图表空间中创建一个图表。
Set cht = cspace.Charts.Add()
cht.HasLegend = True
' 现在调用SetData函数来绑定各个dimensions
' 第二个参数是c.chDataLiteral, 说明最后一个参数是一个变量数组或是一个
' 以tab分隔的字符串。
cht.SetData c.chDimSeriesNames, c.chDataLiteral, vSeries
cht.SetData c.chDimCategories, c.chDataLiteral, vCategories
' 当使用文本数据装载图表时，您必须使用数值分别装载每个系列。
For each ser In cht.SeriesCollection
ser.SetData c.chDimValues, c.chDataLiteral, avValues(ser.Index)
Next 'ser
End Sub 'LoadChartWithLiteral()
当使用文本数据装载图表时，数据既可以包含在变量数组中，也可以包含在以tab分隔的字符串中，每一个元素或标记都代表了一个不同的数值。在随书光盘的文件中，我使用了Array函数将文本数据以数组的形式进行传递。在微软的VBScript和VBA中都支持这个函数。
SetData方法被用来传递文本数据，但是请注意第二个参数（通常是数据源的下标）是常量chDataLiteral。这个等于-1的常量，告诉图表下一个参数是文本数据，而不是一个数据源的一部分。
还要注意的是，在将文本数据传递给图表时您必须使用Wcseries对象（代表一个系列的对象）的SetData方法。因为一个图表控件自身只能接受一维数组的值，如果允许您将文本数据传递给WCChart对象（代表一个图表的对象）的SetData方法，它将无法判断一个值应该属于哪一个系列。前面的过程通过简单的遍历系列集合，并将相应的数组的值传递给当前系列的SetData方法来解决了这个问题。
绑定到电子表格组件
下面的子过程，来自随书光盘中Chap03子目录下的LoafFromSpreadsheet.htm文件，它演示了如何将图表组件绑定到电子表格组件的区域上。
'-------------------------------------------------------------------------
' BindChartToSpreadsheet()
'
' 目的: 将一个图表绑定到源电子表格中的指定区域上。
' 传入参数:cspace ChartSpace对象的引用
' sheet Spreadsheet对象的引用
' srngSeries 字符串区域的引用，其中定义了系列的名称
' srngCategories 字符串区域的引用，其中定义了类别的名称
' srngValues 字符串区域的引用，其中包括了各个数值
'
Sub BindChartToSpreadsheet(cspace, sheet, srngSeries, srngCategories, _
srngValues, fSeriesInCols)
' 局部变量
Dim cht ' 将在图表空间中创建的Chart对象
Dim ser ' 临时系列
Dim rngValues ' 存储各数值的Range对象
Set c = cspace.Constants
cspace.Clear
' 首先告诉图表它的数据来自于电子表格
Set cspace.DataSource = sheet
' 在图表空间中创建一个图表。
Set cht = cspace.Charts.Add()
cht.HasLegend = True
' 现在调用SetData方法来绑定各个dimensions
' 第二个参数是0，说明如果有多个数据源，应该使用第一个数据源。
cht.SetData c.chDimSeriesNames, 0, srngSeries
cht.SetData c.chDimCategories, 0, srngCategories
' 电子表格子只能绑定到一维区域上，
' 因此遍历系列集合，并单独为每个系列设置数值。
Set rngValues = sheet.Range(srngValues)
For Each ser In cht.SeriesCollection
If fSeriesInCols Then
ser.SetData c.chDimValues, 0, _
rngValues.Columns(ser.Index + 1).Address
Else
ser.SetData c.chDimValues, 0, _
rngValues.Rows(ser.Index + 1).Address
End If
Next 'ser
End Sub 'BindChartToSpreadsheet()
现在说明这个例子的一些相关信息。首先，为了将图表和电子表格空间绑定起来，我将图表控件的DataSource属性设置为电子表格控件的实例。在接收到指向电子表格控件实例的指针后，图表控件就可以向电子表格控件请求某个区域中的单元格的数值。
第二，使用SetData方法首先绑定系列名称维，然后是类别维。您必须使用这个顺序来绑定图表的各维：先是系列名称，然后是类别名称，最后是数值。在一些少见的例子中，您只有一个系列的信息，你可以跳过设置系列名称维，而只绑定类别和数值。这样做会使控件使用缺省的名称“Series”来为您创建一个系列，您可以通过设置WCSeries对象的Name或Caption属性来改变这个名称。
我要谈到的最后一点是，当绑定到电子表格组件上时，Chart组件需要接收到每个系列的准确的区域引用。遗憾的是，您不能将一个二维区域的数值传递给图表，并让它自动判断各数值属于那一个系列和类别。您必须将一个一维区域的引用传递给每个WCSeries对象的SetData方法的最后一个参数。前面的例子使用一种常用的方式来完成这个任务：使用电子表格组件的Range对象来获得在二维区域中每列或每行的一个区域引用。并依次将它传递给各系列的SetData方法。标志fSeriesInCols说明一个指定系列的各数值是在电子表格中按列排列还是按行排列。如果标志为真，将使用列的集合；如果为假，将使用行的集合。
还需要说明的是，例子中在系列的下标值上加上1。这是因为为了保持与Excel编程模型的兼容性，列和行集合的下标是以1为基础的。而图表组件的系列的下标是以0为基础的。将系列的下标加1，从而获得相对应的区域中列或行的下标。
绑定到数据源组件
下面的方法，来自随书光盘中Chap03子目录下的LoadFromDSC.htm文件，它演示了如何将图表组件绑定到从数据源组件(DSC)返回的一个记录集上。（我们将在第5章中详细的讨论DSC。）
'------------------------------------------------------------------------
' BindChartToDSC()
'
' 目的: 将图表绑定到数据源组件的一个记录集上。（这个例子创建一个饼图。）
' 输入参数:cspace ChartSpace对象的引用
' dsc 数据源控件的引用
' sRSName 绑定到的数据源控件中记录集的名字
' sCategories 包含类别的结果列的名称
' sValues 包含数值的结果列的名称
'
Sub BindChartToDSC(cspace, dsc, sRSName, sCategories, sValues)
' 局部变量
Dim cht ' 将在图标空间中创建的Chart对象
Dim ser ' 临时系列
Set c = cspace.Constants
cspace.Clear
' 首先告诉图表它的数据来自数据源控件。
Set cspace.DataSource = dsc
' 接着告诉它数据源控件中它将会被绑定到的记录集
cspace.DataMember = sRSName
' 在图表空间中创建一个饼图。
Set cht = cspace.Charts.Add()
cht.HasLegend = True
cht.Type = c.chChartTypePie
' 现在调用SetData函数绑定各维，第二个参数为0，说明如果有多个数据源，应该使用第一个
' 在这个饼图的例子中，我们将手工添加一个系列，并使用这个系列的SetData方法
Set ser = cht.SeriesCollection.Add()
ser.SetData c.chDimCategories, 0, sCategories
ser.SetData c.chDimValues, 0, sValues
' 最后，为这个例子添加一些数据标签，因为它是一个饼图。
Set dls = ser.DataLabelsCollection.Add()
dls.HasPercentage = True
dls.HasValue = False
End Sub 'BindChartToDSC()
DSC既可以从图标组件和透视组件中提取数据，也可以为二者提供数据。它也实现了Visual Basic和IE的数据源控件所实现的相同的数据源COM接口（IDataSource），因此，这段代码可以被这些环境中的任何其它有效的数据源控件所使用。
ChartSpace对象的DataSource和DataMember属性是这个例子的基础。因为他们是由Visual Basic和IE所建立起的数据绑定标准的一部分，所以你常会在这些环境中的其它数据绑定控件中看到它们。DataSource属性被设置为只想提供数据的控件（在这个例子中是DSC），而DataMember属性被设置为命名所需要的特定数据集的字符串。因为一个DSC可以同时暴露多个数据集，所以DataMember属性被用来告诉图表控件应该请求那个数据集。如果您不设置DataMember属性。图标控件会请求缺省的数据集，具体哪个是缺省数据集，是由DSC决定的。
本例中使用SetData方法的方式与在BindChartToSpreadsheet方法中使用SetData方法的方式极为相似，除了函数调用中的“数据部分”（SetData函数的最后一个参数）现在变成了DSC返回的记录集中一个列的列名。数据部分还可以是列的列下标（0，1，2，等等）。图表控件将把数据部分的值传给给ADO的Fields集合对象的Item方法，因此任何这个Item方法认为有效的参数都可以在SetData方法的最后一个参数中被使用。一般来说，如果您认为列名不会在将来改变，但列的顺序会在将来改变，那么您应该使用列名，而如果您认为将来列名会改变，而列的顺序不会变化，那么应该使用列的下标。
前面提到过，饼图的一个有趣的特性是图例中的项是类别，而不是系列。因此，当绑定一个饼图时，您应该将您希望显示在图例中的列绑定到类别维上，而不是绑定到系列维上。这也适用于堆积饼图和圆环图。
本例中最后一段代码创建了一些数据标签（我们刚刚讨论过）来显示每个数据点在总体中所占的百分比。图表控件可以为每个数据点显示许多数值，缺省是显示实际的数字数值。因为我只希望显示每一片所占地百分比，所以我将HasPercentage属性设为True，而将HasValue属性设为False。
虽然这个特定的例子创建的是一个饼图，但是您可以在绑定到DSC上时使用任何支持的图表类型。我选择在这个例子中创建饼图只是为了说明在使用饼图时是如何实现绑定的。
绑定到数据集
下面的函数，来自随书光盘中Chap03子目录下的LoadFromRecordset.htm文件，它演示了如何将一个图表控件绑定到一个ADO记录集对象上：
'------------------------------------------------------------------------
' BindChartToRecordset()
'
' 目的: 将一个图表绑定到一个记录集上（本例中创建的是一个散布图。）
' 传入参数:cspace ChartSpace对象的引用
' rst 要绑定到的Recordset对象的引用
' sfldSeries 系列的字段名
' sfldXValues 包含X数值的字段名
' sfldYValues 包含Y数值的字段名
'
Sub BindChartToRecordset(cspace, rst, sfldSeries, sfldXValues, _
sfldYValues)
' Local variables
Dim cht ' Chart object that we'll create in the chart space
Dim ser ' Temporary series pointer
Dim ax ' Temporary axis pointer
' Grab the Constants object so that we can use constant names in
' the script. Note: This is needed only in VBScript -- do not include
' this in VBA code.
Set c = cspace.Constants
' Clear out anything that is in the chart space
cspace.Clear
' First tell the chart that its data is coming from the Recordset
Set cspace.DataSource = rst
' Create a Scatter chart in the chart space
Set cht = cspace.Charts.Add()
cht.HasLegend = True
cht.Type = c.chChartTypeScatterMarkers
' Now call SetData to bind the various dimensions
' Second parameter is zero, meaning the first data source should be
' used if there are multiple data sources
' In this example of a Scatter chart, we will bind
' two value dimensions: X and Y
cht.SetData c.chDimSeriesNames, 0, sfldSeries
cht.SetData c.chDimXValues, 0, sfldXValues
cht.SetData c.chDimYValues, 0, sfldYValues
' Finally, let's add some axis labels using
' the column names as the axis captions
Set ax = cht.Axes(c.chAxisPositionBottom)
ax.HasTitle = True
ax.Title.Caption = sfldXValues
ax.Title.Font.Name = "Tahoma"
ax.Title.Font.Size = 8
ax.Title.Font.Bold = True
ax.NumberFormat = "#,##0"
Set ax = cht.Axes(c.chAxisPositionLeft)
ax.HasTitle = True
ax.Title.Caption = sfldYValues
ax.Title.Font.Name = "Tahoma"
ax.Title.Font.Size = 8
ax.Title.Font.Bold = True
ax.NumberFormat = "$#,##0"
' Let's also set the marker size a bit smaller than normal
For Each ser In cht.SeriesCollection
ser.Marker.Size = 5
Next 'ser
End Sub 'BindChartToRecordset()
毫无疑问，您会立刻注意到这个例子除了使用的是散布图之外，它和前面包括DSC的例子很相似，这是因为在Visual Basic和IE中ADO的记录集对象本身就是一个有效的数据源。它也实现了与DSC相同的数据源接口。这使得可以采用和使用DSC时设置图表控件的DataSource属性一样的方式将控件的DataSource属性指向记录集对象。但是，记录集对象被定义为只能暴露一个数据集，因此在绑定到记录集上时不需要设置DataMember属性。
因为在本例中使用的图表类型是散布图，所以代码设置了两个数值维：X值和Y值。在前面“支持的图表类型”一节中讲到过，散布图对每个数据点使用两个数值，因此我们需要将X数值维绑定到记录集中的一列上，而将Y数值维绑定到另一列上。（您可以将它们都绑定到同一列上，但是这样会产生一个高度相关的散布图！）
良好架构造成的一次意外
当我将本章交给图表组件开发者们浏览时，一位开发者提出，实际上组件并不“支持”从记录集中加载，而且OWC小组也没有正式测试过这个功能。但我们在内部编写的几乎所有演示和实际的页面都使用了这个方法，因此，实际上这个功能是相当稳定的。
将记录集装载到图表组件中能够正常工作的原因是良好架构造成的一次意外。当Visual Basic和IE开发小组选择了标准的数据源接口（IDataSource）时，ADO小组认为让Recordset对象实现这个接口是很有意义的，因为这样它就能从GetDataMember方法中方便的返回底层的IRowset接口。因为图表控件在从DSC中装载数据时使用IDataSource接口，所以Recordset对象正好能够很好的工作。对于图表控件来说，记录集对象和其它数据源控件是完全一样。
当绑定到记录集对象时，您必须确定记录集正在使用微软Windows游标引擎（WCE）或者能够被浏览和游历。WCE是一个ADO组件，它能够在任何OLE DB 记录集上提供了浏览、排序、过滤和其它功能，而不必考虑记录集的来源或原始功能。为了使用这个引擎，需将您的ADO连接或记录集的CursorLocation属性设置为adUseClient，如果您所使用的环境不能识别这个常量，可以使用它的值――3来代替。为了保证图表控件能够在记录集中进行游历，您可以使用adOpenStatic游标类型，它的值也是3。在记录集的Open方法的CursorType参数中，或Recordset对象的CursotType属性中使用adOpenStatic或者3（第4章中会详细讨论WCE）。
请浏览随书光盘中LoadFromRecordset.htm文件里的完整源码清单来查看设置这个必须设置的记录集属性的例子。当浏览这个文件时，注意例子使用列名作为轴的标题。散布图具有两个数值轴（X轴和Y轴），因此您应该为这些轴设置标题来解释您在这些轴上显示的是什么数值。使用记录集中的列名来标注这些轴是一个简便的方法；当然，如果记录集中的列名难以理解，您可以使用您自己的标题来进行设置。
绑定到透视组件
图表组件最后一个可能的数据来源是透视组件。（在下一章中将详细的讨论这个组件。）和您所猜想的一样，这个组件也实现了与VB和IE中所有有效的数据源所暴露的数据源接口相同的接口，因此下面这个从随书光盘的Chap03文件夹下的LoadFromPivot.htm文件中取出的例子，和我们前面所讨论的DSC的例子很相似，但是也有一些重要的不同：
'------------------------------------------------------------------------
' BindChartToPivot()
'
' Purpose: Binds a chart to a PivotTable component
' In: cspace reference to the ChartSpace object
' ptable reference to the PivotTable object
' fSeriesInCols Boolean flag indicating whether the series
' of the chart should come from the column
' axis or the row axis of the PivotTable control
'
Sub BindChartToPivot(cspace, ptable, fSeriesInCols)
' Local variables
Dim cht ' Chart object that we'll create in the chart space
Dim ax ' Temporary axis reference
Dim fnt ' Temporary font reference
' Grab the Constants object so that we can use constant names in
' the script. Note: This is needed only in VBScript -- do not include
' this in VBA code.
Set c = cspace.Constants
' Clear out anything that is in the chart space
cspace.Clear
' First tell the chart that its data is coming from the
' PivotTable component
Set cspace.DataSource = ptable
' Create a chart in the chart space
Set cht = cspace.Charts.Add()
cht.HasLegend = True
cht.Type = c.chChartTypeBarClustered
' Now call SetData to bind the various dimensions
' Second parameter is zero, meaning the first data source should be
' used if there are multiple data sources
If fSeriesInCols Then
cht.SetData c.chDimSeriesNames, 0, c.chPivotColumns
cht.SetData c.chDimCategories, 0, c.chPivotRows
Else
cht.SetData c.chDimSeriesNames, 0, c.chPivotRows
cht.SetData c.chDimCategories, 0, c.chPivotColumns
End If 'fSeriesInCols
' Set the values dimension. The value
' you pass for the data reference (the last parameter)
' is the index of the total you want to use.
' Since there is only one total in this example,
' we pass zero, indicating the first one.
cht.SetData c.chDimValues, 0, 0
' Finally, let's add an axis title to the value
' axis, using the label on the pivot total
' as the caption, and set the number format
Set ax = cht.Axes(c.chAxisPositionBottom)
ax.HasTitle = True
ax.Title.Caption = ptable.ActiveView.DataAxis.Totals(0).Caption
Set fnt = ax.Title.Font
fnt.Name = "Tahoma"
fnt.Size = 8
fnt.Bold = True
ax.NumberFormat = ptable.ActiveView.DataAxis.Totals(0).NumberFormat
End Sub 'BindChartToPivot()
和前面的记录集合DSC例子一样，为了将图表控件绑定到透视控件，首先您将图表控件的DataSource属性设者为指向透视表控件的一个实例。和记录集的例子一样，透视表控件只暴露一个数据集，因此您不必改变DataMember属性的缺省设置。
本例和DSC的例子的关键区别是用于SetData方法最后一个参数的特定常量。您应该记得，这个最后参数定义了您需要将哪部分的数据绑定到指定的图表维。在透视表控件中，有效的逻辑部分是行和列的透视轴，以及试图中的所有总和。在透视表控件中，行轴指显示在表左下方的所有标签，而列轴指显示在横穿表顶部的所有标签（下一章将更多的讲述这些轴）。图表控件都必须知道的所有信息就是：您需要将哪个轴绑定到系列名称维上，哪个轴绑定到类别维上。
本例中绑定图表控件的各个值维的方式有一些不同。因为一个透视表报表能够同时显示许多总和值，所以图表控件必须知道您需要将哪个总和用于指定的值维。这是通过将一个总和的下标传递给SetData方法的最后一个参数来指明的。在BindChartToPivot例子中，我们使用了簇形柱形图，因此每个数据点只有一个数值。我们告诉图表控件使用下标0处的总和（即第一个，因为是从0开始的）作为数据点的数值。注意这是在透视表控件的当前视图中有效总和中的下标――而不是数据源中所有可能的总和。
但透视表控件确实实现了一个数据源控件所需要的标准COM接口，它只是不知道如何返回ADO记录集和OLE DB行集合。图表控件中包含了专门的代码，以实现读取透视表控件中显示的交叉数据，并恰当地忽略了子总计和外部总计，如果使用这些总计，将会扭曲图表的刻度，并不是所有的数据绑定控件都能将透视表控件用作它们的数据源。
图表和轴的标题
图表因为以易于理解，可视的方式显示大量的信息，而成为一种高效的机制。但是，绘制在图表中的数据几乎不能自我描述；因此您经常需要为您的图表和在图表中显示的轴添加一个描述性的标题。
为多值的图表使用多个总和
散布图、气泡图、极坐标图，和OHLC图表类型都有一个相同的地方：它们需要使用多于一个的数值来确定图表中的一个数据点。散布图需要X值和Y值，气泡图也需要这两个值，还有一个气泡尺寸值，极坐标图需要Theta和R值，而OHLC图，就像它的名称所表明的一样，需要4个数值。因为透视表报表一次可以显示多个总和，所以常常希望将这些总和映射为不同的图表数值，以便形成一个单个数据点。
您可以通过在SetData方法最后一个参数中指定透视表视图中总和的下标来完成这个任务。例如，为了将第一个总和绑定到散布图的X上，而将第二个总和绑定到Y上，您应该书写这样的代码：
cht.SetData c.chDimXValues, 0, 0 ' First total
cht.SetData c.chDimYValues, 0, 1 ' Second total
有时您需要在一个数据点只使用一个数值的图表类型中使用这些多个总和，例如堆积柱形图。在这种情况下，您可能希望将总和的标题显示为一个嵌套类别或一个嵌套系列的标签。您可以使用一个特殊的常量来完成这个任务：
cht.SetData c.chDimCategories, 0, c.chPivotRowAggregates
这个设置将使用显示在行轴上的任何总和标题作为分级的类别轴的嵌套类别名称。为了得到您需要的结果，您还应该将PivotView对象的TotalsOrientation属性设置为plTotalOrientationRow常量，以便总和的标题显示在每一行上。
可能您需要将多个总和在图表中显示为不同的系列。为了完成这个任务，将PivotTable报表上的列轴保持为空，添加上您需要显示为不同的系列的总和，然后书写下面的代码：
cht.SetData c.chDimSeriesNames, 0, c.chPivotColAggregates
这段代码将在图表中为PivotTable报表中的每个总和创建一个系列。请参考随书光盘中Chap03文件夹下的PivotTotalsAsSeries.htm例子文件，并查看它的具体做法。
如果您将多个图表添加到图表控件中（在“图表空间中的多个图表”一节中会详细介绍），您可以为每个图表设置它们自己的标题。实际上，您可以为图表控件自身设置一个全局的标题，他会显示在所有单独的图表的上方。可以使用所有的基础字体属性（字体名称，大小，是否黑体，是否斜体，是否具有下划线，颜色）来格式化图表标题。您也可以设置它们的背景色，或者让它们保持透明。对于轴标题也是一样的。
缺省情况下，新创建的图表不会具有图表标题和轴标题。您可以在设计阶段使用属性工具箱来添加图表标题和轴标题，也可以使用代码在运行阶段设置。下面这段来自随书光盘Chap03文件夹下的AddTitles.htm文件的方法，演示了如何添加一个图表标题：
'------------------------------------------------------------------------
' SetChartTitle()
'
' Purpose: Sets the chart's title
' In: cht reference to a chart
' sTitle new title caption
'
Sub SetChartTitle(cht, sTitle)
Dim fnt ' Temporary font reference
' If the title is nonblank
If Len(sTitle) > 0 Then
' Add a title if necessary
cht.HasTitle = True
' Set the caption and its font formatting
cht.Title.Caption = sTitle
Set fnt = cht.Title.Font
fnt.Name = "Tahoma"
fnt.Size = 10
fnt.Bold = True
Else
' Title is blank. Remove it.
cht.HasTitle = False
End if
End Sub 'SetChartTitle()
您可以使用与添加图表标题相同的方法添加轴标题，除了一个区别：方法的第一个参数接受一个Axis对象的引用，而不是一个图表对象的引用。
还要注意您既可以将标题的字体或者背景的Color属性设置为一个RGB颜色值，也可以将它设置为一个IE的颜色名称――例如，“FireBrick”或者“PapayaWhip”。这也适用于图表控件中任何使用颜色的地方，例如图表的背景色，绘图区的颜色，系列的颜色，等等。
轴标签
缺省情况下，图表控件会在您所有轴上设置标签，以便显示数据点位于刻度上的何处（值轴），或数据点属于那个类别（类别轴）。您可能需要在运行阶段使用代码，或在设计阶段使用属性工具箱调整这些标签的一些外观。
对于值轴来说，标签显示了轴上全部值刻度的数字点。这些数字最初都没有被格式化，除非数据源是一个单元格包含了明确的数字格式化信息的电子表格。为了改变缺省的数字格式，可以将轴的NumberFormat属性设置为一个新的数字格式名称或者格式字符串。您可以使用的格式名称的列表位于电子表格控件的帮助文件（通过点击电子表格上的帮助按钮可以打开这个文件）中的“电子表格中的数字格式化”主题下。您也可以象在Excel中一样创建一个自定义的格式字符串，在自定义数字格式中可以使用的符号可以在Excel2000的帮助文件中找到，相关的概要主题标题是“创建一个自定义的数字格式。”
对于值轴和类别轴，您都可以选择丢弃一些标签――例如，每五个标签中只显示第五个。这对于那些包含太多标签的轴来说很有用，可以使轴上的标签不至于重叠，还有对于那些可以从保留的标签中推导出那些被丢弃的标签的图表来说，这也很有用，例如，一系列的日期。 类别轴缺省是不取消标签的。但是假设您在类别轴上包含一大组的时间。您可以通过设置相应WCAxis对象的TickLabelSpacing属性来选择取消一部分标签，即可以采用在设计阶段选择标签并使用属性工具箱的方式来完成，也可以通过运行阶段的代码来完成。这个属性的设置决定了在显示的标签之间要跳过多少标签。注意这个属性只影响标签，这意味着轴仍然会在标签应该显示的地方显示标记符号。不过，您也可以通过将TickMarkSpacing属性调整为TickLabelSpacing属性相同的值来减少一些标记符号。
图例
缺省情况下，新创建的图表不包含图例。如果您计划显示多个系列的数据点，您可能需要为您的图表添加一个图例，以说明哪种颜色对应哪个系列。既可以在设计阶段通过选择图表，并使用属性工具框来添加图例，也可以在运行阶段通过代码将WCChart对象的HasLegend属性设置为True来添加图例。
最初，图例缺省为每个系列包含一项，但是您可以改变它。为了隐藏一个图例的项，使用WCLegend对象的LegendEntries集合来获得您需要隐藏的图例项，然后将获得的对象的Visible属性设为False。在设计阶段，您可以简单的选择图例项，并通过按Delete键来删除它。
如果您在图表控件中包含了多个图表，您可以为每个单独的图表创建一个图例，或者您也可以为整个控件创建一个图例。例如，如果您在显示多个饼图，可能为所有的饼图只显示一个图例更有意义，因为颜色/类别的映射对每个图表都是一样的。如果一组系列在各图表之间的显示是不相同的，那么这些在图例中显示系列的图表类型能从独立的图例中获益。
对于在图例中显示系列的多个图表来说（除了饼图，堆积饼图和圆环图之外的图表），为它们使用同一个图例有一个相当恼人的问题：图表控件在初始化图例时，会为每个图表中每个出现的系列在图例中添加一项。如果您在五个图表中显示两个系列，图表空间图例会包含10个图例项，相同的两项重复了五次。删除额外的图例项的方法和前面提到的一样，可以在设计阶段选择并删除它们，或在代码中使用WCLegendEntry对象的Visible属性。
附录：英文原文
Chapter 3
The Chart Component
Now that you have an understanding of the Spreadsheet component, let's examine the functionality and programming model of the Chart component. As in the previous chapter, after describing a given feature, I will present a few interesting uses for it. In Part II of the book, you will see many of these ideas actually implemented.
This chapter will also show you some clever ways to add functionality to the Chart component using your own code. The Chart component might seem a bit less complex than the Spreadsheet or PivotTable components at first, but it has some powerful features that you can use in your solutions. Plus, it offers many "knobs" that you can adjust to customize a chart to your liking.
The Basics of the Chart Component
The Chart component is a COM control that was built by the same team that developed charting in Microsoft Excel 2000. It provides basic business charting both as an onscreen COM control and as an in-memory, GIF-generating engine. In this first version, the Chart control supports all the two-dimensional chart types found in Excel (except for the Contour type) with the addition of the Polar, Stacked Pie, and Filled Scatter chart types. This version does not contain any three-dimensional chart types or effects.
One new feature that I will discuss in this chapter is the Chart control's ability to display more than one chart within the overall chart space of the component. The Chart control is actually composed of an entire chart space containing one or more charts that all share the same set of categories. (I will explain the term "categories" in more detail later in this section.) Normally, you will have only one chart in the control at a time; however, having multiple charts in the same chart space makes it easier to compare similar information at a glance. I will describe chart spaces in more detail toward the end of the chapter.
Like the Spreadsheet component, the Chart component has a number of basic features and some distinct terminology that you should become familiar with before we move on to more advanced topics.
The Nomenclature of Charting
During the development of a new feature in Excel 2000 charting, the OWC team conducted a number of usability tests to determine whether our design was easy to use. For those who have never heard of such tests, we take real customers from companies and homes throughout the Seattle area and put them in front of a prototype for a new feature. We ask the people to perform a number of tasks and watch to see how they approach them and whether the design was effective in helping them accomplish the assignment. Often, we find that our expectations were far from reality—meaning we have to go back and redesign the feature.
In one particular test, we showed people various charts we created in Excel with certain parts circled. We asked them to tell us what they thought the name of the circled part should be. Logically, you would expect us to find some commonality and discover the "name" that most people already associate with a particular element. We could then use that name in our documentation, programming models, onscreen user interface, and so on. Much to our dismay, we discovered absolutely no standard names for elements within a chart. You might expect that people would know which is the X axis and which is the Y axis, but many people don't remember much from their math classes and commonly mix them up.
NOTE
For those whose minds are now racing to remember which is which, the X axis is horizontal and the Y axis is vertical. Of course, most charts have what's called a category axis and a value axis, and their orientation depends on the particular chart type. Scatter and Bubble charts have X and Y axes since they compare two (or three) values against each other. In chart types such as Radar or Polar, the axes actually extend from the center of the chart, so X and Y are meaningless.
Since no common language seems to exist for describing elements of a chart, it is quite hard to talk about creating and manipulating charts. To gain any understanding of the Chart component's features and programming model, we must first define a number of key terms used in the component and look at what elements they represent. Many of the terms have specific connotations in different chart types, so if you see a chart type in the following discussion that you are not familiar with, refer to the "Supported Chart Types" for a screen shot and a description.
Series
A series is one of the most important constructs of the Chart component. In fact, most of the internal structures in this component are oriented around the series. Figure 31 points out the series in a Column chart. Notice that each series correlates to an entry in the chart's legend.
[url=http://dev.csdn.net/javascript:fullSize(]
Figure 3-1. A Column chart containing two series.
A series represents a sequence of data points that you want to display in a certain manner. People commonly think that a chart has a particular type (such as a Line chart, Bar chart, or Pie chart). But in the Chart control, it's the series that has a particular type—which means you can create a combination chart by setting one series as a Line type and another as a Column type. All the data points in a series commonly have the same color (though you can override this, as we will see later). Plus, elements such as trendlines and error bars are attached to particular series.
By default, an entry in the legend exists for each series, but you can hide specific entries in the legend if you want. (I will explain how to do that later.)
Categories
Categories are a little harder to explain than series. Figure 3-2 points out the category labels contained in a Column chart.
[url=http://dev.csdn.net/javascript:fullSize(]
Figure 3-2. Categories in a Column chart.
All charts have a notion of categories, but not all charts have a category axis. In Figure 3-2, the sales representatives' names are the categories, and each series contains a single data point in each category. In most charts, the intersection of a category and a series creates a data point. Note, however, that a particular series might not have a data point for a given category although the other series do. When this is the case, the Chart control merges all categories from all series and simply does not plot a data point at that series-category intersection.
Category axes differ from value axes in three important ways:
There is no inherent ordering of categories.
There is no minimum and no maximum category.
Data points are neatly assigned to a specific category.
If Salesperson is your category axis, specific sale amounts are naturally attributed to specific salespeople, and no data points sit between salespeople. A value axis has a defined minimum and maximum, and the space along the axis is evenly divided into units that increase as you move from the minimum to the maximum. Each data point is therefore plotted wherever it lies along the axis.
A Scatter or Bubble chart does not have a category axis because its data points are defined by X and Y coordinates as well as a bubble size for Bubble charts. Although these chart types do not have category axes, the data points can still belong to specific categories and you can retrieve the category name for a given data point. This mechanism is useful for encoding extra information into the data points of a Scatter or Bubble chart, allowing you to display that information when the user's mouse hovers over a data point.
NOTE
Of course, in the real world, sales often can be attributed to more than one sales representative. However, most sales information systems perform an allocation and store each representative's contribution to the sale as a specific value that is then plotted on the chart. The point about data fitting neatly into a category is made to contrast discrete categories from continuous values. For example, a value of 1.4567454 can be plotted along a value axis between 0 and 2, but it does not fit into a discrete "bucket" on the axis the way data in categories do.
Values, Values, Values
In most of the simpler chart types, you have only one set of values to worry about. Figure 3-3 shows the sales values plotted by year by salesperson. This chart contains only one value per data point—which holds true for most of the simple chart types.
[url=http://dev.csdn.net/javascript:fullSize(]
Figure 3-3. Values in a Column chart.
Scatter and Bubble charts, on the other hand, introduce the necessity for having two or three values for every data point. In a Scatter chart, each data point has an X value and a Y value, the combination of which defines an (X,Y) point in two-dimensional, Cartesian space. A Bubble chart adds a third value: a bubble size value that determines the radius of the bubble centered at the (X,Y) point. Optionally, the bubble size value can be set to represent the area of the bubble instead of the radius.
Things get a little more complicated when using a High-Low-Close (HLC) chart because again three values make up each data point, except that they are now called the high value, low value, and close value. An Open-High-Low-Close (OHLC) chart has four values associated with each data point, and the extra value is called, not surprisingly, the open value. (These types of charts are often called Stock charts because they are most commonly used to display such data.)
When dealing with a Polar chart, you need to provide another set of values: R values and Theta values. The R value defines the distance of the data point from the center of the chart, and the Theta value represents the angle away from a horizontal line passing through origin.
Axes
The term "axes" is probably more familiar to you. In the Chart component, axes have essentially the same meaning that they do in the world of geometry. However, the Chart control adds another layer of meaning to an axis by referring to it either as a category axis or a value axis. In Figure 3-4, the category and value axes are labeled.
[url=http://dev.csdn.net/javascript:fullSize(]
Figure 3-4. Category and value axes in a Column chart.
A category axis is subdivided into equal segments—one for each distinct category, and data points are plotted in the middle of each category. No notion of a minimum or maximum exists for a category axis. A value axis, on the other hand, is a continuous axis that has a minimum and a maximum. Along a value axis, data points are plotted where they would fall between the minimum and maximum points.
Since the placement of the category and value axes depends on the chart type, "category" and "value" are logical names that do not directly map to X and Y. For example, a Column chart has the category axis extending horizontally along the bottom (X) and the value axis rising vertically on the left (Y). But in a Bar chart, the placements are reversed, with the category axis on the left and the value axis along the bottom. For more on the various chart types, see "Supported Chart Types."
Scaling
Although you have certainly heard the term "axis," you might not have heard the term "scaling" before. Every axis has a scaling, though you commonly use a scaling with a value axis. A scaling defines a measurement scale for its axis, determining the minimum and maximum values for the axis. The scaling also determines whether the intervals along the axis are linear or logarithmic. A linear scale subdivides the axis into even segments that increase linearly from the minimum value to the maximum value (for instance, 20, 40, 60, 80, 100). A logarithmic scale also divides the axis into even segments; however, the increment from one segment to the next is logarithmic instead of linear (for example, 1, 10, 100).
We will discuss axis scaling in more detail later in the chapter when we talk about split axes, as well as in Chapter 6, where we will see how to "zoom in" to show a portion of your data in more detail.
Trendlines
The Chart component supports the creation of one trendline for each series in your chart. As in Excel, a trendline is used to show the trend of data in a series. Trendlines are commonly used in trend analysis and forecasting when you want to predict what a certain value will be in the future if it keeps increasing or decreasing at the historical rate. Like Excel, the Chart control offers a few different trendline calculations, including linear, logarithmic, polynomial, exponential, and power. However, the Chart control does not offer the moving average trendline type found in Excel. Figure 3-5 shows an example of a trendline.
Error Bars
Error bars, shown in Figure 3-6, are short line segments extending from the data points that indicate some uncertainty about your data, known as the error amount. Each data point can display an error bar, which indicates that the data point's true value can be anywhere within the error range.
Why a Separate Scaling Object?
You might be wondering why the concept of scaling is not just part of an axis. After all, it seems that the scaling determines the minimum and maximum values of an axis. However, the scaling's minimum and maximum values determine the dimensions of the viewable region called the plot area. An axis displays tick marks and labels, but it is the scaling that determines the exact pixel/value ratio for that dimension of the plot area. By separating the scaling from the axis, the Chart control can support charts that have no visible axes.
[url=http://dev.csdn.net/javascript:fullSize(]
Figure 3-5. A trendline in a Scatter chart.
[url=http://dev.csdn.net/javascript:fullSize(]
Figure 3-6. Error bars in a Column chart.
Like trendlines, error bars are attached to a series. An error bar is displayed for each data point in the series, and the collection of error bars for that series can be set to show a positive error amount, a negative error amount, or both. The error amount can be expressed as a percentage (such as +10%, -10%, or +/-10%), a relative value (such as +2, -2, or +/-2), or a custom amount (for example, an upper bound of 12 and a lower bound of 8 for a data point of 10). Error bars can also be data-bound, in which case the Chart control treats the values in the result columns as custom error values for each data point. Although Excel charting also provides Standard Error and Standard Deviation options for the error amounts, the Chart control does not yet natively support these. Of course, you could calculate these values yourself and use custom error amounts to display them in the chart.
Data Labels
A data label is a small piece of text placed next to a data point that you can set to display the data point's value, percentage in the series, category name, series name, or bubble size. (See Figure 3-7 for an example.) You can display any combinations of these pieces of information in a data label. Plus, you can control the font, color, and border formatting attributes—even which separator character string to use between each piece of information.
[url=http://dev.csdn.net/javascript:fullSize(]
Figure 3-7. Data labels in a Column chart.
Data labels are obviously useful when you want to show numbers next to your data points, especially for comparing data points that might be very close to each other. The percentage contribution is of course useful any time you want to show the percent a data point contributes to the overall series, such as in a Pie chart. Showing the category name is a nice way to display extra categorical information in chart types that do not have category axes, such as Scatter and Bubble charts.
Like trendlines and error bars, data labels are attached to a series. You cannot format, hide, or show a data label for an individual data point. All manipulations to data labels affect all data points in the series.
Supported Chart Types
The first question I usually hear from a developer's mouth when I speak about the Chart component is, "What chart types are supported?" Chart types are the bread and butter of charting, so the more types the merrier. I have a reference book on my shelf that contains almost 450 pages of different chart types, chart elements, and information graphics techniques! (This book, Information Graphics: A Comprehensive Illustrated Reference [Management Graphics, 1997], is an excellent resource for anyone involved in information graphics, as are any of Edward Tufte's books.)
The first version of the Chart component includes the full set of two-dimensional chart types found in Excel 2000 (except for the Contour type), with the addition of Polar, Stacked Pie, and Filled Scatter chart types. The Chart control does not have any three-dimensional chart types or effects in this version, nor does it support the fancy fill effects offered in Excel.
Let's look at several examples of the supported chart types and discuss what kind of data they are useful for displaying.
Column and Bar Charts
The most typical chart types used in business, Column and Bar charts, show a filled bar for each data point, extending from the zero point on the value axis to the data point.
Most people don't make much of a distinction between Column and Bar charts—after all, they are essentially the same, they just extend in different directions. The Chart component uses the term "Column" to refer to a vertical column that extends up and down the screen and the term "Bar" for a horizontal bar that extends across the screen. Figure 3-8 shows an example of a Column chart and a Bar chart.
[url=http://dev.csdn.net/javascript:fullSize(]
Figure 3-8. A Column chart (top) and a Bar chart.
These chart types typically are useful for data containing categories that do not need to appear in any particular order. Unlike Line charts, Column and Bar charts don't portray a sense of order or progression.
As with many of the chart types, there are a few Column and Bar chart subtypes. The default subtype, called Clustered Column or Bar, plots bars from different series adjacent to each other within each category. (Figure 3-8 shows a Clustered Column chart and a Clustered Bar chart.) The Clustered subtype is the most useful subtype when the different series are fairly unrelated or when they should not be aggregated visually. For example, if you plot a budget in one series and the actual amount spent in the other, you do not want to aggregate those values. Instead, you would want to compare the bars or columns side by side.
Stacked Column and Bar charts display the different series as stacked upon one another. In such charts, the length of the column or the bar represents the sum of the data points for the category. Figure 3-9 shows an example of a Stacked Column chart.
[url=http://dev.csdn.net/javascript:fullSize(]
Figure 3-9. A Stacked Column chart.
Stacked Column and Bar charts are useful for displaying data in which the series' values can and should be aggregated to depict a visual total for each category. For example, if you plot sales information by country and by product, you might want to use a Stacked Column chart to show the total sales for each country (the category) across all products. The bar is still segmented by the exact value each product contributes—meaning top-selling products will have longer segments, while products that do not sell well will have shorter segments. Stacked charts are useful when it is not as necessary to assess the relative contribution as it is to assess the total for each category.
Finally, the 100% Stacked subtype is a bit like a Pie chart: it draws a bar or column all the way across the plot area and then subdivides the bar or column into segments representing the percent contribution of each series' data point. The key difference between this subtype and the Stacked subtype is that the length of each segment is the percentage of the data point's contribution to the total of the data points in that category, not the literal value. Since all the bars are the same length (100%), such a chart is not useful for comparing one category's total to another. However, this type of chart is useful for viewing the same type of information a pie chart shows, but for many categories and series at once.
Most of the other chart types described here have the same set of subtypes—Clustered, Stacked, and 100% Stacked. I will not redefine each of these three subtypes in the descriptions that follow, but I will indicate when they are available. Refer back to this section for a description of the subtypes and which types of data are appropriate to display using them.
Pie, Stacked Pie, and Doughnut Charts
Pie charts are also common in business charting, which is almost a shame since they provide the least dense (and the least efficient) display of information available. However, their simplicity also makes them very understandable, and often, very persuasive. For example, when showing a breakdown of market share information, the effect of an extremely large or small slice is indeed powerful. Figure 3-10 shows a typical Pie chart.
[url=http://dev.csdn.net/javascript:fullSize(]
Figure 3-10. A Pie chart.
The important quirk of Pie charts is that the legend shows the category values instead of the series values. Most charts show the various series in the legend, but since a Pie chart shows only one series, the legend is used to show the category labels that correspond to the colored pie "slices."
A Pie chart is obviously useful for showing the percentage contribution or breakdown of a total. A Pie chart shows only one dimension of data because, as stated a moment ago, it can display only one series of data points.
The Stacked Pie and Doughnut chart types, however, can show multiple series at once, much like the 100% Stacked Column chart can display data for many series and categories at once. The only real difference between the Stacked Pie chart and the Doughnut chart is that the Doughnut chart has a hole in the middle (the "doughnut hole" if you will). Figure 3-11 depicts the same information in first a Stacked Pie chart and then a Doughnut chart.
Figure 3-11. A Stacked Pie chart and a Doughnut chart.
I admit that these are somewhat bizarre chart types; in fact, I recommend using them only in those unique circumstances under which no other chart type will suffice (for example, displaying the percentage chemical makeup of soil in concentric rings around a bomb explosion site). The concentric circles can be misleading for abstract data because their relative size and order are not based on any numeric value.
Line, Smooth Line, and Area Charts
Line charts and Area charts fall within the group of simpler chart types but unfortunately are not used as often as they should be. Figure 3-12 shows what a typical Line chart and Area chart look like.
Line and Area charts are useful for displaying data in which the categories actually have a meaningful order, such as a series of dates or times. For example, plotting sales over a series of dates or plotting stock prices over a series of hours is more effectively displayed in a Line chart than in a Column chart. This is because it is easier to tell whether there's a trend up or down when lines are drawn between the data points in a chart.
The only real difference between a Line chart and an Area chart is that in the Area chart, the section between the category axis and the line is filled with the series color. The occlusion that occurs when one series' values are higher than another's can make Area charts somewhat difficult to work with—unless you are using the Stacked subtype we discussed earlier. Since the series are drawn over each other in order, the last series drawn will cover any series previously drawn. Use nonstacked Area charts only when you know that a series has consistently higher values than all the series that follow it.
[url=http://dev.csdn.net/javascript:fullSize(]
Figure 3-12. A Line chart and an Area chart.
As in the Column and Bar chart cases, Line charts and Area charts have the Clustered, Stacked, and 100% Stacked subtypes. However, the term "Clustered" is not commonly used to describe the default subtypes, and these defaults are simply called Line chart and Area chart without any special distinction.
Line charts have one other subtype that Column and Bar charts do not. Lines in a chart can be drawn either "straight" or "smoothed." It should come as no surprise that charts drawn with smoothed lines are called Smooth Line charts. Instead of drawing the line straight from one data point to another, the Chart component draws the line on a curve so that there are no jagged peaks or valleys.
Scatter and Bubble Charts
Scatter charts are used less often in business presentations, which is unfortunate considering that they can be a more powerful analysis tool than the simpler chart types described earlier. Although a Scatter chart has series and categories, it also has two values (rather than one) that determine the location of a data point. Each data point in a Scatter chart has an X value and a Y value, and the combination of the two determines its placement on the plot area.
The key difference between a Scatter chart and a Bubble chart is that the data points in a Bubble chart are circles that have dynamic size. A data point in a Bubble chart contains a third value called bubble size, which determines either the radius or the area of the bubble. Figure 3-13 shows typical Scatter and Bubble charts.
[url=http://dev.csdn.net/javascript:fullSize(]
Figure 3-13. A Scatter chart and a Bubble chart.
Scatter charts and Bubble charts are useful for comparing two different values to discover a correlation or distribution pattern. For example, using a Scatter chart to plot a department's morale budget allocation against its revenue might reveal a strong correlation, showing that a high morale budget commonly increases revenue. (At least, most employees would like to think this is true!)
Scatter charts have a few unique subtypes. The default subtype is simply called Scatter Markers and uses markers (small geometric shapes such as diamonds or squares) to indicate the data points. Different shapes are used to indicate the different series. You can choose to connect the markers of each series with a smoothed or straight line. Furthermore, you can choose to have lines without any markers at all. The Chart component includes a final subtype that is not included in Excel. It allows you to fill in the polygon made from the data points and connecting lines, creating a Filled Scatter chart.
Fun with Filled Scatter Charts
You can try a rather creative Filled Scatter chart demo on the companion CD. Open the DrawWithChart.htm file in the Chap03 folder, and click the chart surface to create points of a filled polygon. Double-click the mouse to end the shape. This demo was written by Jeff Couckuyt, one of the extremely talented Chart component developers, and was created using only the Chart control and a Filled Scatter chart type.
Keep in mind that Bubble charts have the same problem of occlusion as Area charts. A large bubble will hide any data points underneath it, so only use Bubble charts when you know the chance of occlusion is low; otherwise, consider setting the bubble fill to transparent.
Radar Charts
Radar charts do not seem to be typically used in the United States, but I understand that they are much more common in Asian countries for portraying data such as nutritional information about food products. Figure 3-14 shows what this interesting and useful chart type looks like.
[url=http://dev.csdn.net/javascript:fullSize(]
Figure 3-14. A Radar chart.
A Radar chart has categories, series, and values like the other simple chart types; however, Radar charts plot the category labels in a circle surrounding the chart and contain spokes extending from the center of the chart out to each category label. Each spoke is a value axis. The data points for each category are plotted on the corresponding spoke's scale at the appropriate point and in the appropriate series color. The chart then joins the data points of each series with a line and optionally fills the series color from the line toward the origin. Filled Radar charts have the same old problem of occlusion that Area and Bubble charts have, so beware of using the Filled subtype unless you know that your data will not cause occlusion (or unless you are not concerned about it).
Radar charts also support the Smooth Line subtype we discussed earlier. Plus, you have the option of plotting data point markers in both the Smooth Line and Straight Line subtypes.
High-Low-Close and Open-High-Low-Close Charts
Anyone displaying information about stocks or financial securities will be interested in these two chart types. The High-Low-Close chart type (or HLC chart) displays a line segment for each category. Each line segment extends from the low to the high value and features a small tick mark that denotes the close value. Figure 3-15 shows an example of an HLC chart.
[url=http://dev.csdn.net/javascript:fullSize(]
Figure 3-15. A High-Low-Close chart.
You should consider using this chart type any time you have data containing a range of values for a given period and a special value that needs to be marked within the range. For example, you could use an HLC chart to display temperature readings over an extended period of time.
An Open-High-Low-Close chart (or OHLC chart) adds one extra piece of information to the data of an HLC chart: the open value. The chart indicates the open value by displaying a filled rectangle between the open value and the close value, as Figure 3-16 shows.
The filled rectangle either will be the series color or it will be black, depending on whether the difference between the close value and the open value is positive or negative. Positive differences get the normal series color, while negative differences are shown as black. This shows the viewer whether the values increased or decreased during the specified period so that he or she can ascertain whether the value improved or worsened and by how much.
[url=http://dev.csdn.net/javascript:fullSize(]
Figure 3-16. An Open-High-Low-Close chart.
Both the HLC and the OHLC chart types still maintain the notion of series. However, in an HLC chart type, the Chart component places different series in the same horizontal position. In other words, the control will draw multiple series over one another. Generally, this chart type is most useful with only one series of data points. OHLC charts, on the other hand, can manage multiple series and draw the bars of multiple series next to one another.
Polar Charts
The Polar chart type is the one new chart type that appears in the Chart component but not in Excel charting. Polar charts, which Microsoft Office users have been requesting for some time, are useful for displaying relationships between angles and distances. Polar charts are commonly used in the audio and radio fields, for example, to show the power and direction of a microphone's pickup. Figure 3-17 depicts a typical Polar chart.
Figure 3-17. A Polar chart.
The Polar chart includes the Smooth Line subtype, and you can choose whether to display data point markers with the lines.
Combination Charts
As mentioned at the beginning of the chapter, one of the great secrets of charting is that a chart does not really have a chart type. Instead, each individual series has a type, and if all series in a chart happen to have the same type, the Chart object's Type property returns that type. However, you can use this distinction to create more complex combination charts, in which you plot some series as columns or bars while plotting others as lines.
Not all chart types can be combined, and in this version of Office the Chart component allows you to combine only the Column, Line, and Area chart types. The most common combination chart used for business data is a mixture of the Column and Line chart types.
Loading Data
Now that you know what the various chart elements are called and what chart types the Chart control can display, you need to learn how to load a chart with data. Like most of the Office Web Components, the Chart control can load data from a variety of sources, and the loading can be performed by using the Chart Wizard in a design environment or by writing code. The Chart component binds to all the other Office Web Components—the Spreadsheet, PivotTable, and Data Source components—as well as to all other controls that implement the IDataSource interface (the standard interface for a data source control in Microsoft Internet Explorer or Microsoft Visual Basic, documented in the Microsoft Developer Network Libraries and the OLE DB SDK), ADO Recordset objects, and even literal arrays or delimited strings of data.
The general approach to loading data into the Chart control is to tell the chart where it should retrieve data from and what "parts" of the data source should be used for the series, categories, and values the current chart type requires. In the programming model, the Chart control refers to these chart elements as dimensions that you can bind to some part of the data source. When binding to a spreadsheet, the "part" of the data source you specify is a range reference, such as A1:C1. For an OLE DB data source, you specify what column name or ordinal index of the resultset to use. For the PivotTable control, you specify which pivot axis to use. (We will discuss this further in Chapter 4.) In literal data, only one "part" exists: the array or delimited string itself.
The Chart Wizard performs much of the binding for you, presenting you with a simple user interface for specifying this information. However, the Chart Wizard merely calls the public programming model of the Chart control, so by writing your own code you can do anything the Chart Wizard does—and more. The Chart Wizard is fairly self-explanatory, and its online help covers much of its use. Since this book focuses on developing custom solutions with the Office Web Components, I will not detail using the Chart Wizard here. Instead, I'll dive into the code you need to write when programmatically filling the chart with data.
Loading the Chart with Literal Data
The following subroutine, taken from the LoadFromLiteral.htm file in the Chap03 folder on your companion CD, shows how to load the Chart control with literal data:
'------------------------------------------------------------------------
' LoadChartWithLiteral()
' Purpose: Loads the chart with literal data
' In: cspace reference to the ChartSpace object
' vSeries variant array or
' tab-delimited string of series names
' vCategories variant array or
' tab-delimited string of category names
' avValues array of variant array or
' tab-delimited string of values;
' one entry in the outer array per series
'
Sub LoadChartWithLiteral(cspace, vSeries, vCategories, avValues)
' Local variables
Dim cht ' Chart object we'll create in the chart space
Dim ser ' Temporary series
' Grab the Constants object so that we can use constant names in
' the script. Note: This is needed only in VBScript -- do not include
' this in VBA code.
Set c = cspace.Constants
' Clear out anything that is in the chart space
cspace.Clear
' Create a chart in the chart space
Set cht = cspace.Charts.Add()
cht.HasLegend = True
' Now call SetData to bind the various dimensions
' Second parameter is c.chDataLiteral, meaning the last parameter
' is a variant array or a tab-delimited string
cht.SetData c.chDimSeriesNames, c.chDataLiteral, vSeries
cht.SetData c.chDimCategories, c.chDataLiteral, vCategories
' When loading the chart with literal data, you must
' load each series with values individually
For each ser In cht.SeriesCollection
ser.SetData c.chDimValues, c.chDataLiteral, avValues(ser.Index)
Next 'ser
End Sub 'LoadChartWithLiteral()
When loading the chart with literal data, the data can be contained either in an array of variants or in a tab-delimited string, each element or token representing a different value. In the file on your companion CD, I pass the literal data as an array using the Array function that is supported in Microsoft VBScript as well as in Microsoft VBA.
The SetData method is used to pass the literal data, but note that the second argument (normally the data source index) is the constant chDataLiteral. This constant, which is equal to -1, tells the chart that the next argument is literal data and not part of a data source.
Also note that you must use the SetData method of the WCSeries object (the object representing a series) when passing literal values to the chart. Since the Chart control itself can accept only a one-dimensional array of values, if it allowed you to pass literal values to the SetData method of the WCChart object (the object representing a chart), it would have no way of knowing which values belong to which series. The previous procedure handles this by simply looping through the series collection and passing the appropriate array of values to the current series' SetData method.
Binding to the Spreadsheet Component
The following subroutine, taken from the LoadFromSpreadsheet.htm file in the Chap03 folder on your companion CD, shows how to bind a Chart component to ranges in the Spreadsheet component:
'-------------------------------------------------------------------------
' BindChartToSpreadsheet()
'
' Purpose: Binds a chart to specified ranges in the source spreadsheet
' In: cspace reference to the ChartSpace object
' sheet reference to the Spreadsheet object
' srngSeries string-based range reference to where the
' series names come from
' srngCategories string-based range reference to where the
' category names come from
' srngValues string-based range reference to where the
' values are
'
Sub BindChartToSpreadsheet(cspace, sheet, srngSeries, srngCategories, _
srngValues, fSeriesInCols)
' Local variables
Dim cht ' Chart object that we'll create in the chart space
Dim ser ' Temporary series
Dim rngValues ' Range object of values
' Grab the Constants object so that we can use constant names in
' the script. Note: This is needed only in VBScript -- do not include
' this in VBA code.
Set c = cspace.Constants
' Clear out anything that is in the chart space
cspace.Clear
' First tell the chart that its data is coming from the spreadsheet
Set cspace.DataSource = sheet
' Create a chart in the chart space
Set cht = cspace.Charts.Add()
cht.HasLegend = True
' Now call SetData to bind the various dimensions
' Second parameter is zero, meaning the first data source should be
' used if there are multiple data sources
cht.SetData c.chDimSeriesNames, 0, srngSeries
cht.SetData c.chDimCategories, 0, srngCategories
' The spreadsheet can bind to one-dimensional ranges only,
' so loop through the series collection and set the values
' for each series individually
Set rngValues = sheet.Range(srngValues)
For Each ser In cht.SeriesCollection
If fSeriesInCols Then
ser.SetData c.chDimValues, 0, _
rngValues.Columns(ser.Index + 1).Address
Else
ser.SetData c.chDimValues, 0, _
rngValues.Rows(ser.Index + 1).Address
End If
Next 'ser
End Sub 'BindChartToSpreadsheet()
I would like to point out a few things about this example. First, to bind the Chart and Spreadsheet controls, I set the Chart control's DataSource property to the instance of the Spreadsheet control. By receiving the pointer to the Spreadsheet control instance, the Chart control can now ask the Spreadsheet control for cell values in the specified ranges.
Second, the SetData method is used to bind first the Series Names dimension and then the Categories dimension. You must bind the chart dimensions in this order: series names first, then categories, then values. In the rare case in which you have only one series of information, you can skip the Series Names dimension and just bind categories and values. Doing so will create one series for you with the default name Series, which you can change by setting the Name or Caption property of the WCSeries object.
My last point is that when binding to the Spreadsheet component, the Chart component needs to receive explicit range references for the values in each series. Unfortunately, you cannot just hand the chart a two-dimensional range of values and let it automatically figure out which series and categories the values belong to. Instead, you must pass a one-dimensional range reference to the last parameter in the SetData method for each WCSeries object. The previous example does this in a generic fashion by using the Spreadsheet component's Range object to yield a range reference for each column or row in the two-dimensional range, which in turn gets passed to the series' SetData method. The fSeriesInCols flag indicates whether the values for a given series are arranged down a column or across a row in the spreadsheet. If the flag is True, the Columns collection is used; if False, the Rows collection is used.
Also note that this example adds 1 to the series' index value. This is because the Columns and Rows collections are 1-based for compatibility with Excel's programming model, while the Chart component's series index is 0-based. Adding 1 to the series index yields the corresponding column or row in the range.
Binding to the Data Source Component
The following method, taken from the LoadFromDSC.htm file in the Chap03 folder on the companion CD, shows how to bind a Chart component to a Recordset returned from the Data Source component (DSC). (We'll discuss the DSC more thoroughly in Chapter 5.)
'------------------------------------------------------------------------
' BindChartToDSC()
'
' Purpose: Binds a chart to a Recordset in the Data Source component.
' (This example creates a Pie chart.)
' In: cspace reference to the ChartSpace object
' dsc reference to the Data Source control
' sRSName name of Recordset to bind to in the
' Data Source control
' sCategories name of the result column containing categories
' sValues name of the result column containing values
'
Sub BindChartToDSC(cspace, dsc, sRSName, sCategories, sValues)
' Local variables
Dim cht ' Chart object that we'll create in the chart space
Dim ser ' Temporary series
' Grab the Constants object so that we can use constant names in
' the script. Note: This is needed only in VBScript -- do not include
' this in VBA code.
Set c = cspace.Constants
' Clear out anything that is in the chart space
cspace.Clear
' First tell the chart that its data is coming from
' the Data Source control
Set cspace.DataSource = dsc
' Next tell it what Recordset within the Data Source control
' it will bind to
cspace.DataMember = sRSName
' Create a Pie chart in the chart space
Set cht = cspace.Charts.Add()
cht.HasLegend = True
cht.Type = c.chChartTypePie
' Now call SetData to bind the various dimensions
' Second parameter is zero, meaning the first data source should be
' used if there are multiple data sources
' In this example of a Pie chart, we will add one
' series manually and use the SetData method there
Set ser = cht.SeriesCollection.Add()
ser.SetData c.chDimCategories, 0, sCategories
ser.SetData c.chDimValues, 0, sValues
' Finally, for this example, add some
' data labels since it's a Pie chart
Set dls = ser.DataLabelsCollection.Add()
dls.HasPercentage = True
dls.HasValue = False
End Sub 'BindChartToDSC()
The DSC can retrieve data from and provide data to both the Chart component and the PivotTable component. It also implements the same data source COM interface (IDataSource) that Visual Basic and Internet Explorer data source controls implement, so this code can be used for any other valid data source control used in those environments.
The DataSource and DataMember properties of the ChartSpace object are fundamental to this example. Since they are part of the data-binding standards established by Visual Basic and Internet Explorer, you will commonly see them on other data-bound controls in those environments. The DataSource property is set to point at the control providing the data (in this case the DSC), and the DataMember property is set to a string value naming the specific data set desired. Because a DSC can expose many data sets at once, the DataMember property is used to tell the Chart control which data set to request. If you leave the DataMember property blank, the Chart control will ask for the default data set, the identity of which is determined by the DSC.
The SetData method is used in this example much the same way it was used in the BindChartToSpreadsheet method, except that the "data part" (the last parameter to SetData) is now the name of a column in the Recordset returned from the DSC. Alternatively, this data part can be the ordinal index of the column (0, 1, 2, and so on). The Chart control will pass this to the ADO Fields collection object's Item method, so any value that is valid for that method can be used as the last parameter to the SetData method. Typically, you use column names if you don't expect them to change over time but do expect the ordinal positions to change; you use ordinal indexes if you don't expect them to change but do expect the column names to change.
As stated earlier, an interesting twist of Pie charts is that the entries in the legend are categories, not series. Therefore, when binding a Pie chart, you should bind the column you want to appear in the legend to the Categories dimension rather than the Series dimension. This also applies to Stacked Pie charts and Doughnut charts.
The last code block in this example creates some data labels (which we just discussed) to show the percentage each data point contributes to the whole. The Chart control can show a number of values for each data point, and by default the actual number value is displayed. Since I wanted to show only the percentage contribution of each slice, I set the HasPercentage property to True and HasValue to False.
Although this particular example creates a Pie chart, you can use any of the supported chart types when binding to the DSC. I chose to create a Pie chart in this example only to show how binding is performed using the Pie chart type.
Binding to a Recordset
The following function, taken from the LoadFromRecordset.htm file in the Chap03 folder on the companion CD, shows how to bind a Chart control to an ADO Recordset object:
'------------------------------------------------------------------------
' BindChartToRecordset()
'
' Purpose: Binds a chart to a Recordset. (This example creates a
' Scatter chart.)
' In: cspace reference to the ChartSpace object
' rst reference to Recordset object to bind to
' sfldSeries name of the series field
' sfldXValues name of the field containing the X values
' sfldYValues name of the field containing the Y values
'
Sub BindChartToRecordset(cspace, rst, sfldSeries, sfldXValues, _
sfldYValues)
' Local variables
Dim cht ' Chart object that we'll create in the chart space
Dim ser ' Temporary series pointer
Dim ax ' Temporary axis pointer
' Grab the Constants object so that we can use constant names in
' the script. Note: This is needed only in VBScript -- do not include
' this in VBA code.
Set c = cspace.Constants
' Clear out anything that is in the chart space
cspace.Clear
' First tell the chart that its data is coming from the Recordset
Set cspace.DataSource = rst
' Create a Scatter chart in the chart space
Set cht = cspace.Charts.Add()
cht.HasLegend = True
cht.Type = c.chChartTypeScatterMarkers
' Now call SetData to bind the various dimensions
' Second parameter is zero, meaning the first data source should be
' used if there are multiple data sources
' In this example of a Scatter chart, we will bind
' two value dimensions: X and Y
cht.SetData c.chDimSeriesNames, 0, sfldSeries
cht.SetData c.chDimXValues, 0, sfldXValues
cht.SetData c.chDimYValues, 0, sfldYValues
' Finally, let's add some axis labels using
' the column names as the axis captions
Set ax = cht.Axes(c.chAxisPositionBottom)
ax.HasTitle = True
ax.Title.Caption = sfldXValues
ax.Title.Font.Name = "Tahoma"
ax.Title.Font.Size = 8
ax.Title.Font.Bold = True
ax.NumberFormat = "#,##0"
Set ax = cht.Axes(c.chAxisPositionLeft)
ax.HasTitle = True
ax.Title.Caption = sfldYValues
ax.Title.Font.Name = "Tahoma"
ax.Title.Font.Size = 8
ax.Title.Font.Bold = True
ax.NumberFormat = "$#,##0"
' Let's also set the marker size a bit smaller than normal
For Each ser In cht.SeriesCollection
ser.Marker.Size = 5
Next 'ser
End Sub 'BindChartToRecordset()
You will no doubt quickly notice that this example is similar to the previous example involving the DSC, except that this example uses a Scatter chart. This is because the ADO Recordset object is itself a valid data source in Visual Basic and Internet Explorer, and it implements the same data source interface as the DSC. This makes it possible to set the Chart control's DataSource property to point to the Recordset object just as you would set it when using the DSC. However, the Recordset object by definition has only one data set to expose, so the DataMember property does not need to be set when binding to it.
Because the chart type used in this example is a Scatter chart, the code sets two value dimensions: X Values and Y Values. As described earlier in the section on supported chart types, a Scatter chart uses two values for each data point, so we need to bind the X Values dimension to one column in the Recordset and the Y Values dimension to another column. (You could bind them to the same column, but that would make for a highly correlated Scatter chart!)
An Accident of Good Architecture
When I sent this chapter to the Chart component developers for review, one of them commented that the component does not actually "support" loading from a Recordset and that the OWC team had not officially tested this scenario. However, almost all the demos and real pages we wrote internally use this method, so in actuality, this scenario was quite well tested.
The reason that loading the Chart component from a Recordset works is an accident of good architecture. When the Visual Basic and Internet Explorer development teams selected the standard data source interface (IDataSource), the ADO team decided that it made sense for the Recordset object to implement this interface since it could easily return the underlying IRowset interface from the GetDataMember method. Because the Chart control uses IDataSource when loading data from a DSC, it all just worked. To the Chart control, the Recordset object looks like any other data source control.
When binding to a Recordset object, you must ensure that the Recordset is using the Microsoft Windows Cursor Engine (WCE) or is capable of being sorted and scrolled. The WCE is an ADO component that provides scrolling, sorting, filtering, and more on any OLE DB Rowset regardless of the Rowset's source or native capabilities. To use this engine, set the CursorLocation property of your ADO Connection or Recordset object to adUseClient, which has a value of 3 if you are in an environment that does not recognize constants. To ensure that the Chart control can scroll around the Recordset, you can use the adOpenStatic cursor type, which also has a value of 3. Use adOpenStatic or 3 for the CursorType parameter of the Recordset's Open method or for the CursorType property of the Recordset object. (Chapter 4 will discuss the WCE in more detail.)
For an example of setting the necessary Recordset properties, see the full source listing in the LoadFromRecordset.htm file on the companion CD. When viewing this file, note the use of the column names for the axis captions. Scatter charts have two value axes (X and Y), so you should give those axes titles and explain what values you are showing on them. Using the column names in the Recordset can be an easy way to label these axes; of course, you can set your own captions if the column names in the Recordset are not intelligible.
Binding to the PivotTable Component
The last possible source of data for the Chart component is the PivotTable component. (We will talk more about this component in the next chapter.) As you might expect, this component also implements the same data source interface that all valid data sources expose in Visual Basic and Internet Explorer, so the following example, taken from the LoadFromPivot.htm file in the Chap03 folder on the companion CD, looks similar to the DSC example we discussed earlier but has a few important differences:
'------------------------------------------------------------------------
' BindChartToPivot()
'
' Purpose: Binds a chart to a PivotTable component
' In: cspace reference to the ChartSpace object
' ptable reference to the PivotTable object
' fSeriesInCols Boolean flag indicating whether the series
' of the chart should come from the column
' axis or the row axis of the PivotTable control
'
Sub BindChartToPivot(cspace, ptable, fSeriesInCols)
' Local variables
Dim cht ' Chart object that we'll create in the chart space
Dim ax ' Temporary axis reference
Dim fnt ' Temporary font reference
' Grab the Constants object so that we can use constant names in
' the script. Note: This is needed only in VBScript -- do not include
' this in VBA code.
Set c = cspace.Constants
' Clear out anything that is in the chart space
cspace.Clear
' First tell the chart that its data is coming from the
' PivotTable component
Set cspace.DataSource = ptable
' Create a chart in the chart space
Set cht = cspace.Charts.Add()
cht.HasLegend = True
cht.Type = c.chChartTypeBarClustered
' Now call SetData to bind the various dimensions
' Second parameter is zero, meaning the first data source should be
' used if there are multiple data sources
If fSeriesInCols Then
cht.SetData c.chDimSeriesNames, 0, c.chPivotColumns
cht.SetData c.chDimCategories, 0, c.chPivotRows
Else
cht.SetData c.chDimSeriesNames, 0, c.chPivotRows
cht.SetData c.chDimCategories, 0, c.chPivotColumns
End If 'fSeriesInCols
' Set the values dimension. The value
' you pass for the data reference (the last parameter)
' is the index of the total you want to use.
' Since there is only one total in this example,
' we pass zero, indicating the first one.
cht.SetData c.chDimValues, 0, 0
' Finally, let's add an axis title to the value
' axis, using the label on the pivot total
' as the caption, and set the number format
Set ax = cht.Axes(c.chAxisPositionBottom)
ax.HasTitle = True
ax.Title.Caption = ptable.ActiveView.DataAxis.Totals(0).Caption
Set fnt = ax.Title.Font
fnt.Name = "Tahoma"
fnt.Size = 8
fnt.Bold = True
ax.NumberFormat = ptable.ActiveView.DataAxis.Totals(0).NumberFormat
End Sub 'BindChartToPivot()
As in the earlier Recordset and DSC examples, to bind the Chart control to the PivotTable control, you start by setting the Chart control's DataSource property to point to an instance of the PivotTable control. As in the Recordset example, the PivotTable control has only one data set to expose, so you do not need to change the DataMember property from its default setting.
The critical difference between this example and the DSC example is the use of special constant values for the last parameter to the SetData method. As you will remember, this last parameter specifies which part of the data you want to bind to the specified chart dimension. In a PivotTable control, the logical parts available are the row and column pivot axes and all the totals in the view. In a PivotTable control, the row axis refers to all the labels displayed down the left side of the table, and the column axis refers to all the labels displayed across the top of the table (more on these axes in the next chapter). All the Chart control needs to know is which axis you want to bind to the Series Names dimension and which axis you want to bind to the Categories dimension.
Binding the various values dimensions of the Chart control is slightly different. Since a PivotTable report can display many totals at once, the Chart control needs to know which total you want to use for the specified values dimension. You indicate this by passing the ordinal index of the total as the last parameter to the SetData method. In the BindChartToPivot example, we use a Clustered Bar chart so that each data point has only one value. We tell the Chart control to use the total at index zero (the first one, since this is 0-based) for the data point values. Note that this is the ordinal index of the available totals shown in the PivotTable control's current view—not all the possible totals in the data source.
While the PivotTable control does implement the standard COM interface for a data source control, it does not know how to return ADO Recordsets or OLE DB Rowsets. The Chart control has special code for knowing how to read the PivotTable control's crosstab data display and properly ignore subtotals and grand totals, which if used, would skew the chart's scale. Therefore, even though the Chart control can consume data from the PivotTable control, not all data-bound controls will be able to use the PivotTable control as their data source.
Chart and Axis Titles
Charts can be effective mechanisms for displaying large quantities of information in a quick-to-assimilate visual manner. However, the data plotted in charts is rarely self-explanatory; often you will want to add a descriptive title to your chart or to the axes displayed within it.
Using Multiple Totals for Multivalued Charts
The Scatter, Bubble, Polar, and OHLC chart types all have something in common: they need more than one value to determine a data point on the chart. A Scatter chart requires both an X value and a Y value, a Bubble chart needs both those values as well as a bubble size value, a Polar chart needs Theta and R values, and an OHLC chart needs the four values its name suggests. Since PivotTable reports can show more than one total at a time, it's often desirable to map those totals to the different chart values to make a single data point.
You can do this by specifying the index of the total in the PivotTable view as the last parameter to the SetData method. For example, to bind the first total to X and the second total to Y in a Scatter chart, you would write this code:
cht.SetData c.chDimXValues, 0, 0 ' First total
cht.SetData c.chDimYValues, 0, 1 ' Second total
Sometimes you want to use those multiple totals with a chart type that uses only one value per data point, such as a Stacked Column chart. In this case, you might want the caption of the total to appear as a nested category or a nested series label. To do this, you use a special constant:
cht.SetData c.chDimCategories, 0, c.chPivotRowAggregates
This setting would use any total captions appearing on the row axis as nested category names on a hierarchical category axis. To get the results you want, you also should set the TotalsOrientation property of the PivotView object to the plTotalOrientationRow constant so that the total captions are displayed on each row.
Perhaps you want to display multiple totals as different series in the chart. To do this, leave the column axis on the PivotTable report empty, add the totals you want to appear as different series, and then write the following code:
cht.SetData c.chDimSeriesNames, 0, c.chPivotColAggregates
This code will create a series in the chart for each total in the PivotTable report. Refer to the sample file PivotTotalsAsSeries.htm in the Chap03 folder on your companion CD to see what this looks like.
If you add more than one chart to the Chart control (detailed in the section "Multiple Charts in 'Chart Space'"), you can give each chart its own title. In fact, you can give the Chart control itself a global title that will be displayed above all the individual charts. Chart titles can be formatted with all the basic font attributes (name, size, bold, italic, underline, and color). You can also set their backgrounds to a specific color or leave them transparent. The same is true for axis titles.
By default, newly created charts will have neither a chart title nor axis titles. You can add chart and axis titles either at design time using the Property Toolbox or at runtime using code. The following method, taken from the AddTitles.htm file in the Chap03 folder on the companion CD, shows how to add a chart title:
'------------------------------------------------------------------------
' SetChartTitle()
'
' Purpose: Sets the chart's title
' In: cht reference to a chart
' sTitle new title caption
'
Sub SetChartTitle(cht, sTitle)
Dim fnt ' Temporary font reference
' If the title is nonblank
If Len(sTitle) > 0 Then
' Add a title if necessary
cht.HasTitle = True
' Set the caption and its font formatting
cht.Title.Caption = sTitle
Set fnt = cht.Title.Font
fnt.Name = "Tahoma"
fnt.Size = 10
fnt.Bold = True
Else
' Title is blank. Remove it.
cht.HasTitle = False
End if
End Sub 'SetChartTitle()
You add an axis title the same way you add a chart title, except that the first parameter of the method accepts an Axis object reference instead of a Chart object reference.
Also note that you can set the Color property for the title's font or background to either an RGB color value or to one of the Internet Explorer color names—for instance, "FireBrick" or "PapayaWhip". This also applies to any use of color in the Chart control, such as the chart's background color, plot area color, series color, and so on.
Axis Labels
By default, the Chart control will include labels on all your axes to show where a data point lies on the scale (for value axes) or which category a data point belongs to (for category axes). You might want to adjust a few aspects of these labels using either code at runtime or the Property Toolbox at design time.
For value axes, the labels show numeric points on the overall value scale of the axis. These numbers initially have no formatting unless the source is a spreadsheet with cells containing explicit numeric formatting. To change the default number formatting, set the axis's NumberFormat property to a new number format name or string. The list of named formats you can use appears in the Spreadsheet control's help file (opened by clicking the Help button on the spreadsheet) under the topic, "Number formats in a spreadsheet." You can also build a custom format string just like you can in Excel, and the symbols used in custom number formats are documented in the Excel 2000 help files starting with the overview topic titled, "Create a custom number format."
For both value and category axes, you can choose to drop some of the labels—for example, showing only every fifth label. This is useful only for axes that have too many labels to show without overlapping and for which the dropped labels can be inferred from visible labels such as a series of dates. Category axes will not drop labels by default. But suppose you have a large set of dates on a category axis. You can choose to drop some of them by setting the TickLabelSpacing property of the appropriate WCAxis object, either by selecting the axis and using the Property Toolbox at design time or in code at runtime. The setting for this property determines how many labels to skip between the labels that appear. Note that this property affects only labels, meaning the axis will still show tick marks where each label would have appeared. However, you can drop some of these tick marks as well by adjusting the TickMarkSpacing property to the same value as TickLabelSpacing.
The Chart Legend
By default, newly created charts do not have a legend. If you plan on showing more than one series of data points, you might want to add a legend to your chart to explain which color maps to which series. To add a legend, either select the chart and use the Property Toolbox at design time, or set the HasLegend property of the WCChart object to True in code at runtime.
Initially, the legend will contain one entry per series by default, but you can adjust that. To hide a legend entry, use the LegendEntries collection of the WCLegend object to retrieve the legend entry you want to hide, and then set the returned object's Visible property to False. At design time, you can simply select the legend entry itself and press the Delete key.
If you have more than one chart in the Chart control, you can create a legend for each individual chart or you can create one for the control as a whole. If you are showing multiple Pie charts, for example, it probably makes the most sense to show only one legend for all the pies since the color/category mapping will be same for each chart. Other chart types that show the series in the legend can benefit from individual legends if the set of series displayed differs among charts.
One rather annoying aspect of using a single legend for multiple charts that show series in the legend (charts other than Pie, Stacked Pie, and Doughnut) is that the Chart control will initially add an entry to the legend for each occurrence of each series in each chart. If you are showing two series on five charts, the chart space legend will contain ten legend entries, the same two entries repeated five times. To eliminate the extra legend entries, select and delete them at design time or use the WCLegendEntry object's Visible property in code as described earlier.
