Add XML Functionality in Microsoft Office Excel 2003 from Microsoft Visual Basic .NET

Article
07/11/2006

Frank Rice
Microsoft Corporation

February 2004

Applies to:
Microsoft® Office Excel 2003
Microsoft Visual Basic® .NET

Summary: Programmatically adding XML functionality can increase the utility of your applications. Examine three examples illustrating ways that you can work with XML from Microsoft Visual Basic .NET so that users can open and use files in Microsoft Office Excel 2003. (8 printed pages)

Introduction
Generate XML from a DataSet for Use in Excel
Format the XML Using a Stylesheet
Use Code to Open the Transformed XML
Conclusion

Introduction

Microsoft® Excel 2002 introduced functionality for opening files in the Extensible Markup Language (XML) format. Microsoft Office Excel 2003 significantly enhances this functionality. For example, you can create or open a workbook, attach a custom XML schema to the workbook, and then use the XML Source task pane to map cells to elements of the schema.

XML is a markup language that has evolved to convey information between systems in a human- and application-readable format. It was originally designed to deliver information over the World Wide Web.

An XML file that is well-formed can be opened directly in Excel 2003 by using either the user interface or with code. Well-formed XML has the following characteristics:

It contains exactly one root element with a unique name that does not appear in any other element in the document.
Elements are properly nested so that no tags overlap between elements.
All element tags are closed.
Element start and end tags use a consistent case (XML is case-sensitive).
All element attributes are enclosed in quotation marks, either double or single.
Special characters (such as &, <, >) are defined as built-in entities (&, <, >).

With Microsoft Visual Basic® .NET, you can take advantage of the XML functionality of Excel to transfer data seamlessly to a workbook to present the data with formatting and an arrangement of elements of your choice. This article demonstrates how to accomplish this task.

Generate XML from a DataSet for Use in Excel

This section illustrates how to create a DataSet object and export the data that it contains to an XML file by using the WriteXML method. DataSet objects, a key part of data access in the Microsoft .NET Framework, are in-memory objects that can hold tables, views, and relationships.

You can open the XML file that is generated directly in Excel. To demonstrate, the DataSet object is created from the Access Northwind sample database by using the Jet OLEDB Provider. However, similar code works with any DataSet object that you create with Visual Basic .NET.

Start Microsoft Visual Studio® .NET. On the File menu, click New and then click Project. Select Windows Application from the Visual Basic Projects types. Form1 is created by default.
On the View menu, select Toolbox to display the Toolbox and add a button to Form1.
Double-click Button1. The code window for the Form appears.
Add the following private member variable to the Form1 class:
```
Private strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
"C:\Program Files\Microsoft Office\Office11\Samples\Northwind.mdb;"
```
Note You may need to modify the path to Northwind.mdb in the connection string to match your installation. You can download the Northwind Traders sample database from the Microsoft Download Center. In addition, notice that there is a space between Data and Source in the path String.

Add the following code to the Button1_Click handler:

'Connect to the data source.
Dim objConn As New System.Data.OleDb.OleDbConnection(strConn)
Try
   objConn.Open()
   'Fill the dataset with records from the Customers table.
   Dim strSQL As String
   Dim objDataset As New DataSet
   Dim objAdapter As New System.Data.OleDb.OleDbDataAdapter
   'The OLEDbDataAdapter acts as a bridge between the data source,
   'in this case the Customers table, and the dataset.
   strSQL = "Select CustomerID, CompanyName, ContactName, " & _
      "Country, Phone from Customers"
   objAdapter.SelectCommand = New System.Data.OleDb.OleDbCommand( _
      strSQL, objConn)
   objAdapter.Fill(objDataset)

   'Create the FileStream to write with.
   Dim strFilename As String
   strFilename = "C:\Customers.xml"
   Dim fs As New System.IO.FileStream(strFilename, _
      System.IO.FileMode.Create)

   'Create an XmlTextWriter for the FileStream.
   Dim xtw As New System.Xml.XmlTextWriter(fs, _
      System.Text.Encoding.Unicode)

   'Add the processing instruction to the beginning of the XML
   ' file, leaving the one which indicates a style sheet commented.
   xtw.WriteProcessingInstruction("xml", "version='1.0'")
   'xtw.WriteProcessingInstruction( _
      '"xml-stylesheet", "type='text/xsl' href='customers.xsl'")

   'Write the XML from the dataset to the file.
   objDataset.WriteXml(xtw)
   xtw.Close()

   MsgBox("Customer data has been exported to C:\Customers.xml.")
Catch ex As Exception
   MsgBox(ex.Message)
End Try

Press F5 to build and run the program.
Click Button1 to create the XML file, and then close Form1 to end the program.
Start Excel and open the C:\Customers.xml output file.
After you observe how Excel parses the XML into rows and columns in the new workbook, close the file and quit Excel.

Format the XML Using a Stylesheet

This step shows you how to use Extensible Style Language (XSL) stylesheets to transform how Excel formats and arranges XML data in an Excel workbook.

Note In an XML stylesheet file, XML tags describe the data in a text file, but XML alone does not specify how to present the data to the user. Formatting rules for XML data are often contained in XSL stylesheets. XSL stylesheets are well-formed XML documents that can use XSL to transform XML data for presentation. You can have many stylesheets for the same XML data. While your actual XML data structure remains the same, XSL stylesheets give you flexibility to present the data in many different ways.

Using any HTML editor or a text editor (such as Microsoft Notepad), save the following XSL as C:\Customers.xsl:

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
  <xsl:template match="/">
    <HTML>
      <HEAD>
        <STYLE>   
          .HDR { background-color:bisque;font-weight:bold }
        </STYLE>
      </HEAD>
      <BODY>
        <TABLE>
          <COLGROUP WIDTH="100" ALIGN="CENTER"></COLGROUP>
          <COLGROUP WIDTH="200" ALIGN="LEFT"></COLGROUP>
          <COLGROUP WIDTH="200" ALIGN="LEFT"></COLGROUP>
          <COLGROUP WIDTH="100" ALIGN="LEFT"></COLGROUP>
          <COLGROUP WIDTH="100" ALIGN="LEFT"></COLGROUP>
          <TD CLASS="HDR">Customer ID</TD>
          <TD CLASS="HDR">Company</TD>
          <TD CLASS="HDR">Contact</TD>
          <TD CLASS="HDR">Country</TD>
          <TD CLASS="HDR">Phone</TD>
          <xsl:for-each select="NewDataSet/Table">
            <TR>
              <TD><xsl:value-of select="CustomerID"/></TD>
              <TD><xsl:value-of select="CompanyName"/></TD>
              <TD><xsl:value-of select="ContactName"/></TD>
              <TD><xsl:value-of select="Country"/></TD>
              <TD><xsl:value-of select="Phone"/></TD>
            </TR>
          </xsl:for-each>
        </TABLE>
      </BODY>
    </HTML>
  </xsl:template>
</xsl:stylesheet>

Remove the comment tag on the following line of code in the Button1_Click handler:
```
xtw.WriteProcessingInstruction( _
   "xml-stylesheet", "type='text/xsl' href='customers.xsl'")
```
This line of code writes a processing instruction to the XML file that Excel uses to locate the XSL stylesheet (Customers.xsl).
Press F5 to build and run the program.
Click Button1 to create the XML file, and then close Form1 to end the program.
Start Excel and open the C:\Customers.xml output file.
Because Excel sees the processing instruction for the XSL stylesheet in the XML, you receive a dialog box prompt when you open the file. In the Import XML dialog box, select Open the file with the following stylesheet applied. In the list, select Customers.xsl and click OK. Note that Excel formats the XML data and arranges the columns according to the XSL stylesheet.
Close the file and quit Excel.

Use Code to Open the Transformed XML

Up to this point, you opened the XML file by using the user interface in Excel. This section demonstrates how to automate Excel to open the workbook programmatically. The following sample illustrates how to open the transformed XML without user intervention by first transforming the XML in the DataSet object to Hypertext Markup Language (HTML).

In the Visual Basic .NET project, add another button to Form1.

Double-click Button2. When the code window for the form appears, add the following code to the Button2_Click handler:

    'Connect to the data source.
    Dim objConn As New System.Data.OleDb.OleDbConnection(strConn)
Try
    objConn.Open()
    'Fill a dataset with records from the Customers table.
    Dim strSQL As String
    Dim objDataset As New DataSet
    Dim objAdapter As New System.Data.OleDb.OleDbDataAdapter
    strSQL = "Select CustomerID, CompanyName, ContactName, " & _
        "Country, Phone from Customers"
    objAdapter.SelectCommand = New System.Data.OleDb.OleDbCommand( _
        strSQL, objConn)
    objAdapter.Fill(objDataset)

    ' Create the FileStream to write with.
    Dim strFilename As String
    strFilename = "C:\Customers.htm"
    Dim fs As New System.IO.FileStream(strFilename, _
        System.IO.FileMode.Create)

    'Create an XmlTextWriter for the FileStream.
    Dim xtw As New System.Xml.XmlTextWriter(fs, _
        System.Text.Encoding.Unicode)
    'Transform the XML using the stylesheet.
    Dim xmlDoc As System.Xml.XmlDataDocument = _
        New System.Xml.XmlDataDocument(objDataset)
    Dim xslTran As System.Xml.xsl.XslTransform = _
        New System.Xml.Xsl.XslTransform
    xslTran.Load("c:\customers.xsl")
    xslTran.Transform(xmlDoc, Nothing, xtw, Nothing)

    'Write the transformed XML to the file.
    objDataset.WriteXml(xtw)
    xtw.Close()

    'Start Excel and open the resulting file.
    Dim oExcel As Object
    oExcel = CreateObject("Excel.Application")
    oExcel.Workbooks.Open("C:\Customers.htm")

    'Make Excel visible and give the user 
    'control of the application.
    oExcel.Visible = True
    oExcel.UserControl = True
Catch ex As Exception
    MsgBox(ex.Message)
End Try

Press F5 to build and run the program.
Click Button2 to open the transformed XML in Microsoft Excel.

Note While the object model for Excel exposes an OpenXML method that enables you to open an XML file programmatically with stylesheets applied, the previous sample does not call this method due to a known problem with using this method from an automation client. The OpenXML method works as expected when it is called from an Excel macro; however, when this method is called from an automation client, the <StyleSheet> parameter is ignored. Automation is the process of controlling one product from another product with the end result that the client product can take advantage of the objects, methods, and properties of the server product.

Conclusion

This article illustrated generating a dataset from XML, formatting an XML data by using a schema, and automating Excel and then displaying XML. You can use each of these operations to add important functionality to your applications. By experimenting with the sample code in this article, you can combine the operations and increase the effectiveness of your applications.