Is this a bug with ColdFusion Spreadsheet functionality?

This post is more than 2 years old.

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 senior developer evangelist for Adobe. He focuses on document services, JavaScript, and enterprise cat demos. If you like this article, please consider visiting my Amazon Wishlist or donating via PayPal to show your support. You can even buy me a coffee!

Lafayette, LA https://www.raymondcamden.com

Archived Comments

Comment 1 by Adam Cameron posted on 10/16/2011 at 1:31 AM

Hi Ray
Yeah, this is a bug in spreadsheetRead(), I reckon. It should do what it says in the docs.

To solve your problem you can use spreadsheetRemoveSheet() to ditch the sheets you don't want.

I think there's a shortfall in spreadsheetRemoveSheet(), though: spreadsheetRead() allows one to specify a sheet name or sheet number to identify the sheet to read (even though it reads all of 'em ;-). spreadsheetRemoveSheet() only permits sheet name. These two functions should conform in what arguments they accept, I reckon.

It you... err... raise an issue for this... I'll vote for it...

--
Adam

Comment 2 by Raymond Camden posted on 10/16/2011 at 3:12 PM

Would you believe I did not think there was a remotesheet function? Why? Because I forgot to double check my CF901 PDF. -sigh- Thanks Adam.

Comment 3 by Raymond Camden posted on 10/19/2011 at 6:34 PM
Comment 4 by Ken Caldwell posted on 10/24/2011 at 7:42 AM

Ray,
Thanks for this. I will add my name to the bug fix.
The need to remove sheets has made for a bit more work.
As I give my users the ability to indicate which sheets are to be included, I then need to loop all the sheets to determine which ones are not included so that I can then delete those.

again, Thank you.
Ken