How get cfspreadsheet to return the query dump in Excel?

[Guest]

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.

[Guest]

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.

[-__cfSearching__-]

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==-

[Guest]

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__-]

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.

[Guest]

My initial problem may start with "Simply create your spreadsheet as usual." How do I do that?

With the code I just tried below, I did get a spreadsheet. So using cfheader and cfcontent is correct?

I did not get 2 sheets though.

    <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"); 
                             SpreadsheetAddRows(theSheet,QO_getAllData);
                                 //Create a new sheet.
                            SpreadsheetCreateSheet (theSheet, "QO_getAllData_TBI_2008"); // 2nd query
                            //Set the sheet as active.
                            SpreadsheetSetActiveSheet (theSheet, "QO_getAllData_TBI_2008");                             
                            //Populate each object with a query. --->                             
                             SpreadsheetAddRows(theSheet,QO_getAllData_TBI_2008);
                        </cfscript>
                                                
                        <!--- Write the sheet --->
                    <cfspreadsheet action="write" filename="#theFile#" overwrite="true"  name="theSheet"
                        sheetname="QO_getAllData">
                               
                    <!--- (no formatting) Works best. 8/10/11 2:54 pm  --->    
                    <cfheader name="Content-Disposition" value="inline; filename=TrackEverythingXLS.xls">
                        <cfcontent type="application/vnd.msexcel">
                         <cfspreadsheet action="read" src="#theFile#" sheetname="QO_getAllData"
                        query="spreadsheetData">
                       <cfdump var="#spreadsheetData#" />

Also, will I be able to code to Sort? Right now, I get an error message  saying: "This operation requires the merged cells to be identically  sized. (I had selected a Custom Sort on the entire sheet.) Is this a formatting issue?

Thanks for helping me out.

[-__cfSearching__-]

You are trying to learn way too many things once.  Let us step back and do one piece at a time.

First create a single sheet, and populate it with data

    <cfset theSheet = SpreadsheetNew("TBI_2009")>
    <cfset SpreadsheetAddRows(theSheet,QO_getAllData)>

So using cfheader and cfcontent is correct?

No. Like I said earlier, to display the spreadsheet you use cfcontent's variable attribute like so:

     <cfheader name="Content-Disposition" value="inline; filename=TrackEverythingXLS.xls">

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

Get this part working first. Then move on to "adding a second tab".  Make sense?

[Adam Cameron.]

So using cfheader and cfcontent is correct?

No. Like I said earlier, to display the spreadsheet you use cfcontent's variable attribute like so:

One other thing the OP needs to bear in mind here is that CFCONTENT is not some magic-converto tag.  It does up to three things:

* clears the response buffer;

* puts data into the response buffer;

* sets the response's MIME type.

That's all it does.

What it does not do is convert mark-up to some other file format.

So in the original example, all that's happening is the CFDUMP tags are outputting HTML, however the CFCONTENT is telling the browser "this is application/vnd.msexcel data... just in case you have a special way of handling that" (like, for example, opening it with Excel, if Excel happens to be installed on the client PC).  It doesn't magically covert the HTML that is being returning into an Excel file.  However Excel sees this HTML coming through, and given a CFDUMP is basically an HTML table, Excel knows how to convert the HTML table into Excel rows and columns.  Which is exactly what it's doing.

However if one wants to do more advanced Excel stuff like having multiple sheets, then one needs to actually return an Excel file.  Not just some HTML with a application/vnd.msexcel MIME type.

--

Adam

[-__cfSearching__-]

• clears the response buffer;

Would that translate to disregards any output generated before the cfcontent tag?

[Adam Cameron.]

• clears the response buffer;

Would that translate to disregards any output generated before the cfcontent tag?

That's what the RESET=TRUE option does, yeah.

This is provided one hasn't already done a CFFLUSH earlier in the piece, anyhow.  Once one's started flushing it down to the browser, one can't grab it back again and og "um... actually I've changed my mind about that..." 😉

--

Adam

[-__cfSearching__-]

That's what the RESET=TRUE option does, yeah.

Makes sense. So when would reset=false be useful? I usually stick with the default (true) and have not given that setting much thought.

[Adam Cameron.]

I never thought about it, to be honest.

According to my perennial friend, the docs, the default behaviour is TRUE, and one would specify FALSE if one wanted to keep the previously buffered output, and just wanted to set the MIME type or output a variable or file or something.

I have to concede I mostly use CFCONTENT as a mechanism to make sure the buffer is clear, rather than serving up files or setting the MIME type, so was not aware it implicitly resets the buffer in these situations (well that's my reading of the docs, anyhow).

http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-7c82.html

--

Adam

[-__cfSearching__-]

resets the buffer in these situations (well that's my

reading of the docs, anyhow).

Based on the behavior, I would agree. Really I was just curious if there was some neat use for the setting I had been missing all these years.

-Leigh