The application owner wanted to see his survey results in (hmm, can you guess?) MS Excel. "No problem", I said, and then was surprised at how much trouble I had figuring out how to do that straight from TG. AFAIK, TurboGears doesn't have a handy way to output CSV. I could certainly produce a webpage with CSV data, and even use the default method in my controller to serve it with a .CSV extension ("http://myserver/report.csv"), but it still had webpage-type headers and thus Excel still didn't know what to do with it. And no, I wasn't about to tell him to cut-and-paste.
What I eventually hit on was to provide the results as a simple HTML table, then take advantage of the fact that Excel can open a webpage that contains (only) a table rather nicely. Then I gave him a batchfile that simply said
"c:\Program Files\Microsoft Office\Office11\excel.exe" http://myserver/report... double-clicking it gets a live view of the data.
If he'd been cooler, the batchfile could just as well have said
"c:\Program Files\OpenOffice.org 2.2\program\scalc.exe" http://myserver/report- I tried that, too, it works.
Actually, I didn't serve it under http://myserver/report, I served it under https://myserver/reportWithHideousGUIDblahblah4242rtfm22hike. That was my quick-and-dirty way to provide some basic security - the report URL is unguessable, so unless he shares the link, it should be safe. On the other hand, anybody who can sniff his request can extract the magic URL and use it. Can anybody comment on how much of a risk that is? There's no real sensitivity to the data in this case, but it would be nice to know if this the-URL-is-the-password scheme is a worthwhile shortcut or is terribly dumb.
6 comments:
I once did this by simply outputting a table and changing the content-type and it opened up in Excel.
I think you can also pass a 'content_type' keyword arg into the @expose decorator to manually set the content type. This poster does it by hand, but the principle is similar: http://groups.google.co.uz/group/turbogears/msg/ba1d778c25fb5e65
Several years ago I used some of the Python/COM integration features in pywin32 to manipulate Excel spreadsheets directly. Actually we were reading existing Excel files instead of creating them, but it should work either way.
see http://www.knoda.org/
-> wiki -> Howtos
How to create an Excel File
install Perl Modules
Text::CSV_XS
Spreadsheet::WriteExcel
get http://search.cpan.org/src/JMCNAMARA/Spreadsheet-WriteExcel-2.18/examples/csv2xls.pl
replace
my $csv = Text::CSV_XS->new;
my $csv = Text::CSV_XS->new ({ binary => 1 });
double quotes in the table/view used by hk_exportcvs have to be escaped by a double quote (see Text::CSV_XS docu) by using this function:
replace(field name,'"','""')
hk_exportcvs with option --charset=Latin1
csv2xls.pl file.csv file.xls
DONE
We do this a lot, by making use of pyExcelerator:
# set some headers to indicate we are returning excel response.headers['Content-type'] = "application/excel"
response.headers['Content-Disposition'] = "attachment; filename=event.xls"
import pyExcelerator as xl
# create an excel workbook
xldoc = xl.Workbook()
#Add a worksheet
mysheet = xldoc.add_sheet("Data")
... add stuff to workbook ...
from cStringIO import StringIO
# Create a file
buffer = StringIO()
# Save the excel to that file
xldoc.save(buffer)
# return the raw excel stream
return buffer.getvalue()
This is all in TG2 so YMMV - for us it serves the excel up all warm and toasty
I am totally on track with Tim. Follow his direction and you will be creating Excel reports in no time!
-Grahm
Excel Reports
Post a Comment