error CFR CF 2016 to CF 2018

[Juan Adrián5FC5]

Hi all.
I recently had a problem with reports created with CFR when I try to open them in Excel with Coldfusion 2018, it has an error

"Invalid cell range, having lastRow  firstRow lastCol  firstCol, had rows 12  38 or cells 13 38 null "

or

"Cannot add merged region B2 E2 to sheet because it overlaps with an existing merged region A1E2 null
The error occurred on line 1. "

apparently there are problems with lines that have overlapping cells this did not happen in CF 2011- 2016 I would like to know if someone has had the same error or if this problem has been corrected in some way.

[WolfShade]

Hello, Juan,

I don't know how much help I can be with this, as I'm not using CF2018, but I'm going to give it the old college try.

According to a blog entry by Charlie Arehart, CF2016 uses version 3.12 of the POI library (this is what allows CF to work with Excel and Word documents).  According to Apache POI, the current version is 4.1.2.

I've done some Googling, and cannot find what version of POI is in CF2018, but I'm going to guess that Adobe actually went with a newer POI library, and this _might_ explain why you are seeing this error in CF2018, but not in CF2016.  I'm totally guessing, here.

But if this is the case, then the overlapping cells might be a game changer.

HTH,

^ _ ^

[Dave Watts]

My guess, based on a quick look at the contents of the cfusion\lib directory, is that CF 2018 uses 3.17. That's what's in all the file names there.

Dave Watts, Eidolon LLC

[Juan Adrián5FC5]

Thanks WolfShade, Dave,

I tried to use the jars from the poi-CF2016 in CF2018 but it didn't work.
I had the following errors attached to the images.
In effect the 2018 poi is 3.17
and in 2016 3.12.
Unfortunately I keep getting this error. The problem is only when generating the Excel.

Thanks for your help.
I'll keep looking

[Charlie Arehart]

You're getting lots of great help here, Juan. But before we presume you may have found a bug, I have a couple questions you should consider:

