Excel 2003 XML Spreadsheet example

excel_xml_spreadsheet_example.md

Excel 2003 XML Spreadsheet example

Example

spreadsheet.xml

Solved errors

Problem During Load because of wrong ss:ExpandedRowCount.

If this value is out-of-sync with the table, the specified XML Spreadsheet document is invalid.

Check out XML Spreadsheet Reference for details.

Problem During Load because of empty rows and cells. Check out Table options.

Excel not enough available memory or disk space available, because Content-Type header was set to application/vnd.ms-excel. Change Content-Type to application/xml.

Styling

Fonts

<Style ss:ID="Bold">
  <Font ss:Bold="1"/>
</Style>

Formatting

<Style ss:ID="Fixed">
  <NumberFormat ss:Format="Standard"/>
</Style>

Applying styles

<Row ss:StyleID="Bold">
  <Cell ss:StyleID="Fixed"><Data ss:Type="Number">14.2201672241793</Cell>
</Row>

Formulas

Excel RC absolute reference

ss:Formula="=SUM(R2C:R[-2]C)", where R — Row, C — Column

Example

<Row>
  <Cell ss:StyleID="Bold"><Data ss:Type="String">Total:</Data></Cell>
  <Cell ss:StyleID="Fixed" ss:Formula="=SUM(R2C:R[-2]C)"><Data ss:Type="Number"></Data></Cell>
</Row>

Table options

Columns index

Usage: ss:Index="<value>"

Example:

<Cell ss:Index="4"><Data ss:Type="Number">73</Data></Cell>

Empty rows

<Row>
  <Cell ss:Index="4"/>
</Row>

XML Spreadsheet Reference

spreadsheet.xml

<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:o="urn:schemas-microsoft-com:office:office"
 xmlns:x="urn:schemas-microsoft-com:office:excel"
 xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:html="http://www.w3.org/TR/REC-html40">
 <Styles>
  <Style ss:ID="Bold">
   <Font ss:Bold="1"/>
  </Style>
  <Style ss:ID="Fixed">
   <NumberFormat ss:Format="Standard"/>
  </Style>
 </Styles>
 <Worksheet ss:Name="List">
  <Table>
   <Column ss:Width="60.75"/>
   <Column ss:Width="54.75"/>
   <Column ss:Width="60.75"/>
   <Column ss:Width="33"/>
   <Row ss:AutoFitHeight="0" ss:StyleID="Bold">
    <Cell><Data ss:Type="String">Title</Data></Cell>
    <Cell><Data ss:Type="String">Unit price</Data></Cell>
    <Cell><Data ss:Type="String">Amount</Data></Cell>
    <Cell><Data ss:Type="String">Total</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="String">0220/12-507</Data></Cell>
    <Cell ss:StyleID="Fixed"><Data ss:Type="Number">14.2201672241793</Data></Cell>
    <Cell><Data ss:Type="Number">1</Data></Cell>
    <Cell ss:StyleID="Fixed" ss:Formula="=RC[-2]*RC[-1]"><Data ss:Type="Number" /></Cell>
  </Row>
  <Row>
    <Cell><Data ss:Type="String">1803-02-96-507</Data></Cell>
    <Cell ss:StyleID="Fixed"><Data ss:Type="Number">58.95</Data></Cell>
    <Cell><Data ss:Type="Number">1</Data></Cell>
    <Cell ss:StyleID="Fixed" ss:Formula="=RC[-2]*RC[-1]"><Data ss:Type="Number" /></Cell>
  </Row>
  <Row>
    <Cell><Data ss:Type="String">0211/11-507</Data></Cell>
    <Cell ss:StyleID="Fixed"><Data ss:Type="Number">76.8558182812698</Data></Cell>
    <Cell><Data ss:Type="Number">1</Data></Cell>
    <Cell ss:StyleID="Fixed" ss:Formula="=RC[-2]*RC[-1]"><Data ss:Type="Number" /></Cell>
  </Row>  
  <Row>
    <Cell><Data ss:Type="String">2561-96-507</Data></Cell>
    <Cell ss:StyleID="Fixed"><Data ss:Type="Number">104.46</Data></Cell>
    <Cell><Data ss:Type="Number">1</Data></Cell>
    <Cell ss:StyleID="Fixed" ss:Formula="=RC[-2]*RC[-1]"><Data ss:Type="Number" /></Cell>
  </Row>  
  <Row>
    <Cell><Data ss:Type="String">2511-93-507</Data></Cell>
    <Cell ss:StyleID="Fixed"><Data ss:Type="Number">134.62</Data></Cell>
    <Cell><Data ss:Type="Number">1</Data></Cell>
    <Cell ss:StyleID="Fixed" ss:Formula="=RC[-2]*RC[-1]"><Data ss:Type="Number" /></Cell>
  </Row>
  <Row ss:AutoFitHeight="0">
    <Cell ss:Index="4"/>
  </Row>
  <Row>
    <Cell ss:StyleID="Bold"><Data ss:Type="String">Total:</Data></Cell>
    <Cell ss:Index="4" ss:StyleID="Fixed" ss:Formula="=SUM(R2C:R[-2]C)"><Data ss:Type="Number"></Data></Cell>
  </Row>
  </Table>
 </Worksheet>
</Workbook>