A

Anonymous

Question

error CFR CF 2016 to CF 2018

Forum|Forum|5 years ago
September 9, 2020
4 replies
3355 views

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.

This topic has been closed for replies.

A

Anonymous

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

Participating Frequently

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.

D

defaultnhe0nha7gieu

Participant

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:)

scottm56010691

Participating Frequently

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

Participating Frequently

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.

A

Anonymous

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.

BKBK

Community Expert

Hi Juan Adrián5FC5

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

WolfShade

Legend

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,

^ _ ^

D

Dave Watts

Community Expert

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

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded