Create 2 Excel files at once

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
LVL 1
MaxwellTurnerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

_agx_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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MaxwellTurnerAuthor 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
0
_agx_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.
0
MaxwellTurnerAuthor 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
0
_agx_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 :-)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ColdFusion Language

From novice to tech pro — start learning today.