How to transfer XML data to Microsoft Excel 2002 by using Visual C# (307029)

SUMMARY

Excel 2002 introduces functionality for opening files in the Extensible Markup Language (XML) format. An XML file that is well-formed can be opened directly in Excel 2002 or Excel 2003 by using either the user interface or code.

With Microsoft Visual C# 2005 or Microsoft Visual C# .NET, you can take advantage of Excel's XML functionality 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.

Generate XML from a DataSet For Use In Excel 2002 or Excel 2003

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. The XML file that is generated can be opened directly in Excel. For illustration purposes, the DataSet object 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 C# 2005 or Visual C# .NET.

Start Microsoft Visual Studio 2005 or Microsoft Visual Studio .NET. On the File menu, click New and then click Project. Select Windows Application from the Visual C# 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 using directives to the top of Form1.cs:
```
using System.Data.OleDb;
using System.Xml;
					
```

Add the following private member variable to the Form1 class:

private string strConn ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
	+ " C:\\Program Files\\Microsoft Office\\Office10\\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 handler:

//Connect to the data source.
         OleDbConnection objConn = new OleDbConnection (strConn);
         try
         {
            objConn.Open();			

            //Fill a dataset with records from the Customers table.
            OleDbCommand objCmd = new OleDbCommand(
               "Select CustomerID, CompanyName, ContactName, " 
               + "Country, Phone from Customers", objConn);
            OleDbDataAdapter objAdapter = new OleDbDataAdapter();
            objAdapter.SelectCommand = objCmd;
            DataSet objDataset = new DataSet();
            objAdapter.Fill(objDataset);


            //Create the FileStream to write with.
            System.IO.FileStream fs = new System.IO.FileStream(
               "C:\\Customers.xml", System.IO.FileMode.Create);

            //Create an XmlTextWriter for the FileStream.
            System.Xml.XmlTextWriter xtw = 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();

            //Close the database connection.
            objConn.Close();
         }
         catch (System.Exception ex)
         {
            MessageBox.Show(ex.Message);
         }

Note You must change the code in Visual Studio 2005. By default, Visual C# adds one form to the project when you create a Windows Forms project. The form is named Form1. The two files that represent the form are named Form1.cs and Form1.designer.cs. You write the code in Form1.cs. The Form1.designer.cs file is where the Windows Forms Designer writes the code that implements all the actions that you performed by dragging and dropping controls from the Toolbox.

For more information about the Windows Forms Designer in Visual C# 2005, visit the following Microsoft Developer Network (MSDN) Web site:

http://msdn2.microsoft.com/en-us/library/ms173077.aspx

Press F5 to build and run the program.
Click Button1 to create the XML file, then close Form1 to end the program.
Start Excel 2002 or Excel 2003 and open the C:\Customers.xml output file.
After you have observed how the XML has been parsed 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 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.exe), 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>

Uncomment 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, then close Form1 to end the program.
Start Excel 2002 or Excel 2003 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 have been arranged according to the stylesheet.
Close the file and quit Excel.

Use Code to Open the Transformed XML

Up to this point, you have 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 HTML.

Add a reference to the Microsoft Excel 10.0 Object Library or Microsoft Excel 11.0 Object Library. To do this, follow these steps:
1. On the Project menu, click Add Reference.
2. On the COM tab, locate Microsoft Excel 10.0 Object Library or Microsoft Excel 11.0 Object Library and click Select.
3. Click OK in the Add References dialog box to accept your selection. If you receive a prompt to generate wrappers for the library that you selected, click Yes.

Add the following using directives to the top of Form1.cs:

using Excel = Microsoft.Office.Interop.Excel;

In the Visual C# 2005 or Visual C# .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.
OleDbConnection objConn = new OleDbConnection (strConn);
objConn.Open();			

//Fill a dataset with records from the Customers table.
OleDbCommand objCmd = new OleDbCommand(
	"Select CustomerID, CompanyName, ContactName, " 
	+ "Country, Phone from Customers", objConn);
OleDbDataAdapter objAdapter = new OleDbDataAdapter();
objAdapter.SelectCommand = objCmd;
DataSet objDataset = new DataSet();
objAdapter.Fill(objDataset);

//Create the FileStream to write with.
System.IO.FileStream fs = new System.IO.FileStream(
	"C:\\Customers.htm", System.IO.FileMode.Create);

//Create an XmlTextWriter for the FileStream.
System.Xml.XmlTextWriter xtw = new System.Xml.XmlTextWriter(
	fs, System.Text.Encoding.Unicode);

//Transform the XML using the stylesheet.
XmlDataDocument xmlDoc = new XmlDataDocument(objDataset);
System.Xml.Xsl.XslTransform xslTran = new System.Xml.Xsl.XslTransform();
xslTran.Load("C:\\Customers.xsl");
xslTran.Transform(xmlDoc, null, xtw);

//Open the HTML file in Excel.
Excel.Application oExcel = new Excel.Application();
oExcel.Visible=true;
oExcel.UserControl=true;
Excel.Workbooks oBooks = oExcel.Workbooks;
object oOpt = System.Reflection.Missing.Value; //for optional arguments
oBooks.Open("c:\\customers.htm", oOpt, oOpt, oOpt, 
	oOpt, oOpt, oOpt, oOpt, oOpt, oOpt, oOpt, oOpt, 
	oOpt, oOpt, oOpt);

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

Note While the Excel 2002 and Excel 2003 Object Model does expose an OpenXML method that enables you to programmatically open an XML file 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. For additional information, click the article number below to view the article in the Microsoft Knowledge Base:

307230 BUG: StyleSheets Parameter of the OpenXML Method Ignored When Automating Excel 2002