Saving Worksheets to XML Files

Vertex42 The Excel Nexus

Professional Excel Templates

Get Instant Access

Saving existing data from a worksheet to an XML file is easy. Select File, Save As from the application window and choose one of two possibilities for XML file types from the Save As dialog box as shown in Figure 8.8.

Save as an XML> spreadsheet

Excel's Save As dialog.

Save as an XML> spreadsheet

Excel's Save As dialog.

-Save as XML data

Saving Data as an XML Spreadsheet

If you choose to save the data as an XML spreadsheet, Excel will use its own schema to define the document. As you might expect, the XML required to define a spreadsheet is quite long, but you don't have to worry about that because Excel creates it for you. The root tag is <Workbook> and it will contain nested <Worksheet> tags for every worksheet in a workbook. In addition to the <Worksheet> tags, there are several other tags that describe the formatting and objects in the workbook. The following code shows the basic format of a document saved using the Excel-XML structure (data and attributes have been deleted for brevity and clarity).


<DocumentProperties/> <OfficeDocumentSettings/> <ExcelWorkbook/> <Styles>

<NamedRange"/> </Names> <Worksheet> <Table> <Column> <Row> <Cell> <Data/>

<WorksheetOptions/> </Worksheet> </Workbook>

The document resulting from saving a workbook with the Excel-XML structure is just a text file; however, it is also an XML file defined using the Excel-XML schema. As a well-formed and schema-defined XML document, it can be opened by other applications that support XML such that the formatting and other elements of the spreadsheet (for example, formulas) are preserved. Unfortunately, Excel cannot save objects such as autoshapes and charts in an Excel workbook to an XML document.

Saving a Worksheet as XML Data

Saving data in a worksheet to an XML document without following the Excel-XML schema is a bit more complicated. In fact, you can't save worksheet data to a new XML file using the file type XML Data (see Figure 8.8) unless it has first been mapped to an existing schema. The easiest way to save data to a new XML file without using the Excel-XML format is to first open or import an existing XML file with the desired structure as a list into a worksheet. The XML file doesn't even need data, just the required tags. After opening the XML file and editing the data in Excel, you can simply save it as a new XML file using the map created by Excel when you first opened or imported the file.

Was this article helpful?

0 0
Biorhythm Awareness

Biorhythm Awareness

Who else wants to take advantage of biorhythm awareness to avoid premature death, escape life threatening diseases, eliminate most of your life altering mistakes and banish catastrophic events from your life.

Get My Free Ebook

Post a comment