excel|visual|xml| data is generated from the dataset using XML in Excel 2002 or Excel 2003
This section describes how to create a DataSet object and how to use the WriteXml method to export the data contained in that object to an XML file. The resulting XML file can be opened directly in Excel. For illustration purposes, use the Jet OLE DB provider to create a DataSet object from the Microsoft Access Northwind sample database. However, similar code can be used with any DataSet object that you create with Visual C #. NET. 1. Start Microsoft Visual Studio. NET. On the File menu, click New, and then click Items. Select a Windows application from the Visual C # project type. The Form1 is created by default.
2. On the View menu, select the Toolbox to display the Toolbox, and then add a button to the Form1.
3. Double-click Button1. The code window for the form appears.
4. Add the following using directive to the top of the Form1.cs:
Using System.Data.OleDb;
Using System.Xml;
5. 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 of the Northwind.mdb in the connection string to match the location you are installing.
6. Add the following code in the button1_click handler:
Connect to the data source.
OleDbConnection objconn = new OleDbConnection (strconn);
Try
{
objConn.Open ();
The 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);
}
7. Press the F5 key to build and run the program.
8. Click Button1 to create the XML file, and then close Form1 to end the program.
9. Start Excel 2002 or Excel 2003 and open the C:\Customers.xml output file.
10. After you have seen the XML parsed into rows and columns in a new workbook, close the file and exit Excel.
Back to the top of the page
Format XML with style sheets
This procedure describes how to use style sheets (XSL) to transform the format and arrangement of XML data in an Excel workbook. 1. Use any HTML editor or text editor (for example, Notepad.exe) to 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= "align=" "CENTER" ></COLGROUP>
<colgroup width= "align=" "left" ></COLGROUP>
<colgroup width= "align=" "left" ></COLGROUP>
<colgroup width= "align=" "left" ></COLGROUP>
<colgroup width= "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>
2. 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 find the style sheet (customers.xsl).
3. Press the F5 key to build and run the program.
4. Click Button1 to create the XML file, and then close Form1 to end the program.
5. Start Excel 2002 or Excel 2003 and open the C:\Customers.xml output file.
6. Because you can see the processing instructions for the stylesheet in the XML from Excel, you receive a dialog box prompt when you open the file. In the Import XML dialog box, select Open the file and apply the following style sheet. In the list, select Customers.xsl and click OK. Note that the XML data is formatted and the columns are arranged according to the style sheet.
7. Close the file and exit Excel.
Back to the top of the page
To open a converted XML using code
At this point, you have opened the XML file using the user interface in Excel. This section describes how to programmatically enable Excel to open a workbook automatically. The following example shows how to open a transformed XML without user intervention by first converting the XML in the DataSet object to HTML.
