Skip to content

Instantly share code, notes, and snippets.

@ilyazub
Last active January 4, 2023 03:47
Show Gist options
  • Save ilyazub/2bced708865150b275b3 to your computer and use it in GitHub Desktop.
Save ilyazub/2bced708865150b275b3 to your computer and use it in GitHub Desktop.
Excel 2003 XML Spreadsheet example

Excel 2003 XML Spreadsheet example

Example

image

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

<?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>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment