本文将介绍如何在Django 框架中方便的在 Admin 界面加入 Excel导出功能。
1. 安装依赖
本文中的 Excel 文件的生成模块为 xlwt
pip install xlwt
2. 构建通用的 Excel 文件返回函数
import datetime
import xlwt
from django.http import FileResponse
def file_iterator(filename, chuck_size=512):
"""
将文件分块返回
:param filename: 文件名称
:param chuck_size: 块的大小,默认 512
:return: 文件以可迭代对象的方式分块返回
"""
with open(filename, "rb") as f:
while True:
c = f.read(chuck_size)
if c:
yield c
else:
break
def export_excel(queryset, headers, columns, filename='file_name', choices_fields_value=None):
"""
通过传递进去的数据构建 Excel 文件,并且以数据流的返回返回文件
:param queryset: 数据列表,通常为筛选后的结果。
:param headers:
Excel 的表头,以列表的方式传入。
例如: ['ID', '姓名', '手机号', '金额变更', '变更后余额']
:param columns:
数据列的名称,以列表的方式传入。
例如:['pk', 'customer__name', 'customer__mobile', 'amounts', 'current_amounts']
:param filename: 返回文件的文件名,必须以 .xls 为后缀
:param choices_fields_value:
包含 choices 的字段。
例如:{'status': [('1', '未到货'), ('2', '部分到货'), ('3', '已到货')]}
:return:
"""
wb = xlwt.Workbook()
sheet = wb.add_sheet("data")
choice_fields_dict = dict()
if choices_fields_value:
for field, choices in choices_fields_value.items():
choice_dict = dict()
for c in choices:
choice_dict[c[0]] = c[1]
choice_fields_dict[field] = choice_dict
for i, h in enumerate(headers):
sheet.write(0, i, h)
cols = 1
for query in queryset.values(*columns):
for i, k in enumerate(columns):
v = query.get(k)
if isinstance(v, datetime.datetime):
v = v.strftime('%Y-%m-%d %H:%M:%S')
if isinstance(v, datetime.date):
v = v.strftime('%Y-%m-%d')
if k in choice_fields_dict:
v_display = choice_fields_dict.get(k).get(v)
if v_display:
v = v_display
sheet.write(cols, i, v)
cols += 1
wb.save(filename)
response = FileResponse(file_iterator(filename))
response['Content-Type'] = 'application/vnd.ms-excel'
response['Content-Disposition'] = 'attachment; filename={0}'.format(filename)
return response
3. 在 Admin 模块创建 action
例如我有一个 Model,如下所示
class AmountChangeRecord(models.Model):
"""
余额变更记录
"""
customer = models.ForeignKey(
Customer,
null=True,
on_delete=models.CASCADE,
verbose_name=_('关联客户')
)
amounts = models.DecimalField(
verbose_name=_('金额变更'), max_digits=10, decimal_places=2, null=True)
current_amounts = models.DecimalField(
verbose_name=_('变更后余额'), max_digits=10, decimal_places=2, null=True, blank=True)
notes = models.TextField(_('备注'), max_length=1000, null=True, blank=True)
created_by = models.ForeignKey(
WxUser,
null=True,
blank=True,
on_delete=models.SET_NULL,
related_name='amount_change_record_created_by',
verbose_name=_('创建人员')
)
confirmed_by = models.ForeignKey(
WxUser,
null=True,
blank=True,
on_delete=models.SET_NULL,
related_name='amount_change_record_confirmed_by',
verbose_name=_('审核人员')
)
datetime_created = models.DateTimeField(_('记录时间'), auto_now_add=True)
datetime_updated = models.DateTimeField(_('更新时间'), auto_now=True)
objects = models.Manager()
class Meta:
ordering = ['-id']
verbose_name = _('余额变更记录')
verbose_name_plural = _('余额变更记录')
def __str__(self):
return "{0} {1} {2}".format(
self.customer,
self.amounts,
self.notes,
)
原本的 Admin 模块如下
@admin.register(AmountChangeRecord)
class AmountChangeRecordAdmin(AutoUpdateUserModelAdmin):
readonly_fields = ['current_amounts', 'created_by', 'confirmed_by', 'datetime_created', 'datetime_updated']
list_display = [
'pk', 'customer', 'amounts', 'current_amounts', 'notes',
'created_by', 'confirmed_by', 'datetime_created', 'datetime_updated']
list_display_links = ['pk', 'customer', 'amounts', 'current_amounts', 'notes']
search_fields = ['customer__name', 'customer__mobile']
autocomplete_fields = ['customer']
fieldsets = (
(_('基础信息'), {'fields': ('customer', 'amounts', 'current_amounts', 'notes')}),
(_('操作记录'), {'fields': ('created_by', 'confirmed_by', 'datetime_created', 'datetime_updated')})
)
在刚才的 AmountChangeRecordAdmin 模块下面加入以下代码
def save_execl(self, request, queryset):
filename = 'media/{0}_{1}.xls'.format('amounts', datetime.datetime.now().strftime('%Y%m%d%H%M%S'))
headers = [
'ID', '姓名', '手机号', '金额变更', '变更后余额', '创建人员', '最后变更人员', '创建日期', '最后更新时间']
columns = [
'pk', 'customer__name', 'customer__mobile', 'amounts', 'current_amounts',
'created_by__full_name', 'confirmed_by__full_name', 'datetime_created', 'datetime_updated']
return export_excel(queryset, headers, columns, filename)
save_execl.short_description = "导出Excel"
actions = [save_execl]
此时 Excel 导出功能已经开发完成,Admin页面的动作里面会有一项【导出 Excel】功能。
不过有一点你需要注意一下,通过此方法构建的 Excel 导出功能只有在勾选了数据以后才能导出数据。