Registration

Dear SAP Community Member,
In order to fully benefit from what the SAP Community has to offer, please register at:
http://scn.sap.com
Thank you,
The SAP Community team.
Skip to end of metadata
Go to start of metadata

 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.

Note!

Below hierarchy contains only list of tags which we can use in XML document, but doesn't mention about attributes available for each to use. For full reference on this please visit XML Spreadsheet Reference.

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. 

Reference

http://msdn.microsoft.com/en-us/library/aa140062.aspx