Skip to content

Instantly share code, notes, and snippets.

@leisurelicht
Last active February 13, 2024 16:53
Show Gist options
  • Save leisurelicht/f2b0348c28cbd348ec04748ec7448235 to your computer and use it in GitHub Desktop.
Save leisurelicht/f2b0348c28cbd348ec04748ec7448235 to your computer and use it in GitHub Desktop.
Export queryset to Excel workbook
from django.http import HttpResponse
from .utils import queryset_to_workbook

def download_workbook(request):
    queryset = User.objects.all()
    columns = (
        'first_name',
        'last_name',
        'email',
        'is_staff',
        'groups')
    workbook = queryset_to_workbook(queryset, columns)
    response = HttpResponse(mimetype='application/vnd.ms-excel')
    response['Content-Disposition'] = 'attachment; filename="export.xls"'
    workbook.save(response)
    return response

utils.py

import datetime

import xlwt
from django.core.exceptions import ObjectDoesNotExist
from django.forms.forms import pretty_name

HEADER_SYTLE = xlwt.easyxf('font:bold on')
DEFAULT_STYLE = xlwt.easyxf()
CELL_STYLE_MAP = (
    (datetime.datetime, xlwt.easyxf(num_format_str="YYYY/MM/DD HH:MM")),
    (datetime.date, xlwt.easyxf(num_format_str='DD/MM/YYYY')),
    (datetime.time, xlwt.easyxf(num_format_str="HH:MM")),
    (bool, xlwt.easyxf(num_format_str="BOOLEAN")),
)


def multi_getattr(obj, attr, default=None):
    attributes = attr.split(".")

    for i in attributes:
        try:
            if obj._meta.get_field(i).choices:
                obj = getattr(obj, f"get_{i}_display")()
            else:
                obj = getattr(obj, i)
        except AttributeError:
            if default:
                return default
            else:
                raise

    return obj


def get_column_head(obj, name):
    names = name.split(".")

    tmp = ''

    for i in names:
        tmp += obj._meta.get_field(i).verbose_name
        tmp += '.'


return pretty_name(tmp)


def get_column_cell(obj, name):
    try:
        attr = multi_getattr(obj, name)
    except ObjectDoesNotExist:
        return None

    if hasattr(attr, '_meta'):
        return str(attr).strip()
    elif hasattr(attr, 'all'):
        return ', '.join(str(x).strip() for x in attr.all())

    if isinstance(attr, datetime.datetime):
        from django.utils.timezone import localtime
        attr = localtime(attr)
        attr = attr.replace(tzinfo=None)

    return attr


def queryset_to_workbook(queryset,
                         columns,
                         header_style=HEADER_SYTLE,
                         default_style=DEFAULT_STYLE,
                         cell_style_map=CELL_STYLE_MAP):
    workbook = xlwt.Workbook()
    report_date = datetime.date.today()
    sheet_name = f"Export {report_date.strftime('%Y-%m-%d')}"
    sheet = workbook.add_sheet(sheet_name)

    obj = queryset.first()

    for num, column in enumerate(columns):
        value = get_column_head(obj, column)
        sheet.write(0, num, value, header_style)

    for x, obj in enumerate(queryset, start=1):
        for y, column in enumerate(columns):
            value = get_column_cell(obj, column)
            style = default_style

            for value_type, cell_style in cell_style_map:
                if isinstance(value, value_type):
                    style = cell_style

                    break
            sheet.write(x, y, value, style)

    return workbook
@leisurelicht
Copy link
Author

do this : from django.forms.utils import pretty_name instead of this from django.forms.forms import pretty_name if you are using django 4

thank you, but I have not used django for a long time.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment