Skip to main content
August 1, 2011
Answered

cfspreadsheet mulitple sheets

  • August 1, 2011
  • 1 reply
  • 6893 views

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)>
       </cfloop>

       <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,
                       {
                               bold=true,
                               fgcolor="lemon_chiffon",
                               fontsize=14
                       },
                       1)>

       <!--- 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.

This topic has been closed for replies.
Correct answer existdissolve

Look at the "Usage" note on the following: http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSe9cbe5cf462523a0-676cdab312216addf18-8000.html

Basically, you can add data just like you are...you just need to make sure that the new sheet is the "active" sheet before trying to add the data.

The example on the link above shows this in action.

Hope that helps.

1 reply

existdissolve
existdissolveCorrect answer
Inspiring
August 1, 2011

Look at the "Usage" note on the following: http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSe9cbe5cf462523a0-676cdab312216addf18-8000.html

Basically, you can add data just like you are...you just need to make sure that the new sheet is the "active" sheet before trying to add the data.

The example on the link above shows this in action.

Hope that helps.

August 1, 2011

@

existdissolve . Thanks a lot. It helped me.