The way we have always handled excel spreadsheet generation (CF8), has been to take a formatted spreadsheet, save it as and XML sheet, open that XML in Notepad, copy the XML code and paste it inside a <cfsavecontent variable="strXmlData"> tag...then edit the code to make it dynamic (e.g. Rows, Columns, sheets, etc.). Then we run the following code to have it open in Excel...
<cfheader name="content-disposition" value="attachment; filename=#fileName#"/> <cfcontent type="application/vnd.ms-excel" variable="#ToBinary( ToBase64( strXmlData.Trim().ReplaceAll( '>\s+', '>' ).ReplaceAll( '\s+<', '<' ) ) )#"/> When we upgrade to CF9 next quarter, the only use I really have for the new office functionality, is to modify existing sheets.... Anyways, that's just my $0.02 -----Original Message----- From: fun and learning [mailto:funandlrnn...@gmail.com] Sent: Monday, August 01, 2011 10:30 AM To: cf-talk Subject: cf9 multiple sheet excel 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, but I do not want to save the excel, rather have the user save it. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:346428 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
