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

Chapter 6

The code presented in Listing 6-15 first loads data into the chart through range A1:E1. A chart Trendline cannot be rendered without data. In fact, an exception is thrown if you attempt to manipulate a Trendline of chart that does not contain data. Since the Trendline is part of the series, we use a reference to the series to gain access to the Trendline. For our purposes, we simply add a moving average Trendline. A moving average Trendline uses the average of a specific number of data points to create the Trendline. The net effect of using an average in this smoothes out the fluctuations that can occur in data.

You should notice that the line of code that creates a Trendline is very unfriendly and intimidating. However, the ugliness is a necessity since you cannot access a Trendline through any other means. The VSTO chart can support six types of Trendlines: linear, logarithmic, polynomial, power, exponential, and moving average. In each case, the approach is identical to Listing 6-14. Simply change the

Excel.XlTrendlineType.

Custom Trendlines

For most charts and requirements, the internally implemented Trendline will suffice. However, there are some special cases or some lofty end-user requirements that necessitate taking control over the trending process. Let’s examine a very rudimentary example so that you can get the basic idea. After that, you are free to let your imagination run wild.

Consider the piece of code in Listing 6-16.

Visual Basic

Private Sub AddCustomTrendline()

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

Excel.Chart)

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

xlChart.SetSourceData(cellRange.CurrentRegion) Globals.ThisWorkbook.ActiveChart.ChartType = Excel.XlChartType.xl3DColumn

Dim series As Excel.Series = Globals.ThisWorkbook.ActiveChart.SeriesCollection(1)

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

Dim line As Excel.Shape = xlChart .Shapes.AddLine(500, 250, 100, 100) line.Name = “Custom trend line”

End Sub

C#

private void AddCustomTrendline()

{

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.CurrentRegion, missing);

248

The Charting Tool

Globals.ThisWorkbook.ActiveChart.ChartType =

Excel.XlChartType.xl3DColumn;

Excel.Series series = Globals.ThisWorkbook.ActiveChart.SeriesCollection(1) as Excel.Series;

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

Excel.Shape line = Globals.ThisWorkbook.ActiveChart.Shapes.AddLine(500, 250, 100, 100);

line.Name = “Custom trend line”;

}

Listing 6-16, Custom trendline generation

The code in Listing 6-16 produces the chart in Figure 6-23.

Figure 6-23

As you can see from Figure 6-23, a custom line is imposed on the chart surface. Before we go further, you should note that this is a simple example that creates a basic line on a chart. It does not necessarily relate

249

Chapter 6

to the data points. However, you can render fancy Bezier curves, rectangles, and a multitude of VSTOsupported shapes that are based on the data in the series. In every case, the code will follow the approach outlined in Figure 6-23.

Another point of interest is that the line on the chart behaves much like an object added to a Microsoft Word document; that is, you can interact with the Trendline by dragging it and applying some customization to it, assuming that you have not set the locked property of the Trendline object to true. Finally, the line does not have to be hard-coded in. You can use the maximum and minimum scale property of the chart to determine where to draw the line relative to the series values. Or, the values may be calculated dynamically based on the values of each point. The data labels section shows code that can manipulate each point.

Error Bar Analysis

As the term implies, error bars denote a potential error with the data in the series. Usually, error bars and their associated notations are reserved for scientific charting or applications where the interpretation of data on a series must be mathematically correct. In this case, the error bar represents some fluctuation around the actual data point.

Aside from the fluffy definition, ErrorBars are not necessarily complicated. First, they apply to any chart that contains one or more series. However, ErrorBars are typically reserved for charts that must calculate some mathematical quantity. It’s uncommon to find ErrorBars in a pie chart, for instance. Listing 6-17 has some code for error bars.

Visual Basic

Private Sub AddErrorBars()

Dim xlChart As Excel.Chart = Globals.ThisWorkbook.Charts.Add() Dim cellRange As Excel.Range = Me.Range(“a1”, “e1”) xlChart.SetSourceData(cellRange)

xlChart.ChartType = Excel.XlChartType.xlLine

Dim series As Excel.Series = xlChart.SeriesCollection(1)

Dim errorBars As Excel.ErrorBars = (DirectCast(series.ErrorBar(Excel.XlErrorBarDirection.xlY, Excel.XlErrorBarInclude.xlErrorBarIncludeBoth, Excel.XlErrorBarType.xlErrorBarTypeFixedValue), Excel.ErrorBars))

End Sub

C#

private void AddErrorBars()

{

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.xlLine;

Excel.Series series = xlChart .SeriesCollection(1) as Excel.Series; Excel.ErrorBars errorBars =

(Excel.ErrorBars)series.ErrorBar(Excel.XlErrorBarDirection.xlY,

Excel.XlErrorBarInclude.xlErrorBarIncludeBoth, Excel.XlErrorBarType.xlErrorBarTypeFixedValue, missing, missing);

}

250

The Charting Tool

Listing 6-17 Error bar customization

Figure 6-24 shows the code in action.

Figure 6-24

According to the code presented in Listing 6-16, the VSTO Excel object presents no direct access to the ErrorBars collection. This is exceedingly unfriendly to code because we must rely on an ugly cast. Once we perform the necessary surgery, we can then add an error bar to the chart by calling the ErrorBars method. The first parameter tells the chart the particular axis that will receive the ErrorBar. In our case, it is the Y axis.

The second parameter specifies which part of the error bar to include. An ErrorBar is made up of two parts; the top part and bottom part. The code instructs the chart to render both parts. The third parameter is the ErrorBar type. The fourth and fifth parameters are optional, having to do with the xlErrorBarTypeCustom setting. Normally, these features should not form part of the immediate data on the series unless specifically required. However, it is prudent to provide such features to the end user through some sort of click event.

Char t Customization through Events

If you have been following the material presented in this book so far, you should realize that events have a consistent model in Visual Studio Tools for the Office System. Additionally, the approach to programming events remains the same even though the object target changes. For instance, the event hookup for Excel is largely the same for Outlook. There is no reason to expect this model to change for the charts as well.

251

Chapter 6

The basic premise still remains; find a suitable event and wire it up. When the event fires, the VSTO engine notifies all subscribers that the event has fired. The handling code is then called into action. If you have indicated your intent to handle a particular event, your code is executed.

Let’s proceed with a simple example. Our example adds a different line marker style to a chart once the chart is clicked. Listing 6-18 has the code.

Visual Basic

Public Class Sheet1

Private Sub MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal x As

Integer, ByVal y As Integer)

‘get a reference to the series collection to customize a chart point

Dim series As Excel.Series =

DirectCast(Globals.ThisWorkbook.ActiveChart.SeriesCollection(1),Excel.Series)

If series.MarkerStyle <>

Microsoft.Office.Interop.Excel.XlMarkerStyle.xlMarkerStylePlus Then

series.MarkerStyle =

Microsoft.Office.Interop.Excel.XlMarkerStyle.xlMarkerStylePlus

series.MarkerSize = 4

series.MarkerBackgroundColor = ColorTranslator.ToOle(Color.Red)

series.MarkerForegroundColor = ColorTranslator.ToOle(Color.White)

Dim oPoint As Excel.Point = DirectCast(series.Points(2),Excel.Point)

oPoint.HasDataLabel = True

Globals.ThisWorkbook.ActiveChart.Refresh()

End If

End Sub

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

‘first chart

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

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

xlChart.ChartType = Excel.XlChartType.xlRadar

AddHandler xlChart.MouseUp, AddressOf MouseUp

End Sub

Private Sub Sheet1_Shutdown(ByVal sender As Object, ByVal e As

System.EventArgs) Handles Me.Shutdown

End Sub

End Class

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;

252

The Charting Tool

using Office = Microsoft.Office.Core;

namespace ChartEvent

{

public partial class Sheet1

{

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

{

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.xlRadar;

//wire event handler xlChart.MouseUp += new

Microsoft.Office.Interop.Excel.ChartEvents_MouseUpEventHandler(xlChart_MouseUp);

}

void xlChart_MouseUp(int Button, int Shift, int x, int y)

{

//get a reference to the series collection to customize a chart point Excel.Series series =

Globals.ThisWorkbook.ActiveChart.SeriesCollection(1) as Excel.Series; if (series.MarkerStyle !=

Microsoft.Office.Interop.Excel.XlMarkerStyle.xlMarkerStylePlus)

{

series.MarkerStyle = Microsoft.Office.Interop.Excel.XlMarkerStyle.xlMarkerStylePlus;

series.MarkerSize = 4;

series.MarkerBackgroundColor = ColorTranslator.ToOle(Color.Red); series.MarkerForegroundColor = ColorTranslator.ToOle(Color.White); Excel.Point oPoint = series.Points(2) as Excel.Point; Globals.ThisWorkbook.ActiveChart.Refresh();

}

}

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

{

}

}

}

Listing 6-18 Customization using the event model

From the code presented in Listing 6-18, a reference to the series must be obtained. Next, the marker styles and settings are applied to the series. The result of this customization is shown in Figure 6-25. You should note that the markers are emphasized with color so that they appear visible. Normally, the markers are small enough so that they may not be readily visible. Also, you should note that after the customization has been applied to the marker, the chart must be refreshed so that the customizations can appear. The markers and customizations will not be available otherwise.

253

Chapter 6

Figure 6-25

A well-designed chart application can improve the end-user experience through interaction, while preventing information overload. Information is passed along to the end user in digestible quantities. For instance, consider a chart that appears with only a series on the chart surface. However, captions and titles are added when the end user clicks a particular area on the chart. Information presented in this way has two distinct advantages. The chart surface remains uncluttered and easy to read. The information presented in an uncluttered matter is not intimidating to the end user.

