SpreadsheetFormatRows format color ColdFusion

Question

I'm creating Excel files using ColdFusion and the SpreadsheetNew, SpreadsheetAddRows, SpreadsheetFormatRows, etc. functions. According to the docs that I have read located here their is a propery for color, and fgcolor. I'm a bit confused as to what the difference between the two are. Is one the text color and the other the background color? I've been using fgcolor to set the background color of rows.

// HEADER ROW FORMAT
formatHeaderRow = StructNew();
formatHeaderRow.fgcolor="royal_blue";

My main question is, according to the docs I can supply any value in the org.apache.poi.hssf.util.HSSFColor color class as my color. However, I REALLY need to supply either a HEX value or RGB. I know Excel can handle it as you can enter either within excel's colorpicker. Is there ANY way to enter a HEX or RGB value for my row colors?

thank you!

UPDATE

<cfscript>
// create XLSX workbook with a few cells
// and grab underlying POI objects
cfSheet = Spreadsheetnew("Sheet1", true);
poiWorkbook = cfSheet.getWorkBook();
poiSheet = poiWorkbook.getSheet("Sheet1");


// Create reusuable style objects 
// NOTE: Excel limits the maximum number of styles allowed. So do not create a new
// style for every cell. Create distinct styles once, and apply to multiple cells/rows.
Color = createObject("java", "java.awt.Color");

// Style 1: Cell with background color (only)
backgroundOnlyStyle = poiWorkbook.createCellStyle();
backgroundOnlyStyle.setFillPattern( backgroundOnlyStyle.SOLID_FOREGROUND );
XSSFColor = createObject("java", "org.apache.poi.xssf.usermodel.XSSFColor");
backgroundOnlyStyle.setFillForegroundColor( XSSFColor.init(Color.decode("##055910")) );

// Apply styles to cell A1. Note: POI indexes are 0-based
SpreadSheetSetCellValue(cfSheet, "background color only", 1, 1);
poiSheet.getRow( 0 ).setRowStyle( backgroundOnlyStyle );


</cfscript>

<!--- stream it to the browser --->
<cfheader name="Content-Disposition" value="inline; filename=reportName.xlsx">
<cfcontent type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" variable="#SpreadSheetReadBinary(cfSheet)#">

Answer 1

I'm a bit confused as to what the difference between the two are.

Understandably. The property names were modeled after the conventions used in POI (underlying java library) which are a bit confusing to begin with IMO. Since ColdFusion only implements a subset of the POI features, the names are taken out of context, making it even more confusing. To answer your question, in POI there are actually three (3) relevant color properties here:

1. Font Color - ie Font.setColor()

   The color of the cell text. In CF, this is controlled by the dataFormat.color property.

2. Cell Pattern Foreground Color - ie CellStyle.setFillForegroundColor

   Despite the name, this is what most people think of as the cell background color (yellow in the image below). In CF this is controlled by the dataFormat.fgColor property.

3. Cell Pattern Background Color - CellStyle.setFillBackgroundColor

   (Optional) Secondary color used in multi-color cell patterns (red in the image below). There is no ColdFusion equivalent.

Is there ANY way to enter a HEX or RGB value for my row colors?

Last I checked it is not supported by the core CF functions. However, you could tap into the underlying POI library which does support it. Assuming you are using the newer .XLSX format, it can be done by creating a CellStyle and applying the desired XSSFColor.

Here is an example (tested with CF11) of how to set the font and/or cell background colors via POI. Though in the real code, I would recommend wrapping up the basic logic in a reusable function.

Example:

// create XLSX workbook with a few cells
// and grab underlying POI objects
cfSheet = Spreadsheetnew("Sheet1", true);
poiWorkbook = cfSheet.getWorkBook();
poiSheet = poiWorkbook.getSheet("Sheet1");


// Create reusuable style objects 
// NOTE: Excel limits the maximum number of styles allowed. So do not create a new
// style for every cell. Create distinct styles once, and apply to multiple cells/rows.
Color = createObject("java", "java.awt.Color");

// Style 1: Cell with background color (only)
backgroundOnlyStyle = poiWorkbook.createCellStyle();
backgroundOnlyStyle.setFillPattern( backgroundOnlyStyle.SOLID_FOREGROUND );
XSSFColor = createObject("java", "org.apache.poi.xssf.usermodel.XSSFColor");
backgroundOnlyStyle.setFillForegroundColor( XSSFColor.init(Color.decode("##055910")) );

// Style 2: Cell with font color (only)
textOnlyStyle = poiWorkbook.createCellStyle();
textFont = poiWorkbook.createFont();
XSSFColor = createObject("java", "org.apache.poi.xssf.usermodel.XSSFColor");
textFont.setColor( XSSFColor.init(Color.decode("##bd13be")) );
textOnlyStyle.setFont( textFont );

// Style 3: Cell with both backgound and Text color
backgroundAndTextStyle = poiWorkbook.createCellStyle();
textFont = poiWorkbook.createFont();
XSSFColor = createObject("java", "org.apache.poi.xssf.usermodel.XSSFColor");
textFont.setColor( XSSFColor.init(Color.decode("##a20932")) );
backgroundAndTextStyle.setFont( textFont );
XSSFColor = createObject("java", "org.apache.poi.xssf.usermodel.XSSFColor");
backgroundAndTextStyle.setFillPattern( backgroundAndTextStyle.SOLID_FOREGROUND );
backgroundAndTextStyle.setFillForegroundColor( XSSFColor.init(Color.decode("##192fda")) );

// Apply styles to cell A1. Note: POI indexes are 0-based
SpreadSheetSetCellValue(cfSheet, "background color only", 1, 1);
poiSheet.getRow( 0 ).getCell( 0 ).setCellStyle( backgroundOnlyStyle );

// Apply styles to cell A2
SpreadSheetSetCellValue(cfSheet, "text color only", 2, 1);
poiSheet.getRow( 1 ).getCell( 0 ).setCellStyle( textOnlyStyle );

// Apply styles to cell A3
SpreadSheetSetCellValue(cfSheet, "background AND text color", 3, 1);
poiSheet.getRow( 2 ).getCell( 0 ).setCellStyle( backgroundAndTextStyle );

// Save to file
SpreadSheetWrite(cfSheet, "c:/path/to/yourFile.xlsx", true);