Skip to main content
A
Anonymous
August 11, 2011
Question

How get cfspreadsheet to return the query dump in Excel?

  • August 11, 2011
  • 2 replies
  • 3792 views

I read my data as shown in examples, but my data just displays the query dump. If I add cfheader and cfcontent, the query dump just displays in an Excel.

I'm using CF 9.01 and Excel 2007

shell. Here is my  code:

            <cfscript>
                            //Use an absolute path for the files. --->
                            theDir=GetDirectoryFromPath(GetCurrentTemplatePath());
                            theFile=theDir & "TrackEverythingXLS.xls";
                            //Create two empty ColdFusion spreadsheet objects. --->
                            theSheet = SpreadsheetNew("TBI_2009"); 
                            theSheet2 = SpreadsheetNew("TBI_2008");
                            //Populate each object with a query. --->
                             SpreadsheetAddRows(theSheet,QO_getAllData); 
                             SpreadsheetAddRows(theSheet2,QO_getAllData_TBI_2008);
                        </cfscript>                   

    
                        <!--- Write the sheet --->
                    <cfspreadsheet action="write" filename="#theFile#" overwrite="true"  name="theSheet"
                        sheetname="QO_getAllData"> 
                        <cfspreadsheet action="update" filename="#theFile#"  name="theSheet2" 
                        sheetname="QO_getAllData_TBI_2008">   

             
                      <cfheader name="Content-Disposition" value="inline; filename=TrackEverythingXLS.xls">
                        <cfcontent type="application/vnd.msexcel"> 
                         <cfspreadsheet action="read" src="#theFile#" sheetname="QO_getAllData"
                        query="spreadsheetData">
                        <cfspreadsheet action="read" src="#theFile#" sheetname="QO_getAllData_TBI_2008"
                        query="spreadsheetData2">
                       <cfdump var="#spreadsheetData#" />
                        <cfdump var="#spreadsheetData2#" />

As you can see, I'm trying to write 2 tabs. That doesn't work either. All the data is dumped into one tab.

This topic has been closed for replies.

2 replies

-
-__cfSearching__-
Inspiring
August 11, 2011
If I add cfheader and cfcontent, the query dump just displays in an Excel.

<cfcontent type="application/vnd.msexcel"> 

<cfdump var="#spreadsheetData#" />
<cfdump var="#spreadsheetData2#" />

That is what you are telling CF to do.  If you want to display a spreadsheet object, use SpreadSheetReadBinary() and cfcontent's variable attribute.

           <cfcontent type="application/vnd.msexcel" variable="#SpreadSheetReadBinary(someSheetObjectHere)#">

As you can see, I'm trying to write 2 tabs. That doesn't work either. All the data is dumped into one tab.

Some of the online examples are a bit convoluted. Probably in an attempt to demonstrate other features.  But there is really no need to write the sheet to disk and then "update" it just to create multiple tabs. Simply create your spreadsheet as usual. Then use the SpreadsheetCreateSheet() function to add another sheet.  Before you can add data to the new sheet, you must activate the sheet with SpreadsheetSetActiveSheet or SpreadsheetSetActiveSheetNumber.   That is it.

Message was edited by: -==cfSearching==-

A
Anonymous
August 11, 2011

Thank you. I'll try that.

My biggest problem at the moment is that the spreadsheet that displays  as a query dump, regardless of whether it's for 1 or 2 sheets.

The query dump doesn't display in Excel without cfheader and cfcontent. And even with that, it still displays only the query dump, in a Excel shell. Is that

what it's supposed to do? I haven't attempted any formatting yet.

Thanks.

-
-__cfSearching__-
Inspiring
August 11, 2011
My biggest problem at the moment is that the spreadsheet that displays  as a query dump, regardless of whether it's for 1 or 2 sheets.

Go back and read my response again ;-) You are not displaying a spreadsheet. You are just generating a cfdump of two query objects, which is plain html. 

         ie  <cfdump var="#yourQueryObject#" />

See my previous response for how to display the spreadsheet object instead.

A
Anonymous
August 11, 2011

The first paragraph should read:

I read my data as shown in examples, but my data just displays the query  dump.

If I add cfheader and cfcontent, the query dump just displays in  an Excel SHELL.

Thanks for any help.

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices