Skip to main content
WolfShade
WolfShade
Legend
August 23, 2016
Question

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

  • August 23, 2016
  • 2 replies
  • 3386 views

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,

^_^

    This topic has been closed for replies.

    2 replies

    K
    kurt.tosczak
    Participating Frequently
    August 26, 2016

    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
    WolfShadeAuthor
    Legend
    August 26, 2016

    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,

    ^_^

    K
    kurt.tosczak
    Participating Frequently
    August 26, 2016

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

    BKBK
    Community Expert
    BKBKCommunity Expert
    Community Expert
    August 24, 2016

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

    WolfShade
    WolfShadeAuthor
    Legend
    August 24, 2016

    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,

    ^_^

    H
    haxtbh
    Inspiring
    August 24, 2016

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

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices