Link to home
Start Free TrialLog in
Avatar of diecasthft01
diecasthft01

asked on

Using setRepeatingRows with Coldfusion 2021 and Excel Spreadsheets

Good day!! I have a Coldfusion application where I use some code to set some formatting for Excel spreadsheets, and everything worked fine in CF2016, but I just upgraded to CF2021, and this one line is throwing a fit. All it is intended to do is have row 1 of each spreadsheet sheet print on each page when printed. The line is below:

poiSheet.setRepeatingRows(CellRangeAddress.valueOf("$1:$1")); and the error is:

The setRepeatingRows method was not found.Either there are no methods with the specified method name and argument types or the setRepeatingRows method is overloaded with argument types that ColdFusion cannot decipher reliably.

Does anyone know if this was deprecated, or if there is a replace, better way to do what Im trying to do? The formatting code is below:


poiSheet = mySheet.getWorkBook().getSheet("FY22 BI #dateLocked#");

poiSheet.setMargin(poiSheet.LeftMargin, 0.25);

poiSheet.setMargin(poiSheet.RightMargin, 0.2);   

   

ps = poiSheet.getPrintSetup();

ps.setScale(79);

//ps.setLandscape(true);   

   

cellRangeAddress = CreateObject("java", "org.apache.poi.ss.util.CellRangeAddress");

poiSheet.setRepeatingRows(CellRangeAddress.valueOf("$1:$1"));


Thanks!!


Avatar of gr8gonzo
gr8gonzo
Flag of United States of America image

I'm just browsing through questions that don't have any comments and found this one. I haven't written ColdFusion since early 2000s, but a few things pop out at me.


First off, the code has to make it's way through getting a sheet successfully and calling other Sheet class methods like getPrintSetup, and it does so without error, which means it's successfully using the POI library.


Second, the POI library's Sheet class does include the setRepeatingRows method still, and I don't see any deprecation notices in the documentation for that function. So in all likelihood, it is still there and exists.


All that said, I figure it's one of three things.


1. Either the code is case-sensitive and CellRangeAddress needs to have a lowercase starting c because that's how you created the object. (I don't think it's this because it seems valueOf is a static function that returns an instance of CellRangeAddress)


2. Or the $1:$1 reference is invalid, resulting in a bad value that therefore does not match the expected parameter type (it expects a CellRangeAddress). Just to test the theory, change it to something hard-coded like valueOf("A:A").


3. Or there is an issue with the valueOf function as the parameter. Maybe in a newer version of CF, it's passing the function itself as the parameter rather than evaluating it and using the result as the parameter. To test it, move the valueOf call into it's own line above and assign the result back to a variable, then use the variable as the parameter to setRepeatingRows.

Avatar of diecasthft01
diecasthft01

ASKER

I was hoping one of the ideas above would work, but still not getting there...everything I see keeps pointing back to needing to do a javacast, but Im not familiar enough to know what Im actually trying to javacast.....

JavaCast is for converting values to Java-specific data types. I'm not sure why it would be needed here, but I suppose you can always try casting the cell range string.


Before doing that, can you confirm that you tried this specific approach?


cellRangeObject = createObject("java", "org.apache.poi.ss.util.CellRangeAddress");
cellRange = cellRangeObject.valueOf("$1:$1");
poiSheet.setRepeatingRows(cellRange);

Open in new window


If you did, then you can try a javaCast:


cellRangeObject = createObject("java", "org.apache.poi.ss.util.CellRangeAddress");
cellRange = cellRangeObject.valueOf(javaCast("string","$1:$1"));
poiSheet.setRepeatingRows(cellRange);

Open in new window


I don't think you'd need to javaCast the cellRange object, but if the above cast doesn't work...


cellRangeObject = createObject("java", "org.apache.poi.ss.util.CellRangeAddress");
cellRange = cellRangeObject.valueOf(javaCast("string","$1:$1"));
poiSheet.setRepeatingRows(javaCast("org.apache.poi.ss.util.CellRangeAddress",cellRange));

Open in new window

So I tried all three options and all three failed.....the first two gave me the same error as before:

"The setRepeatingRows method was not found.Either there are no methods with the specified method name and argument types or the setRepeatingRows method is overloaded with argument types that ColdFusion cannot decipher reliably. ColdFusion found 0 methods that match the provided arguments. If this is a Java object and you verified that the method exists, use the javacast function to reduce ambiguity."

But the third option seems closer, with a different error this time:
"JavaCast type org.apache.poi.ss.util.CellRangeAddress must be one of the following types: byte, char, short, int, long, float, double, boolean, string, bigdecimal,  their corresponding array representation (eg : int[]), or null."

On all three options, if I remove the line "poiSheet.setRepeatingRows...." I get no errors.

Okay, let's try something a little different.


cellRangeObject = createObject("java", "org.apache.poi.ss.util.CellRangeAddress"); 
cellRange = cellRangeObject.init(javaCast("int",1),javaCast("int",1),javaCast("int",1),javaCast("int",1)); 
poiSheet.setRepeatingRows(cellRange);

Open in new window


Same result Im afraid.....

"The setRepeatingRows method was not found.Either there are no methods with the specified method name and argument types or the setRepeatingRows method is overloaded with argument types that ColdFusion cannot decipher reliably. ColdFusion found 0 methods that match the provided arguments. If this is a Java object and you verified that the method exists, use the javacast function to reduce ambiguity."

Okay, can you writeDump (I'm assuming this is cfscript) the poiSheet and cellRange objects before calling setRepeatingRows and share the output?


It almost seems like either the poiSheet instance is either just "gone" or it's not the expected type or something. I presumed based on the other successful calls before it that it's a valid "Sheet" object and that you haven't stripped out any code in the middle in the snippet that you provided, so that ps = poiSheet.getPrintSetup(); works as expected and there's nothing that would invalidate / clear the poiSheet variable afterwards...

I havent used writeDump....what var should I call out for the dump?
And yes, this is CFScript.....first time using it to get the spreadsheet formatted in a way that I wanted it.

writeDump(var = poiSheet, label = "poiSheet");

writeDump(var = cellRange, label = "cellRange");


Here's the dump in pdf.
writeDump.pdf

So that looks good, which makes me think the only thing left is that the POI library version is too old and you're getting a mismatch of some kind in the object type (e.g. somehow the sheet is on version X but the cell range address object is on version Y) . Have you tried updating it and making sure you don't have multiple copies loaded?

I will check with the server guys....we just upgraded last week from CF2016 to CF2021, and that's when all this started. It worked perfectly in 2016. But you mentioning this has me wondering now.
Looks like Im running 4.1.2, but I see the latest is 5.2.3

Personally, I would imagine that any incompatibility issues would show up earlier in the code, but you can always maybe try specifying the bundle and version when you do your createObject calls.


I think the code would look like this, to specify the apache.poi bundle with version 4.1.2:


cellRangeObject =  createObject("java", "org.apache.poi.ss.util.CellRangeAddress", "apache.poi","4.1.2"); 


You'd have to ensure that the same bundle/version was specified in your createObject of any other POI objects (workbook,etc).

I tried to call out the apache POI version but it said "Jar not found in repository : apache.poi:4.1.2"....maybe a syntax thing? Ive been looking around but havent found anything yet.

Do you have access to the server filesystem so you can manually check the repository? Really the main thing here to check is for any conflicting versions.

Also, is there any chance you can create a separate page that is nothing but the simplest steps you're taking that will reproduce the error? I can set up cf2021 on my side and check to see if I can reproduce the problem with the same code and same POI library version.

I have limited access to the system, but I can see in the \ColdFusion2021\bundles\repo there are three different jar files in there: poi-3.1.7.jar, poi-4.1.0.jar and poi-4.1.2.jar. I sent a note over to the server folks to remove the previous two. But now that I look at this, there are also poi-ooxml-schemas-xxx jar files for both the 3.1.7 and the 4.1.0 jars, but not the 4.1.2 one......should I have that file?

I will put a bare bones code together and get it out here.
Here's a stripped down code, it works without the poiSheet.setRepeatingRows(cellRangeAddress); on line 95
spreadsheet1.cfm
ASKER CERTIFIED SOLUTION
Avatar of gr8gonzo
gr8gonzo
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Oh you're more than "pretty close".....you are SPOT ON!!!! Thank you so much!!!!! That's exactly what I was missing, and I'm right back to where I was before my CF2021 upgrade.

So I think what was happening here was that you were using "SpreadsheetNew" to create the workbook instance, which used the latest POI library it had, 4.1.2. However, when you were using createObject(), it was falling back to version 3.17 for some reason, so it was trying to insert a version 3.17 CellRangeAddress into a version 4.1.2 Sheet. I'm not sure why CF bundles in 3.17 like that, but ah well. A problem for another day.

Great!