How to transfer Xml data to microsoft excel 2002 by using visual c # .net
Article ID: 307029Last Review: July 14, 2004Revision: 6.1
This Article Was previously distribLished Under Q307029
For a Microsoft Visual Basic .NET VERSION OF This Article, See
307021.
In this task
Summary
• Generate XML from a dataset for use in Excel 2002 or Excel 2003 • Format the xml use a stylesheet • Use code to open the transformed XML • References
On this Page
Summaryreferences
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 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.
Back to the top
Generate XML from a dataset for use in Excel 2002 or Excel 2003this Section Illustrates How To Create A
DataSet Object And Export The Data That Contains To An Xml File By Using T
Writexml method. The Xml File That IS generated can be 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 # .NET.
1.Start 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.2.On the View menu, select Toolbox to display the Toolbox And Add a Button to Form1.3.double-Click Button1.double-Click Button1.4.add The Following Using Directives To The Top of Form1.cs: Using System.Data.OLDB; 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 to northwind.mdb in the connection .6.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 customer.
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 fileestream 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 indeicates a style sheet.xtw.writeprocessinginstruction ("XML", "Version = '1.0');
//xtw.writeprocessinginstructionstructionenstruction ("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 f5 to build and rain the program1 xml file, the version form1 to end . Female has observed How The Xml Has Been Parsed Into Rows and Column in The New Workbook, Close The File and Quit Excel.
Back to the top
Format the XML Using A Stylesheetthis Step Shows You How To Use A Stylesheet (XSL) To Transform How XML Data IS Formatted and Arranged In an Excel Workbook.
1.using any html editor or a text editor (Such as notepad.exe), save the folowing xsl as c: /customers.xsl:
