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.
Back to top
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; use system.xml; add the following private member variable to the Form1 class: private string strconn = "proviker = 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 the location you install.
Button1_Click handler add the following code: // 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.writeProcessinginstructionStruction ("XML", "Version = '1.0'); //xtw.writeprocessinginstructionstructionStruction ("XML-STYLESHEET", // "type = 'text / xsl' href = 'C Ustomers.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 generate 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. Back to top
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:
