Forum OpenACS Development: html table to excel

Collapse
Posted by Vince T on
I'd like to export the results of a dynamic table to an (popup) excel spreadsheet. Through something like a "export to excel" button on the page.

I would imagine it would some sort of javascript code.

Thanks

Collapse
3: Re: html table to excel (response to 1)
Posted by Jade Rubick on
List-builder does this (export to CSV, which opens in Excel). List-builder rocks!

You'll need to be on OpenACS 5 though.

There are other ways to do this, as well, but list-builder is worth learning if you have the time.

http://rubick.com:8002/openacs/list-builder

Collapse
2: Re: html table to excel (response to 1)
Posted by Brad Duell on
The following in tcl should do what you want:

} - on_submit {
  ReturnHeadersNoCache "application/vnd.ms-excel" $filename
  db_foreach .... {
      ns_write $first_column\t$second_column\n
  }
  ad_script_abort
}

ReturnHeadersNoCache is deprecated, but gives you the content_type and filename support.

Having the Excel sheet show up in the browser automatically, or having the option to save to file is a user-specific option.

Collapse
4: Re: html table to excel (response to 1)
Posted by Mark Aufflick on
You can actually force IE (and some other browsers) to download instead of viewing inline for known file types.

You need to add a header like:

Content-disposition: attachment; filename=fname.ext

As per RFC 2183 http://www.nic.mil/ftp/rfc/rfc2183.txt

Famously, support for this is broken in IE 4.01, but like that matters these days (in 4.01 it's not honoured, but then you haven't lost anything).

Collapse
5: Re: html table to excel (response to 4)
Posted by Mark Aufflick on
hmm, the nic.mil site appears to be broken - see the rfc on netsys.com: http://www.netsys.com/rfc/rfc2183.txt
Collapse
6: Re: html table to excel (response to 1)
Posted by Jade Rubick on
A couple of things:

First of all, ReturnHeadersNoCache is deprecated -- how would I do the same thing with ad_conn -set  The documentation isn't very clear on this.

I'm not entirely sure how to use it to add the content-disposition header either.

It seems like this is something that should be done in the

list::write_output

function by list-builder, no? Currently, I just get a browser window full of csv text, instead of a download.

Collapse
7: Re: html table to excel (response to 1)
Posted by Jade Rubick on
I tried doing it like this (at Bart's suggestion), and he says he has it almost exactly like this, and it works for him.

For me, unfortunately, it doesn't, on Mac Safari, IE, and Mozilla.

    set csv [list::write_output -name pan]
    set outputheaders [ns_conn outputheaders]
    ns_set cput $outputheaders "Content-Disposition" "attachment; filename=pan.csv"
    ns_return 200 "application/vnd.ms-excel" "$csv"
Any suggestions?
Collapse
8: Re: html table to excel (response to 1)
Posted by Dave Bauer on
I use ns_return 200 "application/text" and it works in Mozilla and IE. I didn't test in other browsers.

I also created an index.vuh that internally redirects foo.xls to foo.tcl so you get a nice filename for the download.

See this thread for this and more sample code:

https://openacs.org/forums/message-view?message_id=106523

Collapse
9: Re: html table to excel (response to 8)
Posted by Tilmann Singer on
Use doc_return instead of ns_return, then your database handles will be freed and you'll get nocache headers (starting with 5.0).
Collapse
10: Re: html table to excel (response to 1)
Posted by Jade Rubick on
This still isn't working for me. I'd like to put this in the FAQ, and talk with Lars about documenting this for list-builder. Any other suggestions? The other threads in the link Dave suggested either use deprecated procs, or don't explain what they do (using .vuh files for example) or seem too complicated (using the .vuh files for example).

It displays on the screen very nicely, just doesn't pop up in Excel.

    set csv [list::write_output -name pan]
    set outputheaders [ns_conn outputheaders]
    ns_set cput $outputheaders "Content-Disposition" "attachment; filename=pan.csv"
    doc_return 200 "application/text" "$csv"

Collapse
11: Re: html table to excel (response to 10)
Posted by Randy O'Meara on
Jade,

Have you tried changing your MIME type to something different? Maybe "Application/vnd.ms-excel"? You'll have to send the type that your browser has configured to pop the Excel application.

http://www.stanford.edu/group/ia/reporting/xlsmimetype.html

Randy