XML Format for Excel

Here is a guide to create an xls file in an XML document.

First step is the declaration of XML document. This defines the XML version and the encoding.

<?xml version=”1.0″? encoding=”ISO-8859-1″?>
<?mso-application progid=”Excel.Sheet”?>

Next is the root tag and schemas for excel.

<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″&gt;

Under the <Workbook> Tag, there are Elements that constant for an excel format.

First element is the <DocumentProperties>. This tag set the excel properties such as Author, Title, Date and Time created and so on.

<DocumentProperties xmlns=”urn:schemas-microsoft-com:office:office”>

As for child node of <DocumentProperties>.

<Author>
<LastAuthor>
<Created>
<Version>
<Title>
<Subject>
<Keywords>
<Category>
<Manager>

The next element is <ExcelWorkbook>. Below is the format and the corresponding child node.

<ExcelWorkbook>
<WindowHeight>8700</WindowHeight>
<WindowWidth>12315</WindowWidth>
<WindowTopY>120</WindowTopY>
<WindowTopX>60</WindowTopX>
<ProtectStructure>False</ProtectStructure>
<ProtectWindows>False</ProtectStructure>
</ExcelWorkbook>

Now is for the style of the data to be represent in the excel spreadsheet. This is similar to the CCS.

The tag element will be <Styles> and each child node will be <Style>. And each element of <Style> node is the format of how is the data to be represent.

<Styles>
<Style ss:ID=”Default” ss:Name=”Normal”>
<Alignment ss:Vertical=”Bottom”/>
<Borders/>
<Font/>
<Interior/>
<NumberFormat/>
<Protection/>
</Style>
<Style ss:ID=”DefaultNumber”>
<Alignment ss:Horizontal=”Right”/>
</Style>
<Style ss:ID=”BoldItalic”>
<Font ss:Bold=”1″ ss:Italic=”1″/>
</Style>
<Style ss:ID=”SimpleUnderline”>
<Font ss:Underline=”Single”/>
<Alignment ss:Horizontal=”Right”/>
</Style>
<Style ss:ID=”BoldAndUnderline”>
<Font ss:Bold=”1″ ss:Underline=”Single”/>
<Alignment ss:Horizontal=”Left”/>
</Style>
<Style ss:ID=”DoubleUnderline”>
<Font ss:Underline=”Double”/>
<Alignment ss:Horizontal=”Right”/>
</Style>
<Style ss:ID=”Currency3Decimals”>
<NumberFormat ss:Format=”&quot;$&quot;#,##0.000″/>
<Alignment ss:Horizontal=”Right”/>
</Style><Style ss:ID=”Header”>
<Font ss:FontName=”Comic Sans MS” x:Family=”Swiss” ss:Size=”12″/>
<Alignment ss:Horizontal=”Center” ss:Vertical=”Center”/>
</Style>
</Styles>

Those are some example style. You can put other style depending on how you like your data to be represent.

The next node is the <Worksheet> node. This node hold the Table informations and data and style of each cells.

<Worksheet ss:Name=”Sheet Name”>

Then under the <Worksheet> Node, is the <Table> node.

<Table ss:ExpandedColumnCount=”256″ ss:ExpandedRowCount=”21″ x:FullColumns=”1″ x:FullRows=”1″>

We will create a table with 5 columns.

<Column ss:AutoFitWidth=”0″ ss:Width=”10″/>
<Column ss:StyleID=”DefaultNumber” ss:AutoFitWidth=”0″ ss:Width=”80″/>
<Column ss:StyleID=”BoldItalic” ss:AutoFitWidth=”0″ ss:Width=”80″/>
<Column ss:StyleID=”SimpleUnderline” ss:AutoFitWidth=”0″ ss:Width=”90″/>
<Column ss:StyleID=”Currency3Decimals” ss:AutoFitWidth=”0″ ss:Width=”100″/>

The first width is the height of the cell and the second width is the actual width of the cell.

The StyleID is the id we declared in the <Styles> node.

<Row Num=”1″>
<Cell ss:Index=”2″ ss:StyleID=”Header” ss:MergeAcross=”4″><Data ss:Type=”String”>This is the first row</Data></Cell>
</Row>

The ss:Index=”2″, means that the data will be place at the 2nd column which is Column B. The ss:MergeAcross=”4″, merge the cells from column B to E.

<Row Num=”3″ ss:Index=”3″>
<Cell ss:Index=”2″><Data ss:Type=”String”>DefaultNumber</Data></Cell>
<Cell><Data ss:Type=”String”>BoldItalic</Data></Cell>
<Cell><Data ss:Type=”String”>SimpleUnderline</Data></Cell>
<Cell><Data ss:Type=”String”>Currency3Decimals</Data></Cell>
</Row>
<Row Num=”4″ ss:Index=”4″>
<Cell ss:Index=”2″><Data ss:Type=”Number”>123456</Data></Cell>
<Cell><Data ss:Type=”String”>Bold and Italic</Data></Cell>
<Cell><Data ss:Type=”String”>Underline</Data></Cell>
<Cell><Data ss:Type=”Number”>123456</Data></Cell>
</Row>

The last node for the worksheet is the <WorksheetOptions>. Its contain the Print, Pane, Selected, etc.

<WorksheetOptions xmlns=”urn:schemas-microsoft-com:office:excel”>
<Print>
<ValidPrinterInfo/>
<HorizontalResolution>200</HorizontalResolution>
<VerticalResolution>200</VerticalResolution>
<NumberofCopies>0</NumberofCopies>
</Print>
<Selected/>
<Panes>
<Pane>
<Number>3</Number>
<ActiveRow>1</ActiveRow>
</Pane>
</Panes>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>

The <Worksheet> node can be use repeatedly, if you want to create more than one sheet in your excel file.

And now we can close the XML with the </Workbook> closing tag.

</Workbook>

Hope this guide help you with your project.