Code Walkthrough: Printing the PivotTable Component from Microsoft Internet Explorer

  • Article

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

 

Frank C. Rice
Microsoft Corporation

March 2003

Applies to:
     Microsoft® Office XP Web Components
     Microsoft Internet Explorer 5.0
     Microsoft Excel 2002

Summary:   Use another application's printing capability to enhance your Office XP Web Component applications. This article examines one of the samples from the Office XP Web Component Toolpack that allows you to print data from a PivotTable component hosted in Internet Explorer. (19 printed pages)

Contents

Introduction
Need for Better Printing
The Office XP Web Component Toolpack
The PivotTable List Printing Code Sample
The PivotTable List Printing Code Sample Web Page
Adding Command Buttons
Initializing the HTML Component File
The PrintPivotInXL Subroutine
Conclusion

Introduction

Johannes Gutenberg's idea for a printing press in 1452 is widely thought of as the origin of mass communication—it marked Western culture's first practical method of disseminating ideas and information from a single source to a large and dispersed audience. In the 550 years since that invention, printing still remains an extremely important aspect of today's culture despite the claims from some that software would replace paper as the media of choice. Let's face it, for discussing ideas and summarizing points, nothing beats having a printout to curl up with.

Most people would further agree that software has vastly improved and increased the printing options available to users, while relegating some old standards such as the typewriter to museums and attics (be glad that you weren't a major shareholder in one of the typewriter manufacturing firms in the 1980's).

Software has made it easier to combine printing with other features and capabilities. For example, Microsoft® Word combines the features of a powerful word-processor with a versatile printing capability. Microsoft Excel provides a powerful calculation engine with dynamic printing abilities. Likewise, albeit on a smaller scale, the Microsoft Office XP Web Components, consisting of the Spreadsheet component, PivotTable® component, Chart component, and Data Source component, have given developers an easy way to add additional capabilities to their applications.

Need for Better Printing

In spite of the many benefits that Web components can bring to your applications, one area where the Office XP Web Components do not provide any built-in support is printing. Instead, they rely on the hosting container (for example, Microsoft Internet Explorer) to supplement these capabilities and print the contained data (except for the Data Source component which has no user interface). This arrangement is sufficient for many printing jobs in which the user wants to see the data and does not need to exercise much control over how the data is printed. Keep in mind that the Office XP Web Components are Component Object Model (COM) controls that communicate with the host through various interfaces. Without going into a discussion of COM interfaces, this arrangement does not always lend itself to optimal printing. For example, although Microsoft Internet Explorer versions 5.5 and 6.0 feature greatly improved printing support over previous versions, they do not support all of the desired printing features for a component like the PivotTable component. Problems like row breaks, not respecting page boundaries, and not being able to horizontally paginate mean that data may be cropped and hard to read or even be unreadable.

This does not mean that you should not use the Office XP Web Components in applications that require printing. On the contrary, they still provide tremendous functionality that would be expensive or time-consuming to implement individually. And in most cases, the hosting application can provide the level of printing that fits the need. But what if your particular application does require more control when printing than the hosting application can provide? What is the solution? One option to consider is to take advantage of the printing features of another application. For example, Microsoft Excel was designed to provide rich support for printing the cells and ranges of a PivotTable list. So why not implement a solution that lets each participant contribute what they do best; Internet Explorer hosts and displays the Office XP Web Component, the Office XP Web Component provides the added functionality to the Web page, and another application, such as Excel, provides the printing capability. This combination allows you to provide users with the best of all worlds. In this article, we will look at a sample that uses a combination of Microsoft JScript®: and Microsoft Visual Basic® code to automate Excel and print the data from a PivotTable component hosted in Internet Explorer.

Note   By default, the Office XP Web Components are installed in all editions of Microsoft Office XP. You can find more information about installation and licensing in the knowledge base article 288729 OFFXP: General Information About Licensing and Using Office XP Web Components.

The Office XP Web Component Toolpack

In this article we will examine the PivotTable Printing Code sample. This sample is included in the Office XP Web Component Toolpack (referred to as the Toolpack for the remainder of the article). The Toolpack contains a wealth of information, demonstrations, and code samples to help you get the most out of the Office XP Web Components. This includes code walkthroughs, an interactive code library, and a number of code samples to illustrate creating business intelligence solutions with the PivotTable component. This article will also focus on the PivotTable component.

The PivotTable List Printing Code Sample

The PivotTable List Printing Code sample provides the code and files that can be used to create a dynamic link library (DLL) which links the PivotTable component hosted in a Web page in Internet Explorer with the printing capabilities of Excel.

Aa155758.odc_pvtprint01(en-us,office.10).gif

Figure 1. The PivotTable List Printing Code sample

The PivotTable List Printing Code sample adds a Printing object onto the PivotTable object. It has support for Print and Print Preview modes.

To summarize what the sample does, the code retrieves a hypertext markup language (HTML) representation of the current view from the PivotTable component. It then automates an instance of Excel and inserts the HTML view. Depending on the parameters passed to it, Excel will either display the data from the PivotTable component in Print Preview mode or print the data. This gives PivotTable component users all of the power of printing from Excel within their Web browser.

PivotTable List Printing Code Sample Contents

The PivotTable List Printing Code sample consists of the following files:

  • PivotPrint.htm—The Web page user interface containing the PivotTable component and the code that initializes and controls the toolbar, command buttons, and context-menu options.
  • PivotPrint.htc—The HTML component file that contains code that enhances the functionality of the Office XP Web Components. This file sets up certain events that respond when the user clicks command buttons on the PivotPrint.htm Web page. It also contains the procedure that calls the code that automates Excel.
  • Helper.cls—A class module that contains the code which automates Excel and passes the PivotTable component data. This module is contained in the PivotPrint.dll.
  • BrowserSafe.bas—This module contains the code to implement the IObjectSafety interface. This module is also contained in the PivotPrint.dll.
  • otphelp.cab—Contains the PivotPrint.dll and others files used to deploy the sample.
  • DataConn.js—Contains the code to connect to the data source and retrieve the records used by the PivotTable component.
  • PivotPrint.vbp—Contains Helper.cls and BrowserSafe.bas. Can be used to create the PivotPrint.dll from Microsoft Visual Basic.

The following figure illustrates where each file fits into the flow of the sample. Each file will be discussed in more detail in this article.

Aa155758.odc_pvtprint02(en-us,office.10).gif

Figure 2. Files used in the PivotTable List Printing Code sample

The PivotTable List Printing Code Sample Web Page

You can start the sample by opening the PivotPrint.htm Web page (see Figure 1) in Internet Explorer. The page consists of the PivotTable component, some explanatory text, and a combination of HTML and JScript code. When the page is first opened, the statement <body onload='init()'> ensures that the following JScript code runs. The init function does a couple of things.

...
function init()
   { 
   document.all.pt.PrintCodeBase = "../common/otphelp.cab";
   if (!InvoicesConnection(1).ApplyTo(pt))
      {
      alert ("Error connecting to data source.");
      }
   }
...

First, the init function sets the PrintCodeBase property equal to the location of the cabinet (CAB) installation file. CAB files are typically used for deploying ActiveX® and other controls and files. In this case, PrintCodeBase points to the otphelp.cab file which contains PivotPrint.dll and other files used to define the Printing class of the PivotTable component used with this sample. The PrintCodeBase property is defined in the PivotPrint.htc file which we will examine shortly.

The init function also contains an if statement that encloses the code to establish a connection to the data source for the PivotTable component. The InvoicesConnection function, which is stored in the DataConn.js file, creates the connection to the Invoices query in the Northwind.mdb sample database:

function InvoicesConnection(iDepth)
   {
   var ci = new ConnectionInfo();

   ci.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; 
      User ID=Admin; Data Source=" + getLocalPath(iDepth) + 
        "\\data\\Northwind.mdb; Mode=ReadWrite";
   ci.DataMember = "Invoices";

   return ci;
   }

The InvoicesConnection function returns a ConnectionInfo expression with the information to connect to the Invoices query. This information comes from The ConnectionInfo function.

**Note   **Functions in JScript, such as the

ConnectionInfo

function, are considered expressions when declared with parentheses.

The ConnectionInfo function contains the ApplyTo function that is used to create and execute a SQL statement. In this case, the SQL statement returns all of the records from the Invoices query:

function ConnectionInfo()
   {
   this.ConnectionString = null;
   this.DataMember = null;
...

   function applyTo(objIn, fOptionalViaCommandText)
      {
      var fViaCommandText;
      var iFSCount;

      if (arguments.length == 1)
         fViaCommandText = false;
      else
         fViaCommandText = fOptionalViaCommandText;
      
         if (this.ConnectionString == null || this.DataMember == null)
            return false;
      
         try 
            {
            objIn.ConnectionString = this.ConnectionString;

            if (fViaCommandText)
               objIn.CommandText = "Select * From 
                    [" + this.DataMember + "]";
            else
               objIn.DataMember = this.DataMember;

            if (objIn.ActiveView != null)
               iFSCount = objIn.ActiveView.FieldSets.Count;
            return true;
            }
         catch (e)
            {
            alert("Error in connecting to database:\n\n" + e.description);
            return false;
            }
        }
    }

In addition to the above code, the following code is also run when the page is opened:

<style>
...
...
   .Printing
      {
      behavior: url(PivotPrint.htc);
      }
</style>

This is an example of a dynamic HTML (DHTML) behavior. DHTML behaviors are components that encapsulate specific functionality or behavior on a page. When applied to a standard HTML element on a page, a behavior enhances that element's default behavior. In this segment of code, the behavior of the Printing class (which is defined for the PivotTable component in the following statement) is directed to refer to the code in the PivotPrint.htc file.

Note   HTML components (HTCs) provide a mechanism to implement components in script as DHTML behaviors. Saved with the .htc extension, an HTC is an HTML file that contains script and a set of HTC-specific elements that define the component. More information about HTML Components can be found in the "HTC Reference."

The code in the PivotPrint.htc file adds different functionality to the Printing class as we will see later in this article.

Adding Command Buttons

As mentioned earlier, the PivotPrint.htm Web page contains the following two command buttons:

  • Print via Print Behavior Object Model button. Clicking this button prints the contents of the PivotTable component by executing the onclick event which calls the omPrint function.
  • Print Preview via Print Behavior Object Model button. This button is the counterpart of the above button in that it allows the user to display the contents of the PivotTable component in print preview. Clicking the button executes the onclick event which calls the omPrintPreview function.

The omPrint and omPrintPreview functions appear below:

function omPrint()
   {
   document.all.pt.Printing.Print();
   }

function omPrintPreview()
   {
   document.all.pt.Printing.PrintPreview();
   }

The statements in each of the functions refer to the document object that represents the HTML document displayed in the browser window. The all collection contains the collection of HTML elements contained by the document object. Pt is the identifier assigned to the PivotTable component and Printing is the class assigned to that component.

When either of the printing buttons is clicked by the user, execution is transferred to the code in the PivotPrint.htc.

Initializing the HTML Component File

In addition to code that is run to initialize items on the Web page, there is also code that runs in the PivotPrint.htc page to initialize items there. When the PivotPrint.htc page is initially loaded into memory, the statement <public:attach event='oncontentready' onevent='init()'/> attaches the init function to the oncontentready event. The oncontentready event is triggered when the contents of the PivotTable component has been parsed and loaded into memory.

Once the oncontentready event triggers, the init function runs:

function init()
   {
   element.attachEvent("OnConnect", beforeAttachPrinting);
   }
function beforeAttachPrinting()
   {
   window.setTimeout(attachPrinting, 1);  // 'slip the clutch' 
        in order to fix an issue with IE5: when you refresh you 
             get a weird IE cannot open this page operation aborted error.
   }

The init function executes the attachEvent method that attaches the beforeAttachPrinting function to the OnConnect event. The OnConnect event occurs when the PivotTable component connects to the PivotPrint.htc file after the user has clicked the button that executes either the document.all.pt.Printing.Print() statement or the document.all.pt.Printing.PrintPreview() statement. The beforeAttachPrinting function calls the setTimeout method that then calls the attachPrinting function after a 1 millisecond delay used to overcome an issue with Internet Explorer.

