Skip to main content
J
jarviswabi
Inspiring
June 17, 2019
Question

SpreadsheetFormatRow and SpreadsheetFormatColumn interacting strangely in CF2018

  • June 17, 2019
  • 2 replies
  • 954 views

Having just recently migrated my intranet to CF2018 Enterprise (from CF10), I'm finding some weird behavior in my Excel spreadsheets.  I use SpreadsheetNew("true") to create spreadsheets and then directions like this to format the rows and columns:

<cfset SpreadsheetFormatRow(s,{bold='true'},1)>

<cfset SpreadsheetFormatColumn(s,{dataformat='##.00'},13)>

This ends up producing my first row all in bold (correct), my 13th column formatted as _.00 (correct), but then my 13th column also ALL in bold.  Basically, any column I apply formatting to ends up extending the row formatting all the way down.

And if I do it in reverse:

<cfset SpreadsheetFormatColumn(s,{dataformat='##.00'},13)>

<cfset SpreadsheetFormatRow(s,{bold='true'},1)>

That part works, but in specialized cases where I've tried to bold particular cells (to highlight data), that instruction is ignored:

<cfset SpreadsheetFormatCell(s,{bold='true'},s.RowCount,13)>

No cells in column 13 are bold except for row 1, even though I set it explicitly.

Has anybody run into similar irregularities with the Spreadsheet functionality?  So far, it's not catastrophic, and may just require a lot of tedious refactoring, but somewhere it's going cause a real loss of functionality, and it doesn't make much sense why it broke.  Would love to see a hotfix for it in the near future.

    This topic has been closed for replies.

    2 replies

    scottm56010691
    scottm56010691
    Participating Frequently
    December 3, 2020

    Hi, I'm glad you posted this issue because it's something we are encountering in our organisation as well.

    Formatting on the ROW seems to be applied to some columns too.

    I don't have any more details to contribute, but just wanted to mention that this isn't an isolated case.

    Charlie Arehart
    Community Expert
    Charlie ArehartCommunity Expert
    Community Expert
    June 17, 2019

    I can't say I've seen that problem, or discussion of it, but it's quite a specific issue.

    I will say that you would vastly increase your odds of getting help here and especially "a hotfix for it in the near future" by offering a reproducible case, that any of us can run, which of course then needs to stand alone (without any dependencies on your end). It may not take more than a few lines of code to show what you are experiencing.

    Then Adobe (or others reading along here) can a) confirm it happens to them and b) propose a solution for it (or perhaps workaround), or c) confirm a bug and then work on a fix.

    A great way to create and test such a stand-alone preproducible case is to use Adobe's cffiddle.org site, where you can run about any CFML, against either CF2018 or 2016. Such a standalone example working there would confirm it requires nothing unique to your environment.

    That said, the fiddle site is always running with the latest versions of CF 2018 and 2016. Along those lines, you should also let us know here what update of CF2018 you are running (in case it's a problem fixed in some update--the latest being 4, from last week).

    Finally, it would also help for you to confirm that there are no errors in whatever last update you DID do to this CF server having the problem. Sometimes, mistakes in updates can lead to unexpected errors. For more on finding/confirming if there are errors in a CF update, see this post of mine:

    Having problems after applying a CF update? What to check, and how to recover! - ColdFusion

    I realize you just wanted someone to verify your problem and get a fix for it. I realize I have added extra steps, but I hope they would help you (help us) get you to a solution.

    /Charlie (troubleshooter, carehart. org)
    J
    jarviswabiAuthor
    Inspiring
    June 17, 2019

    Thanks for the reply, Charlie - no worries on adding extra steps, I'm glad to follow your suggestions.  Here's a reproducible example:

    Here's an example (linked to cffiddle.org):

    <cfset s=SpreadsheetNew("true")>

    <cfset SpreadsheetAddRow(s,"ITEMNO,DESCRIPTION,COST,PRICE")>

    <cfset SpreadsheetFormatRow(s,{bold='true'},s.RowCount)>

    <cfset SpreadsheetAddRow(s,"TEST1,TEST ITEM 1,100,200")>

    <cfset SpreadsheetAddRow(s,"TEST2,TEST ITEM 2,200,300")>

    <cfset SpreadsheetAddRow(s,"TEST3,TEST ITEM 3,300,400")>

    <cfset SpreadsheetFormatColumn(s,{dataformat='##.00'},3)>

    <cfset SpreadsheetFormatColumn(s,{dataformat='##.00'},4)>

    <cfheader name="content-disposition" value="attachment; filename=newjwl_#DateFormat(Now(),'YYYYMMDD')#.xlsx">

    <cfcontent type="application/msexcel" variable="#spreadsheetReadBinary(s)#" reset="true">

    Seems to reproduce my issue in both CF18U4 and CF16U11.  I'm on Update 3 right now, and will install 4 and test it on my dev server, but doesn't seem like it'll matter for now.

    Charlie Arehart
    Community Expert
    Charlie ArehartCommunity Expert
    Community Expert
    June 17, 2019

    Thanks for that, and hope it helps. Now one more help would be to clarify what we SHOULD be seeing. :-) Some reading along may not work with the spreadsheet functions often, and also what you wrote doesn't really match exactly what you wrote originally, so rather than have them study the code to figure it out, do just let them know, lest they perhaps miss your point. :-)

    I see cols C and D being bold and formatted as numbers. Should they not be?

    And you're using spreadsheetformatcolumn (not to be confused with spreadsheetformatcolumns, plural). But your text in your opening message says you were wanting to format a specific cell. Why then are you not using spreadsheetformatcell? If I add this line:

    <cfset SpreadsheetFormatCell (s, {underline='true'}, 2, 2)>

    It does indeed underline only row 2, cell 2. Could it be that this is what you are wanting instead? Or what, again, would show something about that not working for you?

    /Charlie (troubleshooter, carehart. org)
    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices