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>.


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


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.

<Style ss:ID=”Default” ss:Name=”Normal”>
<Alignment ss:Vertical=”Bottom”/>
<Style ss:ID=”DefaultNumber”>
<Alignment ss:Horizontal=”Right”/>
<Style ss:ID=”BoldItalic”>
<Font ss:Bold=”1″ ss:Italic=”1″/>
<Style ss:ID=”SimpleUnderline”>
<Font ss:Underline=”Single”/>
<Alignment ss:Horizontal=”Right”/>
<Style ss:ID=”BoldAndUnderline”>
<Font ss:Bold=”1″ ss:Underline=”Single”/>
<Alignment ss:Horizontal=”Left”/>
<Style ss:ID=”DoubleUnderline”>
<Font ss:Underline=”Double”/>
<Alignment ss:Horizontal=”Right”/>
<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”/>

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>

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 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>

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

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

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.


Hope this guide help you with your project.