7.5 Editing XML Maps with SpreadsheetML

SpreadsheetML is primarily useful for getting information into and out of Excel from other programs. In general, it's hard to imagine why you'd prefer to edit SpreadsheetML directly when Excel's graphical interface offers a much easier way to see and edit your information. There is, however, one case where Excel doesn't provide a graphical interface, and the SpreadsheetML provides a useful way to edit information that isn't otherwise accessible. Figure 7-9 shows a spreadsheet from Chapter 6 that uses an XML Map.

Figure 7-9. A spreadsheet using an XML Map, previously shown in Figure 6-25

Example 7-12 shows a portion of the SpreadsheetML that is produced when you save the spreadsheet itself as SpreadsheetML.

Example 7-12. Part of the SpreadsheetML for a spreadsheet containing an XML Map

 <Worksheet ss:Name="Sheet1">   <Names>    <NamedRange ss:Name="_FilterDatabase" ss:RefersTo="=Sheet1!R1C1:R2C8"     ss:Hidden="1"/>   </Names>   <Table ss:ExpandedColumnCount="8" ss:ExpandedRowCount="2" x:FullColumns="1"    x:FullRows="1">    <Column ss:AutoFitWidth="0" ss:Width="79.5"/>    <Column ss:AutoFitWidth="0" ss:Width="75"/>    <Column ss:AutoFitWidth="0" ss:Width="78"/>    <Column ss:AutoFitWidth="0" ss:Width="58.5"/>    <Column ss:AutoFitWidth="0" ss:Width="55.5"/>    <Column ss:AutoFitWidth="0" ss:Width="38.25"/>    <Column ss:AutoFitWidth="0" ss:Width="99.75"/>    <Column ss:AutoFitWidth="0" ss:Width="78.75"/>    <Row>     <Cell ss:Style><Data ss:Type="String">recipient</Data><NamedCell       ss:Name="_FilterDatabase"/></Cell>     <Cell ss:Style><Data ss:Type="String">signing_date</Data><NamedCell       ss:Name="_FilterDatabase"/></Cell>     <Cell ss:Style><Data ss:Type="String">signing_time</Data><NamedCell       ss:Name="_FilterDatabase"/></Cell>     <Cell ss:Style><Data ss:Type="String">birthyear</Data><NamedCell       ss:Name="_FilterDatabase"/></Cell>     <Cell ss:Style><Data ss:Type="String">birthday</Data><NamedCell       ss:Name="_FilterDatabase"/></Cell>     <Cell ss:Style><Data ss:Type="String">male</Data><NamedCell       ss:Name="_FilterDatabase"/></Cell>     <Cell ss:Style><Data ss:Type="String">payment_amount</Data><NamedCell       ss:Name="_FilterDatabase"/></Cell>     <Cell ss:Style><Data ss:Type="String">years_to_pay</Data><NamedCell       ss:Name="_FilterDatabase"/></Cell>    </Row>    <Row>     <Cell ss:Style><NamedCell ss:Name="_FilterDatabase"/></Cell>     <Cell ss:Style><NamedCell ss:Name="_FilterDatabase"/></Cell>     <Cell ss:Style><NamedCell ss:Name="_FilterDatabase"/></Cell>     <Cell ss:Style><NamedCell ss:Name="_FilterDatabase"/></Cell>     <Cell ss:Style><NamedCell ss:Name="_FilterDatabase"/></Cell>     <Cell ss:Style><NamedCell ss:Name="_FilterDatabase"/></Cell>     <Cell ss:Style><NamedCell ss:Name="_FilterDatabase"/></Cell>     <Cell ss:Style><NamedCell ss:Name="_FilterDatabase"/></Cell>    </Row>   </Table>   <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">    <Selected/>    <Panes>     <Pane>      <Number>3</Number>      <ActiveRow>1</ActiveRow>      <ActiveCol>1</ActiveCol>     </Pane>    </Panes>    <ProtectObjects>False</ProtectObjects>    <ProtectScenarios>False</ProtectScenarios>   </WorksheetOptions>  </Worksheet> ...  <x2:MapInfo x2:HideInactiveListBorder="false"   x2:SelectionNamespaces="xmlns:ns1='http://simonstl.com/ns/example/contract'">   <x2:Schema x2: x2:Namespace="http://simonstl.com/ns/example/contract"><xs: schema xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified"  targetNamespace="http://simonstl.com/ns/example/contract" xmlns:contract="http://simonstl. com/ns/example/contract">     <xs:element name="contracts">         <xs:complexType>             <xs:sequence>                 <xs:element maxOccurs="unbounded" ref="contract:contract"/>             </xs:sequence>         </xs:complexType>     </xs:element>     <xs:element name="contract">         <xs:complexType>             <xs:sequence>                 <xs:element ref="contract:recipient"/>                 <xs:element ref="contract:signing_date"/>                 <xs:element ref="contract:signing_time"/>                 <xs:element ref="contract:birthyear"/>                 <xs:element ref="contract:birthday"/>                 <xs:element ref="contract:male"/>                 <xs:element ref="contract:payment_amount"/>                 <xs:element ref="contract:years_to_pay"/>             </xs:sequence>         </xs:complexType>     </xs:element>     <xs:element name="recipient" type="xs:string"/>     <xs:element name="signing_date" type="xs:date"/>     <xs:element name="signing_time" type="xs:time"/>     <xs:element name="birthyear" type="xs:gYear"/>     <xs:element name="birthday" type="xs:gMonthDay"/>     <xs:element name="male" type="xs:boolean"/>     <xs:element name="payment_amount" type="xs:decimal"/>     <xs:element name="years_to_pay" type="xs:integer"/> </xs:schema>   </x2:Schema>   <x2:Map x2: x2:Schema x2:RootElement="contracts">    <x2:Entry x2:Type="table" x2: x2:ShowTotals="false">     <x2:Range>Sheet1!R2C1</x2:Range>     <x2:HeaderRange>R1C1</x2:HeaderRange>     <x:FilterOn>True</x:FilterOn>     <x2:XPath>/ns1:contracts/ns1:contract</x2:XPath>     <x2:Field x2:>      <x2:Range>RC</x2:Range>      <x2:XPath>ns1:recipient</x2:XPath>      <x2:XSDType>string</x2:XSDType>      <ss:Cell>      </ss:Cell>      <x2:Aggregate>None</x2:Aggregate>     </x2:Field>     <x2:Field x2:>      <x2:Range>RC[1]</x2:Range>      <x2:XPath>ns1:signing_date</x2:XPath>      <x2:XSDType>date</x2:XSDType>      <ss:Cell>      </ss:Cell>      <x2:Aggregate>None</x2:Aggregate>     </x2:Field>     <x2:Field x2:>      <x2:Range>RC[2]</x2:Range>      <x2:XPath>ns1:signing_time</x2:XPath>      <x2:XSDType>time</x2:XSDType>      <ss:Cell>      </ss:Cell>      <x2:Aggregate>None</x2:Aggregate>     </x2:Field>     <x2:Field x2:>      <x2:Range>RC[3]</x2:Range>      <x2:XPath>ns1:birthyear</x2:XPath>      <x2:XSDType>gYear</x2:XSDType>      <ss:Cell>      </ss:Cell>      <x2:Aggregate>None</x2:Aggregate>     </x2:Field>     <x2:Field x2:>      <x2:Range>RC[4]</x2:Range>      <x2:XPath>ns1:birthday</x2:XPath>      <x2:XSDType>gMonthDay</x2:XSDType>      <ss:Cell>      </ss:Cell>      <x2:Aggregate>None</x2:Aggregate>     </x2:Field>     <x2:Field x2:>      <x2:Range>RC[5]</x2:Range>      <x2:XPath>ns1:male</x2:XPath>      <x2:XSDType>boolean</x2:XSDType>      <ss:Cell>      </ss:Cell>      <x2:Aggregate>None</x2:Aggregate>     </x2:Field>     <x2:Field x2:>      <x2:Range>RC[6]</x2:Range>      <x2:XPath>ns1:payment_amount</x2:XPath>      <x2:XSDType>decimal</x2:XSDType>      <ss:Cell>      </ss:Cell>      <x2:Aggregate>None</x2:Aggregate>     </x2:Field>     <x2:Field x2:>      <x2:Range>RC[7]</x2:Range>      <x2:XPath>ns1:years_to_pay</x2:XPath>      <x2:XSDType>integer</x2:XSDType>      <ss:Cell>      </ss:Cell>      <x2:Aggregate>None</x2:Aggregate>     </x2:Field>    </x2:Entry>   </x2:Map>  </x2:MapInfo> </Workbook>

