[Zope] Tip - Export to excel

Mon, 28 Oct 2002 10:34:33 -0800

There was a thread here a while ago about trying to get decent reports into
Excel. I posted a bit about generating CSV files and setting the right
headers. Alas, there are a host of problems with this, including different
behaviors bewteen browsers and Excel versions.

I realized one thing Excel does do well is import HTML. So I propose this
odd solution for people who need to export to excel (it works for me with
both mozilla and IE6, exporting to excel 2k):

Start your export file with '<html><body><table>'.
List your rows as '<tr><td>xxx</td><td>xxx</td></tr>'
And wrap up with </table></body></html>'

In other words, just send a basic HTML document with a table in it.

Then change your headers as follows:

response.setHeader('Content-type', 'application/vnd.ms-excel')
response.setHeader('Content-Length', len(result_str))
response.setHeader('Content-Disposition', 'attachment;filename=report.xls')

This icky solution works because:

1) Excel recognizes HTML quite well no matter what the suffix.
2) Mozilla obeys the content-type header and launches excel.
3) IE ignores content-type and instead fixates on the filename suffix.

The nice benefits are, of course, full HTML formatting and a reduced
likelihood of needing to escape your data cells' content. The drawback is
this is excel specific and your data is not in the common CSV format. There
is also now a need to format your data since excel seems to remove all
formatting (including cell borders) when importing an unadorned table.

FWIW, I've started porting Spreadsheet Writer in my spare time. I've managed
to port a surprising amount but I won't know if any of it works until I get
much farther down the road. If anyone would like to help, drop me a line. I
don't think I'm in a position to use external help yet but eventually I'll
need folks to help with porting the tests and possibly working on the more
exotic functions like formula support.