I have a couple of tables that I export to excel using the system.dataset.datasetToExcel() function. I am using the data property from each table. The tables on the screen use the configure cell extension function to color the text red if the value is negative. Is there a way to keep this same configuration when I export to excel?
Hello Phil,
This is possible, but it is not going to be easy. The main problem is that system.dataset.datasetToExcel() takes a dataset only, which does not have any of the table formatting associated with it. The easiest way I could think of to get the desired results is to edit the XML string output by the Excel export function. You will need to come up with a function to make these xml string modifications to get the desired format, which will be more complicated than setting up cell formatting on table components. Below is an example of how to do this.
"""
Modifying DatasetToExcel formatting by manipulating XML string directly
"""
ds = system.dataset.toDataSet(['Val1', 'Val2'], [[-4.2, 3.4], [0.0, -0.9]])
def colorNegativeRed(excelXMLStr):
"""Adds red background to negative numbers"""
# Add new xml style type
withRedStyle = excelXMLStr.replace('<Styles>', ('<Styles>'
'<Style ss:ID="s64">'
'<Interior ss:Color="#FF0000" ss:Pattern="Solid"/>'
'</Style>'))
# If you want font color changed instead of background, use this style modification
#</Style>
# <Style ss:ID="s63">
# <Font ss:FontName="Arial" x:Family="Swiss" ss:Color="#FF0000"/>
#</Style>
# replace negative number default style with red background
withRedCells = withRedStyle.replace('<Cell><Data ss:Type="Number">-',
'<Cell ss:StyleID="s64"><Data ss:Type="Number">-')
return withRedCells
redCells = colorNegativeRed(system.dataset.dataSetToExcel(True, [ds]))
savePath = system.file.saveFile('redcells.xml', 'xml', 'Excel sheet')
system.file.writeFile(savePath, redCells)
5 Likes
Thanks for the response. Ill give this a try and see what happens.
