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

Chapter 6

font.Italic = true;

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

}

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

{

}

}

}

Listing 6-11 Font object customization

From Listing 6-11, the Chart Wizard is used to load a single series from range A1:E1. What is of interest to us is the reference to the Font object. Notice that the font object is obtained from the ChartArea object. It means that the customization applied in the next few lines of code will affect the entire chart area to include Labels, Titles, Legends, and Captions. The rest of the code is trivial to understand.

You should take from this discussion that it is possible to specifically target areas of the chart with font customizations. These customizations can serve as an override for the default settings. If you care to reset the customizations after they have been imposed but before the application has ended, simply call the clearformat() method. Once the application has ended, the default settings are restored. If you care to persist your customizations, you will need to implement code to save and load these settings from a persistent data store.

Number Format Customization

The NumberFormat object allows an object to apply appearance customizations to the target object. Usually, the customizations affect labels on the axis. You may recall that the NumberFormat property was discussed in Chapter 3. Although, it was discussed within the context of the Excel object, the principles may be applied to the chart object as well. Proceeding from that earlier discussion, let’s examine an example (see Listing 6-12).

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:=”Formatter Chart”)

chartObject1.Activate()

Dim axisScale As Excel.Axis = DirectCast ( chartObject1

.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary), Excel.Axis) If Not axisScale Is Nothing Then

axisScale.HasTitle = True

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

ticklabels.Orientation = Excel.XlTickLabelOrientation.xlTickLabelOrientationHorizontal

238

The Charting Tool

ticklabels.NumberFormat = “#.0##” 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, “Formatter Chart”, missing, missing, missing);

chartObject1.Activate();

Excel.Axis axisScale = (Excel.Axis) chartObject1

.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary); if (axisScale != null)

{

axisScale.HasTitle = true; Excel.TickLabels ticklabels =

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

Excel.XlTickLabelOrientation.xlTickLabelOrientationHorizontal; ticklabels.NumberFormat = “#.0##”;

}

}

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

{

}

}

}

Listing 6-12 Number format customization

From the code in Listing 6-12 and the results in Figure 6-19, observe that the vertical axis contains labels that are oriented horizontally. These values are also formatted with a decimal point. You should notice also that the customization is applied to the category axis. It is possible to customize the value axis and series axis as well by supplying the appropriate enumeration to the first parameter of the axes method.

The NumberFormat must be applied only to labels or pieces of text that are rendered on the chart surface. Not all target objects expose a NumberFormat property. However, axes and data labels usually expose this property. For a more detailed probe of the NumberFormat and the possible options that may be used with the formatter, see Chapter 3.

239

Chapter 6

Figure 6-19

Chart Surface Customization

The walls of the chart are fully customizable. You can add backgrounds, fonts, and patterns to these surfaces. The chart object also supports a number of default patterns and backgrounds that may be used. Unfortunately, this area of customization is prone to abuse. It’s quite common to view charts with gaudy backgrounds that are distracting and conspire to focus attention away from the data.

Consider Listing 6-13, which will customize the surface area of the walls of the chart.

Visual Basic

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

‘xlChart.Location(Excel.XlChartLocation.xlLocationAsObject, Me.Name)

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

Excel.Chart)

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

xlChart.SetSourceData(cellRange.CurrentRegion)

240

The Charting Tool

xlChart.ChartType = Excel.XlChartType.xl3DBarClustered

‘set the pattern on the wall

Dim wall As Excel.Walls = xlChart.Walls

Dim interior As Excel.Interior = wall.Interior interior.Pattern = Excel.XlPattern.xlPatternLightUp

interior.Color = ColorTranslator.ToOle(Color.White) ‘add color to the floor

Dim floor As Excel.Floor = xlChart.Floor

Dim floorInterior As Excel.Interior = floor.Interior floorInterior.Color = ColorTranslator.ToOle(Color.Yellow)

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 Perspectives

{

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”, “a5”] as Excel.Range;

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

//set the pattern on the wall Excel.Walls wall = xlChart .Walls; Excel.Interior interior = wall.Interior;

interior.Pattern = Excel.XlPattern.xlPatternLightUp; interior.Color = ColorTranslator.ToOle(Color.White); //add color to the floor

Excel.Floor floor = xlChart .Floor; Excel.Interior floorInterior = floor.Interior;

floorInterior.Pattern = Excel.XlPattern.xlPatternGray16; floorInterior.Color = ColorTranslator.ToOle(Color.Yellow);

}

}

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

{

}

}

}

Listing 6-13 Chart surface customization

241

Chapter 6

The code in Listing 6-13 produces Figure 6-20.

Figure 6-20

Listing 6-12 shows code to load data into the chart. Once the chart has been loaded with data, the chart type is set. With the necessary plumbing out of the way, a reference is obtained pointing to the walls of the chart. The wall is the vertical background of the chart not including the floor or ceiling of the chart. A predefined pattern is applied to the wall, and it is colored white.

Next, a reference for the floor object is obtained. The color is set to yellow. We could have added a pattern to the floor as well, but the contrast in patterns would probably be distracting. Because the chart is a clustered bar chart, it appears flipped on its side. So, the floor of the chart is not necessarily found at the bottom of the chart space. Further, the chart floor only applies to 3-D drawings since, there is perceived depth. Floor customizations for 2-D charts will fail. You should note again that the background is extremely important because the data essentially sits on that surface. If it is too loud, it will distract from the task at hand. The Excel.XlPattern enumeration contains 20 default patterns that may be applied as customizations. The chart does not allow you to add a custom pattern. However, you can work around this limitation by adding your custom pattern to an image and loading the image into the chart. An example of this is provided later.

Char t Legend Manipulation

A formal definition of a chart legend has already been presented at the start of the chapter. Expanding from this definition, the legend feature is exposed in a Legend object. Since the legend belongs to the chart as a whole and not to a particular series, you should expect to access this Legend object through the chart object.

242

The Charting Tool

The legend object contains legend entries. Legend entries are exposed through a LegendEntries object. It’s helpful to note that LegendEntries are added to the legend by default as each chart series is being rendered. The entries follow the order of the series addition. Because each LegendEntries and Legend object contain formatting objects, it is possible to customize the appearance of each entry in the legend. The appearance and position of the legend can also be customized. Finally, the order of the legend entries may be overridden through code.

First, let’s focus on a customer requirement. The customer would like the ability to apply customizations at runtime to a chart. The customization allows the end user to determine where to place an existing legend on the chart surface. The border and attributes must be customizable. Additionally, the end user must have the ability to change the background of the chart and impose font attributes on the chart title. The code in Listing 6-14 implements these end-user requirements.

Visual Basic

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) xlChart.ChartType = Excel.XlChartType.xl3DBarClustered

xlChart .HasLegend = True

Dim legend As Excel.Legend = xlChart .Legend legend.Position = Excel.XlLegendPosition.xlLegendPositionLeft legend.Shadow = True

legend.Interior.Color = ColorTranslator.ToOle(Color.WhiteSmoke)

‘reposition the legend to fall inside the chart legend.Height += legend.Height

legend.Width += legend.Width legend.Left += 140

‘customize the legend entry

Dim enTry As Excel.LegendEnTry = DirectCast(legend.LegendEntries(1), Excel.LegendEnTry)

enTry.Font.Underline = True enTry.Font.Name = “Courier New”

enTry.Font.Color = ColorTranslator.ToOle(Color.Blue)

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; using System.Globalization;

namespace Legend

{

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);

243

Chapter 6

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

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

Globals.ThisWorkbook.ActiveChart.HasLegend = true; Excel.Legend legend = xlChart .Legend;

legend.Position = Excel.XlLegendPosition.xlLegendPositionLeft; legend.Shadow = true;

legend.Interior.Color = ColorTranslator.ToOle(Color.WhiteSmoke);

//reposition the legend to fall inside the chart legend.Height += legend.Height;

legend.Width += legend.Width; legend.Left += 140;

//customize the legend entry

Excel.LegendEntry entry = legend.LegendEntries(1) as Excel.LegendEntry; entry.Font.Underline = true;

entry.Font.Name = “Courier New”;

entry.Font.Color = ColorTranslator.ToOle(Color.Blue);

}

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

{

}

}

}

Listing 6-14 Chart legend manipulation

The code in Listing 6-14 shows that manipulation of the legend involves obtaining a reference to the Legend object. However, before proceeding, you must always set the HasLegend property to true; otherwise, a runtime exceptions will be thrown. By default, the Legend enumeration position allows the legend to be set to specific quadrants on the chart surface. However, the code shows that the left property can be used to move the entire legend within the specified quadrant. In our case, we have moved the legend to an area inside the chart plot area (see Figure 6-21). This is only for illustrative purposes; the legend should remain outside of the chart plot area.

Finally, the code retrieves the first legend entry. By default, the legend entries are added in the order that the series are rendered on the chart surface. In our case, we simply impose some formatting on the first entry. The legend entry cannot be removed or hidden. For extra credit, we double the area of the legend and add a shadow. The customization gives you a foot holder if you decide to add cosmetic touchups to the legend object.

As the text has pointed out, the legend entry order is predetermined based on the order that each series holds in the series collection. However, you may want to impose your own order on the legend entries. This is not a particularly daunting task. It simply involves reordering your data set so that the legend entries are rendered in the correct order.

244

The Charting Tool

Figure 6-21

You should note also that marker styles (the small colored squares next to the legend entries in the legend) are automatically embedded in the legend keys. For instance, if you apply a diamond marker style to the series, the legend key will automatically be updated to reflect the new shape. There is no way to customize this appearance through code. We focus on marker objects in a later section.

Analyzing Data

A chart transforms data into impact. Behind this impact, there may be trends, downturns, eroding profit margins, or new business opportunities. Although that type of information may be elusive in raw data, a chart can immediately surface these concerns to decision makers. From this point, a decision maker can transform this concern into action. Therefore, it is fundamentally important that a chart facilitate the data evaluation process.

Charts that obstruct this process are distracting and counterproductive because they tend to focus attention on unimportant areas. For that reason, it is important to choose the right type of chart. More often than not, the developer cannot fully appreciate the significance and context of the data and may choose to display the raw data in an unsuitable chart type. However, such inconveniences are easily sterilized by providing the end user with the option to use different charts to display the data.

245

Chapter 6

The ability to choose different chart types is simply one mechanism of data analysis. VSTO provides others, such as the trend line and error bar functionality. These features allow the end user to examine the data for consistency and even make predictions and assumptions based on the state of the data. The next few sections examine these concepts in more detail.

Trending through Trend Lines

A trend line is a line drawn on a chart that indicates a trend in the data. For VSTO-based charts, the trend line functionality is exposed through the Trendline object. The feature set is not necessarily limited to a straight line. The trend line may be a curve or derived from a logarithmic expression. Figure 6-22, shows a curved trend line on a chart.

Figure 6-22

Internally Supported Trendlines

Let’s have a look at the VSTO-supported Trendlines. The functionality is exposed through a Trendlines object collection. Each item in this collection contains a Trendline object. The Trendline object contains properties and methods that allow the developer to push and prod the Trendline to her or his own liking. Listing 6-15 shows some code to do this.

246

The Charting Tool

Visual Basic

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

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

‘get a reference to the series collection to plot a trend line Dim series As Excel.Series = xlChart.SeriesCollection(1)

Dim line As Excel.Trendline = (DirectCast(series.Trendlines(), Excel.Trendlines)).Add(Excel.XlTrendlineType.xlMovingAvg, , 2, 0.4, 0.4, , True, , “Moving ave.”)

line.Select()

line.Border.LineStyle = Excel.XlLineStyle.xlDot line.Border.Color = ColorTranslator.ToOle(Color.Red) line.InterceptIsAuto = True

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 MultiChart

{

public partial class Sheet1

{

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

{

//first chart 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);

//get a reference to the series collection to plot a trend line Excel.Series series = xlChart .SeriesCollection(1) as Excel.Series;

Excel.Trendline line = ((Excel.Trendlines)series.Trendlines(missing)).Add(Excel.XlTrendlineType.xlMovingAv g, missing, 2, 0.4, 0.4, missing, true, missing, “Moving ave.”);

line.Select();

line.Border.LineStyle = Excel.XlLineStyle.xlDot; line.Border.Color = ColorTranslator.ToOle(Color.Red); line.InterceptIsAuto = true;

}

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

{

}

}

}

Listing 6-15 Trend line showing moving average	247