Create 2 Excel files at once

MaxwellTurner
MaxwellTurner used Ask the Experts™
on
ColdFusion 9

Create 2 excel files at once

I use this to create a single excel file:

<cfquery database="somedb" name="somequery">
       ... my query ...
</cfquery>

<cfspreadsheet action="write" query="somequery" filename="/C-Items_#custno#_#contractid#.xls" sheetname="temp" overwrite="true">
<cfheader name="Content-Disposition" value="attachment; filename=C-Items_#custno#_#contractid#.xls">
<cfcontent type="application/vnd.ms-excel" file="/C-Items_#custno#_#contractid#.xls" deletefile="yes">

I have found myself in a situation where I need a second excel file created (under certain circumstances).

EG.  
<cfif filedneeded eq "yes">
      ... my code above ...
</cfif>
<cfif secondfileneeded eq "yes">
     ...different query than above, but <cfspreadsheet/cfheader/cfcontent> code similar ...
</cfif>

When I try this I only get the first excel file and not the second ... as if the <cfheader> or <cfcontent> aborts the code and the second <cfif> never runs.  I can easily create either spreadsheet from 2 separate links on my page that directs the user to the correct chunk of code, but I'd like to automate this because the need for a second excel file is only sometimes, and my users seems to often forget to click the second link.

Hope that makes sense.

Regards,
Max
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2015
Commented:
(Updated - Sorry, was on my phone yesterday. Added more detail...)

Yes, that is because browsers/http don't really support multiple file downloads.  This recently came up on another thread. While there are hacks, I'd recommend against using them as full browser support is iffy.

This thread explains it well:

    HTTP is a request/response system. Any response has to come as a reply to a request. Given that, you can't send multiple responses to a single request. If nothing else, there would be no client listening for those responses (because it already got the response it was waiting for).
    So essentially you have two options:

        1. Issue multiple requests, one for each "file" being downloaded. This will create multiple responses for the client to expect.
        2. Combine the files into a single file using some archiving tool (Zip libraries are pretty standard for this)
        ....


1. Instead, either generate a single spreadsheet file which contains multiple worksheets

<!--- create workbook and populate 1st sheet --->
<cfset wBook = SpreadsheetNew("SomeNameFor1stSheet")>
<cfset spreadSheetAddRows(wBook, firstQuery)>

<!--- create and populate 2nd sheet --->
<cfset SpreadSheetCreateSheet(wBook, "SomeNameFor2ndSheet")>
<cfset SpreadSheetSetActiveSheet(wBook, "SomeNameFor2ndSheet")>
<cfset spreadSheetAddRows(wBook, secondQuery)>

<!--- save both sheets to single (randomly named) file --->
<cfset tempFilePath = "c:/path/to/#createUUID()#.tmp">
<cfset SpreadsheetWrite(wBook, tempFilePath)>
<!--- download and delete file --->
<cfheader name="Content-Disposition" value="attachment; filename=FileNameYouWantToDisplayHere.xls">
<cfcontent type="application/vnd.ms-excel" file="#tempFilePath#" deletefile="yes">

Open in new window

OR

2. Generate multiple files, zip them, and return a single zip file. This thread is about generating PDF's, but the concept (multiple files) is exactly the same.

https://www.experts-exchange.com/questions/29109580/Multiple-cfcontent-downloads-via-query.html#a42627303

Author

Commented:
Thanks _agx_, I considered the multiple sheets and zipped and initially wrote them off because neither really fit the process in I have place, however, upon further consideration, I think zipping them into one file actually might work with a small process tweak ... not ideal, but it would force the user to view both files (if there are 2).  I will investigate that option further.  In the meantime, I also did a design tweak to make the 'Create Excel1' and 'Create Excel2' links on my page much more obvious ... could be that a big reason for this problem 'might' be due to poor design ... but I'll never admit that!  lol  

Thanks again,
Max
Most Valuable Expert 2015

Commented:
>> 'might' be due to poor design

... or short user attention spans - or a little of both ;-)

FWIW might also take a look at the possible options on the this thread.  "Supposedly" some of the options can force multiple downloads. Some of them are problematic IMO, but ... doesn't hurt to know about possible options.

Author

Commented:
lol,  yeah that's why I quoted 'might'.  I already know that this issue will only get bigger in the future ... right now it is still an infant.  Luckily this system is not public and currently only has a few users that I can speak to (or spank) directly, and I can control factors like browser type to some degree.

I will definitely check it out ... always hungry for neat workarounds.  I will award you the solution in the meantime, as I already have been pushed in another direction for the next week or so.  The department in question seems happy with my design changes ... for now, but I'm guessing that won't last and I'll have to figure this out eventually.

Thanks _agx_ and happy Friday!

Cheers,
Max
Most Valuable Expert 2015

Commented:
Heh, improving the UI can't hurt, but ... no matter how intuitive it is - hard to make anything completely "user-proof".  

Oops, just realized the link with the "other options" is this one.

>  I already know that this issue will only get bigger in the future
Especially if it's expanded from intra-to-internet.  Unfortunately the more "tricks" or "bells and whistles" involved the more likely some percentage of users will have problems due to browser compatibility issues.  I tend to be more cautious and avoid tricks or cutting edge stuff for important features. For stuff like downloads, I stick with standard options (multi-sheet or a .zip file) virtually guaranteed to be supported in every possible browser since NN :-)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial