Search code examples
xmlexcelspreadsheetml

Format Excel Cells with XML

I want to generate an Excel programmatically (Salesforce Apex) something similar to below screenshot. The number of Cells, background color of the cells will be decided at the run time and hence using programmatic way. enter image description here

To achieve this I tried to apply inline styles for Cell > Data but it seems we can't apply inline styles there. For example styles get applied to first Cell with ss:StyleID="s66". But for the second Cell it doesn't work inline styles in that manner. In my requirement since I can't pre-define the style I need some dynamic way. Can anyone confirm if this is not possible or provide any guidance?

<Row>

    <Cell ss:StyleID="s66"><Data ss:Type="String">Test Sheet1</Data></Cell>
    <Cell ><Data ss:Type="String"><Font ss:Color="#FF0000">Sample Text</Font></Data></Cell>

</Row>
Solution

  • The XML you are trying to use is Office 2003 SpreadsheetML. The reference is XML Spreadsheet Reference.

    If you look at the "XML Spreadsheet Tag Hierarchy", you will see, that the namespace ss is always prefixed there. So it is not the default namespace. The default namespace is html. So the Font, B, Sup tags are not prefixed by namespace.

    But in current Excel versions, if saved as Office 2003 SpreadsheetML, the default namespace is set to xmlns="urn:schemas-microsoft-com:office:spreadsheet" which is ss. So if one wants using html tags, they must be prefixed by html.

    Example:

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

 <Worksheet ss:Name="Tabelle1">
  <Table>
   <Row>
    <Cell><Data ss:Type="String"><html:Font x:Color="#FF0000">Test</html:Font></Data></Cell>
    <Cell><Data ss:Type="String"><html:B>E = m c <html:Sup>2</html:Sup></html:B></Data></Cell>
   </Row>
  </Table>
 </Worksheet>

</Workbook>

    Another option would be changing the default namespace to xmlns="http://www.w3.org/TR/REC-html40" which is html. Then the html tags needs not be prefixed by html but then the ss tags must.

    Example:

    <?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<ss:Workbook xmlns="http://www.w3.org/TR/REC-html40"
 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">

 <ss:Worksheet ss:Name="Tabelle1">
  <ss:Table>
   <ss:Row>
    <ss:Cell><ss:Data ss:Type="String"><Font x:Color="#FF0000">Test</Font></Data></Cell>
    <ss:Cell><ss:Data ss:Type="String"><B>E = m c <Sup>2</Sup></B></Data></Cell>
   </ss:Row>
  </ss:Table>
 </ss:Worksheet>

</ss:Workbook>