Advanced Char t Manipulation

Now that you have the basics of charting, it’s time to take it up a notch or two. Before you plunge in, observe this word of caution. Be very careful to avoid dazzling end users with endless functionality. Remember that a chart transforms data so that it makes an impact in a way that does not overwhelm the end user. A visually interesting chart that has a lot going on tends to intimidate the average user. Intimidation is counterproductive. It’s better to keep a chart clean and simple, using events here and there to bring in extra information.

Charts with Multiple Series

Usually, charts are rendered with a single series for simple data sets. However, for more involved data analysis, more than one series may be required to represent the data. In this circumstance, two or more series are rendered on the chart surface. Consider the bar chart in Figure 6-26, showing two series.

254

The Charting Tool

Figure 6-26

All of the functionality available to one series is available to the other series. Therefore, the multiple series approach is no different from the single series approach. Listing 6-19 shows the code.

Visual Basic

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

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

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

xlChart.SetSourceData(cellRange.CurrentRegion) Globals.ThisWorkbook.ActiveChart.ChartType =

Excel.XlChartType.xl3DBarClustered End Sub

C#

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

{

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

Excel.Range cellRange = this.Range[“a1”, “b5”] as Excel.Range;

xlChart.SetSourceData(cellRange.CurrentRegion, missing); xlChart .ChartType = Excel.XlChartType.xl3DBarClustered;

}

Listing 6-19 Multi-series charting code

255

Chapter 6

Listing 6-19 shows the fourth chart title associated with a chart. Notice that, as discussed earlier, a chart can have as many as five titles imposed on its surface.

A single series is given by a single row of data in the spreadsheet. Consider an example that sources data from the range A1:A5. This produces a single series on the chart. A contiguous range A1: E5 actually renders five different series, since there are five rows in this range. Listing 6-19 shows an easy way to create a multiple series chart. The multiple series is sourced from two Excel columns. Examine Figure 6-27 to see the data and the results of the code when it is executed.

Figure 6-27

You can exercise the same precise control even though there may be more than one series on the chart surface. The trick is to always obtain a reference to the correct series. With a suitable reference, the code to modify the series remains the same as presented in the series customization section. Remember, a multi-series chart customization is handled the same way; one series at a time.

Combination Charts

For comparison purposes, it can be handy to render a combination of two charts instead of a single chart with multiple series. Each chart in a combination chart surface is independent of the other and may be targeted with code similar to the approach for building single charts. Listing 6-20 shows the code.

256

The Charting Tool

Visual Basic

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

Excel.ChartObjects)

Dim chartObject As Excel.ChartObject = ChartObjects.Add(1, 20, 250, 250)

chartObject.Chart.ChartWizard(Me.Range(“A1”, “E1”), Excel.XlChartType.xl3DColumn, Title:=”First Chart”)

chartObject.Name = “InitialiChartObject”

ChartObjects = DirectCast (Me.ChartObjects(), Excel.ChartObjects)

Dim chartObject2 As Excel.ChartObject = ChartObjects.Add(251, 20, 250, 250)

chartObject2.Chart.ChartWizard(Me.Range(“F1”, “K1”), Excel.XlChartType.xl3DColumn, Title:= “Second Chart”)

chartObject2.Name = “SecondaryChartObject” chartObject2.Activate()

C#

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

{

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

Excel.ChartObject chartObject = ChartObjects.Add(1, 20, 250, 250);

chartObject.Chart.ChartWizard(this.Range[“A1”, “E1”], Excel.XlChartType.xl3DColumn, missing, missing, missing, missing, missing, “First Chart”, missing, missing, missing);

chartObject.Name = “InitialiChartObject”;

ChartObjects = (Excel.ChartObjects)this.ChartObjects(missing); Excel.ChartObject chartObject2 = ChartObjects.Add(251, 20, 250, 250);

chartObject2.Chart.ChartWizard(this.Range[“F1”, “K1”], Excel.XlChartType.xl3DColumn, missing, missing, missing, missing, missing, “Second Chart”, missing, missing, missing);

chartObject2.Name = “SecondaryChartObject”; chartObject2.Activate();

}

Listing 6-20 Rendering multiple charts on a single spreadsheet

As the name implies, combination charts combine two charts on a single Excel spreadsheet. Notice that this is distinctly different from multiple charts imposed on a single chart surface. VSTO does not currently support multiple charts on a single chart surface. It’s important to note here that this is a VSTO imposed limitation since combination charts imposed on a single chart surface are supported in the Office Web Components and through the regular Excel Interop libraries. The idea behind combination charts is to expose some sort of contrast in the data.

Notice the adjustment with the numerical parameters so that the two charts line up correctly on the spreadsheet. Using these position parameters, it is possible to site a chart in specific regions of the spreadsheet. Although the numerical parameters are hard-coded based on the size of the chart space, it is just as easy to perform at runtime. Just obtain a reference to the chartspace object and use the width and height properties.

257