Format Excel Cells with XML

Question

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.

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>

Cannot see your picture. Could you use a safe for work host? — Pclaverie
Attachment is correctly showing for me(checked as a guest user too). Media sharing sites are blocked in my office network unfortunately. Anyway what it has is a table with different number of cells/colors but with same width for each row — highfive

Axel Richter Axel Richter · Accepted Answer · 2018-03-13T07:37:35

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>

Format Excel Cells with XML

1 Answers