Professional VSTO 2005 - Visual Studio 2005 Tools For Office (2006) [eng]

Chapter 6

chartObject1.Chart.ChartWizard(this.Range[“A1”, “A3”], Excel.XlChartType.xlLine, missing, missing, missing, missing, missing, “Scale Chart”, missing, missing, missing);

chartObject1.Activate();

Excel.Axis axisScale = (Excel.Axis) chartObject1.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary);

if (axisScale != null)

{

axisScale.ScaleType = Excel.XlScaleType.xlScaleLogarithmic; axisScale.MajorUnitIsAuto = false;

axisScale.MajorUnit = 1000;

}

}

Listing 6-7 Scaling automation code

Listing 6-7 first loads data from a Range object and sets the type to a line chart. Next, the code retrieves a reference to the primary value axis. In case you were wondering, the XlAxisGroup enumeration provides access to the different types of axis that may be found in charts with multiple series. Since we are examining the simple case, the primary axis is returned.

There is one quirk associated with the logarithmic scale. It will tend to distort the values on the value axis. If you consider this an eyesore, one workaround is to set the MajorUnit and MinorUnit to the same quantity. This will smooth out the scale distortion without affecting the chart accuracy.

With a reference to the value axis, we set the scale type to logarithm. Chart scales may either be linear or logarithmic with the default being linear. When a logarithmic chart is plotted, by default, the value axis will scale in powers of 10. For instance, the units will be equal to 1, 10, 100, 1000, and so on. This is most often appropriate. However, for cases where this isn’t appropriate, the code in Listing 6-6 shows how to set the MajorUnit to 1000. With that setting applied, every major tick mark will be some multiple of 1000. While it isn’t strictly necessary to set the MajorUnitIsAuto property to true, the code sets it so that you may be aware of this property. By default, when the MajorUnit is adjusted through code, the MajorUnitIsAuto is automatically set to false. You can see the results in Figure 6-14.

The code could have gone on to set the MinorUnit as well, but the meaning for such a setting can be derived easily from the MajorUnit property. You should note that these settings apply to the value axis. MinorUnits applied to the category axis have no effect unless the chart type is set to timescale.

Tick Marks

In the typical case, chart data is simply rendered to the chart surface without any sort of customization. While this may work for the majority of cases, there are times when this approach needs to be refined a bit. For instance, consider the case where there are a number of data points to be rendered. By default,

228

The Charting Tool

the chart will attempt to fit as many points as necessary on the category axis. If this means increasing the chart width to accommodate the data, then this is performed automatically and no programming intervention is required. However, the end result may be a cluttered chart surface with unreadable data.

One approach to cleaning up the chart surface is to restrict the number of points being rendered. This does not change the accuracy of the chart; it simply allows the chart to plot less axis labels so that the category axis remains uncluttered and readable. The missing values can easily be inferred from the surrounding data.

Figure 6-14

An effective way to achieving this customization is to use the tick mark object. A tick mark is a label drawn on a chart to represent the data coordinates being plotted. Figure 6-15, shows an example of tick marks (vertical and horizontal bars on each axis).

Unfortunately, tick marks apply to the chart as a whole. It is not possible to customize an individual tick mark per se. Customizations that apply to the tick mark are fitted to the entire chart. Tick marks can also contain labels, but for the same reasons, these labels apply to the chart as a whole. By default, these labels are derived from the category titles.

229

Chapter 6

Figure 6-15

Tick marks have an array of associated objects that are usually used in concert to allow for further customization. The TickLabelSpacing, MajorUnit, Minimum, and Maximum scale are all used as a singular unit to effect customizations. Listing 6-8 has an example.

Visual Basic

Private Sub Sheet1_Startup(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Startup

Dim ChartObjects1 As Excel.ChartObjects = DirectCast (Me.ChartObjects(), Excel.ChartObjects)

Dim chartObject1 As Excel.ChartObject = ChartObjects1.Add(100, 20, 400,

300)

chartObject1.Chart.ChartWizard(Me.Range(“B1”, “B5”), Excel.XlChartType.xlArea, Title:=”Tick Chart”)

chartObject1.Activate()

Dim axisScale As Excel.Axis = DirectCast (Globals.ThisWorkbook.ActiveChart.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary), Excel.Axis)

If axisScale IsNot Nothing Then

Dim ticklabels As Excel.TickLabels = CType(axisScale.TickLabels, Excel.TickLabels)

230

The Charting Tool

ticklabels.Orientation = Excel.XlTickLabelOrientation.xlTickLabelOrientationVertical

ticklabels.Font.Color = ColorTranslator.ToOle(Color.DarkBlue)

ticklabels.Font.Name = “courier new” End If

End Sub

C#

using System; using System.Data;

using System.Drawing; using System.Windows.Forms;

using Microsoft.VisualStudio.Tools.Applications.Runtime; using Excel = Microsoft.Office.Interop.Excel;

using Office = Microsoft.Office.Core;

namespace Ticks

