Creating Download for excel file using hidden variables

[olmos]

I need to create a download for excel file. I have a page that displays
the query information I need. I would like to
use hidden variables instead of running the same query again because it takes too long . How can I do this ? When I tried to bring
over just the hidden variables and download to excel the formatting is
all wrong, it puts everything on one line.

Thanks,
olmos

 

[djunaedwards]

There are several ways to try. If you download the query results as a text file instead of directly into a spreadsheet, when you open the text file in Excel, it will prompt you as to how you want to import the data.

The other way is to use cfloop to parse through the data first and put some form of delimiter such as a comma between each record before passing it to the Excel file.

Hope this gives you some ideas to try.

 

[csteinhilber]

Now I'm confused.

You're trying to get the hidden fields to be recognized by Excel somehow? Or are the hidden fields just to store the query (to generate an additional Excel spreadsheet)?

It seems like the proper way to do this would be to use a session or application variable to store the query (in effect, cache it)... so you can run as many spreadsheets of the data as you want, and you'll only be hitting the database once.



-Carl

 

[djunaedwards]

I guess I am not quite clear on what you are trying to do.
<cffile> will allow you to create a text file that, when you open Excel, then open the text file, Excel will prompt you as to how you want the data formatted.
If you try to open the file directly (do a open with or give the file an extension of .xls ) then you will get everything in one cell.

Are you trying to go directly to Excel via a link or button?

 

[csteinhilber]

If you try to open the file directly (do a open with or give the file an extension of .xls ) then you will get everything in one cell

That's not necessarily true.

First, olmos needs to clarify whether, by &quot;download&quot;, he/she means download in the browser, or download to a local drive and open in Excel.

If the question is about downloading to the browser, you can set the MIME type of the transmission via CFCONTENT, to easily open the Excel speadsheet directly in the browser (if the browser is IE, it will open in the Excel ActiveX control in the IE window... if the browser is Netscape, it will usually launch Excel automatically and load the spreadsheet).

If the question is about downloading to a local drive, you can write out the data with CFFILE with a .CSV file extension, with commas between cell data and the ASCII carriage return

#Chr(13)#

between rows, and the resulting file will open in Excel correctly formatted... or I've had luck using CFFILE write out as a .XLS file with

#Chr(9)#

between cell data, but that's far from being reliably tested.




-Carl