SpreadsheetAddRows fails with a datetime column under clean install of 6.2.1.122 but not upgrade from 6.2.0.321

[editcorp]

This may be a corner case, but when adding rows via spreadsheetaddrows from a query of queries containing a datetime column, I’m receiving the following error:

‘double lucee.runtime.functions.math.Min.call(lucee.runtime.PageContext, double, double)’ The error occurred in /org/cfpoi/spreadsheet/Spreadsheet.cfc: line 2952
Called from /org/cfpoi/spreadsheet/Spreadsheet.cfc: line 1054
Called from /SpreadsheetAddRows.cfm: line 26

We do this a lot (a quick search revealed over 250+ files) and it stopped working on one server but not another (both Windows).
The failing server in question was a clean install running Lucee version 6.2.1.122; loader version 6.2.1.122; Servlet container Apache Tomcat/11.0.6 with Java 21.0.7.
A working server running the same application code but upgraded from Lucee Version 6.2.0.321 to Lucee version 6.2.1.122; with loader version 6.2.0.321; Servlet container Apache Tomcat/10.1.36 with Java 21.0.6.

I’m wondering if the root of the problem is not the underlying Java version since both servers are running the same version of Lucee and the extensions.

I’ve simplified things down to a QofQ with a single row and a single datetime column, coming from the results of a SQL Server stored procedure.

Here is an example piece of code exhibiting the problem:
.
.
.
cfquery name=“Local_ExcelQuery” dbtype=“query”
select cast(DateNeeded as varchar) as DateNeeded
from Local_SQLServerStoredProcQuery
/cfquery
.
.
.
cfscript
sheet1 = SpreadsheetNew(“TestFile”);
SpreadsheetAddRows(sheet1,Local_ExcelQuery);
/cfscript

I receive the same error whether I cast the datetime column in the QofQ as varchar or not. An example value for the column would be: {ts ‘2025-02-06 00:00:00’}. If no datetime columns are present in the QofQ, the spreadsheetaddrows works just fine on both servers.

p.s. I just upgraded a third server via the Lucee Administrator from 6.2.0.321 to 6.2.1.122 and it does not exhibit the problem either.

[KabutoTX1]

The debug lines don’t exactly match up to Github I viewed. The only Min() I see is doing a len(). Maybe getting an error on one row of data? Can you output len(Local_ExcelQuery.DateNeeded) and see if it errors anywhere.

[editcorp]

Sure, I did a cfdump on the len(Local_ExcelQuery.DateNeeded) and got 26. Here is a partial screenshot including a cfdump of the QofQ:

To be clear, this is just an example. I’ve tried it across multiple sets of data with different columns. It seems like it can be any datetime datatype column.

[KabutoTX1]

OK. Found the correct code and I was looking at the right area.
Is the cfdump date you did above known to cause the error? Just checking if the query is returning something bad.

The addrows is doing a call to estimateColumnWidth(Local_ExcelQuery.DateNeeded);
Part of that is the len() you did and also a getDefaultCharWidth();
You should get a number return on getDefaultCharWidth(); This does some font calls for a default width for the cell.
You should get a number return on len(Local_ExcelQuery.DateNeeded);
It then does a math calc with these 2 numbers. I assume something is going wrong on this causing min() to fail.
You can also try calling and should get a number return on estimateColumnWidth(Local_ExcelQuery.DateNeeded);

[Zackster]

problem is this, the extension bundles very, very old lucee bytecode which isn’t supported anymore, plus the extension is bundling very old poi java libs which have a heap of CVEs

Regression in Lucee 6.1.1.118 for cfspreadsheet java.lang.NoSuchMethodError

@Leftbower how about just releasing a new version, at a minimum just removing the ancient class file which was complied with java 6.0 almost a decade ago? But honestly, nobody should be using such old, unmaintained software, it’s bundling ancient CVE ridden jars There are multiple XXE vulnerabilities all over the place, as Office files are XML based, your server can be hacked by just by opening a document https://mvnrepository.com/artifact/org.apache.poi/poi/3.15 https://mvnrepository.com/…

[martin]

I can recommend using spreadsheet-cfml for working with Excel. It is actively maintained and should be pretty straight forward to convert any existing code to work using it.

GitHub - cfsimplicity/spreadsheet-cfml: Standalone library for working with spreadsheets...

[editcorp]

The value shown in the cfdump was the value causing the error. It seems to be very consistent with any (valid) datetime value present in the QofQ.
The two functions you mentioned are throwing “No matching function” errors when I try calling them from the cfm code. I’m assuming they are internal functions and not available directly from the cfm.

[editcorp]

I’ll start investigating what will be involved with switching over to cfsimplicity within the app as a long term solution. A quick code search shows over 250 uses of spreadsheetaddrows, so nothing will be quick.

Understanding that you don’t recommend this :), is there a straight-forward way of downgrading a clean install of 6.2.1.122 to 6.2.0.321? Or, maybe just replacing the underlying bits that remain after an upgrade from within the Lucee Administrator? As I mentioned, the upgraded servers do not have this issue, only the clean install of 6.2.1.122. If I can replace those “new” bits with the “old” bits, that will buy me some time. As a last resort, I could wipe the new server and reinstall with 6.2.0.321, but I’d rather avoid that if possible.

[editcorp]

TY for the recommendation. I’ll investigate it for our long term use.

[Julian_Halliwell]

@editcorp

A quick code search shows over 250 uses of spreadsheetaddrows, so nothing will be quick.

You might be surprised.

spreadsheet = New Spreadsheet();//path to Spreadsheet.cfc
workbook = spreadsheet.new();
spreadsheet.addRows( workbook, myQuery );

addRows

The library should co-exist with the extension too, so you can migrate line by line if you want.