There are several types of information relating to the XML map here. The Worksheet element's Table contains the rows and cells that hold the actual data, with headers and style information, as well as a range named _FilterData. That really just reflects choices that can be made (and unmade) through the GUI.

The information in the x2:MapInfo element, which comes after all the Worksheet elements, however, is information that is created when you import an XML document or XSD schema. The only way to modify this information through Excel is to delete it. If, however, you just want to tweak something in the schema perhaps Excel guessed that a given field in an XML document was a number rather than text or vice-versa you can save the spreadsheet as SpreadsheetML, make the changes to the x2:MapInfo element's contents, and re-open it in Excel.

Remember that SpreadsheetML doesn't represent everything in an Excel document. If the spreadsheet whose map you want to alter already contains VBA, Charts, or other features that SpreadsheetML doesn't capture, be certain to have them backed up and be prepared for some cutting from the original spreadsheet and pasting into the new.

Editing the schema in the x2:Schema element works fine, so long as you produce a valid schema that conforms to Excel's limited understanding of XSD. You'll need to manually ensure that the x2:Field elements still correspond to the contents of that schema; if you change a type in the schema, be sure to change it in the x2:XSDType element of the corresponding x2:Field element. You can also make changes to the x2:XPath element, if you need to change the location in the document from which Excel retrieves the field's contents, typically if you add or remove a container element from the XML document structure.

This kind of editing is definitely at your own risk, and likely best restricted to relatively small changes, but it does provide a useful set of tools that aren't (yet) in Excel itself.