XML Support in Excel

Vertex42 The Excel Nexus

Professional Excel Templates

Get Instant Access

Microsoft Excel 2002 offers extensive XML support. To begin with, instead of saving your workbook file as a standard *.xls file, you can just as easily save it as an XML spreadsheet. To do this, no programming skills are required. In the Microsoft Excel application window, simply choose File | Save As and select XML Spreadsheet in the Save as type drop-down box of the Save As dialog box (Figure 17-1). You can also open the XML spreadsheet back into Excel and use it as if it were a standard Excel file (Figure 17-2).

Figure 17-1:

Saving the Excel 2002 workbook in the XML format is easily accomplished by choosing XML Spreadsheet (*.xml) in the Save as type drop-down box.

0 Micioioft Excel - Equipment nidI

Figure 17-1:

Saving the Excel 2002 workbook in the XML format is easily accomplished by choosing XML Spreadsheet (*.xml) in the Save as type drop-down box.

0 Micioioft Excel - Equipment nidI

IS] Fils Edfi Waw [nsart Foimat Tods Data Window Halp -Sx

Jiiä! .10 - [ï]^ U = = = EÜ $ % , 1= _ - «i1 - i - "

IS] Fils Edfi Waw [nsart Foimat Tods Data Window Halp -Sx

Jiiä! .10 - [ï]^ U = = = EÜ $ % , 1= _ - «i1 - i - "

A

B

1 c

1 D

E I

1

Supported Equipment

3

C.KP r]oiy

Type

Mo ili!

Descrlptitm

Uli Iis

3

1

WS

GXt/L

Dell Pill 500 MHz

12

1

1

WS

GX110

Dell Pill 533 MHz

10

5

1

WS

¡3X110

Dell Pill B67 MHz

e

b'

1

WS

0X110

Dell Pill 866 MHz

4

7

2

Monitor

E74

IBM 17-inch Color Monitor

12

B

2

Monitor

M570

Dell 15-inch Color Monitor

Ë

t)

2

Monitor

E77Ü

Dell 17-inch Color Monitor

4

2

Monitor

E55

Viewsonrc 15-inch Color Monitor

10

11

3

Printer

DeskJet 97GciiL

HP Deskjet w/JetDiraci Color Prin

3

12

3

Pnnler

Odra S 1E20N

Lexmark B/W Laser Printer

2;

13

3

Printer

Optra TE14N

Lexmark B/W Laser Printer

1

M

3

Printer

550 DT MT#5221

Tektronix Coior Laser Printer

4

15

16

Total Units

811

M i

M NUIIIU/ 5feet2

jfslraö/

M

iir

Draw -

AutaS hapas-

1 v □ o g 4i Q s a - -a - a . = su e b

Figure 17-2:

An XML spreadsheet file opened in Microsoft Excel looks and behaves like a standard .xls file.

The following sections in this chapter demonstrate how XML files are created programmatically with VBA and how XML data can be formatted for display in a web page by using XSL (Extensible Stylesheet Language) and data binding. You also learn how to use ActiveX Data Objects (ADO) to save data retrieved from the Microsoft Access sample database as XML to disk or to XML in memory. The chapter ends with a more complex hands-on project demonstrating how to post timesheet data to a web server directly from a Microsoft Excel spreadsheet.

Creating XML Spreadsheet Files with VBA

Now that you know how to save and open the XML file using the manual method (see Figure 17-1), let's see how you can save and open XML files in Excel 2002 programmatically with VBA.

The following exercise demonstrates how to use VBA to save and open spreadsheet files in XML format.

1. Create the worksheet file shown in Figure 17-2 or load the Equip-ment.xls file from the book's companion CD-ROM. If you are creating the file from scratch, save it as Equipment.xls.

2. With the Equipment.xls workbook open, activate the Visual Basic Editor window (Alt+F11) and press Ctrl+G to open the Immediate window.

3. To save the Equipment.xls file in the XML spreadsheet format, enter the following instruction in the Immediate window:

ActiveWorkbook.Worksheets("Urnts").SaveAs "Umts.xml", xlXMLSpreadsheet

When you press Enter, Visual Basic displays the message shown in Figure 17-3 below. Click Yes to continue. The above instruction saves the workbook file in the current directory in the XML spreadsheet format by using the xlXMLSpreadsheet file format parameter.

Tile follovwig features from your worfcbook wfl not be saved n die Spreadsheet:

Do you wart to continue saving as an XML Spreadsheet?

Figure 17-3: When a workbook feature cannot be saved in the XML spreadsheet format, Excel displays a message.

Tip 17-1: Saving Excel Workbooks in XML Format

When you save an Excel workbook in XML format, all the information about the workbook and its data is saved, except for charts, images, OLE and drawing objects, groups/outlines, and VBA projects.

4. To find out where the file was saved, type the following in the Immediate window:

?ActiveWorkbook.Path

When you press Enter, the file path appears in the Immediate window. You can save the file directly to a specific directory on a web server by specifying the path as follows:

ActiveWorkbook.Worksheets("Units").SaveAs _

"http://localhost/myfolder/Units.xml", xlXMLSpreadsheet

The above statement must be entered on one line when used in the Immediate window. This statement saves the XML file to a virtual directory, myfolder, on the web server named localhost (see Chapter 16 for how to create a virtual directory).

5. Now go back to the Immediate window and close the Units.xml file by entering the following instruction:

ActiveWorkbook.Close

The Units.xml file is now closed. However, the Excel application is still running and you should be in the Visual Basic Editor window.

6. Reopen the Units.xml file by typing the following statement in the Immediate window:

Workbooks.Open "Units.xml"

Excel opens the Units.xml file. This file looks like the Equipment.xml file shown in Figure 17-2. You can use the XML file as a standard XLS file. You can also open an XML file in Microsoft Excel by using the OpenXML method, as shown below:

Workbooks.OpenXML "Units.xml"

The OpenXML method requires a string indicating the name of the file to open. It can also take a second, optional argument—a value or an array of values specifying which XSLT stylesheet processing instructions to apply. You will learn about stylesheets later in this chapter.

Tip 17-2: What's the Difference between XML and XLS Files?

XLS files in proprietary binary format are recognized by Excel. XML files created in Excel 2002 either manually or with VBA are text files. XML files can be opened or written from scratch in any ASCII text editor.

Viewing the XML Source File in Notepad

You have now created programmatically your first XML spreadsheet document. You've also opened it and saw no difference in the way the XML document appears in the Microsoft Excel user interface. This is because the XML document created from within Excel 2002 preserves the Microsoft Excel spreadsheet format, structure, and data. But let's open the Units.xlm file in Windows Notepad and view the underlying XML code. As mentioned earlier, XML is a markup language that uses custom tags. In order to understand XML files, you must become familiar with the way XML files are tagged. We will continue working in the Immediate window to get some programming practice while learning the new XML concepts.

The following exercise demonstrates how to use Notepad to view the content of XML files.

1. In the Immediate window type the following instruction to open the XML file in Notepad:

Shell "Notepad.exe Units.xml"

Visual Basic launches Notepad and loads the Units.xml file. Switch to Notepad by clicking the appropriate icon in the Windows taskbar. Notepad displays the XML file, as shown in Figure 17-4.

l-lblxll q Units.xml - Notepad

Fie Ed' Format Help

<Workbook smlns=^m:schema£-microsoff-com: office: spreadsheet"

* m I n s: " u m: s c h enrra s-mic ro soft-c o m: offl c e-offi c e" Km I n s: x=" u rn: s c h em a s-mic ro soft-c o m: offl c e: ex c el" xmlns:ss=''urri:schemas-rinicrosoft-com: office: spreadsheet"

* m I n s: ht m l="htt p: /M«w.w3 . o rg/TR/REC- ht mi40" >

<DocumentPrope rties xrrilns="urn: schemas-rriicrosoft-corn: office: office"> <Title>Chapler 17 sample file</Title> <Subject>XML Files «/Subjects <Author>Ju!illa Koto^/Author*

i La etPri nt ed>2002-C6-04T15:41:34£<<^La etPrint edfr <Creafed>2002-0S-03T1S:53:13Z</Created>

La stSave d >2002-06-05132:26:37Z«/La st Sate d > <Ve rsio n > 10.2625< A/e rsio n > </D o cum e ntPro p ert ie s >

sOfficeDocumentSettings Kmln£="urn:schemas-micrasofl-com:E)ffice:office'> <D ownEo a d Com p one nt s/>

< Lo c alio nOICompon ants HRe^Mfile:M5:V7> </Offi ceD o c um e nt Sett i n gs>

<ExcelWorkl>ook xmlna="urn: ache mas-microssSt-com: office: ascal"> <Wi ndowH eig ht >8580 </Wi n d owH e i ght >

< Wi ndowWitft h > 12120 < Wi n dowWi dl h >

Figure 17-4:

The XML file created with Excel VBA can be displayed in Notepad.

An XML document contains a tree of elements. Each element has an element type name (often called the tag name) and a set of attributes. Notice that the XML tags are the labels within < >. Each attribute consists of a name followed by an equals sign and an attribute value. For instance, ss:ID="Default", ss:Name="Sheet1", ss:StyleId="s22", ss:Type="

Number", ss:Size="12", and ss:Formula="=SUM(R[-13]C:R[-1]C)" are just a few examples of attributes that you will find in the Units.xml source code.

The topmost element of an XML spreadsheet document is <Workbook>. This is also the root tag of the spreadsheet document. Under the root you will find other tags that represent child elements. These elements precisely define the spreadsheet document. As you scroll down in the Notepad window, you will find the <Styles> tag that contains the style definitions for the workbook and the <Worksheet> tag that defines a worksheet within the current workbook. At least one instance of this element is required within a valid XML spreadsheet document. Below the Worksheet tag, you should find the <Table> tag that defines a table of cells in the current worksheet with some optional attributes, such as ss:ExpandedColumnCount or ss:ExpandedRowCount that specify the total number of columns and rows in the table. The child element of a table is <Row>. Each Row element may contain one or more <Cell> elements, which in turn contain <Data> elements specifying the value of a cell. If you apply more formatting to your orginal spreadsheet and resave the file in the XML spreadsheet format, you will find other tags describing your spreadsheet document. For example, named and/or autofilter ranges within the worksheet will be indicated with the <Names>, <NamedRange>, and <AutoFilter> tags.

Tip 17-3: The XML Spreadsheet Tag Hierarchy

The details on each XML spreadsheet element and the required and optional attributes can be found at http://msdn.micro-soft.com/library/default.asp?url=/library/en-us/dnexcl2k2/html/ odc_xlsmlinss.asp.

As you can see, the structure of the XML spreadsheet document is very logical and therefore easy to follow. Tags are nested inside other tags. Each element must have both a start tag and an end tag. Unlike HTML, XML is less forgiving; it does not allow you to omit the end tag. The name of the start tag must match the name in the corresponding end tag exactly. For example, a start tag of <Row> must have an end tag of </Row>. The first line in the XML file is a processing instruction:

This instruction identifies the file as an XML file. If you remove this processing instruction and attempt to open the XML file in Excel, the file won't be recognized as XML and Excel will open it as a text file. Note that the processing instruction begins and ends with a question mark (?) and contains the name of the application (in this example "xml") to which the instruction is directed, as well as additional information that needs to be passed to the XML processor, such as the version number, character set to be applied, name of a file to use, etc. The second instruction:

<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"

xmlns:o="urn:schemas-microsoft-com:office:office"

xmlns:x="urn:schemas-microsoft-com:office:excel"

xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"

xmlns:html="http://www.w3.org/TR/REC-html40">

lists namespaces referenced in the Excel XML spreadsheet document (see Tip 17-4).

Tip 17-4: What is a Namespace?

A namespace is a collection of names in which all names are unique. Because XML allows you to invent your own tag names, how can you ensure that your tags will not conflict with someone else's tags when two or more XML documents are combined? The <TABLE> tag will certainly have a different meaning and content in an Excel XML spreadsheet document than the <TABLE> element used to describe different types of tables listed in a catalog for a furniture store chain. There must be some way to differentiate elements and attributes that have the same name. The XML Namespaces specification ensures that element names do not conflict with one another and are unique within a particular set of names (a namespace). The attribute "xmlns" is an XML keyword for a namespace declaration. The namespace is identified by a Uniform Resource Identifier (URI) — either a Uniform Resource Locator (URL) or a Uniform Resource Name (URN).

Usually the namespace declaration is placed within the start tag of the element where you want to use the namespace, as in the following:

<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:o="urn:schemas-microsoft-com: office:office"

xmlns:html="http://www.w3.org/TR/ REC-html40">

In this example, the first declaration indicates that the urn:schemas-microsoft-com:office:spreadsheet namespace is the default XML namespace. A namespace without a prefix is referred to as a "default namespace." All elements and attributes within the Workbook element are by default from that namespace.

The second declaration associates the "o" prefix with the urn:schemas-microsoft-com:office:office namespace. The last declaration declares the html namespace. This is a special namespace that always points to the following URL: http://www.w3.org/TR/REC-html40.

There is no requirement that the specified URI is valid or that it conforms to any sort of specification. Most namespaces use URIs for the namespace names because URIs are guaranteed to be unique. What you should remember from this tip is that namespaces don't really exist. They are arbitrary names that allow you to distinguish between tags with the same names that need to be processed differently. Namespaces prevent naming conflicts that might arise in XML documents.

Was this article helpful?

0 0

Post a comment