SUMMARY EXCEL 2002 introduces features that open files in the Extended Markup Language (XML) format. Using user interface or code can open the constructive XML file directly in Excel 2002 or Excel 2003.
In Visual C # .NET, you can use Excel's XML feature to seamlessly transmit data in your workbook, rendering data in your format and arrangement. This article demonstrates how to complete this task.
The XML used from the data set in Excel 2002 or Excel 2003 explains how to create
DataSet object, and how to use
The WriteXML method exports the data contained in the object to the XML file. The generated XML file can be opened directly in Excel. To facilitate explanation, use the Jet OLEDB provider from the Microsoft Access Northwind sample database.
DataSet object. However, similar code can be created with Visual C # .NET.
DataSet objects are used together.
Start Microsoft Visual Studio .NET. On the File menu, click New, and then click Project. Select a Windows application from the Visual C # project type. Create Form1 by default. On the View menu, select the Toolbox to display Toolbox and add a button to Form1. Double click on Button1. The code window of the form will appear. Add the following USING instruction to the top of Form1.cs: use system.data.oledb;
USING SYSTEM.XML;
Add the following private member variables 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 the Northwind.mdb in the connection string to match the location you installed. 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.writeProcessinginstructionStruction ("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);
}
Press F5 to build and run the program. Click Button1 to create an XML file, then turn it off to end the program. Start Excel 2002 or Excel 2003 and open the C: /Customers.xml output file. After you see the lines and columns that have been analyzed into the new workbook, turn off the file and exit Excel.
Using Style Table Formatting XML This step describes how to use Style Table (XSL) to convert XML data in the format and arrangement of the Excel workbook.
Use any HTML editor or text editor (for example, NOTEPAD.exe), save the following XSL as c: /customers.xsl:
