CFContent / Excel Output issue

[XYXOXYXX]

I'm trying to do a simple query to output data to the browser in Excel format but I keep running into the same problem... which is similar to those I've seen in other posts here. I've been browsing the forums for a while trying every suggestion I can find to solve my problem but so far nothing works.

The process is triggered by the user choosing an "Excel" radio button on a form and then clicking a submit button. The form action then processes the user's selection criteria through a series of queries and then attempts to open the output as Excel data. Like others here, Excel tries to open as the CFM url I called instead of the XLS filename I've added to the CFHeader tag. The REALLY strange thing is that if I choose CANCEL and then submit the form a second time, the file opens as it should... without fail.

If I comment the CFContent and CFHeader tags the browser displays the report data as expected with no errors.

I believe Excel is tripping up on something but I just can't figure out what. I have a similar process in another part of my site that uses a link to trigger the report instead of a form submit and that works just fine. Am I missing something here in the difference between a link and a form submit?

Here is where things appear to be failing. The commented lines are just to show you what I have tried. I'm currently using the variables.strExcel_Out variable for output. This was created using CFSaveContent and running the report code dspSemaReportOutput2.cfm. I've also tried running that directly without the variable. I get the same results no matter what I change so I'm missing something.

Any thoughts or suggestions would be appreciated.

[XYXOXYXX]

Oops! I also forgot to mention that I'm using Fusebox 5.1
So when the form submits it is calling a Fusebox fuseaction. I don't know if that makes a difference but I'm trying to think of anything that might be relevant.

[XYXOXYXX]

Sorry to keep reposting to my own thread but I may have more relevant information though I still have not solved the bigger issue.

No matter what I tried I could not display my data using CFContent and CFOutput. So I finally threw in the towel and tried using CFFile to write the Excel content to a file in a temp directory, and then tried reading it with CFContent file="(tempfilename)" deletefile="yes" etc..

Same exact result as before. Doesn't work the first time but works the second time.

So I tried removing the deletefile attribute and tiling my windows to watch the temp folder as the code runs. It appears that CF is trying to open the excel file before it is finished writing the file. I guess I assumed CF would handle that for me. It is not a large file and only takes 4-5 seconds to create. But the download begins after about 2 seconds. My server timeout setting is 600 seconds (for development).

So my next thought would be to set up a loop to check for the file before trying to display it with CFContent. But that feels klunky and ungraceful to me. Is there a more streamlined approach I should be using or am I missing something bigger here?

Again I would appreciate any suggestions.
- Mickey

[Newsgroup_User]

<cfcontent file=...> is a special tag in a way that it affects the whole
page, not just from the point the tag is encountered in the code.

try instead to separate the 2 processes:
- write the file using cffile on one page, then use <cflocation> to call
up the page that has <cfcontent> tag on it to serve up this file

Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/

[XYXOXYXX]

Thanks for the reply. That will probably work or more directly I could probably use cflocation to point directly to the excel file itself. I'm updating someone else's code and that's what they have done in places.

I guess I was really hoping for a solution where I don't need to write the temp file at all but if that's the answer so be it.

I have one working instance like that triggered by a hyperlink but this one triggered by a form submit just doesn't seem to work the same way. The one that works only uses 2 lines of code so I'm not sure what the difference is.

I'm still open for any other ideas to avoid the temp file but I'm thinking it may be time to surrender.... for now.

Thanks again.

[Newsgroup_User]

i somehow missed that you were using fusebox... not sure if that could
be the problem since i don;t use fb...

you may want to ask your question over on CF-Talk list on
houseoffusion.com - there are a lot of good people with fusebox
experience there who may be able to help you.

Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/

[XYXOXYXX]

I feel like I have ruled out FB as the problem but you can never be 100% sure.

I'll give houseoffusion a try.

Thanks again.

[XYXOXYXX · Accepted solution]

Success!!
I have to eat my words slightly in that Fusebox was PART of the problem. Or rather my failure to see how FB was including code that I was ignoring. This may border on off-topic but I'd like to post the answer for anyone else struggling like I have for 2 days with this.

In Fusebox you can include a "global prefusaction" which has the effect of adding the exact same <CFInclude> at the top of EVERY page. In our case we have one of those which includes a template that draws the same page header for everything. This page header has the line...

<cfheader name="cache-control" value="no-cache, no-store, must-revalidate">

... which was the root of all my problems. No matter what I tried I was unintentionally resetting the cache-control value in the http header.

So in reality the problem was not Fusebox per se... but my failure to recognize that every thing I tried still included this one $#@*@!! line of code. This is such a long standing and integral piece of our site that it was easy to overlook.

See this MS post for more detail:
http://support.microsoft.com/default.aspx?scid=kb;en-us;316431

I hope this helps someone else.