{

public partial class Sheet1

{

private void Sheet1_Startup(object sender, System.EventArgs e)

{

Excel.ChartObjects ChartObjects1 = (Excel.ChartObjects)this.ChartObjects(missing);

Excel.ChartObject chartObject1 = ChartObjects1.Add(100, 20, 400, 300); chartObject1.Chart.ChartWizard(this.Range[“B1”, “B5”],

Excel.XlChartType.xlArea, missing, missing, missing, missing, missing, “Tick Chart”, missing, missing, missing);

chartObject1.Activate();

Excel.Axis axisScale = (Excel.Axis)Globals.ThisWorkbook.ActiveChart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary);

if (axisScale != null)

{

Excel.TickLabels ticklabels = (Excel.TickLabels)axisScale.TickLabels;

ticklabels.Orientation = Excel.XlTickLabelOrientation.xlTickLabelOrientationVertical;

ticklabels.Font.Color = ColorTranslator.ToOle(Color.DarkBlue); ticklabels.Font.Name = “courier new”;

}

}

private void Sheet1_Shutdown(object sender, System.EventArgs e)

{

}

}

}

Listing 6-8 Tick mark manipulation

231

Chapter 6

Listing 6-7 shows code to customize the tick marks. While the code isn’t necessarily complicated, you should notice that a portion of the code reflects some font customization. For instance, the appearance and color may be shaped accordingly. The code in Listing 6-7 also orients the labels vertically on the axis. This approach can be used to prevent clutter on the axes.

The Microsoft.Office.Interop.Excel.XlTickMark enumeration allows for some customization on the appearance of tick marks on the chart surface. For instance, the tick marks can be viewed as a cross, placed on the inside of the axis, or removed entirely. By default, the tick marks appear on the outside of the category axis.

To customize the tick marks for the value or series axis, you will need to obtain a reference to the appropriate axis. If you examine the code in Listing 6-7, the axisScale variable points to the primary axis. Simply change the reference to point to the required axis to obtain an appropriate reference. The reference will be valid for all axes except the value axis. A runtime exception will be thrown if you attempt to customize the tick marks for the value axis.

Titles and Captions

Earlier material has indicated quite prominently that a chart can have as many as five titles. Let’s shed some light on this. Figure 6-16 shows a chart with three titles.

Figure 6-16

232

The Charting Tool

Listing 6-9 shows the code to create the chart titles.

Visual Basic

Private Sub Sheet1_Startup(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Startup

Dim ChartObjects1 As Excel.ChartObjects = DirectCast (Me.ChartObjects(), Excel.ChartObjects)

Dim chartObject1 As Excel.ChartObject = ChartObjects1.Add(100, 20, 400,

300)

chartObject1.Chart.ChartWizard(Me.Range(“B1”, “B5”), Excel.XlChartType.xlArea)

chartObject1.Activate()

chartObject1 .HasTitle = True chartObject1.Chart.ChartTitle.Caption = “Main Chart Title 1”

Dim axis As Excel.Axis = DirectCast ( chartObject1

.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary), Excel.Axis) axis.HasTitle = True

axis.AxisTitle.Caption = “Value Axis Title 2”

axis = DirectCast ( chartObject1 .Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary), Excel.Axis)

axis.HasTitle = True

axis.AxisTitle.Caption = “Category Axis Title 3”

End Sub

C#

using System; using System.Data;

using System.Drawing; using System.Windows.Forms;

using Microsoft.VisualStudio.Tools.Applications.Runtime; using Excel = Microsoft.Office.Interop.Excel;

using Office = Microsoft.Office.Core;

namespace Ticks

{

public partial class Sheet1

{

private void Sheet1_Startup(object sender, System.EventArgs e)

{

Excel.ChartObjects ChartObjects1 = (Excel.ChartObjects)this.ChartObjects(missing); Excel.ChartObject chartObject1 = ChartObjects1.Add(100, 20, 400, 300); chartObject1.Chart.ChartWizard(this.Range[“B1”, “B5”],

Excel.XlChartType.xlArea, missing, missing, missing, missing, missing, missing, missing, missing, missing);

chartObject1.Activate();

chartObject1 .HasTitle = true; chartObject1.Chart.ChartTitle.Caption = “Main Chart Title 1”;

Excel.Axis axis = (Excel.Axis) chartObject1 Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary);

axis.HasTitle = true;

233

Chapter 6

axis.AxisTitle.Caption = “Value Axis Title 2”;

axis = (Excel.Axis) chartObject1 .Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary);

axis.HasTitle = true;

axis.AxisTitle.Caption = “Category Axis Title 3”;

}

private void Sheet1_Shutdown(object sender, System.EventArgs e)

{

}

}

}

Listing 6-9 Multi-titled chart

The code is straightforward enough to not require any further explanation. The first three titles may be set through the Chart Wizard constructor as well. However, you may have noticed that we have only showed code to demonstrate three titles. The fourth title applies to charts with multiple series. This option to set chart titles in that scenario is presented in the multiple series section. The fifth title applies to charts that contain time scales and is not presented here.

Char t Groups

Every Microsoft VSTO-based chart application contains one or more chart groups. The ChartGroup object is implemented as a collection and represents the individual charts on the chart surface. The following groups are available for use: AreaGroups, BarGroups, ColumnGroups, DoughnutGroups,

LineGroups, and PieGroups.

It’s more than a programming convenience to use the groups to access the charts rather than retrieving the chart through the global object. There are also performance-based reasons to be had as well. The chart group collection is already available and does not have to incur the overhead of Interop calls to locate and load the respective chart. Listing 6-10 shows some code that manipulates the default chart group.

Visual Basic

Private Sub UseChartGroup()

Dim xlChart As Excel.Chart = DirectCast (Globals.ThisWorkbook.Charts.Add(), Excel.Chart)

Dim cellRange As Excel.Range = DirectCast(Me.Range(“a1”,”e1”),

Excel.Range)

xlChart.SetSourceData(cellRange, missing) xlChart.ChartType = Excel.XlChartType.xlDoughnut Dim chartGroup As Excel.ChartGroup = DirectCast

(xlChart.DoughnutGroups(1), Excel.ChartGroup) ‘perform some operation with chartgroup

xlChart.HasLegend = False

End Sub

234

The Charting Tool

C#

private void UseChartGroup()

{

Excel.Chart xlChart = (Excel.Chart)Globals.ThisWorkbook.Charts.Add(missing, missing, missing, missing);

Excel.Range cellRange = this.Range[“a1”, “e1”] as Excel.Range; xlChart.SetSourceData(cellRange, missing);

xlChart.ChartType = Excel.XlChartType.xlDoughnut; Excel.ChartGroup chartGroup =

(Excel.ChartGroup)xlChart.DoughnutGroups(1); //perform some operation with chartgroup

xlChart .HasLegend = false;

}

Listing 6-10 Code to retrieve the default chart group

To be sure, there is nothing particularly amazing in the code implementation. The chartGroup method simply provides a programming convenience for code in Listing 6-9. However, there are instances where it may be quite useful. For instance, the GapWidth property can only be set through a chart group. The ActiveChart object does not expose the GapWidth property and neither does the seriescollection. The GapDepth properties that these objects expose are not functionally equivalent to the GapWidth property. Figure 6-17 shows the results of the code in Listing 6-9.

Figure 6-17

235

Chapter 6

Formatting Char t Data

Now that you know how to load data into a chart and are familiar with a few key chart objects, let’s focus on the presentation of the data. There is some merit to a chart that has a marginal amount of customization applied. Notice the term marginal in there. The implication is that the formatting must never be overdone because it will focus attention away from the data. Assuming that a good balance can be found, formatting can really act to emphasize the important parts of the data. For instance, anomalies can be colored to stand out. And this is what is important in charting.

The chart provides a number of ways to customize data. This section walks you through three main levers of control that may be used. The font, NumberFormat and Chart surface customization encompass most of the common customizations that may be applied to the chart. It bears repeating that overdoing the customization seriously impedes the charts ability to transform data into impact.

Consider Figure 6-18, which shows some formatting applied to the chart surface. Notice that the chart surface is uncluttered and contains only bare-bones formatting. While this is obviously not a route that the hotshot developer may want to adopt, it is a very prudent strategy because the formatting is conservative.

Figure 6-18

236

The Charting Tool

In case you were wondering, labels are added, and there is some customization of the legend area. That’s about all the customization that is applied. However, you are welcome to add some more customization through events. For instance, you may choose to color the series differently or format the axes appropriately based on end-user events such as mouse clicks. The next few sections show you how to format a chart.

Font Object Customizations

The font object allows for font customizations to the target object. The font object is associated with many objects, including the interior object. Listing 6-11 shows an example.

Visual Basic

Dim ChartObjects1 As Excel.ChartObjects = DirectCast (Me.ChartObjects(), Excel.ChartObjects)

Dim chartObject1 As Excel.ChartObject = ChartObjects1.Add(100, 20, 400,

300)

chartObject1.Chart.ChartWizard(Me.Range(“a1”, “e1”), Excel.XlChartType.xl3DColumn)

Dim font As Excel.Font = chartObject1.Chart.ChartArea.Font font.Bold = True

font.Underline = True font.Shadow = True font.Italic = True

font.Color = ColorTranslator.ToOle(Color.Red)

C#

using System; using System.Data;

using System.Drawing; using System.Windows.Forms;

using Microsoft.VisualStudio.Tools.Applications.Runtime; using Excel = Microsoft.Office.Interop.Excel;

using Office = Microsoft.Office.Core;

namespace ChartFont

{

public partial class Sheet1

{

private void Sheet1_Startup(object sender, System.EventArgs e)

{

Excel.ChartObjects ChartObjects1 = (Excel.ChartObjects)this.ChartObjects(missing);

Excel.ChartObject chartObject1 = ChartObjects1.Add(100, 20, 400, 300);

chartObject1.Chart.ChartWizard(this.Range[“a1”, “e1”], Excel.XlChartType.xl3DColumn, missing, missing, missing, missing, missing, “Custom Chart”, missing, missing, missing);

Excel.Font font = chartObject1.Chart.ChartArea.Font; font.Bold = true;

font.Underline = true; font.Shadow = true;

237