Generating an EXCEL dataset with formating

[Ken Leidner]

I have the need to generate an EXCEL dataset, not macro driven, which
has formatting that just can’t be done in the CSV type of file format.

Generally my requirements are for a 6 column table with bold column
headings and a title line above the column heading that is also bold and
in a merged cell of the 6 columns.

Currently I am generating a .XML formatted document, but you have to
remember to say Open with EXCEL or it opens in the browser and you see
the code to generate the table not the table.

The beginning of the code is below. Is there a better format that can
be used, so EXCEL is the default format? Also where is the “description
of the format at? The dataset is being sent as an attachment to people,
so I can’t control their machines and change the file associations.

<?xml version="1.0"?>
<Workbook xmlns="urn:schemas-microsoft-comffice:spreadsheet"
xmlns="urn:schemas-microsoft-comfficeffice"
xmlns:x="urn:schemas-microsoft-comffice:excel"
xmlns:ss="urn:schemas-microsoft-comffice:spreadsheet"
xmlns:html="http://www.w3.org/TR/REC-html40">
<DocumentProperties xmlns="urn:schemas-microsoft-comfficeffice">
<Author>Kenneth Leidner</Author>
<LastAuthor>Me</LastAuthor>
<Created>2006-11-02T10:03:38Z</Created>
<LastSaved>2006-11-02T10:03:38Z</LastSaved>
<Company>XYZ</Company>
<Version>10.6735</Version>
</DocumentProperties>
<OfficeDocumentSettings
xmlns="urn:schemas-microsoft-comfficeffice">
<DownloadComponents/>
<LocationOfComponents HRef="file:///\\"/>
</OfficeDocumentSettings>
<ExcelWorkbook xmlns="urn:schemas-microsoft-comffice:excel">
<WindowHeight>7935</WindowHeight>
<WindowWidth>15195</WindowWidth>
<WindowTopX>0</WindowTopX>
<WindowTopY>45</WindowTopY>
<ProtectStructure>False</ProtectStructure>
<ProtectWindows>False</ProtectWindows>
</ExcelWorkbook>
<Styles>
<Style ss:ID="Default" ss:Name="Normal">
<Alignment ss:Vertical="Bottom"/>
<Borders/>
<Font/>
<Interior/>
<NumberFormat/>
<Protection/>
</Style>
<Style ss:ID="s21">
<Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
<NumberFormat ss:Format="0000"/>
</Style>
<Style ss:ID="s22">
<Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
</Style>
<Style ss:ID="s23">
<Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
<Font x:Family="Swiss" ss:Bold="1"/>
</Style>
<Style ss:ID="s24">
<Alignment ss:Horizontal="Right" ss:Vertical="Bottom"/>
<Font x:Family="Swiss" ss:Bold="1"/>
</Style>
<Style ss:ID="s25">
<Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
<Font x:Family="Swiss" ss:Bold="1"/>
<NumberFormat ss:Format="0000"/>
</Style>
</Styles>
<Worksheet ss:Name="Book1">
<Table ss:ExpandedColumnCount="6" ss:ExpandedRowCount="265"
x:FullColumns="1"
x:FullRows="1">
<Column ss:Index="2" ss:StyleID="s21"/>
<Column ss:StyleID="s22" ss:Span="3"/>
<Row>
<Cell ss:MergeAcross="5" ss:StyleID="s23"><Data ss:Type="String">The
title line for the month of Oct</Data></Cell>
</Row>
<Row>
<Cell ss:StyleID="s24"><Data ss:Type="String">C1</Data></Cell>
<Cell ss:StyleID="s25"><Data ss:Type="String">C2</Data></Cell>
<Cell ss:StyleID="s23"><Data ss:Type="String">C3</Data></Cell>
<Cell ss:StyleID="s23"><Data ss:Type="String">C4 #</Data></Cell>
<Cell ss:StyleID="s23"><Data ss:Type="String">C5</Data></Cell>
<Cell ss:StyleID="s23"><Data ss:Type="String">C6</Data></Cell>
</Row>
<Row>
<Cell><Data ss:Type="Number">1</Data></Cell>
<Cell><Data ss:Type="Number">0597</Data></Cell>
<Cell><Data ss:Type="Number">06283</Data></Cell>
<Cell><Data ss:Type="Number">02510</Data></Cell>
<Cell><Data ss:Type="String">CAY448</Data></Cell>
<Cell><Data ss:Type="String">T0971A</Data></Cell>
</Row>
And repeat for each row. Then there is some closeing commands for the
table and all at the end.
…

 

[Harlan Grove]

Ken Leidner wrote...

I have the need to generate an EXCEL dataset, not macro driven, which
has formatting that just can't be done in the CSV type of file format.

Generally my requirements are for a 6 column table with bold column
headings and a title line above the column heading that is also bold and
in a merged cell of the 6 columns.

....

There's always the SYLK file format, which is plain text and would open
in Excel rather than in the browser. It won't merge the cells for the
title, but that shouldn't be critical.