This article is also available equally a TechRepublic download, which includes all of the sample lawmaking a more manageable text file format.

George
Bernard Shaw one time described the British and the Americans as two people
separated by a common linguistic communication. Interestingly the same tin as well be said of application developers and the people that use applications.
Worse is that non simply does one group non sympathize the other, but that both
groups are normally blissfully unaware that the other grouping has no idea of what
the other is trying to say. If you lot don't believe me try explaining what you exercise
during an average day to some non-technical friend or relative and spotter their
eyes glaze-over as they effort to follow your description.

As
developers we have a trend to deal with information that is in no mode
user-friendly without really thinking well-nigh how our relatives would handle it. For
example, I'd rather not recollect of how my eldest half-brother would deal with the
XML
document shown in Listing A. If
I was given the consignment of giving him information that maybe he might take
to edit I'd make sure that information technology would be in a format that didn't require
ButterflyXML or XMLSpy. Face it, every bit developers nosotros have toys that not-developers
don't have, what's needed is some kind of common basis, say something that
we'd both take installed on our respective machines.

Listing A — An XML document for my one-half brother

<?xml version="1.0″ ?>
<root>
  <row>
    <column>Cavalcade 1 Row 1</cavalcade>
    <cavalcade>Column two Row one</column>
    <cavalcade>Column 3 Row 1</cavalcade>
  </row>
  <row>
    <column>Cavalcade 1 Row 2</column>
    <cavalcade>Column two Row 2</column>
    <column>Column 3 Row 2</cavalcade>
  </row>
  <row>
    <column>Column 1 Row 3</column>
    <cavalcade>Cavalcade 2 Row iii</column>
    <column>Column 3 Row three</column>
  </row>
  <row>
    <column>Column 1 Row 4</column>
    <cavalcade>Column 2 Row four</cavalcade>
    <column>Column 3 Row 4</cavalcade>
  </row>
</root>

Because
my eldest half-brother works for a brokerage I'd be willing to put money on the
fact that he has a relatively recent vintage Microsoft Office Suite. This means
that he'd have Excel and since I accept Excel we've plant a common ground between
a developer and a non-developer. Alas, it also ways that I'm the one that
needs to figure-out how to jam an XML certificate into Excel.
Possibly using a shoehorn might work?

There
is, however, a little known trick with Excel 2002 that isn't quite a shoe horn,
simply definitely fits the bill. Figure A
offers a little hint as to the pull a fast one on that I intend to utilize.

Figure A

Saving an Excel document

At present
that we know that Excel can deal with XML there is still the pocket-size problem of
how to go from point X to point E. There is, however, a road map that nosotros can
follow, namely the cosmos of a unproblematic spread canvas and saving it equally an XML
document. What we get is a document that looks like the one shown in Figure B and Listing B, one of those bizarre, not written past humans, document.

Figure B

An Excel spread sheet

List B — An XML spread canvas

<?xml version="one.0″?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:ten="urn:schemas-microsoft-com:office:excel"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:html="http://world wide web.w3.org/TR/REC-html40″>
<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
 <Author>ed woychowsky</Author>
 <LastAuthor>Edmond Woychowsky</LastAuthor>
 <Created>2007-01-26T16:54:15Z</Created>
 <LastSaved>2007-01-27T05:18:54Z</LastSaved>
 <Company>None</Visitor>
 <Version>10.3501</Version>
</DocumentProperties>
<OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:role:part">
 <DownloadComponents/>
 <LocationOfComponents HRef="file:///D:\"/>
</OfficeDocumentSettings>
<ExcelWorkbook xmlns="urn:schemas-microsoft-com:function:excel">
 <WindowHeight>8700</WindowHeight>
 <WindowWidth>11355</WindowWidth>
 <WindowTopX>480</WindowTopX>
 <WindowTopY>120</WindowTopY>
 <ProtectStructure>False</ProtectStructure>
 <ProtectWindows>False</ProtectWindows>
</ExcelWorkbook>
<Styles>
 <Fashion ss:ID="Default" ss:Name="Normal">
  <Alignment ss:Vertical="Bottom"/>
  <Borders/>
  <Font/>
  <Interior/>
  <NumberFormat/>
  <Protection/>
 </Style>
</Styles>
<Worksheet ss:Name="Sheet1″>
 <Table ss:ExpandedColumnCount="2″ ss:ExpandedRowCount="2″ x:FullColumns="ane″
  x:FullRows="one″>
  <Row>
   <Cell><Data ss:Blazon="String">cell a1</Data></Cell>
   <Prison cell><Data ss:Type="String">cell b2</Data></Cell>
  </Row>
  <Row>
   <Cell><Data ss:Blazon="String">cell a2</Information></Jail cell>
   <Jail cell><Data ss:Type="String">cell b3</Data></Cell>
  </Row>
 </Tabular array>
 <WorksheetOptions xmlns="urn:schemas-microsoft-com:part:excel">
  <Print>
   <ValidPrinterInfo/>
   <HorizontalResolution>600</HorizontalResolution>
   <VerticalResolution>0</VerticalResolution>
  </Impress>
  <Selected/>
  <Panes>
   <Pane>
    <Number>3</Number>
    <ActiveRow>2</ActiveRow>
   </Pane>
  </Panes>
  <ProtectObjects>False</ProtectObjects>
  <ProtectScenarios>Simulated</ProtectScenarios>
 </WorksheetOptions>
</Worksheet>
<Worksheet ss:Name="Sheet2″>
 <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
  <ProtectObjects>Simulated</ProtectObjects>
  <ProtectScenarios>False</ProtectScenarios>
 </WorksheetOptions>
</Worksheet>
<Worksheet ss:Name="Sheet3″>
 <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
  <ProtectObjects>False</ProtectObjects>
  <ProtectScenarios>False</ProtectScenarios>
 </WorksheetOptions>
</Worksheet>
</Workbook>

Dissecting the XML spreadsheet

As odd
as the document is, there is actually a weird kind of structure to it. For
example, it can exist broken down into the following XML element hierarchy:

Workbook

  DocumentProperties

  ExcelWorkbook

  Styles

    Style

  Worksheet

    Tables

      Row

        Cell

          Information

    WorksheetOptions

      Print

        ValidPrinterInfo

        HorizontalResolution

        VerticalResolution

    Selected

    Panes

      Pane

        Number

        ActiveRow

    ProtectObjects

    ProtectScenarios

Not
nearly as formidable broken down like this, is it? Actually looking at it from
this perspective it becomes rather like shooting fish in a barrel to create an XSL one.0 manner sheet to
transform the XML document from Listing
A into something that my half brother will experience comfortable with. In fact,
the annotated way canvas tin can exist constitute in Listing
C and the results shown in Effigy C
and List D.

Listing C — Annotated XSL mode sheet to create an XML spreadsheet

<?xml version="1.0″ ?>
<xsl:stylesheet version="1.0″ xmlns:xsl="http://world wide web.w3.org/1999/XSL/Transform" xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:10="urn:schemas-microsoft-com:role:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:html="http://www.w3.org/TR/REC-html40″>
<!– : /: Edmond Woychowsky: July 25, 2005: The purpose of this template is to create an Excel/XML spreadsheet from a
uncomplicated xml document.
–>
<xsl:template match="/">
<Workbook>
<xsl:call-template name="DocumentProperties"/>
<xsl:phone call-template name="OfficeDocumentSettings"/>
<xsl:call-template name="ExcelWorkbook"/>
<xsl:phone call-template name="Styles"/>
<xsl:apply-templates select="/*" mode="worksheet"/>
</Workbook>
</xsl:template>
<!– : * worksheet: This template builds the spreadsheets individual worksheets, normally know every bit
tabs.
–>
<xsl:template friction match="*" style="worksheet">
<xsl:variable name="position" select="position()"/>
<Worksheet ss:Name="{concat('Sail', $position)}">
<Tabular array ss:ExpandedColumnCount="{count(./*[ane]/*)}" ss:ExpandedRowCount="{count(./*) + ii}" x:FullColumns="1″ x:FullRows="1″>
<xsl:apply-templates select="*" way="row"/>
</Table>
<xsl:telephone call-template name="WorksheetOptions"/>
</Worksheet>
</xsl:template>
<!– : * row: This template builds the worksheet'southward rows.
–>
<xsl:template match="*" mode="row">
<Row>
<xsl:apply-templates select="*" mode="jail cell"/>
</Row>
</xsl:template>
<!– : * cells: This template builds the row's cells.
–>
<xsl:template lucifer="*" mode="prison cell">
<xsl:variable name="blazon">
<xsl:cull>
<xsl:when test="number(.) = .">Number</xsl:when>
<xsl:otherwise>String</xsl:otherwise>
</xsl:choose>
</xsl:variable>
<Jail cell>
<Data ss:Type="{$type}">
<xsl:value-of select="."/>
</Data>
</Jail cell>
</xsl:template>
<!– : * column: This template describes a worksheet's individual columns.
–>
<xsl:template lucifer="*" mode="column">
<xsl:variable name="name" select="proper noun(.)"/>
<xsl:variable name="length">
<xsl:call-template name="length">
<xsl:with-param proper noun="nodeset" select="//parent::*/parent::*/*/*[name(.) = $name]"/>
</xsl:call-template>
</xsl:variable>
<xsl:variable name="width">
<xsl:choose>
<xsl:when test="($length * 5.75) &lt; 56.25″>56.25</xsl:when>
<xsl:otherwise>
<xsl:value-of select="$length * 5.75″/>
</xsl:otherwise>
</xsl:cull>
</xsl:variable>
<xsl:variable name="style">
<xsl:choose>
<xsl:when examination="parent::*/parent::*/*/*[name(.) = $name] = number(parent::*/parent::*/*[1]/*[name(.) = $name])">
<xsl:choose>
<xsl:when test="string-length(parent::*/parent::*/*/*[proper name(.) = $name][contains(.,'.')]) = 0″>s23</xsl:when>
<xsl:otherwise>s24</xsl:otherwise>
</xsl:choose>
</xsl:when>
<xsl:otherwise>s22</xsl:otherwise>
</xsl:choose>
</xsl:variable>
<Column ss:StyleID="{$style}" ss:AutoFitWidth="0″ ss:Width="{$width}"/>
</xsl:template>
<!– : DocumentProperties: This template describes the document to Excel.
–>
<xsl:template proper noun="DocumentProperties">
<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
<Author>ewoychowsky</Writer>
<Visitor>EAW</Company>
<Version>10.4219</Version>
</DocumentProperties>
</xsl:template>
<!– : OfficeDocumentSettings: This template describes the Role document to Excel.
–>
<xsl:template name="OfficeDocumentSettings">
<OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
<DownloadComponents/>
<LocationOfComponents HRef="file:///\\phlfsnt01\DOWNLOAD\OfficeXPSrc\"/>
</OfficeDocumentSettings>
</xsl:template>
<!– : ExcelWorkbook: This template describes the characteristics of the wookbook to Excel.
–>
<xsl:template name="ExcelWorkbook">
<ExcelWorkbook xmlns="urn:schemas-microsoft-com:function:excel">
<WindowHeight>9210</WindowHeight>
<WindowWidth>15195</WindowWidth>
<WindowTopX>0</WindowTopX>
<WindowTopY>60</WindowTopY>
<ProtectStructure>Faux</ProtectStructure>
<ProtectWindows>Simulated</ProtectWindows>
</ExcelWorkbook>
</xsl:template>
<!– : Styles: This template describes the display styles to Excel.
–>
<xsl:template name="Styles">
<Styles>
<Style ss:ID="Default" ss:Name="Normal">
<Alignment ss:Vertical="Bottom"/>
<Borders/>
<Font/>
<Interior/>
<NumberFormat/>
<Protection/>
</Manner>
</Styles>
</xsl:template>
<!– : WorksheetOptions: This template describes the worksheet options to Excel.
–>
<xsl:template proper noun="WorksheetOptions">
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<Impress>
<ValidPrinterInfo/>
<HorizontalResolution>1200</HorizontalResolution>
<VerticalResolution>1200</VerticalResolution>
</Print>
<ProtectObjects>Simulated</ProtectObjects>
<ProtectScenarios>Faux</ProtectScenarios>
</WorksheetOptions>
</xsl:template>
<!– : length: Determine either the length of the node name or the longest node(southward), which ever is longer.
–>
<xsl:template proper name="length">
<xsl:param proper name="nodeset"/>
<xsl:variable proper noun="longest">
<xsl:telephone call-template name="longest">
<xsl:with-param name="nodeset" select="$nodeset"/>
</xsl:call-template>
</xsl:variable>
<xsl:choose>
<xsl:when exam="cord-length(proper name($nodeset[i])) &gt; string-length($longest)">
<xsl:value-of select="string-length(proper name($nodeset[one]))"/>
</xsl:when>
<xsl:otherwise>
<xsl:value-of select="string-length($longest)"/>
</xsl:otherwise>
</xsl:choose>
</xsl:template>
<!– : longest: This recursive template transverses a nodeset to find the nodes with the longest
cord-length. Please note that the event of this template may itself be a nodeset.
–>
<xsl:template name="longest">
<xsl:param proper noun="nodeset"/>
<xsl:param proper name="length" select="0″/>
<xsl:cull>
<xsl:when examination="count($nodeset[string-length(.) &gt; $length]) &gt; 0″>
<xsl:call-template proper name="longest">
<xsl:with-param proper noun="nodeset" select="$nodeset[string-length(.) &gt; $length]"/>
<xsl:with-param name="length" select="cord-length($nodeset[string-length(.) &gt; $length][1])"/>
</xsl:call-template>
</xsl:when>
<xsl:otherwise>
<xsl:value-of select="$nodeset"/>
</xsl:otherwise>
</xsl:choose>
</xsl:template>
</xsl:stylesheet>

Effigy C

The result in Excel

List D — The result every bit XML

<?xml version="1.0″ encoding="UTF-8″?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:html="http://www.w3.org/TR/REC-html40″ xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel">
<DocumentProperties xmlns="urn:schemas-microsoft-com:part:office">
<Writer xmlns="urn:schemas-microsoft-com:part:role">ewoychowsky</Author>
<Company xmlns="urn:schemas-microsoft-com:office:office">EAW</Visitor>
<Version xmlns="urn:schemas-microsoft-com:office:office">10.4219</Version>
</DocumentProperties>
<OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:function">
<DownloadComponents xmlns="urn:schemas-microsoft-com:office:role" />
<LocationOfComponents xmlns="urn:schemas-microsoft-com:office:function" HRef="file:///\\phlfsnt01\DOWNLOAD\OfficeXPSrc\" />
</OfficeDocumentSettings>
<ExcelWorkbook xmlns="urn:schemas-microsoft-com:role:excel">
<WindowHeight xmlns="urn:schemas-microsoft-com:role:excel">9210</WindowHeight>
<WindowWidth xmlns="urn:schemas-microsoft-com:office:excel">15195</WindowWidth>
<WindowTopX xmlns="urn:schemas-microsoft-com:office:excel">0</WindowTopX>
<WindowTopY xmlns="urn:schemas-microsoft-com:role:excel">threescore</WindowTopY>
<ProtectStructure xmlns="urn:schemas-microsoft-com:office:excel">False</ProtectStructure>
<ProtectWindows xmlns="urn:schemas-microsoft-com:office:excel">Fake</ProtectWindows>
</ExcelWorkbook>
<Styles>
<Way ss:ID="Default" ss:Proper name="Normal">
<Alignment ss:Vertical="Bottom" />
<Borders />
<Font />
<Interior />
<NumberFormat />
<Protection />
</Mode>
</Styles>
<Worksheet ss:Name="Sheet1″>
<Tabular array ss:ExpandedColumnCount="3″ ss:ExpandedRowCount="6″ x:FullColumns="1″ ten:FullRows="1″>
<Row>
<Cell>
<Information ss:Type="String">Cavalcade 1 Row one</Data>
</Cell>
<Cell>
<Data ss:Blazon="Cord">Column two Row 1</Data>
</Prison cell>
<Cell>
<Data ss:Type="Cord">Column iii Row 1</Data>
</Cell>
</Row>
<Row>
<Cell>
<Data ss:Type="String">Column 1 Row 2</Data>
</Cell>
<Cell>
<Data ss:Blazon="Cord">Column 2 Row 2</Data>
</Cell>
<Cell>
<Data ss:Type="Cord">Column 3 Row 2</Data>
</Cell>
</Row>
<Row>
<Jail cell>
<Information ss:Type="String">Cavalcade ane Row 3</Data>
</Cell>
<Cell>
<Information ss:Type="String">Cavalcade two Row 3</Information>
</Cell>
<Prison cell>
<Data ss:Type="Cord">Column 3 Row iii</Data>
</Cell>
</Row>
<Row>
<Cell>
<Data ss:Blazon="String">Column 1 Row 4</Data>
</Prison cell>
<Cell>
<Data ss:Type="String">Column 2 Row 4</Data>
</Prison cell>
<Prison cell>
<Data ss:Type="String">Column three Row 4</Data>
</Cell>
</Row>
</Table>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<Impress xmlns="urn:schemas-microsoft-com:office:excel">
<ValidPrinterInfo xmlns="urn:schemas-microsoft-com:office:excel" />
<HorizontalResolution xmlns="urn:schemas-microsoft-com:office:excel">1200</HorizontalResolution>
<VerticalResolution xmlns="urn:schemas-microsoft-com:office:excel">1200</VerticalResolution>
</Print>
<ProtectObjects xmlns="urn:schemas-microsoft-com:office:excel">Fake</ProtectObjects>
<ProtectScenarios xmlns="urn:schemas-microsoft-com:function:excel">Imitation</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
</Workbook>

Finding comfort

As odd
as these examples might be and equally strange equally the scenario of presenting my
eldest one-half brother with an XML certificate might seem, this attempt actually has
a real-globe application. Imagine getting an assignment where the user
customs in you visitor needs to exist able to review the contents of a database
table and they'd like to meet information technology in Excel. Afterwards all, they're comfy with
Excel and hopefully now you will exist too.