Forum OpenACS Q&A: Squirting out CSV files

Collapse
Posted by Jason B. Standing on

Good afternoon The List,

I'm presently trying to output CSV using Listbuilder, and I've got the data displaying OK on screen, however what I am aiming for is for the user to click on the link and be presented with an Open/Save As dialogue, ostensibly for Excel.

I've worked out that I can use doc_return to return the result as a document of the correct content type, however the data's generated by an <include> which contains the Listbuilder stuff, and doc_report requires a data string to return.

The only way I can think of is to use ns_httpopen to hit the CSV generation page (using 127.0.0.1 as the web host), take the output and then send that out using doc_return, however that seems like an unecessarily complicated route to take.

Even if the Listbuilder call wasn't inside an include I'd still need a workaround, as the list data wouldn't be rendered as CSV output until the <listtemplate> tag in its ADP page was hit, and using doc_return with the template::list variable would mean the server would never reach that point. Or have I made a wrong assumption somewhere along the line ?

The documentation I'm looking at says doc_return is a wrapper for ns_return, however the panoptic.com entry for ns_return isn't exactly informative...

Any pointers much appreciated !!

Jason

Collapse
Posted by Brian Fenton on
Hi Jason,

I don't know if you know this, but if you are using Internet Explorer and if Excel is installed, then right-clicking on a table should give you an option to "Export to Excel", which opens Excel and grabs the data you want. Microsoft do all the work for you! 😊

hope this helps,
Brian

Collapse
Posted by Jason B. Standing on
Hiya all,

I've been playing with this on and off, and template::list::write_csv does 
*almost* what I want, however it seems that inside it returns its results
with 

   ns_return 200 text/plain $__output

what I need is either that output sent back into a string variable so I can
do something with it, or to have a function identical to
template::list::write_csv that returns with something like

   set headers [ns_set new myheaders]
   ns_set put $headers "Content-disposition" \
      "attachment; filename=$output_file_name"
   ns_respond -status 200 \
      -type $mime_type \
      -string $__output \
      -headers $headers

in place of the ns_return statement that currently exists there.

Does anyone know of a function that already does what I want, or will this
have to be a change to the copy of list-procs.tcl that we're using ?

Cheers,

Jason
Collapse
Posted by Jason B. Standing on
As discussed offline, this is only relevant for Excel 2002 onwards.
Collapse
Posted by Jason B. Standing on

Thanks for the tipoff Raul.

I tried the code snippet mentioned in https://openacs.org/forums/message-view?message%5fid=146238#149559 however the fundamental problem still stands that template::list::write_csv uses ns_return to return its output rather than returning a variable to the calling function. After looking at the code in list-procs.tcl it appears that template::list::write_output - if the format type is set to csv - simply calls template::list::write_csv and therefore I'm no better off than I was before.

Am I missing something fundamental here with ns_return ? My understanding/interpretation was that ns_return writes data straight out to the connection/template and as such can't be captured in a variable. Is this incorrect ?

By using the code in Jade & Randy's discussion I can get the browser to spit out an Excel-recognised CSV file, however as

set csv [template::list::write_output -name list_test]

doesn't actually prime the variable with data, the resulting CSV file is 0 bytes !

The solution I've put in place is to add a function to list-procs.tcl which is a direct copy of template::list::write_csv however I've substituted the return line

ns_return 200 text/plain $__output

with

set headers [ns_set new myheaders]
ns_set put $headers "Content-disposition" "attachment; filename=$file"
ns_respond -status 200 -type $mimetype -string $__output -headers $headers

and added some params so you can give it a filename and whatever mimetype you like (if Excel's not your cup of meat).

Anyway so that's where I got to, and it seems to work. Can anybody see any disastrous errors with what I've done ? Ideally I'd like to change it a little more so that there's not redundant code now living in write_csv and send_file_to_browser (my new function's name), however I didn't want to change the file too much.

Cheers,

Jason =)

Collapse
Posted by Matthew Geddert on

The solution you have come up with is the same one I did a while back. One recommendation that I have for you, in order to ease upgradeability, is to create a package for your site named something like packages/mysite and put your new proc a /packages/mysite/tcl/mysite-customized-procs-init.tcl file, which would reference where the original procs are from and what you customized via a comment that is something like:

# From acs-templating/tcl/list-procs.tcl
# changed return make csv download as an
# attachment

ad_proc -public template::list::write_output {
...

This file would contain all procs you customize for your site. That way when you do an upgrade to a new version of acs-templating your customized proc would remain active. Packages are sourced alphabetically first with the -procs files then the -init files. Your custom proc will be initialized after all the procs files load since its an init file thus upgrading won't override your customized procs.

I used to document changes that I needed to make to a bunch of packages and every upgrade was a hassel since I had to redo all my changes - which got trickier and tricker the more i customized. Now, when I upgrade, I simply look in my /packages/mysite/tcl/mysite-customized-procs-init.tcl file and compare those procs to the ones that are in the upgraded packages. If there are differences, especially security changes, between the new ones and the procs I customized off of I choose what and how to modify my custom proc to match the changes made by the upgrade and my sites custom needs. Most of the procs I customized aren't changed with every upgrade so I don't have to do any customizations to those procs to keep running the way my users expect it to.

I hope this helps.