2 Replies Latest reply on Aug 1, 2011 12:08 PM by funandlearning333

    cfspreadsheet mulitple sheets

    funandlearning333 Level 1

      Hi All -

      I am trying to generate a multiple tabs excel sheet. I have the following code. The excel code is copied from coldfusionjedi.com

      <cfset q = queryNew("Name,Beers,Vegetables,Fruits,Meats", "cf_sql_varchar,cf_sql_integer,cf_sql_integer,cf_sql_integer,cf_sql_integer")>
             <cfloop index="x" from="1" to="10">
                     <cfset queryAddRow(q)>
                     <cfset querySetCell(q, "Name", "Name1")>
                     <cfset querySetCell(q, "Beers", 2)>
                     <cfset querySetCell(q, "Vegetables", 15)>
                     <cfset querySetCell(q, "Fruits", 33)>
                     <cfset querySetCell(q, "Meats", 3)>

             <cfset filename = expandPath("./myexcel.xls")>
             <cfspreadsheet action="write" query="q" filename="#filename#" overwrite="true">
             <!--- Make a spreadsheet object --->
             <cfset s = spreadsheetNew()>

             <!--- Add header row --->
             <cfset spreadsheetAddRow(s, "Name,Beers,Vegetables,Fruits,Meats")>
             <!--- format header --->
             <cfset spreadsheetFormatRow(s,

             <!--- Add query --->
             <cfset spreadsheetAddRows(s, q)>
             <cfset SpreadsheetCreateSheet (s, "EvaluationSheet")>

             <cfheader name="content-disposition" value="attachment; filename=myexcel.xls">
             <cfcontent type="application/msexcel" variable="#spreadsheetReadBinary(s)#" reset="true">

      So the first sheet displays fine. To create a new sheet, I am using spreadsheetCreateSheet. How can I add data to the sheet? I mean I can use cfspreadsheet with action write/update, but I do not want to save the excel to a file, rather have the user save it.