Highlighted

Spreadsheet formatting textwrap not working

New Here ,
Oct 21, 2014

Copy link to clipboard

Copied

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:

Capture.PNG

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.

Views

1.2K

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

Spreadsheet formatting textwrap not working

New Here ,
Oct 21, 2014

Copy link to clipboard

Copied

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:

Capture.PNG

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.

Views

1.2K

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Oct 21, 2014 0
New Here ,
Oct 21, 2014

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Oct 21, 2014 0
LEGEND ,
Oct 22, 2014

Copy link to clipboard

Copied

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,

^_^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Oct 22, 2014 0
New Here ,
Oct 22, 2014

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Oct 22, 2014 0
LEGEND ,
Oct 22, 2014

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Oct 22, 2014 0
PiyushN LATEST
Explorer ,
Oct 23, 2014

Copy link to clipboard

Copied

scottberry,

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Oct 23, 2014 0