function attachPrinting()
   {
   var objColl, i;
   if (!fPrintingAttached)
      {

      if (printing == null)
         printing = new ExcelPrinting();

         objPrint = null;
         objColl = window.document.all.tags("OBJECT");

         for (i=0; i<objColl.length; i++)
            {
            if (objColl[i].id == "owcPrintingAddin")
               {
               objPrint = objColl[i];
               }
            }
      if (objPrint == null)
               {
               objPrint = document.createElement("OBJECT");   
               // dynamically create a printing object
         
               if (sCodebase != null)
                  objPrint.codeBase = sCodebase;

                  objPrint.id = "owcPrintingAddin";
                  objPrint.classid = "CLSID:A254CD69-84FF-43DA-
                     B3DC-06D5D27B6FA9"

                  objPrint.attachEvent("onreadystatechange", 
                      printObjectReady);
                  addToolbarButtons();
                  window.document.body.appendChild(objPrint);         
               }

               element.attachEvent("CommandExecute", 
                    pivotCommandExecute);
               element.attachEvent("BeforeContextMenu", 
                   pivotBeforeContextMenu);

               fPrintingAttached = true;
      }
   }

The attachPrinting function sets the printing variable equal to a new instance of the ExcelPrinting function. The ExcelPrinting function will be discussed shortly. The attachPrinting function then dynamically creates the printing <OBJECT> and sets its id to owcPrintingAddin.

Then, the following two statements are also executed:

  • element.attachEvent("CommandExecute", pivotCommandExecute) is used to attach the pivotCommandExecute function to the CommandExecute event.
  • element.attachEvent("BeforeContextMenu", pivotBeforeContextMenu) that attaches the pivotBeforeContextMenu function to the BeforeContextMenu event.

The pivotCommandExecute function checks to see which button the user clicked and executes either the print or printpreview functions that reside in the ExcelPrinting function.

function pivotCommandExecute(vCommand, fSucceeded)
   {
   switch (vCommand)
      {
      case "printDirect":
         printing.Print();
         break;
      case "printPreview":
      printing.PrintPreview();
      break;
   }
   }

The other attachEvent method action involves the pivotBeforeContextMenu function that is used to add Print and PrintPreview options to the context menu. The pivotBeforeContextMenu function is left for you to explore.

The ExcelPrinting function, among other things, calls the PrintPivotInXL subroutine and passes the argument that determines whether the data is displayed in Print Preview mode or whether it is printed:

function ExcelPrinting()
   {
...
   function print()
      {
      objPrint.PrintPivotInXL(element, false, false);
      }

   function printPreview()
      {
      objPrint.PrintPivotInXL(element, true, false);
      }

   function getUseToolbarButtons()
      {
      return fUseToolbarButtons;
      }
...
   }

The PrintPivotInXL subroutine is responsible for activating Excel and either printing the PivotTable component data or displaying the data in PrintPreview mode.

The PrintPivotInXL Subroutine

The PrintPivotInXL subroutine is located in the Helper.cls class module. This procedure copies the contents of the PivotTable component into Excel.

First, the Implements IObjectSafety statement tells the client machine that is running the subroutine that the code is safe. The code to implement the IObjectSafety interface is spread between Helper.cls and BrowserSafe.bas. Next, the subroutine copies the data in the PivotTable component to the Microsoft Windows® Clipboard. An instance of Excel is then opened and a new workbook created. Then, the contents of the Windows Clipboard is copied to the first worksheet and the worksheet is set up for printing by calling routines to retrieve the row and column headers and other PivotTable component information. If the fPreview argument is set to True then the worksheet is set to Print Preview mode. Otherwise, the worksheet is printed. And finally, the objects that were created are closed and the resources released.

Implements IObjectSafety

'-- PrintPivotInXL
...
Public Sub PrintPivotInXL(ptable As Object, fPreview 
      As Boolean, fLandscape As Boolean)
   Dim xlApp As Object           'Ref to Excel application
   Dim wkbk As Object            'Ref to new workbook
   Dim wksht As Object           'Ref to worksheet
   Dim pgsetup As Object         'Ref to page setup object
   Dim nCols As Long             'Number of physical columns used 
                                                      by row headings
   Dim nRows As Long             'Number of physical rows used 
                                                      by column headings
        
   On Error GoTo Err_PrintPivot 
... 
   'Create an instance of Excel (should work with
   'Excel 97 or 2000 or maybe even 95)
   On Error Resume Next
   Set xlApp = Nothing
   Set xlApp = CreateObject("Excel.Application")
   On Error GoTo Err_PrintPivot
    
   'If the create worked...
   If Not (xlApp Is Nothing) Then
      'Add a new workbook to the Excel instance
      Set wkbk = xlApp.Workbooks.Add()
        
      'Get a reference to the first worksheet in the new book
      Set wksht = wkbk.Worksheets(1)
        
      'Paste the contents of the clipbard into that sheet
      wksht.Paste wksht.Range("a1")
        
      'Call AutoFit over the used range to make sure
      'everything is visible.
      wksht.UsedRange.Columns.AutoFit
        
      'Get the page setup object
      Set pgsetup = wksht.PageSetup
        
      'Now setup the print page to repeat the
      'row and column headings
        
      nRows = GetNumHeaderRows(ptable)
      If (nRows > 0) Then
         On Error GoTo PrinterError
         pgsetup.PrintTitleRows = "$1:$" & CStr(nRows)
         On Error GoTo 0
      End If
        
      nCols = GetNumHeaderCols(ptable)
      If nCols > 0 Then
         pgsetup.PrintTitleColumns = "$A:$" & Chr(Asc("A") + (nCols - 1))
      End If
        
      'set page orientation
      If fLandscape Then
         pgsetup.Orientation = 2 'xlLandscape
      Else
         pgsetup.Orientation = 1 'xlPortrait
      End If
        
      'Also tell it to print grid lines
      pgsetup.PrintGridlines = True
        
      'Set a default header/footer
      pgsetup.CenterHeader = ptable.ActiveView.TitleBar.Caption
      pgsetup.CenterFooter = "Page &P of &N"
        
      If fPreview Then
         'put the current worksheet into print preview
         xlApp.UserControl = True
         xlApp.Visible = True
         wksht.PrintPreview
      Else
         'just kick off the print
         xlApp.UserControl = True
         xlApp.Visible = True
         xlApp.dialogs(8).Show
      End If 'fPreview
         'close down our instance of Excel
         'Note, to let the user interact with
         'Excel instead, see the code in the above block
         xlApp.Visible = False
         xlApp.DisplayAlerts = False
         xlApp.Quit

         'release all our Excel objects
         Set pgsetup = Nothing
         Set wksht = Nothing
         Set wkbk = Nothing
         Set xlApp = Nothing
   Else
      'Failed to create an instance of Excel
      Dim sMsg
      sMsg = "Unable to create the Excel.Appliation object!"
      sMsg = sMsg & " This could be for one of the following 
           reasons:" & String(2, vbCrLf)
      sMsg = sMsg & vbCrLf & String(4, " ") & "- Excel is not 
              installed on this system."
      sMsg = sMsg & vbCrLf & String(4, " ") & "- Excel is not 
              registered properly."
        
      MsgBox sMsg, vbCritical
   End If 'xlApp is nothing
    
   Exit Sub
PrinterError:
   MsgBox "Could not print using Microsoft Excel.
           " + vbCrLf + vbCrLf + "A possible reason for this might be 
               that you do not have a printer installed or the selected 
                  printer is disabled.", vbExclamation, "Pivot Printing"
   Exit Sub
Err_PrintPivot:
   MsgBox "Encountered the following error:" & 
          String(2, vbCrLf) & Err.Description, vbCritical, "OWC Helper Library"
   Exit Sub
End Sub 'PrintPivotInXL()

