Transfer XML Data to Microsoft Office Excel 2003 with Microsoft Visual Basic .NET

Article
07/11/2006

Frank Rice
Microsoft Corporation

November 2003

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

Summary: Learn different ways to transfer data from an Access database to Microsoft Office Excel 2003. These include using the user-interface, programmatically creating an XML file, transforming the data and reading it into Excel, and automating Excel and programmatically reading the data file. (9 printed pages)

Introduction
Using the User-Interface to Open an XML File in Excel 2003
Generate XML from a DataSet for Use in Excel 2003
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. You can open a well-formed XML file directly in Excel 2002 or Microsoft Office Excel 2003 by using either the user interface or code.

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

Opening an XML file by using the user-interface in Excel
Generating XML data and using it to create an XML file
Using an XSL file to format the data in an XML file
Automating Excel and opening an XML file in Excel

Using the User-Interface to Open an XML File in Excel

In addition to using Visual Basic .NET to open an XML file in Excel, which we discuss shortly, there are three ways that users can also use the user-interface as illustrated in the following procedure:

Click File, and then click Open.
Go to the file to open, and then click Open.

The Open XML dialog box appears as shown in Figure 1.

Figure 1. Opening an XML file in Excel
Select one of the following options and then click OK.
- As an XML list
  
  An XML list is created in a new workbook.
  
  The contents of the file are imported into the XML list. If the XML data file does not refer to a schema, then Excel infers the schema of the XML data file:
  
  Figure 2. Excel infers a schema where one does not exist
- As a read-only workbook
  
  The XML data file is opened as a read-only workbook. The structure of the file flattens.
- Use the XML Source task pane
  
  The schema of the XML data file is displayed in the XML Source task pane. You can then drag elements of the schema to the worksheet to map those elements to the worksheet.
  
  If you open an XML data file that does not refer to a schema, then Excel infers the schema of the XML data file.

Generate XML from a DataSet for Use in Excel

Next we look at how to create a DataSet object and export the data that it contains to an XML file by using the WriteXML method.

Note You can think of the DataSet object as databases that reside in-memory which contain one or more tables. The most important aspect of a dataset is that it is disconnected from its source data. This makes it ideal for serving many users since it doesn't persist connections to the database.

You can open the XML file that is generated from the dataset directly in Excel. For illustration purposes, the DataSet object used in the example discussed in this article is created from the Microsoft 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.

In Microsoft Visual Studio .NET 2003, on the File menu, click New, and then click Project.
In the Project Types window, click Visual Basic Projects.
In the Templates window, click Windows Application from the Visual Basic Projects types.
Type a name and location for the project and then click OK.

Form1 is created by default.
On the View menu, click 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.

Add the following code to the Button1_Click event procedure:

        '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.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 processing instructions to the beginning of the XML file, one 
        'of which indicates a style sheet.
        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. Click File, and then click Open. Go to the C:\Customers.xml output file created in the previous procedure, and then click Open.
The Open XML dialog box appears. At this point you have all of the user-interface options available to you for working with the XML data, as discussed in the previous user-interface section. Click As an XML list.
After you observe how the XML parses into rows and columns in the new workbook, close the file and quit Excel.

In looking at the code, the first section in the procedure establishes a connection to the data source for the .NET data provider. Note that there are four classes that a .NET data provider implements: Connection, Command, DataReader, and DataAdapter classes. The name of the Connection class implemented by the OLE DB .NET Data Provider is OleDbConnection. This class resides in the System.Data.OleDb namespace. The code opens a connection to an Access database by using a connection string designating the OLE DB provider and pointing to the Northwind sample database. Notice that the connection is declared and instantiated with a single statement using the New keyword.

Next, the OleDbCommand class of the Command class is used for the OLE DB .NET Data Provider. It resides in the same namespace as the OleDbConnection class. This class allows you to perform data access, data manipulation, and data definition against the database. Here, we are using it for the SQL command to retrieve data from the database. Then, the Fill method fills a table within the dataset with the contents of a database object, in this case, the Customers table.

Then, we set up the FileStream object that creates the XML file containing the data from the dataset. It does this with the help of the XmlTextReader and the WriteXml methods. Next, the WriteProcessingInstruction method adds processing instructions to the beginning of the XML file.

And finally, once the file is created, a message box is displayed notifying the user that the task is completed.

Format the XML Using a Stylesheet

This section shows you how to use a stylesheet (XSL) to transform how XML data is formatted and arranged in an Excel workbook.

Using any HTML editor or a text editor (such as 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 from 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 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 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 the XML data is formatted and that the columns are arranged according to the stylesheet.

Figure 3. Importing an XML file with a 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 use automation 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 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)
        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)

        '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

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

Much of the first part of this procedure is similar to that in the Button1 procedure. A connection is opened to the Customers database. The DataSet object is then populated with data from the Customers table by using a SQL statement in an OleDbCommand class. A FileStream object is created that copies data to the HTML file.

Next, an in-memory XML document is created from the dataset. Then the XSL transform file is loaded in memory. The transformation takes and the result is saved to the HTML file. Next, Excel is instantiated using automation and the HTML file opens.

Conclusion

In this article, you learned different ways to transfer data from an Access database to Excel. These included using the user-interface, programmatically creating an XML file, transforming the data and reading it into Excel, and automating Excel and programmatically reading the data file. Adding these methods to your own applications can help make them more versatile and useful.