Applying styles conditionally to an excel spreadsheet rendered as an xml

[Benjamin Samuel]

Hi,

I want to know how to apply styles to the rows of a generated spreadsheet, based on a particular condition.

The scenario is like this:

I have a jsp, jsp1 which displays a report. This jsp contains an 'export to excel' button, on click of which, another jsp, jsp2 is displayed with the report data rendered as an xml. Certain rows of the report is coloured, based on the property 'agingColor' of the report bean, reportLineItemBean.

I need to colour the rows of my generated excel sheet also in a similar way. But i was unable to do the same as i could specify only one interior(background) colour in a particular style. The code is as follows:

<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="s23">
   <Alignment ss:Horizontal="Left" ss:Vertical="Top" ss:WrapText="1"/>
   <Font />
   <Interior ss:Color="#000000" ss:Pattern="Solid"/>
  </Style>
</Styles>

<Worksheet ss:Name="ReportScreenExcel">
<table>
  <logic:notEmpty name="reportLineItemsBean" property="lineItems">
   <logic:iterate id="lineItems" name="reportLineItemsBean" property="lineItems">
    <Row ss:AutoFitHeight="0" ss:Height="25">		
      <Cell ss:StyleID="s23"><Data ss:Type="String">
         <bean:write name="lineItems" property="agingColor"/>
         <bean:write name="lineItems" property="creName"/></Data>
     </Cell>
     <Cell ss:StyleID="s23"><Data ss:Type="String">
        <bean:write name="lineItems" property="agingColor"/>
        <bean:write name="lineItems" property="clientName"/></Data>
    </Cell>
   </Row>
  </logic:iterate>
</logic:notEmpty>
<table>
</Worksheet>
</Workbook>	   
    

The above code displays the hexadecimal value of the agingColor along with the cell value, rather than applying that color to the styles. I need to apply the values for the background color for the cells as specified by the agingColor property, in a similar way the color #000000 is applied to the cells.

Please help.
Thanks in advance.

[Jeanne Boyarsky]

What happened when you tried adding another <style> tag?

[Bear Bibeault]

When using Struts, please be sure to post in the Struts forum. I have moved this post there for you.

[Benjamin Samuel]

Jeanne,

Could you please elaborate on this? Could you provide me a sample code if possible?
Depending on the property i want to color the cell.

Thank you.