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?
Archived Comments
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
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.
Logged: http://cfbugs.adobe.com/cfb...
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