Format a text on cell in two color in Coldfusion

Question

Using Coldfusion 10, I need to format a cell contents in two color(Black and Blue). Let a cell on spreadsheet contains a string "Text1: Text2". where color of "Text1" should be black and "Text2" should be blue. I have tried below code, but it produces all blue text.

SpreadsheetAddRow(spreadsheetData,"Text1: Text2",1,1); 
format1=StructNew(); 
format1.color="black";
SpreadsheetFormatRow(spreadsheetData,format1,1);
format1.color="blue";
SpreadsheetFormatRow(spreadsheetData,format1,1);

How I can apply two color to format single cell ?

Huda

Answer 1

It is not supported with the built in functions. However, you can tap into the underlying POI library and use a RichTextString to accomplish this.

Assuming you have already created the worksheet and an empty cell:

spreadsheetData = SpreadSheetNew("Sheet1", true);
SpreadsheetAddRow(spreadsheetData,"",1,1); 

Grab reference to the underlying Workbook and create fonts with the appropriate colors:

wb = spreadsheetData.getWorkbook();
Colors = createObject("java", "org.apache.poi.ss.usermodel.IndexedColors");
greenFont = wb.createFont();
greenFont.setColor(Colors.GREEN.index);
blueFont = wb.createFont();
blueFont.setColor(Colors.BLUE.index);

Then create a RichTextString object and append each part of the text with the desired color:

// Using GREEN and BLUE for demo purposes
richString = createObject("java", "org.apache.poi.xssf.usermodel.XSSFRichTextString").init();
richString.append("Text1: ", greenFont);
richString.append("Text2", blueFont);

Finally, apply the RichTextString to the empty cell, ie A1 you created earlier. Note, unlike in CF, the indexes are 0-based.

cell = wb.getSheet("Sheet1").getRow( 0 ).getCell( 0 );
cell.setCellValue(richString);

Iterate