HOW TO Export documents data as CSV or XLS

Eric BREHAULT edited this page Sep 22, 2016 · 1 revision

Different ways to produce a CSV or XLS output.

An CSV (or XLS) export can be done in two ways:

  • either by using the Plomino API to export the content of any PlominoView,
  • either by producing your own CSV content using a PlominoAgent.

Let's consider a database where documents have the following items: employee name, country, and age.

If you call /exportCSV or /exportXLS on view's URL, you get the actual content of the view as CSV or XLS.

If you want to provide a button to your users, just create a Redirect action into the view to produce the URL:

context.absolute_url()+"/exportCSV"

Now, let's imagine you want to process the data before producing the output. For instance, instead of exporting the age value, you want to export "Senior" or "Junior" depending on the age is higher or lower than 40.

To do that, we create an agent which will collect the data into an array, and then return the array content as CSV:

db = context.getParentDatabase()
all = db.getAllDocuments()
results = []
for doc in all:
    age = doc.age
    if age >= 40:
        age_range = "Senior"
    else:
        age_range = "Junior"
    results.append([doc.employee, doc.country, age_range])

context.REQUEST.RESPONSE.setHeader('content-type', 'text/csv; charset=utf-8')
context.REQUEST.RESPONSE.setHeader("Content-Disposition", "attachment; filename=my_special_export.csv")
context.REQUEST.RESPONSE.setBody(array_to_csv(results))

To produce an XLS export, do not return a CSV content but an HTML table, and just change the content-type header to 'application/vnd.ms-excel; charset=utf-8', to pretend it is an actual Excel content, so your browser will launch the appropriate application to display it (that is basically what the exportXLS Plomino method does: see source code)

Note: of course in that very simple case, we could also have create an extra view with a column displaying Senior or Junior, and then export that view.

And here again you can provide a redirect acgion to call the agent url more easily:

context.getParentDatabase().absolute_url()+"/export_csv/runAgent"