How to create an Excel file programmatically (Part 2)

Offce 2003 XML method

This method is as simple as saving an .xls file into a template .xml format. When we open the saved XML file, we'll see a rather simple format to follow and places where we can insert new data rows.

<!--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"> <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office" /> <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel" /> <Styles> <Style />... </Styles> <Worksheet ss:Name="Sheet1"> ... {Data table location} <WorksheetOptions /> </Worksheet> </Workbook>

All we have to do then is insert the data table in the XML dialect below, which while different from (X)HTML is still relatively easy to understand and follow.

<Table ss:ExpandedColumnCount="3" ss:ExpandedRowCount="2" x:FullColumns="1" x:FullRows="1" ss:DefaultRowHeight="15"> <Column ss:Width="107.25"/> <Column ss:Width="106.5"/> <Column ss:Width="21"/> <Row> <Cell><Data ss:Type="String">This is Cell A1 in Excel</Data></Cell> <Cell><Data ss:Type="String">This is Cell B1 in Excel</Data></Cell> </Row> <Row> <Cell><Data ss:Type="Number">123</Data></Cell> <Cell><Data ss:Type="Number">456</Data></Cell> <Cell ss:Formula="=RC[-2]+RC[-1]"><Data ss:Type="Number">579</Data></Cell> </Row> </Table>

But even if we don't use Excel's own dialect of table elements, we can still easily substitute in regular HTML (4.0) tags as indicated in the namespace declaration near the beginning of the XML document above:

<Workbook ... xmlns:html=".../REC-html40">

All in all, it's still relatively simple to do.

(Part 3)The OpenXML approach, however, isn't as simple (for me, anyway)