Programming Excel with VBA and .NET

Transform XML Spreadsheets

XML spreadsheets provide Excel data in a format that can be easily used by other applications or transformed into presentation documents, such as HTML web pages. For either task you often need to modify the content of the XML spreadsheet and the best way to do that is with XSLT.

You can use XSLT to perform a wide variety of transformations, such as:

• Extract specific items from a spreadsheet—such as retrieving only worksheets containing data

• Transform the spreadsheet into HTML

• Make global changes to the spreadsheet

• Highlight significant items, such as high or low outlier numbers

To transform an XML spreadsheet, follow these general steps:

1. Create an XSLT file to perform the transformation using Notepad or some other editor.

2. Perform the transformation in code, from the command line, or by including a processing instruction.

3. Save the result.

Table 15-3 compares the three different ways to perform a transformation. The sections that follow describe each of the techniques in more detail.

Table 15-3. Methods to transform XML spreadsheet

Transformation	Use to	Advantages	Disadvantages
Code	Automatically generate the result from within Visual Basic	Can be performed with a single click by the user or in response to an event.	Requires Excel to be running.
Command line	Perform batch transformations	Transformed file is generally smaller than source file.	Uses command-line interface; utility must be downloaded.
Processing instruction	Dynamically transform the file ...