The best way to export a complex report

I have a fairly complex query that generates a fairly complex report in the rowser.. just html tables with some data, some nested tables with its own data, tc. No problem with that part. The part I'm havin trouble tryin to figure out is how to generate some ownloadable reports out of it. It's like, what should the routine be for this? In my head I've come up with using <cfsavecontent> to save a sort of, napshot of the page in a SESSION variable. The user clicks the "export to html" ink, and I'll build an html page they can dload via <cfcontent>. So as far s that's concerned, would that work ok? I also need to export this data to excel. Now, I've been usin ben nadel's sweet OI utility to generate some other excel files, works like a champ. But I'm havin hard time even figuring out the routine to do this, before it even gets to that oint. 1. User lands on the page. 2. Makes some filter selections. 3. report is generated on their .cfm page. 4. User clicks "export to excel" link. 5. What now? Do I re-run the query all over again? Is there a way that query can be saved so I can use it again when he clicks the ink? Thanks, Will

What I've done for this kind of thing is just stuck a cfheader with filetype for excel, right on the HTML page. It streams to the browser and opens in excel. Some issues with this approach: security settings on the client can muck it up. Using a cfsavecontent could get you around that, perhaps write the content to a file, push the user to a page with a link to the newly created file? That would get around some of the security issues inherent with streaming the content immediately. Cheers, Kris ----- Excess quoted text cut - see Original Post for more -----

Using a cfsavecontent could get you around that, perhaps >write the content to a file, push the user to a page with a link to >the newly created file? That would get around some of the security >issues inherent with streaming the content immediately. This is the solution I've been leaning towards. Thanks! Will

> This is the solution I've been leaning towards. It works great if you only need the basics of layout, color etc. After that, you'll need to look at one the wrappers around Apache's POI project that are already out there. -- Tom Chiverton Helping to competently incentivize sexy bandwidth on: http://thefalken.livejournal.com **************************************************** This email is sent for and on behalf of Halliwells LLP. Halliwells LLP is a limited liability partnership registered in England and Wales nder registered number OC307980 whose registered office address is at St James's ourt Brown Street Manchester M2 2JF.  A list of members is available for nspection at the registered office. Any reference to a partner in relation to alliwells LLP means a member of Halliwells LLP. Regulated by the Law Society. CONFIDENTIALITY This email is intended only for the use of the addressee named above and may be onfidential or legally privileged.  If you are not the addressee you must not ead it and must not use any information contained in nor copy it nor inform any erson other than Halliwells LLP or the addressee of its existence or contents. f you have received this email in error please delete it and notify Halliwells LP IT Department on 0870 365 8008. For more information about Halliwells LLP visit www.halliwells.com.

> It works great if you only need the basics of layout, color etc. I indeed did get it working. My main issue is I have a few side-by-side tables. Excel wants to stack these vertically. Is there a trick to getting excel to read them like <td>'s? thanks, Will

> > It works great if you only need the basics of layout, color etc. > I indeed did get it working. My main issue is I have a few side-by-side > tables. > Excel wants to stack these vertically. > Is there a trick to getting excel to read them like <td>'s? Does nesting <table> tags work ? -- Tom Chiverton Helping to greatly administrate corporate materials on: http://thefalken.livejournal.com **************************************************** This email is sent for and on behalf of Halliwells LLP. Halliwells LLP is a limited liability partnership registered in England and Wales nder registered number OC307980 whose registered office address is at St James's ourt Brown Street Manchester M2 2JF.  A list of members is available for nspection at the registered office. Any reference to a partner in relation to alliwells LLP means a member of Halliwells LLP. Regulated by the Law Society. CONFIDENTIALITY This email is intended only for the use of the addressee named above and may be onfidential or legally privileged.  If you are not the addressee you must not ead it and must not use any information contained in nor copy it nor inform any erson other than Halliwells LLP or the addressee of its existence or contents. f you have received this email in error please delete it and notify Halliwells LP IT Department on 0870 365 8008. For more information about Halliwells LLP visit www.halliwells.com.

> Does nesting <table> tags work ? > Tom, I figured out a way around it. I simply stuck each table in it's own side by ide td. Duh!! Seems to be formatting perfectly! Anyone ever seen an easier way to export to excel than this? <cfcontent type="application/vnd.ms-excel"> <cfoutput>#SESSION.coreReport#</cfoutput> There is one little issue that involves IE. You ever seen the way IE opens excel NSIDE the browser? It's kinda odd lookin. My sister uses excel everyday and she xported my report. It opens IN her browser, IN excel, and she doesn't understand hat's goin on. lol! That's not a good sign. Other users will likely get confuseled. FF of course pens it into Excel itself. Thanks, Will

> There is one little issue that involves IE. You ever seen the way IE opens > excel INSIDE the browser? It's kinda odd lookin. My sister uses excel > everyday and she exported my report. It opens IN her browser, IN excel, and > she doesn't understand what's goin on. lol! <cfheader name="content-disposition" value="attachment; filename=foo.xls"> -- Tom Chiverton Helping to administratively build performance-oriented technologies on: http://thefalken.livejournal.com **************************************************** This email is sent for and on behalf of Halliwells LLP. Halliwells LLP is a limited liability partnership registered in England and Wales nder registered number OC307980 whose registered office address is at St James's ourt Brown Street Manchester M2 2JF.  A list of members is available for nspection at the registered office. Any reference to a partner in relation to alliwells LLP means a member of Halliwells LLP. Regulated by the Law Society. CONFIDENTIALITY This email is intended only for the use of the addressee named above and may be onfidential or legally privileged.  If you are not the addressee you must not ead it and must not use any information contained in nor copy it nor inform any erson other than Halliwells LLP or the addressee of its existence or contents. f you have received this email in error please delete it and notify Halliwells LP IT Department on 0870 365 8008. For more information about Halliwells LLP visit www.halliwells.com.

> <cfheader name="content-disposition" value="attachment; filename=foo. > xls"> ? This is what I've decided to do. Instead of streaming the content to the browser o do whatever with it, I'll create a temp file for dload. Thought I could get round it, but IE is gonna screw me yet again. I think that makes the 4th time this week! Thanks tom, Will

Hey, this worked. <cfheader name="content-disposition" value="attachment"> <cfcontent type="application/vnd.ms-excel"> <cfoutput>#SESSION.coreReport#</cfoutput> Still think I'm gonna go the create a temp file route tho. Will

How about using POI? Eric Hey, this worked. <cfheader name="content-disposition" value="attachment"> <cfcontent type="application/vnd.ms-excel"> <cfoutput>#SESSION.coreReport#</cfoutput> Still think I'm gonna go the create a temp file route tho. Will

>How about using POI? > >Eric I originally was gonna use Ben's POI utility. Then figured it'd be easier using y existing html tabular output, then letting excel read the tables and convert t all over. That part it workin nicely, just had the IE trouble. I think I fixed it just by dding the <cfheader> with attachment setting. Might still end up creating a temp .xls file for dload. Thanks much, Will

Going with the attachment setting can work nicely, but if the client has certain security settings enabled, this may still fail. Of course, if you can predict your client's configuration (an internal app for your organization where your company sets the config etc), then this could be moot. Otherwise, you might want to go ahead and do a cfsavecontent on the entire output, write the content to a permanent file (which you can delete later), and redirect the client to a page with a link to download the saved file. This has been the only way I've found to reliably get around some of the IE security settings. Cheers, Kris > I originally was gonna use Ben's POI utility. Then figured it'd be easier sing my existing html tabular output, then letting excel read the tables and onvert it all over. > > That part it workin nicely, just had the IE trouble. I think I fixed it just y adding the <cfheader> with attachment setting. > > Might still end up creating a temp .xls file for load.

I can't figure out why creating a temp file would help me here. I can create the ser's content. I can force a dload by usin this: <cfset thisDate = DateFormat(Now(),"m-dd-yyyy")> <cfheader name="content-disposition" alue="attachment;filename=CoreReport_#thisDate#.xls"> <cfcontent type="application/vnd.ms-excel"> <cfoutput>#SESSION.coreReport#</cfoutput> Why would I need to add the extra step of creating a file, when this does it? I WILL have multiple users hitting these same pages, but I'm using SESSION to old the content, so each user's content should be unique to them. Can't think of any pitfalls here... Thanks, Will

There are some security settings that will prevent the client from receiving streamed content. Again, if you have control over your client's configuration, this may not be an issue for you. Cheers, Kris ----- Excess quoted text cut - see Original Post for more -----

----- Excess quoted text cut - see Original Post for more ----- This isn't streamed content; it's no different than generating content from any other CF page. The only issue you might have is if your client expects your output to have the name of your CF file, when in fact you're giving it some other name via CFHEADER. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! This email has been processed by SmoothZap - www.smoothwall.net

Ahh.... ok. yeah, my naming of the file will work best in this case. I'm generating a report, aming it with the type and date. I guess if 10 people hit this page at the same time, it shouldn't blow up. I've ever built anything like this before. Thanks Dave! Will