SpreadsheetNew(): Programmatically insert a line break into a cell

LEGEND ,
Aug 23, 2016 Aug 23, 2016

Copy link to clipboard

Copied

Hello, all,

I'm using SpreadsheetNew() to create an .xlsx file that will contain groups and all the users associated with said groups.  I'm trying to figure out a way to insert a line break within a cell for groups that have more than one person.  So, ideally, the format would be like:

_______________________________________________

Group One       |             Adam Smith

                |             Jaime Doe

                |             Cindy Morgan

________________|______________________________

Group Two       |             James Coburn

                |             Bruce Lee

________________|______________________________

Group Three     |             Samantha Brooks

                |             Steve Perry

.. etc.

I've set the query (LEFT OUTER JOIN) so that it will aggregate the names and insert a chr(10) between each name (LISTAGG()).  Excel is not seeing this.  All names are on the same line.  How can I programmatically insert a line break that the Excel cell will recognize?

V/r,

^_^

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
community guidelines
Adobe Community Professional ,
Aug 23, 2016 Aug 23, 2016

Copy link to clipboard

Copied

What about the other one "#chr(13)#"?

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
community guidelines
LEGEND ,
Aug 24, 2016 Aug 24, 2016

Copy link to clipboard

Copied

Hi, BKBK​ (are you related to BKBKBK​?) 

I have tried:

LISTAGG(lname || ', ' || fname, chr(10)) GROUP WITHIN (ORDER BY lname) "FullName"

and

LISTAGG(lname || ', ' || fname, chr(13) & chr(10)) GROUP WITHIN (ORDER BY lname) "FullName"

When run in a query analyzer, it does what I intend.  When actually populating the Excel sheet, it doesn't.  Just leaves all the names on one line.

V/r,

^_^

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
community guidelines
Advocate ,
Aug 24, 2016 Aug 24, 2016

Copy link to clipboard

Copied

Do you see any funny characters between the names in the sheet, even when they are on the same line?

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
community guidelines
LEGEND ,
Aug 24, 2016 Aug 24, 2016

Copy link to clipboard

Copied

Negative.  It's like:

Adam SmithJaime DoeCindy Morgan

James CoburnBruce Lee

Samantha BrooksSteve Perry

Because I'm just doing a next line, no spaces or punctuation.

V/r,

^_^

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
community guidelines
Advocate ,
Aug 24, 2016 Aug 24, 2016

Copy link to clipboard

Copied

If you turn on word wrap in excel does it change anything?

Also see excel - Carriage Return Oracle 10g SQL - Stack Overflow

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
community guidelines
LEGEND ,
Aug 24, 2016 Aug 24, 2016

Copy link to clipboard

Copied

The cells are formatted with textwrap set to true.

variables.format = StructNew();

variables.format.font = "Arial";

variables.format.textwrap = "true";

...

SpreadsheetFormatColumns(variables.ssObj,variables.format,"1-#val(variables.rqLen#");

Format is applied both before and after the data is inserted into the cells.

One thing I'm trying (and having yet another issue) is running the select using a pipe to separate names, then using CF to replace the pipe with chr(13) & chr(10).  This works, for the most part, but I have several cells that have nothing but hashtags ("##########################################") in them, indicating that the data is too long for the cell (even though textwrap is on).  If I take out the replacement, I can see all names (stretched out half a mile long, of course, but no hashtags.)

Sigh.

V/r,

^_^

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
community guidelines
Adobe Community Professional ,
Aug 28, 2016 Aug 28, 2016

Copy link to clipboard

Copied

WolfShade wrote:

The cells are formatted with textwrap set to true.

  1. variables.format=StructNew();
  2. variables.format.font="Arial";
  3. variables.format.textwrap="true";
  4. ...
  5. SpreadsheetFormatColumns(variables.ssObj,variables.format,"1-#val(variables.rqLen#");

Textwrap seems to be necessary. Then the problem is possibly line 5. What happens when you add the extra bracket, as in:

SpreadsheetFormatColumns(variables.ssObj,variables.format,"1-#val(variables.rqLen)#");

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
community guidelines
LEGEND ,
Aug 29, 2016 Aug 29, 2016

Copy link to clipboard

Copied

Sorry.. the additional right parenthesis is there, I just missed typing it (my dev system is isolated from the internet.)

V/r,

^_^

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
community guidelines
Adobe Community Professional ,
Aug 24, 2016 Aug 24, 2016

Copy link to clipboard

Copied

How about:

LISTAGG(lname, ',', fname, chr(10))

LISTAGG(lname, ',', fname, chr(13)||chr(10))

Or, mixed with Coldfusion parlance,

LISTAGG(lname, ',', fname, '#chr(10)#')

LISTAGG(lname, ',', fname, '#chr(13)##chr(10)#')

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
community guidelines
LEGEND ,
Aug 24, 2016 Aug 24, 2016

Copy link to clipboard

Copied

I've tried:

'#chr(13) chr(10)#'
'#chr(13) & chr(10)#'

'#chr(13##chr(10)#'

chr(13) & chr(10)

chr(13) || chr(10)

char(13) || char(10) /* Oracle's character */

None of them are working.  Excel does not see Oracle's "line break" command, or the CF line break entered as part of the string.

ReplaceNoCase() kind of works, aside from the occasional cell being filled with hashtags.

V/r,

^_^

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
community guidelines
LEGEND ,
Aug 25, 2016 Aug 25, 2016

Copy link to clipboard

Copied

I'm giving up the ghost on this one.  If anyone in the future can figure it out, I'd still appreciate it, but for now I've changed the query so that it doesn't use LISTAGG(), and I'm manually setting the CFSCRIPT output so that it will output each group once, then output all names on individual lines for that group.  It's a pain and not that efficient, but I can no longer waste time on this as I'm behind schedule on this.  I do appreciate everyone's wisdom and time.

V/r,

^_^

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
community guidelines
Adobe Community Professional ,
Aug 25, 2016 Aug 25, 2016

Copy link to clipboard

Copied

Might this be related to this bug: Spreadsheet Formula Error

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
community guidelines
LEGEND ,
Aug 26, 2016 Aug 26, 2016

Copy link to clipboard

Copied

You think that the Apache POI is what is preventing Excel from recognizing the line break?

V/r,

^_^

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
community guidelines
Adobe Community Professional ,
Aug 26, 2016 Aug 26, 2016

Copy link to clipboard

Copied

Indeed

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
community guidelines
Adobe Community Professional ,
Aug 24, 2016 Aug 24, 2016

Copy link to clipboard

Copied

WolfShade wrote:

Hi, BKBK (are you related to BKBKBK?)

Perhaps my shadow.

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
community guidelines
New Here ,
Aug 26, 2016 Aug 26, 2016

Copy link to clipboard

Copied

try using chr(10).

Here is some code that I used that worked for me.

<cfscript>

cr = chr(10);

row = 1;

col = 4;

fmtheader = {font="Arial",fontsize="12",textwrap="true",bold="true",alignment="left",verticalalignment="vertical_center",topborder="this",bottomborder="thin",leftborder="thin",rightborder="thin",fgcolor="light_cornflower_blue",color="black"};

SpreadsheetSetCellValue(sh, "Runtime"&cr&"Hours"&cr&"YDay", row, col);

SpreadsheetFormatCell(sh, fmtheader, row, col);

</cfscript>

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
community guidelines
LEGEND ,
Aug 26, 2016 Aug 26, 2016

Copy link to clipboard

Copied

Hi, kurt.tosczak​,

Thanks for the thought, but if you refer to reply #2, you can see that I'm attempting to insert a line break inside the Oracle LISTAGG() function, and I've tried both chr(10) and chr(13), neither of which is working.  I've also tried using a pipe, then using CF to replace the pipe with a line break; this almost worked, but a few of the cells were filled with hashtags, indicating that the data was too long for the cell, even though textwrap was turned on.

As BKBK​ pointed out, this may be a bug in the Apache POI.  I'm not sure.  If that were the case, then manually entering a line break in the SpreadsheetSetCellValue() wouldn't work, either.  So, this may go unsolved.

V/r,

^_^

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
community guidelines
New Here ,
Aug 26, 2016 Aug 26, 2016

Copy link to clipboard

Copied

I just wrote the following code and tested it and it works for me. 

I am currently using MS SQL instead of Oracle but the general idea is the same.

<cfquery name="GetPeople" >

    SELECT TOP 10 Firstname+char(10)+Lastname as nm

    FROM person

    ORDER BY firstname,lastname

</cfquery>

<cfscript>

    fmtHeader    = {

          font="Arial",

          fontsize="12",

          textwrap="true",

          bold="true",

          alignment="center",

          verticalalignment="vertical_bottom",

          topborder="thin",

          bottomborder="thin",

          leftborder="thin",

          rightborder="thin",

          fgcolor="light_cornflower_blue",

          color="black"

     };

    sh = SpreadsheetNew("test", False);

    for (row=1;row<=GetUsers.recordcount;row++) {

        SpreadsheetSetCellValue(sh, GetPeople.nm[row], row, 1);

        SpreadsheetFormatCell(sh, fmtHeader, row, 1);

    }

</cfscript>

