Skip to main content
W
whiteboxwebby-WRldNN
Participant
February 25, 2019
Question

CF11 Spreadsheet issues

  • February 25, 2019
  • 2 replies
  • 288 views

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.

This topic has been closed for replies.

2 replies

BKBK
Community Expert
BKBKCommunity Expert
Community Expert
February 25, 2019

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.

W
whiteboxwebby-WRldNNAuthor
Participant
February 26, 2019

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.

W
whiteboxwebby-WRldNNAuthor
Participant
February 26, 2019

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

}

WolfShade
WolfShade
Legend
February 25, 2019

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,

^ _ ^

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices