CF11 Spreadsheet issues

[whiteboxwebby-WRldNN]

Can someone help me resolve why when I generate a 2 tab spreadsheet, the contents for the second sheet show but the first tab is the one highlighted at the bottom of the page? I have tried trying to make the first sheet active again before making the file but it gives me a write error. See attached image.

Second question how do I use cfspreadsheet update functionality to add a sheet to the front of an existing spreadsheet, I cant seem to find any good documentation on it.

[WolfShade]

It's been a while since I've worked with SpreadsheetNew(), and I don't think you can prepend a sheet. I think you can append a sheet and change the order around, though.  Check the Helpx documentation.

(Nope.. my assumption was incorrect.  It looks like you have to add the sheets in the order in which you want them.)

V/r,

^ _ ^

[BKBK]

Could you provide more details? How do you create the original sheet? How do you add the second sheet? Where/How does each sheet get its data? Could you show us the code you have used so far? Perhaps all it needs is just a little tweak.

[whiteboxwebby-WRldNN]

I managed to fix the first problem I was making use of the the sheetname optional in the file generation which was causing the conflict problem, removing that and making sure the first sheet was the active sheet has fixed the first issue.

The second issue I dont have any code for yet as I am still trying to work out the best way to generate the file. Either:

1) Build the first sheet and then read in the existing file as the second sheet

or

2) read the existing file and add the other sheet into that file and save, only problem I see with that one is the ordering of the sheets post the addition.

[whiteboxwebby-WRldNN]

Second question was answered thus incase anyone comes looking for how to do it as its not very clear from the documentation available

Read the file you wish to add as the second sheet, the query gives the sheets contents for you to use

<cfspreadsheet action="read" src="filelocation\test.xls" query="contents" />

then in cfscript tags you can do the following

SpreadSheetCreateSheet(theSheet, "New Sheet");

SpreadSheetSetActiveSheet(theSheet, "New Sheet");

SpreadsheetAddRows(theSheet,contents);

if you want to format the contents you can go row by row doing the folllowing

for (c = 3; c <= theSheet.rowCount; c = c + 1){

     format as usual

}