- Introduction
- Application Overview
- Building an XML Document
- Workbook Processing
- Building the Script
- Conclusion
- For More Information
Application Overview
This article illustrates the step-by-step logic of the conversion of an Excel document to an XML file, and then shows a weekly summary of the final XML repository using an XSL template.
The final solution is not exhaustive but hopefully illuminative. My aim is that you can abstract the logic and further generalize the solution to fit your own workbooks and worksheets.
The following example stems from a weekly worksheet I use to record my weekly points on my Weight Watchers–inspired eating plan. The worksheet contains point values for both estimated food portions and activities (see Figure 1).
NOTE
No claim is made that this represents the Weight Watchers program in any manner; the workbooks are just used as an example of data to be converted to XML.
)
Figure
1 Sample Excel Weight Watchers (WW) diary entry.
The worksheets are summarized on the Totals page with a chart and a table (see Figure 2).
)
Figure
2 Sample WW diary "totals" sheet.
In my quest to gain more facility with XML, I decided to convert the data of multiple workbooks to a single XML file/repository. Figure 3 shows the overall structure of the XML that my build script will be creating (thanks to Microsoft Development Environment 2002).
)
Figure
3 Hierarchy for the XML file.
Figure 4 shows a portion of the actual XML.
)
Figure
4 Partial listing of the final XML file.