How to get cfspreadsheet to render html

Question

I'm trying to create an excel file with cfspreadsheet. In one of the columns I have html code, but for some reason, in the excel file the html doesn't get rendered it's just plain text. eg. <b>blabla</b> instead of being bolded.

Do you know any solutions to this?

score 5 · Answer 1 · edited May 23 '17 at 11:57

The reason is that cfspreadsheet is based on POI which does not support html content.

As user1450455 mentions, you can format whole cells using any of the built in formatting functions such as SpreadsheetFormatCell.

  sheet = spreadSheetNew();
  spreadSheetFormatCell( sheet, {bold=true} , 1, 1 );
  spreadSheetSetCellValue( sheet, "blablah", 1, 1 );

If you are looking to create cells with multiple formats (ie bold some characters but not others) that is only possible using the underlying POI library by creating a RichTextString. So it requires much lower level code.

<cfscript>
    sheet     = spreadSheetNew();
    workbook  = sheet.getWorkBook();
    helper    = workbook.getCreationHelper();
    richText  = helper.createRichTextString("ColdFusion");

    // make first few characters bold ie "Cold"
    firstFont = workbook.createFont();
    firstFont.setBoldweight( firstFont.BOLDWEIGHT_BOLD );
    richText.applyFont( 0, 4, firstFont );

    // make next characters red ie "Fusion"
    secondFont = workbook.createFont();
    secondFont.setColor( secondFont.COLOR_RED );
    richText.applyFont( 4, 10, secondFont );

    // create cell via CF and apply formats
    // note, in POI indexes are base 0
    spreadSheetSetCellValue( sheet, "", 2, 1);
    cellA2 = workbook.getSheetAt(0).getRow(1).getCell(0);
    cellA2.setCellValue( richText );
</cfscript>

Thanks @Peter. I was just going to add the link, but you beat me to it! — Leigh, Dec 17 '12 at 18:01
Heh sorry, just realised this was a recent answer - I normally wait at least 5 minutes before editing, to avoid that. — Peter Boughton, Dec 17 '12 at 18:03
No worries. I tweak pretty frequently anyway, so chances are our edits still would have clashed. ;) — Leigh, Dec 17 '12 at 18:05

score 0 · Answer 2 · edited Dec 17 '12 at 10:57

0

You can use the spreadsheet formatting functions like SpreadsheetFormatRow or SpreadsheetFormatrows or SpreadsheetFormatColumns.

edited Dec 17 '12 at 10:57

wandarkaf

1,839
20
30

answered Dec 17 '12 at 10:33

shemy

573
1
5
15

How to get cfspreadsheet to render html

2 Answers2