Spreadsheet formatting textwrap not working

[scottberry]

See the following code:

spreadsheetObj = SpreadsheetNew('Metrics',false);

SpreadSheetSetColumnWidth(spreadsheetObj, 3, 10);

SpreadSheetAddRow(spreadsheetObj,'Merchant ID,Merchant Name,Return Customer#chr(13)##chr(10)#Conversion Rate');

SpreadSheetSetRowHeight(spreadsheetObj, 1, 50);

SpreadsheetFormatCell(spreadsheetObj, {textwrap=true}, 1, 3);

SpreadsheetFormatRow(spreadsheetObj, {textwrap=1}, 1);

Even using both Cell and Row formatting I can not get the text to wrap. This is what the resulting file looks like:

If I click on C1 and click the "Wrap Text" option in Excel, it formats correctly.. so the line breaks are there. This is CF10 by the way and I believe we run CF11 in production which I think I read solves this problem. Just wondering if anyone else has seen this or documented a fix. I've also tried XLSX and XLS.

[scottberry]

I've tested the same code on CF11 now and the issue persists.

[WolfShade]

The one thing I learned the hard way (at least I don't remember it being documented) is that all formatting needs to be applied as the LAST step.  Even things like row height and column width.  Insert all your data, first, then apply formatting.

CAVEAT:  If you are inserting a number as a string and need to keep any preceding zeroes, then you have to insert the data, apply the formatting (be sure to include "dataformat='general'" in the format), then reinsert the data into the same cells as previous.  Another thing I learned the hard way.

HTH,

^_^

[scottberry]

Thanks, even making sure all formatting was at the end (just moving the col width down to the end per the code above) made no changes in the output. Sadly this doesn't seem any different in CF11 either nor with XLS or XLSX. So disappointing.

[WolfShade]

The only other thing that I can think of, off the top of my head, is to not use SpreadsheetAddRow.

Use SpreadsheetSetCellValue(obj,'value',row,column), instead.  I think there's something not quite right about SpreadsheetAddRow.

Just a thought.

If you need to create headers for columns, this is what I do:

<cfscript>

<!--- going to pretend like you've already established the CFExcel object --->

cols = "ID,Name,Address,City,State,Zip,Phone";

colLen = ListLen(cols,',');

for(x = 1; x lte colLen; x++){

    SpreadsheetSetCellValue(excelObj,'#ListGetAt(cols,x)#',1,x);

    }

</cfscript>

Another good thing about using SpreadsheetSetCellValue() - the columns automatically adjust to the width of the widest value in said column, so unless you want to shrink the size of a column, there's no need to use SpreadsheetSetColumnWidth() as part of formatting.

BTW, Adobe.. I hate the editor that has been chosen to use in these forums.  EVERY SINGLE TIME that I tried to type the variable name "colLen", your stupid editor keeps changing it to "colleen".  Please get a decent editor.

[Piyush Nayak]

scottberry,

     can you please try using "textwrap=true" instead of "textwrap=1".