<cfset filename = "test.xls">

<cfspreadsheet action="write" filename="#expandpath('.')#/../TempFiles/#filename#" name="sh" overwrite="true">

<cfheader name="Content-Disposition" value="attachment; filename=""#filename#""">

<cfcontent type="application/vnd.ms-excel" deletefile="no" file="#expandpath('.')#\..\TempFiles\#filename#">

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
community guidelines
LEGEND ,
Aug 29, 2016 Aug 29, 2016

Copy link to clipboard

Copied

It's entirely possible that it works for you because you are not using Oracle (as you stated, you are using MS SQL.)  I have no choice.  I used to be a MS SQL guy, but then I was hired, here, and now I'm an Oracle guy.  Small things still (after four years) throw me off, and I can never seem to get used to the whole LIMIT 1 thing vs TOP 1.

But I've tried chr(13) & chr(10), char(13) || char(10), \n, <br />, and a few other things, and it will display properly in SQL Developer (Oracle equivalent to MS SQL's Management Studio).  But in the actual application, no deal.  So, it's either a bug, or there is some setting in CFAdmin (that I have zero control over) that is preventing it from working.

V/r,

^_^

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
community guidelines
New Here ,
Aug 29, 2016 Aug 29, 2016

Copy link to clipboard

Copied

Are you sure that the chr(10) is being transferred correctly via your DB driver?

I would do something like:

<cfloop index="i" from="1" to="#len(qry.fullname)#">

     <cfoutput>#asc(mid(qry.fullname,i,1))#, </cfoutput>

</cfloop

From that you can determine if the chr(10) is accurately being transmitted from the DB query through the driver into Coldfusion.

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
community guidelines
LEGEND ,
Aug 29, 2016 Aug 29, 2016

Copy link to clipboard

Copied

kurt.tosczak​, I can't say if it is or isn't.  I assume that it is.  I've never had an issue with chr(10) in anything outside of LISTAGG(). But, then, as I pointed out to BKBK​, it might be failing because I'm now using Office 2013, and perhaps Excel 2013 is having the issue, not CF.

But as much as I want to solve this, the fact is that I have already spent way too much time on it, and was forced to use a standard LEFT OUTER JOIN query, and am outputting using the group attribute, one name per line, but outputting the other information only on the first line per group.  It's not as pretty, but it works, and (so far) the client has not rebelled against it. 

I do appreciate everyone's time and effort on this; but as I stated, earlier, I'm giving up the ghost on this so I can get some work done and (ideally) get this completed by rollout.

V/r,

^_^

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
community guidelines
New Here ,
Aug 29, 2016 Aug 29, 2016

Copy link to clipboard

Copied

I think that you need to test it in chunks to determine the issue.

1. Coldfusion to Excel. 

    Write a test script that inserts chr(10) and confirm that excel is accepting it (no database query required)

     ie.

<cfscript>

    fmt    = {font="Arial",fontsize="12",textwrap="true",alignment="center"};

    sh = SpreadsheetNew("test", False);

    SpreadsheetSetCellValue(sh, "Test"&chr(10)&"Carriage"&chr(10)&"Return", 1, 1);

    SpreadsheetFormatCell(sh, fmt, 1, 1);

</cfscript>

<cfset filename = "test.xls">

<cfspreadsheet action="write" filename="#expandpath('.')#/../TempFiles/#filename#" name="sh" overwrite="true">

<cfheader name="Content-Disposition" value="attachment; filename=""#filename#""">

<cfcontent type="application/vnd.ms-excel" deletefile="no" file="#expandpath('.')#\..\TempFiles\#filename#">

2.  Confirm chr(10) from Oracle to Coldfusion is working

<cfquery name="qry" datasource="????">

     SELECT "Test"+chr(10)+"Carriage"+chr(10)+"Return' AS test

</cfquery>

<cfloop index="i" from="1" to="#len(qry.test)#">

     <cfoutput>#asc(mid(qry.test,i,1))#, </cfoutput>

</cfloop>

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
community guidelines
Advocate ,
Aug 30, 2016 Aug 30, 2016

Copy link to clipboard

Copied

I think CF11 is using Apache POI 3.9 which is dated before Excel 2013 as well. Which means it may not support a lot of 2013

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
community guidelines
LEGEND ,
Aug 30, 2016 Aug 30, 2016

Copy link to clipboard

Copied

haxtbh, that makes sense.  And we're using CF10, currently (it takes FOREVER to get software approved, around here.)  So it pretty much seems (to me) to be Excel that is the issue, not CF.  But, nothing I can do about it.  So, I'll have to keep my archaic method in place.

V/r,

^_^

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
community guidelines