Repurpose SharePoint Services Data Using Excel's SpreadsheetML

NOTE: This article makes use of the custom Outlook AppointmentItem form as well as a custom SharePoint list explained in my previous articles, 1) Outlook the Way You Want It—Build Custom Outlook GUIs with WinForms & VSTO and 2) Easy Time & Billing with SharePoint, Outlook, & Excel (and VSTO too!), respectively. The custom Appointment form is included with this article's source-code file. The instructions for building the SharePoint list can be found in the previous articles. After reading through this article you will know the following. Click here... Here are the prerequisites for building the projects in this article.

It's all XML these days and Microsoft Office is no exception. Microsoft has steadily increased the level of XML support within Office in each release of the product since Office 2000. In fact, XML is so central to the Office development platform that XML will be the default file format of Office "12" when it is released next year. Clearly, XML is a "must know" for anyone looking to develop on the Office platform in the future. This is great—but there are already extensive XML capabilities in Office and today we will open up the SpreadsheetML Schema (aka Excel XML Schema) and dive in to see how far we can get in the next 30 minutes.

This article completes the example time system scenario discussed in my previous two articles by adding a simple reporting function to the existing VSTO-based Outlook add-in. At this stage, the user has created some time records using the custom appointment form (created in article 1) and these records have also been saved to a custom list in SharePoint (created in article 2). What's needed now is a method for the user to spark the creation of an elegant time report that opens inside Excel.

Figure 1. The Example Scenario Illustrated

We will accomplish this task through the process shown in Figure 1.

The first step in the process is to access the custom SharePoint list and retrieve Time records by calling the GetListItems method of the SharePoint Web service. In step 2, the XML returned from SharePoint is transformed to SpreadsheetML by applying an XSL transform file against it. The third and final step saves the resulting XML file-to-file system and opens it in Excel for display to the user.

SpreadsheetML is an XML schema that defines the elements that make an Excel file. As you might expect the schema includes several namespaces such as urn:schemas-microsoft-com:office:excel and urn:schemas-microsoft-com:office:spreadsheet that represent the Excel and Spreadsheet objects respectively. Each namespace contains the elements that describe the information contained in these objects. For example with Excel, key elements include Styles, Workbook, and Worksheet. With the Spreadsheet namespace, key elements are Name, Row, Cell, etc.

To learn all the ins-and-outs of the Office XML Reference Schemas, I recommend downloading the Office 2003: XML Reference Schemas from MSDN. This download includes all the XML schemas for Office (.xsd files) as well as some documentation that will take you as deep as you would like to go into the subject.

For the purposes of this article and this example scenario, the information in Table 1 provides enough information about the elements utilized in the final Report.xml created by the application.

Once you understand the basics of SpreadsheetML, creating the XSL file is a straightforward process. In fact, the easiest way to get started building the XSL file is to use Excel. You can use Excel to build and format the report file as you want and then save it as XML. This XML file could then be used as the basis for building the XSL. This strategy allows you to focus on the actual syntax of XSL that inserts the values read from the XML data input into the XSL file. Figure 2 shows how the file looks when displayed in Excel. In addition, Figure 2 unveils a snippet of the underlying SpreadsheetML.

Figure 2. The Report Template and its SpreadsheetML

I named the transform file for this project Transform.xsl and saved it in the project's file folder. Listing 1 contains the contents of the xsl file used in this project.

This xsl file utilizes the basic Excel XML schema elements that are typical of most Excel files. The key here is the for-each xsl loop that reads all row items of the expected XML that will be provided by SharePoint.

<!--Create a line for each Row in the XML file-->
<xsl:for-each select="/listitems/rs:data/z:row">
  <Row ss:Height="21">
    <Cell ss:StyleID="s33">
      <Data ss:Type="DateTime">
      	<xsl:value-of select="substring-before(@ows_Begin,' ')"/>
      </Data>
    </Cell>
    <Cell ss:StyleID="s35">
      <Data ss:Type="String">
       <xsl:value-of select="@ows_Description"/>
      </Data>
    </Cell>
    <Cell ss:StyleID="s34">
      <Data ss:Type="Number">
        <xsl:value-of select="@ows_TotalTime div 60"/>
      </Data>
    </Cell>
  </Row>
</xsl:for-each>

The for-each statement uses an XPath query to navigate to the z:row portion of the XML chunk that is provided by Windows SharePoint Services (WSS). Then for each row found that matches the query, the xsl creates a row of SpreadsheetML, inserting the row's beginning date, description, and time values. The beginning date value (ows_Begin) requires more processing as only the date, without any time data, is needed. By using the substring-before xsl function, the code inserts only the date characters before the first space found in the value for ows_Begin. The time values that follow the space character are ignored. The value for ows_TotalTime is in minutes, to convert the time to hours the xls divides by sixty.

The data for the report resides inside a custom SharePoint list named Time. The project retrieves the data by calling the SharePoint Web service's GetListItems method. Listing 2 contains all the code for the project's WSS class (code added for this article is formatted in bold). The methods in this class handle reading and writing data to the referenced list. For this project, there is only one new method named RetrieveTimeRecords.

