3

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>
highfive
  • 628
  • 3
  • 12
  • 31
  • Cannot see your picture. Could you use a safe for work host? – Pclaverie Mar 13 '18 at 06:16
  • 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 Mar 13 '18 at 06:21

1 Answers1

6

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>
Axel Richter
  • 56,077
  • 6
  • 60
  • 87
  • Thanks for the clear explanation. One more thing as in OP, will it be possible to change the cell fill color? `` tag is what I found for that but can't be a child of `` it seems – highfive Mar 13 '18 at 08:51
  • 1
    Inline formatting the cell interior seems not possible. But you really should using `ss:Styles` having `ss:Style` in it and only having the `ss:StyleID` in the cell. The maximum number of single cell styles in a workbiook is limited and frequently using inline formatting will exceed that limit very fast. – Axel Richter Mar 13 '18 at 09:52
  • Thanks @Axel Richter. It helps – highfive Mar 13 '18 at 10:08