Is this a bug with ColdFusion Spreadsheet functionality?

A user reported an odd bug to me, and as I’ve not done a lot with cfspreadsheet, I thought I’d share what we both saw and see if others agree that it is a bug. Take a simple Excel file with a few sheets in it.

<cfset source = "c:\users\raymond\desktop\book1.xlsx">

Then read in sheet 2 only…

<cfscript> sObj = SpreadSheetRead(source, 2); writedump(sobj);

Which gives you:

Right away - I notice something odd. The values are right - but note the summary info section. Apparently when you get sheet N, ColdFusion also returns high level info on the file as a whole. Ok… I can see that being useful. Let’s carry on though.

SpreadSheetWrite(sObj, "c:\users\raymond\desktop\updatedFile.xlsx", "yes"); </cfscript>

We wrap the code with a write operation. From the docs for SpreadSheetWrite, we see:

Writes single sheet to a new XLS file from a ColdFusion spreadsheet object.

Which implies, very strongly, it is going to write a single sheet. However, in our testing, it actually wrote all the sheets to the next file. Actually, I just noticed further down in the docs for SpreadSheetWrite:

Write multiple sheets to a single file

And I see there is a function to set an active sheet in a spreadsheet object. This to me implies that even when we read in sheet N of file X, we have an object that contains all the sheets. We just work with one at a time. Therefore, the only way to ‘rip out’ a sheet would be to create a whole new object and copy the data cell by cell. Is there a better way?

Raymond Camden's Picture

About Raymond Camden

Raymond is a developer advocate. He focuses on JavaScript, serverless and enterprise cat demos. If you like this article, please consider visiting my Amazon Wishlist or donating via PayPal to show your support.

Lafayette, LA