Insert Line Break into Excel Cell with Coldfusion 9

Question

I'm using Coldfusion 9 to generate an Excel spreadsheet. I'm using the new functions, such as SpreadsheetNew(), SpreadsheetAddRow(), and SpreadsheetSetCellValue(). I'd like to have data in some cells include line breaks within the cell. I tried this with no luck:

<cfset my_spreadsheet = SpreadsheetNew("My Spreadsheet","false")>
<cfset cell_value = "First Line">
<cfset carr = chr(13) & chr(10)>
<cfset cell_value = cell_value & carr & "Second Line">
<cfset SpreadsheetSetCellValue(my_spreadsheet, cell_value, 1, 1)>

In the Excel output, row 1 column 1 shows this:

First LineSecond Line

But I want it to show:

First Line
Second Line

Any thoughts? Thanks!!

Answer 1

You need to adjust the cell format to enable line wrapping

<cfset sheet = SpreadsheetNew("My Spreadsheet")>
<cfset SpreadsheetSetCellValue(sheet, "foo"& chr(10) &"bar", 1, 1)>
<cfset SpreadsheetFormatCell(sheet, {textwrap=true}, 1, 1)>