• what update of CF 2018 are you running? 
• In the log for that update (in cf's hf-updates folder), does it report thousands of "successes" and 0 fatalerrors? If you had errors, try running the update again. 
• Have you tried writing out just the spreadsheet alone (not within a report)? It may be that THAT is where the problem happens, perhaps related to some unexpected code results prior to that (running differently in 2018 than 2016)
• Assuming the above options don't identify a solution, it will be critical for you to create a small standalone demo of the issue (that anyone could run), for Adobe or anyone to replicate it
• Finally, as for hoping Adobe might fix any "bug", do beware that cfreport is "deprecated and unsupported" since cf2016 (https://helpx.adobe.com/coldfusion/deprecated-features.html), so if it IS specifically a bug in cf report, it will not likely be fixed. 

But again perhaps something else above or that's offered by others here may get you to a resolution. 

[Juan Adrián5FC5]

Charly_Arehart thanks for all your help ,

1. Update my CF2018 but I still have the same problem:

 I will try to generate an example to replicate.

Regards.

[Charlie Arehart]

Thanks for the update, though while you addressed my first and fourth bullets, you didn't address my second and third, which may help in resolving things.

(BTW, you write "charly_arehart". I'm just curious: did that really appear to you anywhere in your view of this thread? That would surprise me, but if I could influence changing it to "charlie_arehart", I would. No worries if it was just a typing change on your part.)

[Juan Adrián5FC5]

Charlie_Arehart I really feel sorry it was not my intention.
And apparently the whole post I did was not uploaded.
I do not know what happened.
I will try to upload what I did in a PDF.

Sorry.

[Juan Adrián5FC5]

Charlie_Arehart,

Attached the tests that I carry out, I will try to generate the demo of the problem.
It is a pdf in google drive that I generate.

https://drive.google.com/file/d/14J7R_wYm1ncYx-MKEuowiV0Zok74I8HJ/view?usp=sharing 

I appreciate your help and advice.

[Charlie Arehart]

So about that PDF, those following along should note that it's a series of screenshots Juan took, some showing the CF admin, some showing logs, some showing code, some showing screenshots of the browser, etc. 

So, Juan, as for you looking at the update log and showing no fatalerrors, why did you show looking at the log for update 8, when you had applied update 10? As for searching for the phrase, please instead page down once or twice in the log, and see the table counting successes and errors--and ensure there are 0 errors of either type.

Next, I appreciaet that you are demonstrating that the problem does clearly happen with the CFR, even a clean one. What I was proposing was that you instead ONLY create the spreadsheet (that you are putting IN the CFR). And view that, on both the version where things "work" and where they "fail". Does that spreadsheet appear identical in those cases? If it's not, then the problem is perhaps not about the CFR but about the spreadsheet (and how your code or CF generated it).

[Juan Adrián5FC5]

Hello Charlie_Arehart

I had not seen the other log I opened the hotfix_filelist.log from the last update the other image was to show that my CF2018 update this is the file Adobe_ColdFusion_2018_Update_10_Install_09_15_2020_10_32_35.log

and it has no errors ...

Sorry, I don't quite understand how you want the spreadsheet to generate.

I comment that generating a spreadsheet from a cfm that generates an html, I have no problem, it generates it well, the problem is the cfr that you want to generate in excel with a complex template.

The excel cfr should be generated without complications, as I mention it is more by the cfr templates, I don't know what the cfr does in automatic to generate the Excel and that is where it seems that it needs to be modified based on the new poi.jar library.

So my possible solution is to generate the CFR template as generic html and use it to generate the Excel download. But this would imply a whole migration of the reports that I have.

Sorry if I was unable to generate an example of this.

[Juan Adrián5FC5]

It is not the best example but it could be generated this way, making a call from the cfr that I send.

<cfquery name="rsReport" datasource="#session.dsn#">
	select 1 as SNcodigo,'name' as SNnombre
</cfquery>

<!--- PruebaCFR2 work --->
<!--- PruebaCFR no --->
<cfreport format="excel" template="PruebaCFR2.cfr" query="rsReport">
</cfreport>

I attach the cfr

pruebacfr

https://drive.google.com/file/d/1spkNWooVWRPATgOpqxUFcSpB3ZIDlCD6/view?usp=sharing 

pruebacfr2

https://drive.google.com/file/d/1_seZVcANc38BRJ2BuuewWLxUzzgvhqyv/view?usp=sharing 

First of all, Thanks

[BKBK]

Hi Juan Adrián5FC5

Sounds like a bug. Could you please share the code that is causing an error.

[scottm56010691]

We have exactly the same problem happening in our organisation.

Reports that render in PDF format will not generate in Excel format due to overlapping merged cells.

I'm in the process of removing every cell to see if there's something that is triggering it, but it seems like we have to strip it back to nothing and then create the report almost as a grid like one would expect to do in Excel.  That's a guess - it still isn't working.

[scottm56010691]

As with the original issue reporter, we've found that after removing several fields from the report the error changes from "Cannot add merged region..."  to "Invalid cell range, having lastRow < firstRow ||...."

I have Googled this problem and this Adobe forum is one of the few places where this error is reported, so there's very little to go off.

[Juan Adrián5FC5]

Hi scottm56010691

The only thing I could do to generate the excel was:

create the cfr as html create the html and then read it to generate the excel

<cfreport format="html" template= "DocumentosSinAplicarResCP.cfr" query="rsReporte" 
filename = "mireporte22.html" encryption = "128-bit" overwrite = "yes" >
</cfreport>
<cf_cfrexcel filename="mireporte22" filexls="DocSinAplicar">
<cfabort>


----cfrexcel.cfm
<cfparam name="Attributes.filename" type="string">				<!--- Mensaje de Error cuando es proceso de Interfaz --->
<cfparam name="Attributes.filexls" 	 type="string" default="">	<!--- showerror cuando NO es proceso de Interfaz --->
<cfset lvarFile = Attributes.filename>
<cfset lvarXls = Attributes.filexls>

	<cfscript>
		setEncoding("form","UTF-8");
		setEncoding("url","UTF-8");
		myFile = fileRead(expandPath("./#lvarFile#.html"));
	</cfscript>

	<cfprocessingDirective pageencoding="utf-8">
	<cfset setEncoding("form","utf-8")>
		<cfsavecontent variable="strTHeadExcel">
			<cfoutput>#myFile#</cfoutput>
		</cfsavecontent>
	<cfcontent type="application/vnd.ms-excel; charset=windows-1252" reset="yes">
	<cfheader name="Content-Disposition" value="filename=#lvarXls#.xls">
	<cfoutput> #strTHeadExcel# </cfoutput>

Maybe you can improve it or find another possible solution.

[scottm56010691]

Great, thanks for the update.  I'll give that a try and report back if we find another solution.

[scottm56010691]

Hi Juan,

Just letting you know that ColdFusion dev team provided my company with a patch that fixed this issue after I raised a support ticket.

Before the patch we could not have any Excel report with group bands, or it would produce the error you reported in this thread.  Now, it works fine in CF2018 as it used to in CF11.

Maybe you could try that approach as well if you are still facing this issue.

[Jared0D4C]

I ran across this after a recent upgrade (CF11 to CF2018). I found the issue wasn't necessarily the fault of the upgrade, but a previously un-caught issue in our CFR file that generated the Excel file. We had a gap between columns in CFR that caused this error. Each column's (B) Layout → Left value should be the previous column's (A) Left value plus that column's (A) Width value.

colA Left + colA Width = colB Left

This needs to be addressed in the header row and details row.

I hope this helps.

-Jared

[scottm56010691]

Hi Jared,

Not OP, but I was facing a similar issue - we did try what you suggested to no avail...any Excel reports were failing if they had group bands in the report (even when everything was lined up in a perfect grid).

I mentioned in a reply elsewhere in this thread that Adobe provided us with a custom patch to fix this issue and it seems to be working in CF2018.

[defaultnhe0nha7gieu]

Just fyi, 2018 doesnt like a couple of other things either with excel reports.  After much trial and error i can usually rebuild the reports by checking the folowing:

1.  Like previously mentioned; no group bands

2  no overlapping cells(sometimes its not apparent even from looking at the properties that this is the issue as they all seem snug but copy all in the band into a new report and you will see the issue most of the time, maybe some form of caching?)

3. no "stretch with overflow" on the bands or the fields in excel

4.  The height of the report should be greater than the amount of rows that will be produced(i believe it is the cause of this problem:Invalid cell range, having lastRow < firstRow )  I am going to reach out for that patch mentioned now:)