SpreadsheetFormatRow and SpreadsheetFormatColumn interacting strangely in CF2018

[jarviswabi]

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.

[Charlie Arehart]

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.

[jarviswabi]

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]

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?

[jarviswabi]

Sorry for not being more clear.  The fiddle I included is just for my first scenario.

In the example I provided, what it SHOULD be doing is making the first row bold (the column headings), and then formatting columns 3 and 4 as numbers, but it shouldn't be making them bold all the way down (only row 1 should be bold based on these instructions).  That's how it works in CF10, which is what I think you would expect - bold row 1, format the columns.

My reference in the original post about wanting to format a specific cell was an additional (more advanced) scenario, which I'm not sure if even worrying about if the first one doesn't work properly.  I was pointing out that a workaround for the main issue was to reverse the instructions by putting the "bold row 1" after the number formatting, but if I did that, I wasn't able to then use SpreadsheetFormatCell to make certain cells bold.

What I think I'm seeing is that if you apply a particular type of formatting (i.e. "bold=true") to a ROW first, any OTHER formatting you apply to a column carries that "bold" all the way down.  Whereas if I format the column first (as a number), and THEN apply bold to the top row, only that row is properly bolded.

It's kind of impossible to enumerate all the ways this seems messed up, so for now, I'll leave it with the original example, which should look like the attached screenshot (row 1 bold, columns 3 and 4 number format, nothing else), as opposed to what happens in the fiddle.

[Charlie Arehart]

Well, I think you’re making a mistake in shifting the focus from what happens in the fiddle. With your added explanation here, I see better now what the problem is. It just wasn’t as clear to me from the original description and code fragments, nor from your more elaborated example.

Bottom line, the use of spreadsheetformatcolumn is causing formatting to be inherited from row 1, when it should (reasonably) not. And an important additional point is that you say it DID NOT in CF10.

I can confirm also (with that additional observation) that it was in fact CF11 when it changed, not 2018 or 2016. (Let that be a lesson when you jump, as you did, from 10 to 2018. The problem you say is new for you “in CF2018”. It can help to be clear about what you skipped.)

And better still, you can see this for yourself (and test future such migration problems) with yet another site, trycf.com. That offers CF 10 and 11, in addition to 2016 and 2018 (and Lucee 5 and 4).

And as for your problem, you can still demonstrate it (in any of the sites) with a simpler 2-row example, that shows that the problem does cause formatting on a prior row (via spreadsheetformatrow) to somehow inherited on another row when spreadsheetformatcolumn is used. Here are the key lines:

Your point is that the last one should not be bold, but only dataformatted.

You can also use it to prove the matter of re-arranging the order of the formatrow (to be after the formatcolumn). But you say that DID help whereas I am finding (in CF2018) that it does NOT help.

Indeed, I setup an example that shows all 3 states (no use of formatcolumn, using it, and then using formatrow AFTER it), each appearing in a separate sheet, for easy viewing. Here is a URL for it at cffiddle.org.

https://cffiddle.org/app/file?filepath=770400f9-a5ad-4ca4-b29e-689d6262daba/bac3c11c-9a0d-4dbc-9dd1-fe9855b3860f/274ca075-7b8b-4259-8239-d1b2d59d4224.cfm

And with that, it may be easier for others to follow along—and to get clarify on the matter of rearranging the order of things, which I did NOT find helped. And I think you do perhaps now have sufficient clarity to file a bug report. You’ll want to indicate that it is ON CF2018 (so they focus on it), but add in the text that we confirmed it happens starting with CF11.

I will add, finally, that I tied to search around for references to others reporting the problem, and I don’t find them, so this is indeed a seeming narrow case. But I agree with you that either it should work as it did for you in 10 and before, or it should be documented that it changed. (I didn’t find that, either.)

Hope that’s helpful.

/charlie

[jarviswabi]

Thanks again for your feedback and your suggestions on how to use cffiddle to better demonstrate the issue.  I'll enter this as a bug tomorrow, though I will stick with my 4 row example because I think it even better demonstrates how the bold from row 1 is inherited all the way down.  Your recap is pretty much the root issue to me - the data formatting from SpreadsheetFormatRow is being inherited on subsequent rows if other formats are then applied to a specific column.  I might play around a little more too to try to provide other examples, though from experience, I know it's best to keep problem focused as narrowly as possible and not try to cover too much ground.  Thus my simplification of the issue in the last post.

Weird that your fiddle doesn't seem to correct the issue when the instructions are reversed.  I'm going to play with that a bit more tomorrow as well using the version that seemed to fix it on my server and see if I can find the pattern.

Crazy to me that nobody's run into this (or at least not reported it) if it cropped up in CF11.  Given how much we use the spreadsheet functions here, I assumed they were pretty widely used.

I think it was pretty clear from my original post that I had migrated from CF10 to CF2018 and hadn't stopped anywhere in between, which I think many of us are compelled to do because it's simply not practical to upgrade to every new version on all our servers (due to time, cost, etc).  Sorry if you thought that wasn't clear, but I tried to be as concise AND clear as I could be.  Good to know about trycf.com - I'll keep that in mind if I have any other issues to report.

Thanks again for the help.

[scottm56010691]

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.