CODEWITHSUNDEEP
coldfusioncoldfusion-10cfspreadsheet
bman2013
bman2013

Reputation: 407

Applying conditional formatting with CFSpreadsheet

In continuation of a previous thread, I've reached pretty close to where I want and have learned a lot. I'm under CF10 on a MSSQL Server 2008 environment. I have a report I'm generating using cfspreadsheet and then spitting out values based on whether a user has an app enabled it will be output as "Yes" and if not output as "No" in the excel spreadsheet.

Problem is, I need to make it a little easier on the eye and so I wanted to see if it was possible to apply conditional formatting to where if the 3 columns with 3 different apps is Y then it will be green and if N it will be red.

Any suggestions or examples would be great, thanks!

Upvotes: 2

Views: 1146

Answers (2)

Leigh
Leigh

Reputation: 28873

Like I mentioned in your other thread, if you return bit values (not strings), it is simple to apply a custom cell format. But you must use the spreadsheet functions, rather than cfspreadsheet (which does not support custom formatting).

Here is an expanded example to demonstrate how you could incorporate conditional color formatting:

<cfscript>
    // build sample query 
    // note: values must be numeric and NOT text/varchar
    qData = queryNew("");
    queryAddColumn(qData, "AppNameAlpha", "bit", listToArray("0,0,1,0,1"));
    queryAddColumn(qData, "AppNameBeta", "bit", listToArray("1,1,0,0,1"));

    // create sample sheet
    sheet = spreadsheetNew();
    spreadsheetAddRows(sheet, qData);
    // apply colorized yes/no format
    spreadsheetFormatColumns(sheet, {dataformat='[Green]"Y";;[Red]"N"'}, "1-2");
    spreadsheetWrite(sheet, "c:/path/to/sheet.xls", true);
</cfscript>

The "dataformat" uses the first three sections of Excel's custom number formatting: <positive><negative><zero>. Translated:

 [Green]"Y";   // <positive values>: display "Y" in green
 ;             // <negative values>: do nothing extra
 [Red]"N"      // <zero values>: display "N" in red

Upvotes: 3

XaxD
XaxD

Reputation: 1538

The function you are looking for is SpreadsheetFormatCell()

Upvotes: 0

Related Questions