XLSRenderer

[cescp]

Hi, In order to render as XLS file, the following code could be used: renderers.py

import xlwt
from datetime import datetime, date

class XLSRenderer(CSVRenderer):
    media_type = 'application/ms-excel'
    format = 'xls'

    def render(self, data, media_type=None, renderer_context=None, sheetname='First'):       
        table = self.tablize(data)
        wb = self.to_workbook(table, sheetname=sheetname)
        return wb

    # source: http://fragmentsofcode.wordpress.com/2009/10/09/xlwt-convenience-methods/
    def to_workbook(self, tabular_data, workbook=None, sheetname=None):
        """
        Returns the Excel workbook (creating a new workbook
        if necessary) with the tabular data written to a worksheet
        with the name passed in the 'sheetname' parameter (or a
        default value if sheetname is None or empty).
        """
        wb = workbook or xlwt.Workbook(encoding='utf8')
        if len(sheetname)>31:
            sheetname = sheetname[:31]
        ws = wb.add_sheet(sheetname or 'Data')
        self.to_worksheet(tabular_data, ws)
        return wb


    def to_worksheet(self, tabular_data, worksheet):
        """
        Writes the tabular data to the worksheet (returns None).
        Thanks to John Machin for the tip on using enumerate().
        """

        default_style = xlwt.Style.default_style
        datetime_style = xlwt.easyxf(num_format_str='dd/mm/yyyy hh:mm')
        date_style = xlwt.easyxf(num_format_str='dd/mm/yyyy')

        for row, rowdata in enumerate(tabular_data):
            worksheet_row = worksheet.row(row)
            for col, val in enumerate(rowdata):
                if isinstance(val, datetime):
                    val = val.replace(tzinfo=None)
                    style = datetime_style
                elif isinstance(val, date):
                    style = date_style
                else:
                    style = default_style

                worksheet_row.write(col, val, style=style) 

A usage example to export several items from admin: admin.py

from datetime import datetime
from django.utils.translation import ugettext_lazy as _

@admin.register(MyModel)
class MyModelAdmin(admin.ModelAdmin):
    model = MyModel
    actions = ['export_xls_from_admin_view']

    def export_xls_from_admin_view(self, request, queryset):
        serializer = MyModelSerializer(queryset, many=True)
        wb = XLSRenderer().render(serializer.data, sheetname='MyModel')

        now = datetime.now()
        filename = "mymodel_%s.xls" % (now.strftime('%Y%m%d-%H%M%S'))

        # Django < 1.7
        #response = HttpResponse(mimetype="application/ms-excel")
        # Django 1.7
        response = HttpResponse(content_type="application/ms-excel")
        response['Content-Disposition'] = 'attachment; filename=%s' % filename
        wb.save(response)

        return response
    export_xls_from_admin_view.short_description = _("Export selected items to XLS")

Francesc

[santiagomalter]

I would be very interested by a native XLS renderer, is it somehow planned? :)

Thanks for the work anyway!