Exporting data to Excel

Note: This how-to assumes you already have a functioning connection to a database and some tables you are pulling data from. If you don't, follow this tutorial: Plone and MySQL

For our example we assume a table within our database called "MovieInfo" with the columns "MovieName", "ActorName", and "Year". Further, it is assumed that within your Plone site you have a folder called (for example) "Movies" in which you have a page template that presents the results from your database via a ZSQL Method. It is from this ZSQL Method that we are going to generate an Excel file with a python script. Assuming the ZSQL Method to be called "Movie_Data" then:

Step 1

In the ZMI create a Python Script called (for example) "exportMovieData" in the same folder as your "Movie_Data" ZSQL Method.

The Python Script will look like:

   from Products.PythonScripts.standard import html_quote
   request = container.REQUEST
   RESPONSE =  request.RESPONSE

   print "Movie \t Actor \t Year" 

   for row in context.Movie_Data():
     print "%s \t %s \t %s" % (row.moviename, row.actorname, row.year)

   RESPONSE.setHeader("Content-type","application/vnd.ms-excel")
   RESPONSE.setHeader("Content-disposition","attachment;filename=Movie Data.xls")

   return printed

A couple of things to point out:

1. The first "print" statement puts column headers in the first row of your Excel file.
2. The "\t" characters are required to tab-delimit the output so that Excel will correctly format your data in different columns. If you leave them out all your data will end up in the first column of your Excel file.
3. The statement "for row in context.Movie_Data():" loops through the data returned from your ZSQL Method "Movie_Data".
4. The last two RESPONSE.setHeader lines define the type of file to export the data to and its filename.

Step 2

Call your Python Script "exportMovieData" as appropriate.

That's all.

(Thanks to Andreas Jung for help on this.)