Render into xlsx

What if we want to have a renderer that always takes the same data as our main renderer (such as mako or jinja2), but renders them into something else, for example xlsx. Then we could do something like this:

 1# the first view_config for the xlsx renderer that
 2# kicks in when there is a request parameter xlsx
 3@view_config(context="myapp.resources.DBContext",
 4             renderer="dbtable.xlsx",
 5             request_param="xlsx")
 6# the second view_config for mako
 7@view_config(context="myapp.resources.DBContext",
 8             renderer="templates/dbtable.mako")
 9def dbtable(request):
10    # any code that prepares the data
11    # this time, the data have been loaded into context
12    return {}

That means that the approach described in custom renderers is not enough. We have to define a template system. Our renderer will have to lookup the template, render it, and return as an xlsx document.

Let's define the template interface. Our templates will be plain Python files placed into the project's xlsx subdirectory, with two functions defined:

  • get_header will return the table header cells

  • iterate_rows will yield the table rows

Our renderer will have to:

  • import the template

  • run the functions to get the data

  • put the data into an xlsx file

  • return the file

As our templates will be python files, we will use a trick. In the view_config we change the suffix of the template to .xlsx so that we can configure our view. In the renderer we look up that filename with the .py suffix instead of .xlsx.

Add the following code into a file named xlsxrenderer.py in your application.

 1import importlib
 2
 3import openpyxl
 4import openpyxl.styles
 5import openpyxl.writer.excel
 6
 7
 8class XLSXRenderer(object):
 9    XLSX_CONTENT_TYPE = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
10    def __init__(self, info):
11        self.suffix = info.type
12        self.templates_pkg = info.package.__name__ + ".xlsx"
13
14    def __call__(self, value, system):
15        templ_name = system["renderer_name"][:-len(self.suffix)]
16        templ_module = importlib.import_module("." + templ_name, self.templates_pkg)
17        wb = openpyxl.Workbook()
18        ws = wb.active
19        if "get_header" in dir(templ_module):
20            ws.append(getattr(templ_module, "get_header")(system, value))
21            ws.row_dimensions[1].font = openpyxl.styles.Font(bold=True)
22        if "iterate_rows" in dir(templ_module):
23            for row in getattr(templ_module, "iterate_rows")(system, value):
24                ws.append(row)
25
26        request = system.get('request')
27        if not request is None:
28            response = request.response
29            ct = response.content_type
30            if ct == response.default_content_type:
31                response.content_type = XLSXRenderer.XLSX_CONTENT_TYPE
32            response.content_disposition = 'attachment;filename=%s.xlsx' % templ_name
33
34        return openpyxl.writer.excel.save_virtual_workbook(wb)

Now you have a renderer. Let's register it with our application's Configurator:

config.add_renderer('.xlsx', 'myapp.xlsxrenderer.XLSXRenderer')

Of course, you need to modify the dotted-string to point to the module location you decided upon. You must also write the templates in the directory myapp/xlsx, such as myapp/xlsx/dbtable.py. Here is an example of a dummy template:

1def get_header(system, value):
2    # value is the dictionary returned from the view
3    # request = system["request"]
4    # context = system["context"]
5    return ["Row number", "A number", "A string"]
6
7def iterate_rows(system, value):
8    for row in range(100):
9        return [row, 100, "A string"]

To see a working example of this approach, visit:

There is a Czech version of this recipe here:

For more information on how to add custom renderers, see the following sections of the Pyramid documentation and Pyramid Community Cookbook: