Skip to content

Instantly share code, notes, and snippets.

What would you like to do?
Some code to make color customization in ColdFusion 9's cfspreadsheet implementation clean
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){
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");
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...
Copy link

DominicWatson commented Sep 14, 2011

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"} );

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment