CFSPREADSHEET CF2016 overwrites single sheet

EDIT: Now that's a fun change.  I can confirm CF11 keeps all of the sheets (though honestly that never made sense to me).

Try removing the sheet number from the "read". Then setting the active sheet before adding the values:

<cfspreadsheet action="read" src="#path#" name="sObj" />
<cfset SpreadsheetSetActiveSheetNumber(sObj, 2) />
... etc....

So I changed the code to:

<cfspreadsheet action="read"
        src="#application.loadpath#\#Session.NewWorking#"
        name="sObj" />

<cfscript>
SpreadsheetSetActiveSheetNumber(sObj, 2);
spreadsheetSetCellValue(sObj,#val(RA)#,2,2);
spreadsheetSetCellValue(sObj,#val(AP)#,3,2);
spreadsheetSetCellValue(sObj,#val(GetCHAP.showvalue)#,4,2);
spreadsheetSetCellValue(sObj,#val(GetFP.showvalue)#,5,2);
spreadsheetSetCellValue(sObj,#val(getrad.showvalue)#,6,2);
spreadsheetSetCellValue(sObj,#val(GetClosed.showvalue)#,7,2);
</cfscript>

<cfspreadsheet action="write" overwrite="true"
        filename="#application.loadpath#\#Session.NewWorking#"
        name="sObj" />

Select allOpen in new window

I get the error

A part with the name '/xl/drawings/drawing2.xml' already exists : Packages shall not contain equivalent part names and package implementers shall neither create nor recognize packages with equivalent part names. [M1.12]

from the line

SpreadsheetSetActiveSheetNumber(sObj, 2);

Select allOpen in new window

Weird .. wonder if it's a red herring, because setting the active sheet number shouldn't having anything to do with drawing2.xml.

1. Any difference if you use the function based version, SpreadSheetRead()?
2. Same question about using SpreadsheetSetActiveSheet (spreadsheetobj, sheetname) instead of SpreadsheetSetActiveSheetNumber(sheet, num)?

https://helpx.adobe.com/coldfusion/cfml-reference/coldfusion-functions/functions-s/spreadsheetread.html

Also, if you cfdump the object directly after the "read", how many sheets does it contain?  In CF 11 it shows:

ROWCOUNT       11
SHEETNAME       Sheet1  <=== active sheet
SHEETNUMBER       1
SUMMARYINFO       
SpreadSheetInfo - struct
SHEETNAMES       Sheet1,Sheet2
SHEETS       2

I changed it to

<cfscript>
sObj = SpreadSheetRead("#application.loadpath#\#Session.NewWorking#","Cap Unit Status");

spreadsheetSetCellValue(sObj,#val(RA)#,2,2);
spreadsheetSetCellValue(sObj,#val(AP)#,3,2);
spreadsheetSetCellValue(sObj,#val(GetCHAP.showvalue)#,4,2);
spreadsheetSetCellValue(sObj,#val(GetFP.showvalue)#,5,2);
spreadsheetSetCellValue(sObj,#val(getrad.showvalue)#,6,2);
spreadsheetSetCellValue(sObj,#val(GetClosed.showvalue)#,7,2);
</cfscript>




<cfspreadsheet action="write" overwrite="true"
        filename="#application.loadpath#\#Session.NewWorking#"
        name="sObj" />

Select allOpen in new window

The error goes away but the stupid thing is still deleting all of the other sheets.

It reads them all before the write.  As soon as the first write happens, poof - just what was the 2nd sheet exists.

Get rid of the sheetname in SpreadSheetRead().  This worked for me with 2016:

<cfscript>
inputPath = "c:/path/myFile.xlsx";
outputPath = "c:/path/newFile.xlsx";

sObj = SpreadSheetRead(inputPath);
SpreadsheetSetActiveSheetNumber(sObj, 2);
spreadsheetSetCellValue(sObj,111,2,2);
spreadsheetSetCellValue(sObj,222,3,2);
spreadsheetSetCellValue(sObj,3333,4,2);
</cfscript>

<cfspreadsheet action="write" overwrite="true"
        filename="#outputPath#"
        name="sObj" />

Select allOpen in new window

>> This worked for me with 2016:
My exact version is  2016,0,0,297996, and the sample file was created with Excel 2013.

So I have narrowed the problem.  The error message came back so I tried calling the sheet name.   Still a problem.   I tried a new spreadsheet with 4 tabs and still the same issue.   Then I realized - I am using an xlsm file and not an xlsx file.   The xlsm is the issue.   It works when it is an xlsx file.  

Do you think the support just stopped for xlsm??   I thought stuff was supposed to improve!

I converted my sample xlsx file to .xlsm and it worked fine for me.  Maybe start with a blank slate.  Create a sample .xlsm file with two sheets. Then run the sample code above exactly "as is".  Any difference?

So this works in a test environment but as soon as I go back to the actual XLSM file that I need to use, i go back to that

A part with the name '/xl/drawings/drawing2.xml' already exists : Packages shall not contain equivalent part names and package implementers shall neither create nor recognize packages with equivalent part names. [M1.12]

The first worksheet has graphs on it.   But the 2nd one - the one that I am writing to is just two columns of data with some formulas...   Is that what is hanging this up now?

The first worksheet has graphs on it.   But the 2nd one - the one that I am writing to is just two columns of data with some formulas...   Is that what is hanging this up now?

Yeah, I'd bet CF is doing something extra with the workbook in the background relating to graphing and that's what's causing the issue.  There are other ways to manipulate the active sheet, but ... if CF discards the extra sheets every time it does a write, not sure there's a "short" way around that. You could create a function to handle it with java, but it won't be as simple as SpreadSheetWrite().

1. For grins, try using this alternative to SpreadsheetSetActiveSheetNumber():

     // Important:  Sheet numbers are 0-based
     sObj.getWorkBook().setActiveSheet(1);

Any change?

2. Also, try using SpreadSheetWrite instead of cfspreadsheet action="write".  Again, any difference?

If not, the only option I can think of is to write your own SpreadSheetWRite() function.

sObj.getWorkBook().setActiveSheet(1); - that fixed the issue.  Thank you so much (as always!!!)

So it turns out that doesn't work - it writes everything to the first sheet that way.    I tried going back to the original SpreadsheetSetActiveSheetNumber(sObj2, 2); but the graphs have to be removed which doesn't help me.  


<cfscript>

inputPath = "#application.loadpath#\#Session.NewWorking#";


sObj2 = SpreadSheetRead(inputPath);
sObj.getWorkBook().setActiveSheet(2);
spreadsheetSetCellValue(sObj2,#val(RA)#,2,2);
spreadsheetSetCellValue(sObj2,#val(AP)#,3,2);
spreadsheetSetCellValue(sObj2,#val(GetCHAP.showvalue)#,4,2);
spreadsheetSetCellValue(sObj2,#val(GetFP.showvalue)#,5,2);
spreadsheetSetCellValue(sObj2,#val(getrad.showvalue)#,6,2);
spreadsheetSetCellValue(sObj2,#val(GetClosed.showvalue)#,7,2);
SpreadSheetWrite(sObj2, "#application.loadpath#\#Session.NewWorking#", "yes");
</cfscript>

Ugh.. yeah it doesn't work as expected :/. CF must be doing something extra behind the scenes to track which sheet it manipulates when using the various functions.  If so, you'll need to use CF functions to change the active sheet. If you could get SpreadsheetSetActiveSheetNumber or SpreadsheetSetActiveSheet  to work, you might be able to work around the issue by creating your own "write" function.  However, if neither of those works without error .. you may be out of luck.

Btw, I tried several tests, with different files and formats (xlsx, xlsm), but couldn't reproduce your original issue.  Do you have a "clean" sample file you could upload?

I just sent you a message - it is definitely the graphs on the main page.  I don't know if there is a naming thing somehow - if I delete most of them it works but I really need them since it is the basis of the dang report.

Thank you SO much!

Glad it helped.  Since the marked answer wasn't in fact the solution, I've submitted a request to reopen. When that's done, can you mark the new code as the solution? Just to save someone else from wasting time on the wrong code. Thanks.

As always, expert help!