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…
sObj = SpreadSheetRead(source, 2);
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");
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?