creating multiple sheets using Coldfusion SpreadsheetWrite and cfscript

Question

I'd like to create a single 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 don't find documentation that explains how to combine multiple sheets in one file. Here's what I find.

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

It states that one can 'Write multiple sheets to a single file.' but whether that means more than one sheet at a time is not clear. You can accomplish this using tags but I need to use cfscript.

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

How does one write the above tag-based call using cfscript?

Community · Accepted Answer · 2017-05-23 11:48:44Z

6

Yes, it is not very clear from the documentation. SpreadsheetNew creates a Workbook with a single worksheet. To add additional sheets, use the SpreadSheetCreateSheet function. Before you can manipulate the new sheet, it must be made active with SpreadSheetSetActiveSheet. Here is a quick example of creating a workbook 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. Last I recall it was a bit buggy.

edited May 23, 2017 at 11:48

CommunityBot

11 silver badge

answered Apr 28, 2016 at 1:40

Leigh

28.9k10 gold badges58 silver badges109 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Richard Tingle Over a year ago

I've never understood why all these examples end with "and then save it to a file", why would you ever want to do that? If you want to send it out to the output stream instead (for the end user to save it as a file) you can do this:

<cfheader name="Content-disposition" value="attachment;filename=#fileName#"> 		<cfcontent type="application/xls" variable = "#SpreadsheetReadBinary(theSheet)#">

Matt Gutting Over a year ago

@RichardTingle If you're FTPing somewhere, or for archiving purposes, maybe?

Collectives™ on Stack Overflow

creating multiple sheets using Coldfusion SpreadsheetWrite and cfscript

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related