Django中利用xlwt将Model中的数据写入excel表格

django 导出数据库中的数据到Excel表格,这里主要用xlwt将模型数据写入到excel。福利-福利-福利,下面是通用代码。代码放在单独一个文件中即可随时调用。

浏览器中 Content-Disposition 的问题,这里下载文件没法实现中文文件名的下载,会出错。

# -*- coding: utf-8 -*-
from __future__ import unicode_literals
from collections import OrderedDict
from itertools import chain
from decimal import Decimal
from io import BytesIO
import xlwt
from django.contrib.admin.utils import label_for_field
from django.utils import formats, timezone
from django.db import models
from django.utils.encoding import force_text
from django.http import StreamingHttpResponse, HttpResponse


def fields_for_model(model, fields=None, exclude=None):
    """
    返回一个model类`字段名`和`字段`组成的元组列表。
    """
    field_list = []
    opts = model._meta
    for f in sorted(chain(opts.concrete_fields, opts.many_to_many)):
        if fields is not None and f.name not in fields:
            continue
        if exclude and f.name in exclude:
            continue
        else:
            field_list.append((f.name, f))
    field_dict = OrderedDict(field_list)
    return field_dict

	
def display_for_value(value):
    """
    返回一个比较人性化的值。
    """
    if value is None:
        return ''
    elif isinstance(value, bool):
        return force_text(value)
    elif isinstance(value, (int, Decimal, float)):
        return formats.number_format(value)
    elif isinstance(value, models.query.QuerySet):
        return ', '.join(force_text(v) for v in [ force_text(i) for i in value ])
    elif isinstance(value, (list, tuple)):
        return ', '.join(force_text(v) for v in value)
    else:
        return force_text(value)

		
def display_for_field(value, field, empty_value_display):
    """返回某个模型字段的一个字段值"""
    if getattr(field, 'flatchoices', None):
        return dict(field.flatchoices).get(value, empty_value_display)
    elif isinstance(field, (models.BooleanField, models.NullBooleanField)):
        boolchoice = {False: "否", True: "是"}
        return boolchoice.get(value)
    elif value is None:
        return empty_value_display
    elif isinstance(field, models.DateTimeField):
        return formats.localize(timezone.template_localtime(value))
    elif isinstance(field, (models.DateField, models.TimeField)):
        return formats.localize(value)
    elif isinstance(field, models.DecimalField):
        return formats.number_format(value, field.decimal_places)
    elif isinstance(field, (models.IntegerField, models.FloatField)):
        return formats.number_format(value)
    elif isinstance(field, models.ForeignKey) and value:
        rel_obj = field.related_model.objects.get(pk=value)
        return force_text(rel_obj)
    else:
        return display_for_value(value)
        
def make_to_excel(object_list=None):
    """
    传入某个模型的查询集(queryset)
    将查询集写入excel表格并响应以附件返回
    """
    body_style = xlwt.XFStyle()
    ''' set borders '''
    borders = xlwt.Borders()
    borders.left = 1
    borders.right = 1
    borders.top = 1
    borders.bottom = 1
    ''' set title cell font '''
    font = xlwt.Font()
    font.bold = True
    ''' set title cell backgroup '''
    ''' 
    May be: 8 through 63. 0 = Black, 1 = White, 2 = Red, 3 = Green,
    4 = Blue, 5 = Yellow, 6 = Magenta, 7 = Cyan, 16 = Maroon,
    17 = Dark Green, 18 = Dark Blue, 19 = Dark Yellow , almost brown),
    20 = Dark Magenta, 21 = Teal, 22 = Light Gray, 23 = Dark Gray,
    '''
    pattern = xlwt.Pattern()
    pattern.pattern = xlwt.Pattern.SOLID_PATTERN
    pattern.pattern_fore_colour = 22
    title_style = xlwt.XFStyle()
    title_style.borders = borders
    title_style.font = font
    title_style.pattern = pattern
    body_style = xlwt.XFStyle()
    body_style.borders = borders
    if object_list is not None:
        model_name = object_list.model._meta.model_name
        wb = xlwt.Workbook(encoding='utf-8')
        ws = wb.add_sheet('{0}List'.format(model_name.capitalize()))
        model = object_list.model
        fields = fields_for_model(model)
        field_names = []
        field_verboses = []
        for attname, field in fields.items():
            field_names.append(attname)
            fv = label_for_field(attname, model)
            field_verboses.append(fv)
        for col in range(len(field_verboses)):
            ws.write(0, col, field_verboses[col], title_style)
        row = 1
        for obj in object_list:
            for index, field_name in enumerate(field_names):
                field = model._meta.get_field(field_name)
                value = field.value_from_object(obj)
                cell_value = display_for_field(value, field, empty_value_display='N/A')
                ws.write(row, index, cell_value, body_style)
            row += 1
        output = BytesIO()
        wb.save(output)
        output.seek(0)
        response = StreamingHttpResponse(output)
        response['content_type'] = 'application/octet-stream'
        response['charset'] = 'utf-8'
        response['Content-Disposition'] = 'attachment; filename="{0}{1}.xls"'.format(
            model_name.capitalize(), timezone.datetime.now().strftime('%Y%m%d%H%M'))
        return response

"""
eg:

`views:`

def download(request):
    objects = User.objects.all() # queryset.
    return make_to_excel(objects) #return

"""

来看下我某个模型导出的结果吧:

image.png

相关阅读RelatedRead

从wordpress到Django

Django App通用admin.py

Windows批处理批量删除redis键值

无心朝政,列表功能分析下

暂无评论添加评论