Dive into SpreadsheetML (Part 2 of 2)

 

Erika Ehrli
Microsoft Corporation

June 2006

Applies to:
Microsoft Office Excel 2003
Microsoft Office 2003 Edition XML Schema References
Microsoft SQL Server 2000
Microsoft Visual Studio 2005
Microsoft Visual Studio 2003

Summary: Learn how to use SpreadsheetML to create Microsoft Office Excel workbooks programmatically. (17 printed pages)

Contents

Introduction
A Real-World Business Scenario: Change Management Systems
Creating Excel Spreadsheets Programmatically
Conclusion
Additional Resources
Acknowledgments

Introduction

In Part 1 of this two-part series, I explored the concept and schema definition of SpreadsheetML, and explained some scenarios where it makes sense to programmatically generate Microsoft Office Excel workbooks using SpreadsheetML.

I have always thought that no theory is worth much without the practice, so I cannot leave you without the fun of experimenting with SpreadsheetML. This article, Part 2 of the series, explores how to create an Excel workbook. I describe a common business scenario for project managers, and walk you through the process of retrieving change management information from a custom-built project database. Then, I uncover the magic used to generate an Excel workbook.

A Real-World Business Scenario: Change Management Systems

When you work on a software development project, you need to do change management to control any change that happens. Change management systems enable you to keep track of the changes and define priorities, and help members of a team understand the impact of changes. You can work with different kinds of change management systems—from a paper notebook where you write down feedback, to sophisticated Web issue-tracking systems that store information in databases. Some companies prefer to use Excel workbooks as a change management system, and you can even find Excel change management templates (for example, this scope change log). Working with Excel is powerful because you can extract data from multiple files, databases, or systems to create an Excel workbook change management report. Or, you can type change management information in an Excel file and save it as an XML spreadsheet, so that other systems can extract data and send it to another data store or messaging system for extra processing.

Creating Excel Spreadsheets Programmatically

In this section, I explain, step by step, how to extract data (formatted as XML) from a project management database, generate an Extensible Stylesheet Language Transformation (XSLT) file to process XML files, and create a sample application that programmatically generates Excel workbooks.

To build this sample application, you need to follow three simple steps, as shown in Figure 1.

Steps to generate SpreadsheetML programmatically

Figure 1. Steps to generate SpreadsheetML programmatically

Step 1: Extracting the Data from an External Datasource

Suppose that you have a project management database where you store project information. Inside the database, you have a Projects table where you keep information related to the project. You also have a ChangeRequestLog table where you track change request items by project. The following figure shows a database diagram that contains the Projects table and the ChangeRequestLog table:

Projects table and ChangeRequestLog table

Figure 2. Projects table and ChangeRequestLog table

As you can see in the previous diagram, these two tables have a master-detail relationship. Each Project table member has a one-to-many relationship to ChangeRequestLog table members. The tables are linked by a primary key in the Projects table to a foreign key in the ChangeRequestLog table.

Creating a SQL Server 2000 Database with the Previous Tables

You can create a database (FabrikamProjects) inside Microsoft SQL Server 2000 and run the script below to create the previous tables and the corresponding foreign key.

[TSQL]
CREATE TABLE [dbo].[ChangeRequestLog] (
   [ChangeID] [int] IDENTITY (1, 1) NOT NULL ,
   [ProjectID] [int] NULL ,
   [RequestDate] [datetime] NULL ,
   [RequestedBy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
   [ReviewedBy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
   [ChangeStatus] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
   [Description] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[Projects] (
   [ProjectID] [int] IDENTITY (1, 1) NOT NULL ,
   [ProjectName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
   [ProjectManager] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[ChangeRequestLog] ADD 
   CONSTRAINT [PK_ChangeRequestLog] PRIMARY KEY  CLUSTERED 
   (
      [ChangeID]
   )  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Projects] ADD 
   CONSTRAINT [PK_Projects] PRIMARY KEY  CLUSTERED 
   (
      [ProjectID]
   )  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[ChangeRequestLog] ADD 
   CONSTRAINT [FK_ChangeRequestLog_Projects] FOREIGN KEY 
   (
      [ProjectID]
   ) REFERENCES [dbo].[Projects] (
      [ProjectID]
   )
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

After you create the tables, do not forget to fill the Projects and ChangeRequestLog tables with sample data.

You can fill the Projects table with sample data, as shown in Table 1.

Table 1. Projects table sample data

ProjectID ProjectName ProjectManager
1 Publishing System Esther Engstrom
2 Helper Tools Frank Martinez

You can fill the ChangeRequestLog table with sample data, as shown in Table 2.

Table 2. ChangeRequestLog table sample data

ChangeID ProjectID RequestDate RequestedBy ReviewedBy ChangeStatus Description
1 2 11/1/2005 Esther Engstrom Frank Martinez Open Update XML files.
2 2 11/1/2005 Frank Martinez Esther Engstrom Open Create Excel import feature.
3 2 11/1/2005 Esther Engstrom Frank Martinez Pending Create Excel export feature.
4 2 11/1/2005 Frank Martinez Esther Engstrom Pending Create change log report.
5 1 11/1/2005 Esther Engstrom NULL Open Improve navigation.
6 1 11/1/2005 Esther Engstrom NULL Open Add Change Request report to the system.
7 1 11/1/2005 Esther Engstrom NULL Pending Send notifications (e-mail messages) to users.

Microsoft SQL Server 2000 enables you to build SQLXML queries. You can use the FOR XML EXPLICIT mode to return a result set formatted as XML. In an EXPLICIT mode, the query writer controls the shape of the XML document returned by the execution of the query. The query must be written in a specific way, so that additional information about expected nesting is explicitly specified as part of the query. You can create a stored procedure or query that retrieves all the Projects and their corresponding ChangeRequestLog items as XML:

<ProjectChangeRequests> 
   <Project ProjectID="1"> 
      <ProjectName>Publishing System</ProjectName> 
      <ProjectManager>Esther Engstrom</ProjectManager> 
      <ChangeRequestLog> 
         <ChangeID>5</ChangeID> 
         <ChangeRequestDate>2005-11-01T00:00:00</ChangeRequestDate> 
         <RequestedBy>Esther Engstrom</RequestedBy> 
         <ChangeStatus>Open</ChangeStatus> 
         <Description>Improve navigation. </Description> 
      </ChangeRequestLog> 
      <ChangeRequestLog> 
         <ChangeID>6</ChangeID> 
         <ChangeRequestDate>2005-11-01T00:00:00</ChangeRequestDate> 
         <RequestedBy>Esther Engstrom</RequestedBy> 
         <ChangeStatus>Open</ChangeStatus> 
         <Description>Add Change Request report to the system. </Description> 
      </ChangeRequestLog> 
      <ChangeRequestLog> 
         <ChangeID>7</ChangeID> 
         <ChangeRequestDate>2005-11-01T00:00:00</ChangeRequestDate> 
         <RequestedBy>Esther Engstrom</RequestedBy> 
         <ChangeStatus>Pending</ChangeStatus> 
         <Description>Send notifications (e-mail messages) to users. </Description> 
      </ChangeRequestLog> 
   </Project> 
   <Project ProjectID="2"> 
      <ProjectName>Helper Tools</ProjectName> 
      <ProjectManager>Frank Martinez</ProjectManager> 
      <ChangeRequestLog> 
         <ChangeID>1</ChangeID> 
         <ChangeRequestDate>2005-11-01T00:00:00</ChangeRequestDate> 
         <RequestedBy>Esther Engstrom</RequestedBy> 
         <ReviewedBy>Frank Martinez</ReviewedBy> 
         <ChangeStatus>Open</ChangeStatus> 
         <Description>Update XML files.</Description> 
      </ChangeRequestLog> 
      <ChangeRequestLog> 
         <ChangeID>2</ChangeID> 
         <ChangeRequestDate>2005-11-01T00:00:00</ChangeRequestDate> 
         <RequestedBy>Frank Martinez</RequestedBy> 
         <ReviewedBy>Esther Engstrom</ReviewedBy> 
         <ChangeStatus>Open</ChangeStatus> 
         <Description>Create Excel import feature.</Description> 
      </ChangeRequestLog> 
      <ChangeRequestLog> 
         <ChangeID>3</ChangeID> 
         <ChangeRequestDate>2005-11-01T00:00:00</ChangeRequestDate> 
         <RequestedBy>Esther Engstrom</RequestedBy> 
         <ReviewedBy>Frank Martinez</ReviewedBy> 
         <ChangeStatus>Pending</ChangeStatus> 
         <Description>Create Excel export feature.</Description> 
      </ChangeRequestLog> 
      <ChangeRequestLog> 
         <ChangeID>4</ChangeID> 
         <ChangeRequestDate>2005-11-01T00:00:00</ChangeRequestDate> 
         <RequestedBy>Frank Martinez</RequestedBy> 
         <ReviewedBy>Esther Engstrom</ReviewedBy> 
         <ChangeStatus>Pending</ChangeStatus> 
         <Description>Create change log report.</Description> 
      </ChangeRequestLog> 
   </Project> 
</ProjectChangeRequests> 

Building a SQLXML Query Using the FOR XML EXPLICIT Mode

You can create a stored procedure (dsp_GetChangeRequestsByProject) inside Microsoft SQL Server 2000 and then run the following script to generate the previously listed XML code:

[TSQL]
CREATE PROCEDURE dbo.dsp_GetChangeRequestsByProject
AS
SELECT 1            as Tag, 
NULL                    as Parent,
Projects.ProjectID      as [Project!1!ProjectID],
Projects.ProjectName    as [Project!1!ProjectName!element],
Projects.ProjectManager as [Project!1!ProjectManager!element],
NULL                    as [ChangeRequestLog!2!ChangeID!element],
NULL                    as [ChangeRequestLog!2!ChangeRequestDate!element],
NULL                    as [ChangeRequestLog!2!RequestedBy!element],
NULL                    as [ChangeRequestLog!2!ReviewedBy!element],
NULL                    as [ChangeRequestLog!2!ChangeStatus!element],
NULL                    as [ChangeRequestLog!2!Description!element]
FROM Projects

UNION ALL
SELECT 2,1,
         Projects.ProjectID,
    Projects.ProjectName,
    Projects.ProjectManager,
         ChangeRequestLog.ChangeID,
         ChangeRequestLog.RequestDate,
         ChangeRequestLog.RequestedBy,
         ChangeRequestLog.ReviewedBy,
         ChangeRequestLog.ChangeStatus,
         ChangeRequestLog.Description
FROM Projects, ChangeRequestLog
WHERE dbo.ChangeRequestLog.ProjectID = dbo.Projects.ProjectID
ORDER BY [Project!1!ProjectID]
FOR XML EXPLICIT
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

**Note   **For more information about creating and running stored procedures in SQL Server 2000, see the Additional Resources section of this article. For more information about SQLXML FOR XML EXPLICIT mode, read the following article: A Survey of Microsoft SQL Server 2000 XML Features.

Step 2: Build a Template SpreadsheetML File and Modify It Using XSLT Processing Instructions

After the source data is stored in an XML file, we generate the Excel XML Spreadsheet file. We start by creating an Excel workbook template that allows software development teams to review a scope change log. You use this template as the structure for the workbook that you generate programmatically.

To create a scope change log Excel workbook template

  1. Start Excel.

  2. Create an Excel workbook identical to the one shown in Figure 3.

    Scope change log Excel workbook template

    Figure 3. Scope change log Excel workbook template

    **Note   **You can download a better-looking scope change log template from the Microsoft Office Online Web site.

  3. On the File menu, click Save As.

  4. In the Save as type list, select the XML Spreadsheet (*.xml) option.

  5. In the File name box, type ScopeChangeLog.xml, as shown in Figure 4.

    Save the file as ScopeChangeLog.xml

    Figure 4. Save the file as ScopeChangeLog.xml

  6. Click Save.

After you create the template XML Spreadsheet file, you save the file as an XSL file and modify the code to create a valid XSL transformation file. The XSL transformation file transforms XML data from the previously created FabrikamProjects database into an XML Spreadsheet file that Excel can open.

To create a valid XSL file

  1. Using Microsoft Windows Explorer, rename ScopeChangeLog.xml to be ScopeChangeLog.xsl.

  2. Using a text editor (for example, Notepad, Microsoft Visual Studio 2003, or Microsoft Visual Studio 2005), open the ScopeChangeLog.xsl file.

  3. Add the following lines (indicated in bold) between the <?xml version="1.0"?> tag and the <?mso-application progid="Excel.Sheet"?> tag:

    <?xml version="1.0"?> 
    <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> 
      <xsl:template match="/"> 
      <?mso-application progid="Excel.Sheet"?> 
    
  4. At the end of the file, add the following lines:

      </xsl:template> 
    </xsl:stylesheet> 
    
  5. Replace the <Worksheet ss:Name="Sheet1"> tag with the following tags:

    <xsl:for-each select="ProjectChangeRequests/Project"> 
    <Worksheet ss:Name="{ProjectName}"> 
    

    The previous XSLT instruction generates a new worksheet for each project contained on the database. Each worksheet is renamed with the value of the corresponding ProjectName element.

  6. Replace the sample data in the Table element with XSLT code that retrieves real data from the database. The required updates are highlighted in bold:

    <Table> 
              <Column ss:AutoFitWidth="0" ss:Width="85.5"/> 
              <Column ss:AutoFitWidth="0" ss:Width="94.5"/> 
              <Column ss:AutoFitWidth="0" ss:Width="82.5"/> 
              <Column ss:AutoFitWidth="0" ss:Width="60"/> 
              <Column ss:AutoFitWidth="0" ss:Width="65.25"/> 
              <Column ss:AutoFitWidth="0" ss:Width="150.75"/> 
              <Row> 
                <Cell ss:StyleID="s21"><Data ss:Type="String"><xsl:value-of select="ProjectName" /></Data></Cell> 
              </Row> 
              <Row> 
                <Cell ss:StyleID="s22"><Data ss:Type="String">Scope Change Log</Data></Cell> 
                <Cell ss:StyleID="s23"/> 
              </Row> 
              <Row ss:Index="4"> 
                <Cell ss:StyleID="s21"><Data ss:Type="String">Project Manager: </Data></Cell> 
                <Cell><Data ss:Type="String"><xsl:value-of select="ProjectManager" /></Data></Cell> 
              </Row> 
              <Row ss:Index="6" ss:Height="25.5"> 
                <Cell ss:StyleID="s27"><Data ss:Type="String">ID</Data></Cell> 
                <Cell ss:StyleID="s27"><Data ss:Type="String">Request Date</Data></Cell> 
                <Cell ss:StyleID="s27"><Data ss:Type="String">Requested by</Data></Cell> 
                <Cell ss:StyleID="s27"><Data ss:Type="String">Reviewed by</Data></Cell> 
                <Cell ss:StyleID="s27"><Data ss:Type="String">Change Status</Data></Cell> 
                <Cell ss:StyleID="s27"><Data ss:Type="String">Description</Data></Cell> 
                <Cell ss:StyleID="s26"/> 
                <Cell ss:StyleID="s26"/> 
                <Cell ss:StyleID="s26"/> 
              </Row> 
              <xsl:for-each select ="ChangeRequestLog"> 
              <Row> 
                <Cell><Data ss:Type="Number"><xsl:value-of select="ChangeID"/></Data></Cell> 
                <Cell><Data ss:Type="String"><xsl:value-of select="ChangeRequestDate"/></Data></Cell> 
                <Cell><Data ss:Type="String"><xsl:value-of select="RequestedBy"/></Data></Cell> 
                <Cell><Data ss:Type="String"><xsl:value-of select="ReviewedBy"/></Data></Cell> 
                <Cell><Data ss:Type="String"><xsl:value-of select="ChangeStatus"/></Data></Cell> 
                <Cell><Data ss:Type="String"><xsl:value-of select="Description"/></Data></Cell> 
              </Row> 
              </xsl:for-each> 
            </Table> 
    
  7. Save the changes and close the file.

Step 3: Build a Tool to Generate SpreadsheetML

The last step is to create a tool that:

  • Connects to the FabrikamProjects database.
  • Retrieves an XML result set that contains the list of change requests by project.
  • Processes the resulting XML string as an XML document and applies the XSLT that generates the SpreadsheetML file.

You can use your preferred language and application to retrieve data from a database and to process (with XSLT) an XML query result set. Just because I prefer Microsoft .NET, I chose to build a managed console application to generate an Excel workbook and I provide source code in both Microsoft Visual Basic and C#.

To build a console application using Visual Basic

  1. Start Microsoft Visual Studio .NET or Visual Studio 2005.

  2. On the File menu, point to New, and then click Project.

  3. In the New Project dialog box, under Project Types, click Visual Basic Projects. Under Templates, click Console Application, and then click OK.

    Note   In Visual Studio 2005, click Visual Basic under Project Types.

  4. Replace the code in Module1 with the following code:

    Imports System
    Imports System.Data
    Imports System.Data.SqlClient
    Imports System.Text
    Imports System.Xml
    Imports System.Xml.Xsl
    
    Module Module1
    
       Public Sub Main(ByVal args() As String)
          GetChangeRequestByProject()
       End Sub
    
       Private Sub GetChangeRequestByProject()
          CreateXMLFile(GetData)
       End Sub
    
       ' Use this method to connect to the FabrikamProjects database
       ' and retrieve change requests by project, formatted as XML.
       Private Function GetData() As String
          Dim connS As String = "data source=(local);database=FabrikamProjects;integrated security=SSPI;persist security info=false"
          Dim sqlConn As SqlConnection = New SqlConnection(connS)
          sqlConn.Open()
          Dim cmd As SqlCommand = New SqlCommand
          cmd.Connection = sqlConn
          cmd.CommandType = CommandType.StoredProcedure
          cmd.CommandText = "dsp_GetChangeRequestsByProject"
          Dim sqlxmlReader As XmlReader = cmd.ExecuteXmlReader
          sqlxmlReader.Read()
          Dim sb As StringBuilder = New StringBuilder
          sb.Append("<?xml version='1.0' encoding='utf-8'? >")
          sb.Append("<ProjectChangeRequests>")
    
          While (sqlxmlReader.ReadState <> ReadState.EndOfFile)
             sb.Append(sqlxmlReader.ReadOuterXml)
          End While
          sb.Append("</ProjectChangeRequests>")
          sqlxmlReader.Close()
          sqlConn.Close()
          Return sb.ToString
       End Function
    
       ' Use this method to save the string as an XMLDocument
       ' and to apply the XSLT transform that generates SpreadsheetML.
       Private Sub CreateXMLFile(ByVal document As String)
          Dim doc As XmlDocument = New XmlDocument
          doc.LoadXml(document)
          doc.Save("scopechangelogRawData.xml")
          Dim xslt As XslTransform = New XslTransform
          xslt.Load("ScopeChangeLog.xsl")
          xslt.Transform("scopechangelogRawData.xml", "scopechangelogResult.xml", Nothing)
       End Sub
    End Module
    
  5. Copy the ScopeChangeLog.xsl to the bin\Debug folder of your application.

  6. Save, build, and then run the project.

To build a console application using C#

  1. Start Visual Studio .NET or Visual Studio 2005.

  2. On the File menu, point to New, and then click Project.

  3. In the New Project dialog box, under Project Types, click Visual C# Projects. Under Templates, click Console Application, and then click OK.

    **Note   **In Visual Studio 2005, click Visual C# under Project Types.

  4. Replace the code in Class1 with the following code:

    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.Text;
    using System.Xml;
    using System.Xml.Xsl;
    
    namespace OfficeTalkCreateExcelWorkbooks {
        class Program {
            static void Main(string[] args) {
                GetChangeRequestByProject();
            }
    
            private static void GetChangeRequestByProject() {
                CreateXMLFile(GetData());
            }
    
            // Use this method to connect to the FabrikamProjects database
            // and retrieve change requests by project, formatted as XML.
            private static string GetData() {
                string connS = "data source=(local);database=MyCompanyProjects;integrated security=SSPI;persist security info=false";
                SqlConnection sqlConn = new SqlConnection(connS);
                sqlConn.Open();
    
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = sqlConn;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "dsp_GetChangeRequestsByProject";
    
                XmlReader sqlxmlReader = cmd.ExecuteXmlReader();
                sqlxmlReader.Read();
    
                StringBuilder sb = new StringBuilder();
    
                sb.Append("<?xml version='1.0' encoding='utf-8'?>");
                sb.Append("<ProjectChangeRequests>");
                while (sqlxmlReader.ReadState != ReadState.EndOfFile) {
                    sb.Append(sqlxmlReader.ReadOuterXml());
                }
                sb.Append("</ProjectChangeRequests>");
    
                sqlxmlReader.Close();
                sqlConn.Close();
    
                return sb.ToString();
            }
    
            // Use this method to save the string as an XMLDocument
            // and to apply the XSLT transform that generates SpreadsheetML.
            private static void CreateXMLFile(string document) {
    
                XmlDocument doc = new XmlDocument();
                doc.LoadXml(document);
                doc.Save(@"scopechangelogRawData.xml");
    
                XslCompiledTransform xslt = new XslCompiledTransform();
                xslt.Load(@"ScopeChangeLog.xsl");
                xslt.Transform(@"scopechangelogRawData.xml", @"scopechangelogResult.xml");
            }
        }
    }
    
  5. Copy the ScopeChangeLog.xsl to the bin\Debug folder of your application.

  6. Save, build, and then run the project.

After you run the application, the scopechangelogResult.xml file appears in the bin\Debug folder of your application. The magic has happened, and you are finished.

To open the XML Spreadsheet file

Using Windows Explorer, open scopechangelogResult.xml. Excel displays a workbook with a worksheet for each project, as shown in Figure 5.

Excel opens a SpreadsheetML file

Figure 5. Excel opens a SpreadsheetML file

Conclusion

Exploring the XML schemas can enable you to understand the potential offered by Microsoft Office applications to help solve your business needs, thanks to tight integration with XML. Ty Anderson recently wrote an article exploring different business scenarios where you can consider using SpreadsheetML:

The possibilities are limited only by your imagination. In the next release of Microsoft Office, XML integration expands the frontiers for solving business needs using Office.

Additional Resources

For more information about tools discussed in this article, see the following resources:

Acknowledgments

I would like to thank Frank Rice and Chad Rothschiller for their contributions to this article.