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

Chapter 7

Notice that if you double-click on one of the cells in the main data area, the pivot table report will display the items that formed this value. This is drill-through. Drill-through functionality exists because the data displayed in the data area is an aggregation of data from the various fields that form the pivot axis. Drill-through functionality will be discussed in more detail later in the chapter.

Pivot Table Terminology

The pivot table defines its own world complete with special query syntax, object hierarchy, and supporting terminology. While it’s not critical to understand the query syntax, Multidimensional Expressions (MDX), it is important to have more than a passing understanding of pivot table terminology. That understanding can go a long way toward carving out functionality from a pivot table application based on customer requirements.

Let’s start with a diagram of another pivot table similar to Figure 7-1. However, this pivot table will indicate the key user interface widgets that form part of the pivot table object.

Datalabel range

Column range

Row range

Data range

Figure 7-5

278

Pivot Table Automation

For the next few sections, refer back frequently to the user interface widget presented in Figure 7-5 to associate the new terms with their on-screen widgets. To acquaint you with pivot terminology, we will mention appropriate terms as we explore examples.

One point of interest is that the pivot table sits inside the Excel spreadsheet, so you can expect to port most of your knowledge of Excel spreadsheets to the pivot table–reporting application. You may have to learn a few new terms, but hopefully, the learning curve will be significantly reduced. For instance, consider that pivot charts are based on Excel charts and the data displayed in the pivot table is based on Excel Range objects. Charts were covered in Chapter 6. Excel Ranges were covered in Chapters 2 and 3.

PivotCache Object

A typical pivot application can process a lot of data, on the order of several hundred thousand rows of data. The VSTO-based pivot table application handles this data manipulation a bit differently from traditional pivot table applications such as the Office Web Components pivot table. Data that is retrieved from an OLAP cube or other data source is stored in a cache called the PivotCache. The cache is queried for data whenever the user begins to manipulate the PivotTable object. The VSTO-based pivot table does not requery the original data source by default. However, this behavior may be changed by setting the appropriate connection property of the connection object. In fact, for a very large dataset you should make every effort to reduce the size of the PivotCache in order to squeeze performance out of the application since a very large cache can overrun the system resources on the local machine.

On-Line Analytical Processing (OLAP) was first described in the early 1990s and rose to prominence soon after. OLAP defines access to and storage of data into a multidimensional data source. The pivot table is an effective way to model the data in an OLAP cube.

Each pivot table report application contains its associated cache. It’s fairly common to have a collection of reports in a single pivot reporting application. Consequently, there may be several PivotCache objects. The native store for each PivotCache object is the PivotCaches object collection. The PivotCaches object collection contains the required objects that enable collection iteration and enumeration.

PivotData Objects

The data displayed in a pivot table report is contained in a pivotdata object. Through this object, you can massage and manipulate the data inside the pivot table. For instance, you can apply number formats or font customizations to the data if it meets a certain criterion. The data contained in each axis may be addressed through the pivotdata object once an appropriate reference is obtained for the axis. Chapters 2 and 3 explained number formats and font customization in great detail. The approach is directly applicable here, since the pivot table exposes its data through Excel Range objects.

Pivot Axis

Pivot axes form an integral part of the pivot table report application. The axes may contain any quantity of data in the form of pivot fields. These axes are used in combination, row versus column, to form a cross-tabulation of data. For instance, a customer name on the row axis may be cross-referenced with a

279

Chapter 7

shipping method on the column axis to generate an intersection of items that were shipped for a particular day. The action of pivoting axes is commonly referred to as slicing and dicing. Recall that an example of slicing and dicing was presented at the start of the chapter.

Pivot Fields

Pivot fields are items that form part of the pivot table axis. Pivot fields may be added to axes through code or at design time. The end user also has the ability to add pivot fields to any axis by dragging and dropping the fields onto the appropriate drop zone axis. Pivot fields may be filtered, have formulas applied to them, or customized appropriately. Figure 7-6 shows a pivot field list object.

Figure 7-6

Pivot Cell Object

The smallest common denominator of a pivot table report is the pivot cell. The pivot cell holds data in the pivot data axis. The underlying implementation of a pivot cell is built on an Excel Range object. You can use pivotcell objects the same way you use Excel Ranges; that is, you can iterate and add customizations on a per cell basis.

280

Pivot Table Automation

Pivot Labels

The pivot labels represent the pieces of text that form part of a pivot axis caption. The underlying implementation of a pivot label is an Excel Range object. To iterate the collection of labels, simply obtain a reference to the appropriate label object such as the DataLabelRange object. You may then iterate the items in this collection or apply font customizations and styling.

Creating Pivot Tables through Code

