Create multiple sheets using Coldfusion SpreadsheetWrite and cfscript

Question

I would like to create one excel file with two sheets using CF9, SpreadsheetWrite and cfscript. Something like:

var data= spreadsheetNew( 'data' ); var key= spreadsheetNew( 'key'); spreadsheetAddRow( data, dataInfo ); spreadsheetAddRow( key, keyInfo ); spreadsheetWrite( data, filePath ); spreadsheetWrite( key, filePath ); 

I can not find the documentation explaining how to combine several sheets in one file. Here is what I find.

http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSe9cbe5cf462523a0-7b585809122c5a12c54-7fff.html

It states that you can "write multiple sheets in one file." but it is not clear whether this means more than one sheet at a time. You can accomplish this with tags, but I need to use cfscript.

 <cfspreadsheet action="update" filename = "filePath" sheetname = "key" > 

How to write the above tag based call using cfscript?

Answer 1

Yes, this is not very clear from the documentation. SpreadsheetNew creates a workbook with one worksheet. To add additional sheets, use the SpreadSheetCreateSheet function. Before you can manipulate a new sheet, you must activate it using SpreadSheetSetActiveSheet . Here is a brief example of creating a book with two sheets:

 <cfscript> // Create new workbook with one worksheet. // By default this worksheet is active Workbook = SpreadsheetNew("Sheet1"); // Add data to the currently active sheet SpreadSheetAddRow(Workbook, "Apples"); SpreadSheetAddRow(Workbook, "Oranges"); //Add second worksheet, and make it active SpreadSheetCreateSheet(Workbook, "Sheet2"); // Add data to the second worksheet SpreadSheetSetActiveSheet(Workbook, "Sheet2"); SpreadSheetAddRow(Workbook, "Music"); SpreadSheetAddRow(Workbook, "Books"); //Finally, save it to a file SpreadSheetWrite(Workbook, "c:/path/to/yourFile.xls", true); </cfscript> 

Side note, I would not recommend using <cfspreadsheet action="update"> anyway. The last thing I remember was a bit of a mistake.