In this post I will be explaining how we can utilize starndard xml tags for formatting data in excel.The use of this approach would eliminate the need of external apis. With standard xml approach we could download data into excel, but we could not do any formatting in the excel file. But this approach also provides lots of flexibility in formatting the excel file. The first thing comes into mind when we hear the word formatting is changing font of the text. But this time we would try to do something beyond the traditional way of formatting e.g. merging of cells, adding drop downs, fixing width of a cell etc.
XML Tags Hierarchy
<ss:Workbook>
<ss:Styles>
<ss:Style>
<ss:Alignment/>
<ss:Borders>
<ss:Border/>
</ss:Borders>
<ss:Font/>
<ss:Interior/>
<ss:NumberFormat/>
<ss:Protection/>
</ss:Style>
</ss:Styles>
<ss:Names>
<ss:NamedRange/>
</ss:Names>
<ss:Worksheet>
<ss:Names>
<ss:NamedRange/>
</ss:Names>
<ss:Table>
<ss:Column/>
<ss:Row>
<ss:Cell>
<ss:NamedCell/>
<ss:Data>
<Font/>
<B/>
<I/>
<U/>
<S/>
<Sub/>
<Sup/>
<Span/>
</ss:Data>
<x:PhoneticText/>
<ss:Comment>
<ss:Data>
<Font/>
<B/>
<I/>
<U/>
<S/>
<Sub/>
<Sup/>
<Span/>
</ss:Data>
</ss:Comment>
<o:SmartTags>
<stN:SmartTag/>
</o:SmartTags>
</ss:Cell>
</ss:Row>
</ss:Table>
<c:WorksheetOptions>
<c:DisplayCustomHeaders/>
</c:WorksheetOptions>
<x:WorksheetOptions>
<x:PageSetup>
<x:Layout/>
<x:PageMargins/>
<x:Header/>
<x:Footer/>
</x:PageSetup>
</x:WorksheetOptions>
<x:AutoFilter>
<x:AutoFilterColumn>
<x:AutoFilterCondition/>
<x:AutoFilterAnd>
<x:AutoFilterCondition/>
</x:AutoFilterAnd>
<x:AutoFilterOr>
<x:AutoFilterCondition/>
</x:AutoFilterOr>
</x:AutoFilterColumn>
</x:AutoFilter>
</ss:Worksheet>
<c:ComponentOptions>
<c:Toolbar>
<c:HideOfficeLogo/>
</c:Toolbar>
</c:ComponentOptions>
<o:SmartTagType/>
</ss:Workbook>
All of the formatting needed in the excel sheet are encapsulated in the <ss:Styles></ss:Styles> section or they can be specified individually for each cell within the tag <ss:Cell></ss:Cell>.
Example
XML CODE
<?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="Default" ss:Name="Normal">
<Alignment ss:Vertical="Bottom"/>
<Borders/>
<Font/>
<Interior/>
<NumberFormat/>
<Protection/>
</Style>
<Style ss:ID="s1">
<Alignment ss:Vertical="Center"/>
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
</Borders>
</Style>
<Style ss:ID="s2">
<Alignment ss:Vertical="Center"/>
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
</Borders>
</Style>
<Style ss:ID="s3">
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
</Borders>
</Style>
<Style ss:ID="s4">
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
</Borders>
<Font x:Family="Swiss" ss:Bold="1"/>
<Interior ss:Color="#CCFFFF" ss:Pattern="Solid"/>
</Style>
<Style ss:ID="s5">
<Alignment ss:Vertical="Bottom" ss:WrapText="1"/>
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
</Borders>
<Font x:Family="Swiss" ss:Bold="1"/>
<Interior ss:Color="#CCFFFF" ss:Pattern="Solid"/>
</Style>
<Style ss:ID="s6">
<Alignment ss:Vertical="Bottom" ss:WrapText="1"/>
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
</Borders>
</Style>
<Style ss:ID="s7">
<Alignment ss:Vertical="Bottom" ss:WrapText="1"/>
</Style>
<Style ss:ID="s8">
<Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
</Borders>
<Font x:Family="Swiss" ss:Size="24" ss:Bold="1"/>
</Style>
</Styles>
<Worksheet ss:Name="Sheet1">
<Table ss:ExpandedColumnCount="4" ss:ExpandedRowCount="9" x:FullColumns="1"
x:FullRows="1">
<Column ss:AutoFitWidth="0" ss:Width="135"/>
<Column ss:Index="3" ss:StyleID="s7" ss:AutoFitWidth="0" ss:Width="66.75"/>
<Row ss:Height="30">
<Cell ss:MergeAcross="3" ss:StyleID="s8"><Data ss:Type="String">Title</Data></Cell>
</Row>
<Row>
<Cell ss:StyleID="s4"><Data ss:Type="String">Column1</Data></Cell>
<Cell ss:StyleID="s4"><Data ss:Type="String">Column2</Data></Cell>
<Cell ss:StyleID="s5"><Data ss:Type="String">Column3</Data></Cell>
<Cell ss:StyleID="s4"><Data ss:Type="String">Column4</Data></Cell>
</Row>
<Row ss:Height="76.5">
<Cell ss:MergeDown="1" ss:StyleID="s1"><Data ss:Type="String">Row 3 & Row 4 Merged</Data></Cell>
<Cell ss:StyleID="s3"><Data ss:Type="String">Red</Data></Cell>
<Cell ss:StyleID="s6"><Data ss:Type="String">Wrap text when width of the text exceeds the specified width</Data></Cell>
<Cell ss:StyleID="s3"/>
</Row>
<Row>
<Cell ss:Index="2" ss:StyleID="s3"><Data ss:Type="String">Black</Data></Cell>
<Cell ss:StyleID="s6"/>
<Cell ss:StyleID="s3"/>
</Row>
<Row>
<Cell ss:StyleID="s3"><Data ss:Type="String">Row 4</Data></Cell>
<Cell ss:StyleID="s3"><Data ss:Type="String">Black</Data></Cell>
<Cell ss:StyleID="s6"/>
<Cell ss:StyleID="s3"/>
</Row>
<Row>
<Cell ss:StyleID="s3"><Data ss:Type="String">Row 5</Data></Cell>
<Cell ss:StyleID="s3"><Data ss:Type="String">Red</Data></Cell>
<Cell ss:StyleID="s6"/>
<Cell ss:StyleID="s3"/>
</Row>
<Row>
<Cell ss:StyleID="s3"><Data ss:Type="String">Row 6</Data></Cell>
<Cell ss:StyleID="s3"><Data ss:Type="String">Black</Data></Cell>
<Cell ss:StyleID="s6"/>
<Cell ss:StyleID="s3"/>
</Row>
<Row>
<Cell ss:MergeDown="1" ss:StyleID="s2"><Data ss:Type="String">Row 8 & 9 Merged</Data></Cell>
<Cell ss:StyleID="s3"><Data ss:Type="String">Green</Data></Cell>
<Cell ss:StyleID="s6"/>
<Cell ss:StyleID="s3"/>
</Row>
<Row>
<Cell ss:Index="2" ss:StyleID="s3"><Data ss:Type="String">Red</Data></Cell>
<Cell ss:StyleID="s6"/>
<Cell ss:StyleID="s3"/>
</Row>
</Table> <DataValidation xmlns="urn:schemas-microsoft-com:office:excel">
<Range>R2C2:R1000C2</Range>
<Type>List</Type>
<CellRangeList/>
<Value>"Red, Black, Green"</Value>
</DataValidation>
</Worksheet></Workbook>
Save this code into an .xml file and open with excel to see whether your code is correct.Now all you have to do is to generate a xml string programmatically and download into .xml file.