'-- GetNumHeaderRows
'
'   Returns the physical number of grid rows
'   taken up by the column headings/title bar in the pivot table
'
Private Function GetNumHeaderRows(ptable)
   'Get the number of included fields on the column axis
   GetNumHeaderRows = 
            GetNumIncFields(ptable.ActiveView.ColumnAxis)
    
   'Add to that a row for the column axis field headers themselves
   'if there are fields and if the drop zone is visible
   If GetNumHeaderRows = 0 Then
      If ptable.ActiveView.ColumnAxis.Label.Visible Then
         GetNumHeaderRows = GetNumHeaderRows + 1
      End If
   Else
      GetNumHeaderRows = GetNumHeaderRows + 1
   End If
    
   'Add to that a row for the row axis/total headers
   '(note: even if total headings are on the rows, there
   ' will still be a row reserved for the row axis field headings)
   GetNumHeaderRows = GetNumHeaderRows + 1

   'Add to that a row if the title bar is visible
   If ptable.ActiveView.TitleBar.Visible Then 
          GetNumHeaderRows = GetNumHeaderRows + 1
    
   'Add 1 row if no filter fields, and 2 rows if there is at least one
   If ptable.ActiveView.FilterAxis.Fieldsets.Count > 0 Then
      GetNumHeaderRows = GetNumHeaderRows + 2
   Else
      GetNumHeaderRows = GetNumHeaderRows + 1
   End If 'filter fields
End Function 'GetNumHeaderRows()

'-- GetNumHeaderCols
'
'   Returns the physical number of grid columns
'   taken up by the row headings in the pivot table
'
Private Function GetNumHeaderCols(ptable)
   GetNumHeaderCols = 
          GetNumIncFields(ptable.ActiveView.RowAxis)
    
   'If there are zero included fields, still leave room
   'for the drop zone (one column) if it is visible
   If (GetNumHeaderCols = 0) And 
     (ptable.ActiveView.RowAxis.Label.Visible) Then
      GetNumHeaderCols = 1
   End If
End Function 'GetNumHeaderCols()

'-- GetNumIncFields
'
'   Common routine for counting the number of
'   included fields on a result axis in a PivotTable
'
Private Function GetNumIncFields(axis)
   Dim fset
   Dim fld
   Dim ct
    
   ct = 0
    
   For Each fset In axis.Fieldsets
      For Each fld In fset.Fields
         If fld.IsIncluded Then
            ct = ct + 1
         End If
      Next 'fld
   Next 'fset
    
   GetNumIncFields = ct
End Function 'GetNumIncFields()

Function GetResource(iResourceID As Integer)
   Set GetResource = LoadResPicture(iResourceID, vbResBitmap)
End Function

Private Sub IObjectSafety_GetInterfaceSafetyOptions(ByVal riid As _
   Long, pdwSupportedOptions As Long, pdwEnabledOptions As Long)

   Dim Rc      As Long
   Dim rClsId  As udtGUID
   Dim IID     As String
   Dim bIID()  As Byte

   pdwSupportedOptions = 
      INTERFACESAFE_FOR_UNTRUSTED_CALLER Or _
        INTERFACESAFE_FOR_UNTRUSTED_DATA

   If (riid <> 0) Then
      CopyMemory rClsId, ByVal riid, Len(rClsId)

      bIID = String$(MAX_GUIDLEN, 0)
      Rc = StringFromGUID2(rClsId, VarPtr(bIID(0)), MAX_GUIDLEN)
      Rc = InStr(1, bIID, vbNullChar) - 1
      IID = Left$(UCase(bIID), Rc)

      Select Case IID
         Case IID_IDispatch
            pdwEnabledOptions = IIf(m_fSafeForScripting, _
            INTERFACESAFE_FOR_UNTRUSTED_CALLER, 0)
            Exit Sub
         Case IID_IPersistStorage, IID_IPersistStream, _
            IID_IPersistPropertyBag
            pdwEnabledOptions = IIf(m_fSafeForInitializing, _
            INTERFACESAFE_FOR_UNTRUSTED_DATA, 0)
            Exit Sub
         Case Else
            Err.Raise E_NOINTERFACE
            Exit Sub
      End Select
   End If
End Sub

Private Sub IObjectSafety_SetInterfaceSafetyOptions(ByVal riid As _
      Long, ByVal dwOptionsSetMask As Long, 
          ByVal dwEnabledOptions As Long)      
End Sub

Conclusion

In this article, we examined the PivotTable Printing Code sample that comes with the Office XP Web Component Toolpack. The sample takes advantage of the printing ability of Excel to print and preview the data in the PivotTable component hosted in Internet Explorer. The Office XP Web Component Toolpack contains a number of samples to illustrate the new features and improvements of the Office XP Web Components. Using the components can enhance the functionality of your own applications without having to add the code yourself.