Some code to make color customization in ColdFusion 9's cfspreadsheet implementation clean

gistfile1.cfm

<cfscript>
void function spreadsheetSetCustomPalette(required any workbook, required struct palette){
	var customPalette = arguments.workbook.getworkbook().getcustompalette();
	var color         = "";
	var colorIndex    = 0;
	var rgb           = "";
	
	for(color in palette){
		try{
			colorIndex = CreateObject("java", "org.apache.poi.hssf.util.HSSFColor$#UCase( color )#").GetIndex();					
		
		} catch(any e){
			throw("badColor", "The color, '#color#', was not found in the org.apache.poi.hssf.util.HSSFColor palette");
		}
		
		try{
			rgb = hexToRGBByteValues(palette[color]);
		} catch(any e){
			throw("badColor", "The color, '#palette[color]#', is not a valid hex color");
		}
		
		customPalette.setcoloratindex(colorIndex, rgb.r, rgb.g, rgb.b);
	}	
}

string function hexToRGBByteValues( required string rgbColor ) {
	var hex     = Replace(arguments.hexColor, '##', '', 'ALL');
	var rgb     = {};
	var rgbList = "";
	var i       = 0;
	
	for (i=1; i lte 5; i=i+2){
	    rgbList = listAppend(rgbList, InputBaseN(mid(hex,i,2),16));
	}
	
	rgb.R = javacast("int", ListGetAt(rgbList, 1)).bytevalue();
	rgb.G = javacast("int", ListGetAt(rgbList, 2)).bytevalue();
	rgb.B = javacast("int", ListGetAt(rgbList, 3)).bytevalue();
	
	return rgb;
}

string function exampleUsage(){

	var workBook = SpreadsheetNew();
	
	spreadsheetSetCustomPalette( workbook, {white="000000", black="FFFFFF", lemon_chiffon="FF0000"} );
	
	// carry on with your workbook as normal, the colors above will be substituted...

}
</cfscript>

Note that this only works for .xls spreadsheets and not .xlsx. The following would fail:

myWorkbook = SpreadsheetNew('Sheet1', true); // true = create xlsx spreadsheet
spreadSheetSetCustomPalette( myWorkbook, {blue="FD098B"} );