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

[WolfShade]

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,

^_^

[BKBK]

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

[WolfShade]

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,

^_^

[haxtbh]

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

[WolfShade]

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,

^_^

[haxtbh]

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

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

[WolfShade]

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,

^_^

[BKBK]

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#");

variables.format = StructNew(); variables.format.font = "Arial"; variables.format.textwrap = "true"; ... 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)#");

[WolfShade]

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

V/r,

^_^

[BKBK]

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)#')

[WolfShade]

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,

^_^

[WolfShade]

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,

^_^

[BKBK]

Might this be related to this bug: Spreadsheet Formula Error

[WolfShade]

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

V/r,

^_^

[BKBK]

Indeed

[BKBK]

WolfShade wrote:

Hi, BKBK (are you related to BKBKBK?)

Perhaps my shadow.

[kurt.tosczak]

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>

[WolfShade]

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,

^_^

[kurt.tosczak]

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#">

[WolfShade]

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,

^_^

[kurt.tosczak]

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.

[WolfShade]

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,

^_^

[kurt.tosczak]

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>

[haxtbh]

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

[WolfShade]

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,

^_^