Friend Sub RetrieveTimeRecords()
    Dim serviceLists As New wss_list.Lists()
    serviceLists.Credentials = System.Net.CredentialCache.DefaultCredentials
    Dim xDoc As New XmlDocument
    Dim nodeResponse As XmlNode
    Dim xFileName As String = "C:\_DevX Projects\Data.xml"
    Dim xTransformName As String = "C:\_DevX Projects\Transform.xsl"
    Dim xReportName As String = "C:\_DevX Projects\Report.xml"

    Dim sXML As String = "<listitems xmlns:rs=""urn:schemas-microsoft-com:rowset"" xmlns:z=""#RowsetSchema"">"
    Dim sXMLend As String = "</listitems>"

    nodeResponse = serviceLists.GetListItems("Time", Nothing, Nothing, Nothing, Nothing, Nothing)
    If nodeResponse.HasChildNodes Then

      Dim fs As New FileStream(xFileName, FileMode.Create)
      Dim xtw As New XmlTextWriter(fs, System.Text.Encoding.Unicode)
      'Write the XML Declaration for the document
      xtw.WriteRaw(sXML)
      xtw.WriteRaw(nodeResponse.InnerXml)
      xtw.WriteRaw(sXMLend)
      xtw.Close()

      'Transform to SpreadsheetML
      Dim xTran As New Xsl.XslCompiledTransform
      xTran.Load(xTransformName)
      xTran.Transform(xFileName, xReportName)

      Dim xlApp As Object
      xlapp = CreateObject("Excel.Application")
      xlApp.workbooks.open(xReportName)
      xlApp.visible = True


    End If
  End Sub

RetrieveTimeRecords handles the tasks explained in the Example Scenario with the exception of creating a button in the Outlook toolbars (which is explained later). The method retrieves XML data from WSS that represents time records. The XML provided by WSS is only an XML node and not a complete XML document. Because of this, a couple of extra lines are needed to create a fully formed XML document that contains the appropriate XML namespaces. These namespaces, contained in the sXML variable, must be included in order to query the node values with XPath queries in the XSL transform file.

Once the SharePoint XML is formatted properly the method applies the XSL transform file and sends the output results to a file named Report.xml. Once saved, the method opens the file in Excel to complete the reporting process.

To complete the project, you need to provide a toolbar button that will allow the user to create an updated time report any time they like. I placed the code for this inside the ThisApplication class. Listing 3 shows the full code for the class with the updates for this project in bold format.

Figure 3. The Outlook Standard Toolbar Displaying the Create Time Report Button

The Application_Startup event contains code that creates a new CommandBarButton and adds it to Outlook's Standard command bar. The class also contains a new method to handle the button's Click event, which will initiate the time reporting process by calling RetrieveTimeRecords (which resides in the WSS class discussed earlier).

When the project loads into Outlook (which occurs when Outlook opens), the project creates a new button and places it as the last button of the Standard toolbar (Figure 3).

Outlook handles CommandBars differently from the other Office applications. With Outlook, the CommandBars are attached to the Explorer object instead of the Application object. So, to crease the button, the method finds the standard command bar of the ActiveExplorer object. Once this reference is made, the method creates the button, adds it to the standard command bar, and sets its display properties.

  Private cb As OFF.CommandBar
  Private WithEvents cbb As OFF.CommandBarButton

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

    tyINS = Me.Inspectors

    cb = Me.ActiveExplorer.CommandBars("Standard")
    cbb = cb.Controls.Add(OFF.MsoControlType.msoControlButton, , , , True)
    With cbb
      .Style = Microsoft.Office.Core.MsoButtonStyle.msoButtonIconAndCaption
      .Caption = "Create Time Report"
      .Tag = "TimeButton"
      .FaceId = 2105

    End With


  End Sub

Now that the button exists it needs some code that will initiate the time report creation process. The best place for this is the button's Click event.

Private Sub cbb_Click(ByVal Ctrl As Microsoft.Office.Core.CommandBarButton, _
    ByRef CancelDefault As Boolean) Handles cbb.Click
    Dim ws As New WSS
    ws.RetrieveTimeRecords()

  End Sub

Figure 4. The Newly Generated Report.xml File Displayed in Excel.

As you can see the event creates a reference to the projects WSS class and calls the RetrieveTimeRecords function built earlier.

Everything should be in place now, so it should be safe to press F5 and see the fruits of your labor. So hit it!

After Outlook opens, go ahead and click the Create Time Report button. After a few seconds Excel should open and display the time report (Figure 4).

People that use Office every day love to have the ability to view data from other systems inside Word and Excel. Now that Microsoft has opened up the file formats of Excel and Word via the Office 2003 Reference Schemas, a whole new world of opportunities exist for developers to easily provide data to business users that they an consume inside Office.

Ty Anderson runs Cogent Company, a consultancy in Dallas specializing in leveraging technology to enable business strategy. Ty is a regular contributor to the Microsoft Developer Network (MSDN) and has recently written a book, Office Programming 2003: Real World Applications, focused entirely on building applications with the Microsoft Office System 2003.

Submit article to: