Skip to content

Instantly share code, notes, and snippets.

Created September 12, 2011 16:27
  • Star 2 You must be signed in to star a gist
  • Fork 1 You must be signed in to fork a gist
Star You must be signed in to star a gist
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

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