There are two approaches to creating pivot tables in VSTO. One approach is to use the PivotTable Wizard. The other way is to use the pivot table object attached to the worksheet. Examining the former first, the PivotTable Wizard can be invoked through code with a specified parameter list. Once these requirements are satisfied, the PivotTable Wizard will handle all the internal plumbing details to create the pivot table. The wizard provides an easy way to build pivot tables because most of the details are handled by the wizard. The wizard is easy to use but the developer has less control over the process. You’ll see an example of this in Listing 7-2.

Before presenting an example based on a PivotTable Wizard, you should note that the code will assume that the range A1:B4 contains data. If there is no data in the range, the PivotTable Wizard will fail. To prepare a range with data suitable for a pivot table, use the code in Listing 7-1.

Visual Basic

me.Range(“A1”).Value = “CustomerID” me.Range(“A2”).Value = “ALFKI” me.Range(“A3”).Value = “WILKH” me.Range(“A4”).Value = “VICTE” me.Range(“B1”).Value = “SHIPVIA” me.Range(“B2”).Value = “3”

me.Range(“B3”).Value = “1” me.Range(“B4”).Value = “2” me.Range(“C1”).Value = “EmployeeID” me.Range(“C2”).Value = “23” me.Range(“C3”).Value = “17” me.Range(“C4”).Value = “39”

C#

this.Range[“A1”, missing].Value2 = “CustomerID”; this.Range[“A2”, missing].Value2 = “ALFKI”; this.Range[“A3”, missing].Value2 = “WILKH”; this.Range[“A4”, missing].Value2 = “VICTE”; this.Range[“B1”, missing].Value2 = “SHIPVIA”; this.Range[“B2”, missing].Value2 = “3”;

this.Range[“B3”, missing].Value2 = “1”; this.Range[“B4”, missing].Value2 = “2”; this.Range[“C1”, missing].Value2 = “EmployeeID”; this.Range[“C2”, missing].Value2 = “23”; this.Range[“C3”, missing].Value2 = “17”; this.Range[“C4”, missing].Value2 = “39”;

Listing 7-1 Sample dataset

281

Chapter 7

Listing 7-2 is an example of code to create a pivot table report using the PivotTable Wizard.

Visual Basic

Dim table1 as Excel.PivotTable = me.PivotTableWizard( Excel.XlPivotTableSourceType.xlDatabase, me.Range(“A1”, “B4”),

,“PivotTable1”, false, false, true, false, missing,

,, , Excel.XlOrder.xlDownThenOver)

If Not table1 IsNot Nothing Then

MessageBox.Show(“The object occupies “ +

Table1.PivotCache().MemoryUsed.ToString() + “ bytes in memory”)

End If

C#

Excel.PivotTable table1 = this.PivotTableWizard( Excel.XlPivotTableSourceType.xlDatabase, this.Range[“A1”, “B4”], missing, “PivotTable1”, false, false, true, false, missing, missing, false, false, Excel.XlOrder.xlDownThenOver, missing, missing, missing);

if (table1 != null)

{

MessageBox.Show(“The object occupies “ + table1.PivotCache().MemoryUsed.ToString() + “ bytes in memory”);

}

Listing 7-2 Pivot table creation using the PivotTable Wizard

As you can see, there isn’t a whole lot of complexity involved in the code presented in Listing 7-2. The Excel.XlPivotTableSourceType enumeration informs the VSTO runtime to use the appropriate data source to generate the pivot table report. The pivot table can source from five different data sources. Of these, the Range object is used in the example. The pivot table report is also flexible enough to source data from a database or array by using the appropriate enumeration. You can use the help documentation to figure out the remaining parameter list.

Additionally, it’s probably a better idea to use named parameters in Visual Basic for Listing 7-2. This can improve readability in the code maintenance phase. Unfortunately, there is no performance improvement to be harvested from such an approach.

Surprisingly, this is all the code that is required to generate an enterprise-level pivot table reporting application. In fact, from this point, the user takes over to massage and manipulate the data. Notice how easy such an application is to create when compared with a GridView or DataGrid application. The pivot table report contains default functionality such as filtering, sorting, drag and drop implementation, and slice and dice functionality. These features have already received hundreds of thousands of hours of testing by Microsoft Product support. There is no need to spend time testing the filtering mechanism to see if it works, for instance.

282

Pivot Table Automation

Visual Studio Tools for Office Visual Basic Edition allows edit and continue in the designer. While debugging, you may simply change the value of a variable on the fly and the Visual Studio runtime will automatically apply these changes to the running code. Visual C# does not allow this magic for VSTO-based applications. You must first stop the executing application, then perform the changes, recompile the code, and reexecute the application.

From this point, the user can choose to export or print this data or drill through it. The drill-through automatically produces a new Excel spreadsheet comprising the fields that make up the cross-section of data. As an example, double-click on any one of the pivot cells that contains data. New data will be displayed in a new spreadsheet similar to Figure 7-7.

Figure 7-7

The second approach to creating a pivot table list object is to use the pivot table attached to the spreadsheet, as shown in Listing 7-3.

283

Chapter 7

Visual Basic

Try

Dim pvtCache As Excel.PivotCache = Globals.ThisWorkbook.PivotCaches().Add(Excel.XlPivotTableSourceType.xlDatabase,”C:\ Download\VSTO\Book\Chpt_7\Data\Orders.xls!Orders”)

Dim pvtTable As Excel.PivotTable = pvtCache.CreatePivotTable(“[PivotChart.xls]Sheet1!R1C1”,”MyPivotTable”,True,Excel.X lPivotTableVersionList.xlPivotTableVersion10)

Catch ex As Exception MessageBox.Show(ex.Message)

End Try

C# try

{

Excel.PivotCache pvtCache = Globals.ThisWorkbook.PivotCaches().Add(Excel.XlPivotTableSourceType.xlDatabase, @”C:\Download\VSTO\Book\Chpt_7\Data\Orders.xls!Orders”);

Excel.PivotTable pvtTable =

pvtCache.CreatePivotTable(“[PivotChart.xls]Sheet1!R1C1”, “MyPivotTable”, true, Excel.XlPivotTableVersionList.xlPivotTableVersion10);

}

catch (Exception ex)

{

MessageBox.Show(ex.Message);

}

Listing 7-3 Code to create a pivot table

This code approach is a bit more involved than the approach presented earlier. However, it is more flexible. A PivotCache object is used to store a reference to the newly created pivot table. The pivot table is created by calling the Add() method of the PivotCaches() object. The Add method accepts two parameters. The first indicates the type of data source and the second indicates the path to the datasource.

After the Add() call is executed, a PivotCache object is defined and added to the PivotCache collection. The subsequent call to CreatePivotTable() actually creates a PivotTable object and fills the pivot table with data. The CreatePivotTable() call enforces incorrect parameters by throwing exceptions, so you should typically wrap the code to create a pivot table in exception handling code. The exception handling code is not presented here.

The CreatePivotTable() method call can be used to connect a pivot table to a data source that is a database, an URI that points to an Internet source, or an OLAP cube. XML files cannot be loaded through this mechanism. XML files must first be loaded into the Excel spreadsheet. Once the data is in the spreadsheet, you can use the code in Listing 7-3 to load data into the pivot table report. Note also that for cross-domain calls or calls that must run remotely, such as loading data from an Internet resource, the correct permissions must be configured. The process responsible for running the pivot table report application on the remote server does not contain the necessary permissions to load data remotely.

284

Pivot Table Automation

In both pivot table loading scenarios, if a pivot table name identifier is not passed in, the pivot table is created with a default pivot table name identifier equal to PivotTable1. You can use this identifier to retrieve a reference to the existing pivot table in code. This is the preferred method. However, you can always use an index into the pivot tables object collection to retrieve a pivot table instance. It’s important to note that an exception is thrown if a named identifier does not exist.

The call to CreatePivotTable must use this special syntax: “[PivotChart.xls]Sheet1!R1C1”, where PivotChart.xls represents the name of the Excel project. The name of the Excel project is optional. This must be followed immediately by the Excel spreadsheet sheet name and row/column addressing. Deviation from this strict pattern results in an exception being thrown, indicating that the parameter is incorrect. The error message does not explicitly indicate which one of the four parameters is the culprit. It is troubling that such exceptions cannot be more specific, to say the least.

Formatting Pivot Data

Assuming that your users require a bit more information or formatting on the pivot table report, that sort of functionality is relatively simple to implement. The key to implementing this functionality successfully is to know the objects in the pivot table list that are responsible for displaying data in the pivot table report user interface widget. Several key objects were presented at the start of this chapter for this very purpose.

As an added bonus, the pivot table returns most, if not all, of its data objects as Excel spreadsheet ranges. So, you will be able to use the knowledge gathered in Chapters 2 and 3 to apply further customization when manipulating pivot table report ranges. The next few sections show how to accomplish this. For each example, you will notice a common theme: find the object of interest, retrieve it as an Excel Range, and iterate that Range.

Font Object Customizations

Building on the premise of Excel Ranges presented in Chapters 2 and 3, the following example demonstrates how to add a bold font to each item in the row axis. Listing 7-4 shows the code.

Visual Basic

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As

System.EventArgs) Handles Button1.Click

Dim table As Excel.PivotTable = Globals.Sheet1.PivotTables(“MyPivotTable”)

If (table IsNot Nothing) AndAlso (table.RowRange IsNot Nothing) AndAlso table.RowRange.Cells.Count > 0 Then

	Dim rng As Excel.Range = table.RowRange
	Dim i As Integer
	For i = 1 To rng.Cells.Count - 1 Step i + 1
	Dim cellRange As Excel.Range = DirectCast(rng.Cells(i, 1),
Excel.Range)	cellRange.Font.Bold = True
	Next

End If

End Sub

285

Chapter 7

C#

private void button1_Click(object sender, EventArgs e)

{

Excel.PivotTable table = Globals.Sheet1.PivotTables(“MyPivotTable”) as Excel.PivotTable;

if (table != null && table.RowRange != null && table.RowRange.Cells.Count > 0)

{

Excel.Range rng = table.RowRange;

for (int i = 1; i < rng.Cells.Count; i++)

{

Excel.Range cellRange = (Excel.Range)rng2.Cells[i, 1]; cellRange.Font.Bold = true;

}

}

}

Listing 7-4 Font customization in a pivot table report

By extension, you should be able to modify the code in Listing 7-3 to apply font customization to the column and data axis. All that is needed is a reference to table.ColumnRange instead of table.RowRange to cause the code to work correctly.

Label Format Customization

As the term implies, label formatting applies formatting attributes to the Label objects in a pivot table report application. Recall from earlier in the chapter that the DataLabelRange displays the data labels on a pivot table report. The approach in Listing 7-3 can be modified to iterate the DataLabelRange as well. Listing 7-5 shows a modification on the code presented in Listing 7-3. It may surprise you to know that label formatting is not necessarily limited to aesthetic adjustments of pieces of text.

Visual Basic

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim table As Excel.PivotTable = Me.PivotTables(“myPivotTable”) If table IsNot Nothing Then

If table. DataLabelRange IsNot Nothing Then

Dim rng As Excel.Range = table.DataLabelRange

rng.Value = rng.Value & “is a field on the pivot table object” rng.Speak()

table.RefreshTable()

End If

End If

End Sub

C#

private void button1_Click(object sender, EventArgs e)

{

Excel.PivotTable table = this.PivotTables(“PivotTable1”) as Excel.PivotTable;

if (table != null && table. DataLabelRange != null && table. DataLabelRange.Cells.Count > 0)

{

286

Pivot Table Automation

Excel.Range rng = table. DataLabelRange; for (int i = 1; i < rng.Cells.Count; i++)

{

Excel.Range cellRange = (Excel.Range)rng2.Cells[i, 1];

cellRange.Font.Bold = true;

}

}

}

Listing 7-5 Label formatting in a pivot table report

As the code in Listing 7-5 shows, a reference to the active PivotTable object is obtained using a named index. Then, the DataLabelRange property of the PivotTable object is used to obtain a suitable reference. However, what comes next underscores the power and flexibility of Microsoft Office. Using the reference to the Range object, a value is inserted into the caption. Then, Microsoft Office’s speech software kicks in to voice the contents of the range. This is fantastic!

For performance reasons, you should not apply formatting attributes to axes that contain a large number of values. Instead, you should try to filter or restrict the axes to the important values before applying the filter. You may also limit the code to iterating the visible range of the Excel spreadsheet.

Speech software is more than just a nicety; it represents a growing niche market where software is customized for the hearing impaired or visual impaired. The Speak method causes the cells of the target range to be spoken in row or column order. Microsoft Office automatically installs the speech assemblies upon first use. If your speakers are turned up and your sound card is functioning correctly, you will hear the label being spoken. The RefreshTable method is called to force the column captions to display with the added text. It is not required for the Speak method to function correctly.

Obviously, this is only the tip of the iceberg, and a lot more is possible. However, this example is crafted to show that such technology is now possible and is easy to implement. Aside from this functionality, the caption on the data label can also be oriented or formatted appropriately. Such functionality is trivial to implement and simply involves setting the appropriate property or calling the correct method.

Style Object Customization

Style customization in the pivot table is implemented through the Excel Range object. This is great news because it means that you do not have to learn new material in order to implement styles. Consider the code in Listing 7-6, which applies a particular style to the row range. The actual style code is based on code presented in Chapter 3.

Visual Basic

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim table As Excel.PivotTable = DirectCast(Me.PivotTables(“PivotTable1”), Excel.PivotTable)

If table IsNot Nothing Then

If table.RowRange IsNot Nothing Then

Dim rng As Excel.Range = table.RowRange

287