CFQuery -> Excel file

[WolfShade]

Hello, everyone.

I'm trying to take a query object and insert the data into an Excel file.  I've tried the CFCONTENT/CFHEADER route, and I get the error message about the file not being the same type as the extension.

Is there a better way to get data into an Excel file?

Thank you,

^_^

[Dan_Bracuk]

start with cfspreadsheet.

[WolfShade]

Trying your suggestion, I'm using CFSPREADSHEET to create the file directly from the query, but the file is blank even when the query has 886 records in it.

Also, how do I tell the browser to offer the file for download, once it's made, without using CFCONTENT and CFHEADER?

Thank you,

^_^

[Dan_Bracuk]

One step at a time, starting with why cfspreadsheet is producing a blank file.  Please post the code you are using to create this file.  Just the code that creates the file, no queries, formatting stuff or anything else.

[WolfShade]

<cfspreadsheet action="write" filename="CTP-#url.tid#_#DateFormat(now(),'yyyymmdd')#.xls" query="compQry" overwrite="yes">

[Dan_Bracuk]

That's odd.  When I run this:

<cfquery name="x" datasource="dw">
select event_code, event_name
from event

</cfquery>

<cfspreadsheet action="write" query="x" filename="abc.xls" overwrite="yes">

I get a spreadsheet with column headers and 20 rows of data.

If I add "where 1=3" to the query, I get a spreadsheet with column headers only.

If I change the query to "select count(*) from event", I get nothing in cell A1 and 20 in cell A2.

I suggest simplifying your query and filename.  Your sql could be "select count(*) records from sometable where 1=3"

This should give you "records" in cell A1 and "0" in cell A2.  Then start building it up until it goes strange.

[WolfShade]

The query is in a CFC, inside the same method that is used to display the data.  It is:

SELECT colA, colB

FROM tableA

WHERE colC = {an integer that is an ID}

ORDER BY colA

The data is there, no doubt.  A CFDUMP / cfabort even shows that the data is there.

It might be the CFHEADER/CFCONTENT.  It's about the only thing I can think of.

^_^

[WolfShade]

Here's something weird.

The Excel file, itself, is empty.. BUT, if you right-click the file and open the .xls file in NotePad, you can see a CF error message.  It does not say what the problem is, nor what line of what document created the error, but the data is not making it to the file, itself.  Just the error message, incomplete as it is.

^_^

[WolfShade]

Now if I take CFSPREADSHEET out of the mix, keep the CFCONTENT and CFHEADER, then just CFOUTPUT the data, it will at least populate the file (I still get the error message about the format not being the same as the file extension.)  WTF?

^_^

[Dan_Bracuk]

This block of code creates an excel file from a query and then offers it to the user.

<cfset theFile = "d:\dw\dwtest\dan\abc.xls">

<cfquery name="x" datasource="dw">
select event_code, event_name
from event
</cfquery>

<cfspreadsheet action="write" query="x" filename="#thefile#" overwrite="yes">
<cfheader name="content-disposition" value="Attachment;filename=#thefile#">

<cfcontent  file="#thefile#" type="application/vnd.ms-excel">

What are you doing differently?

[WolfShade]

Near as I can tell, the only thing I'm doing differently is:

<cfcontent file="filename" type="application/msexcel"> (as opposed to "application/vnd.ms-excel").

I'll give the different type a shot.  But the error message is the same for .csv files, too.

Thank you,

^_^

[WolfShade]

I just noticed something odd.  When I click the button to export to excel, it opens a popup ("export.cfm") that does the work.

Before changing the type of the cfcontent, it would say "The file you are opening 'filename.xls' is not the same type as the file extension."

After changing the type of the cfcontent, it now says "The file you are opening 'export.cfm' is not the same type as the file extension."

[scratching head]

UPDATE: I just noticed something else: of the three file formats that I'm trying to work with (.xls,.csv,.pdf), the two using cfcontent/cfheader give the error message.  The one not using cfcontent/cfheader is using cfdocument, and has no issues.

[WolfShade]

Allow me to edit this so that it actually makes sense.

What I just described was with the CFOUTPUT, not CFTEXTAREA CFSPREADSHEET.

Now that I've switched to CFTEXTAREA CFSPREADSHEET with the new type, it does not matter if I open or save the file, it is blank, 0 bytes.  If I save it to desktop and open it, I still get the error message that the file is not the same as the extension claims.  The data is there, CFTEXTAREA CFSPREADSHEET just isn't putting any into the file.

It's been a long day. 

^_^

[Dan_Bracuk]

With regards to, "<cfcontent file="filename" type="application/msexcel"> (as opposed to "application/vnd.ms-excel").", when I changed my type I match yours, there was no change in behaviour.

Regarding, "

Before changing the type of the cfcontent, it would say "The file you are opening 'filename.xls' is not the same type as the file extension."

After changing the type of the cfcontent, it now says "The file you are opening 'export.cfm' is not the same type as the file extension."",

That sounds like what we were seeing when we were using html table tags to generate our content.  Things worked well until Office 2007 came out and then it got ugly.  I suspect that when you say that your code is almost exactly the same as mine, you have a different definition of "almost" than do I.

[WolfShade · Accepted solution]

I found the solution here.  I don't know why it works, but it does.

I've tried it with the Excel, it works flawlessly; I'm about to apply it to the CSV in a moment. 

^_^