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,

^_^

[haxtbh]

I asked Adobe earlier if they planned on updating the Apache POI, they said in the next update.

Adobe then released CF10 Update 21 and CF11 Update 10 today as well, both are now running a newer version of Apache POI (3.12).

Turns out its part of the security fix that they pushed out in this update.

I would try updating if you can and see if you get the same results.

[WolfShade]

Well, shoot.. my dev environment is isolated from the internet, so I'll have to manually d/l that update.  Sigh.

Do all updates contain previous updates?  I just need the one, right?

V/r,

^_^

[haxtbh]

Yeah this update will contain all the previous updates. They are usually cumulative

ColdFusion 11 Update 10 and ColdFusion 10 Update 21 released » Adobe ColdFusion Blog

[WolfShade]

  Much appreciated, sir.

I'm going to be leaving work in about an hour for an extended weekend.  See you guys after Labor Day!

V/r,

^_^

[BKBK]

Eh, just to get this straight, WolfShade, I made no reference to MS SQL. I created the code sample based on cfdocexamples. This  is the Apache Derby datasource that ships with Coldfusion, so you have it, too.

I also used the Coldfusion function chr() to pass the Linefeed character. That is of course valid for any database.

Translating this to your example, will then give something like

  variables.format=StructNew();

  variables.format.font="Arial";

  variables.format.textwrap="true"; // crucial

  ...

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

followed by

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

FROM tblName

GROUP BY someIDCol

[WolfShade]

BKBK​, sorry, I was replying to kurt.tosczak.  He is using MS SQL.  I should have done like I did in this post, and started it with kurt.tosczak​.

BKBK wrote:

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

I tried this and it isn't working.  At least not for me.  I was recently upgraded to Office 2013 (I'm a guinea-pig for my department coz I'm the FACCSM), and wonder if that might be part of the issue; everyone else, here, is on 2010.  What version of Office are you using?

V/r,

^_^

[BKBK]

Following my last post, here's an example you could just cut, paste and run:

<!--- The || stands for string concatenation in the Apache Derby database --->

<cfquery name="testQ" datasource="cfdocexamples" >

       SELECT COURSE_ID,

               CORNUMBER ,

               (CORLEVEL||'#chr(10)#'||CORNAME) as CORNAME,

               (CORLEVEL||'#chr(10)#'||DEPT_ID) as DEPT_ID,

               LASTUPDATE

       FROM COURSELIST

</cfquery>

<cfscript>

    //Absolute path to file in current directory

       theFile = expandpath("testFile.xls");

    //Create a new Excel spreadsheet object and add the query data.

    theSheet = SpreadsheetNew("testString");

    SpreadsheetAddRows(theSheet,testQ);

    // Define format for the column.

    sheetFormat=StructNew() ;

    sheetFormat.textwrap="true";

    //Apply format to column 1

   SpreadsheetFormatColumns(theSheet,sheetFormat,"3-4");

</cfscript>

<!--- Write the spreadsheet to a file, replacing any existing file --->

<cfspreadsheet action="write" filename="#theFile#" name="theSheet" overwrite="true">

<cfdump var="#testq#">