Django 中操作 Excel 实现基于 Excel 的报表方式

Question

在 Django 中我需要实现报表数据的生成与打印。 Django 中报表的实现不外有几种：

• 生成网页页面模板的方式
  • 优点：这种方式不需要安装任何第三方库或控件
  • 缺点：模板格式不方便制作、调整
• 基于 reportlab 的方式生成 pdf 报表
  • 优点：生成的报表不会被修改
  • 缺点：需要安装第三方库支持；需要使用命令来绘制报表，不直观，入门门槛高
• 基于 Excel 来制作报表
  • 优点：模板格式方便制作、调整；格式美观；入门门槛低
  • 缺点：客户端需要安装 Excel ，需要安装第三方库支持

由于我们要做的是数据型的报表，使用 Excel 方式是一个最佳的选择。

生成报表的基本流程

基于 Excel 模板的报表方式处理的基本流程：

• 读取 Excel 文件
• 读取数据表的数据，对应填充到模板的相应单元格
• 发送修改后的 Excel 文件到下载
• 在客户端用 Excel 打开报表文件进行打印

基本流程有了，第三方库也找了不少进行了测试，但还有问题

不少操作 Excel 的第三方库，读和写是独立的，即：

• 读操作的只能读 Excel 文件，并读取工作簿、工作表、单元格的相关信息
• 写操作的只能创建新的 Excel 文件进行相关的写操作及保存

几经测试及咨询必应，终于找到了 xlutils 库，可以有机的把 xlrd 和 xlwt 结合起来。

使用到的第三方库

这里我们需要用到的第三方库是 xlutils ，它依赖于 xlrd 和 xlwt ，所以 pip 安装后自然会一起安装后面两个库。

pip install xlutils

废话不说，直接上代码最明白：

rb = xlrd.open_workbook(filename)
wb = xlutils.copy.copy(rb)

这里用了 xlrd.open_workbook 来打开一个文件，
再用 xlutils.copy.copy 将 xlrd 读出来的文件复制到 xlwt 的一个 Workbook 对象实例中，
上面的 wb 就是一个 xlwt 的 Workbook 实例。

我们上面说了 xlwt 的 Workbook 是可以修改、保存的，
那么基于 wb 我们就可以操作 Excel 内容的修改，
然后 wb.save(filename) 加以保存。

但是我们是要让修改后的文件直接下载的，所以我们要加上一个页面的 head 再保存到输出，就可以实现下载了。

完整示例

先给出一个完整的示例，再解释一下其中要点。

import os
from django.http import HttpResponse
from xlrd import open_workbook
from xlutils.copy import copy
from django.conf import settings
from django.shortcuts import render, get_object_or_404
from .models import *

def get_dispatch_report(request, id):
  """根据给出的派工单记录号从派工单模板生成相应的打印格式的报表并下载。"""
  rec = get_object_or_404(Dispatch, pk=id)
  rt = os.path.join(settings.MEDIA_ROOT, 'templates/bill/dispatch.xls')  # 报表模板文件位置
  rb = open_workbook(rt, formatting_info=True, encoding_override='utf_8')  # 带格式和字符集读入文件，后面两个参数很关键
  wb = copy(rb)  # 复制到 xlwt 的工作簿，用来修改单元格数据，生成实际数据表格
  # 通过 wb 修改工作簿内容
  # ...
  # 指定文件名并直接输出 Excel 文件到下载
  response = HttpResponse(content_type='application/vnd.ms-excel')
  response['Content-Disposition'] = 'attachment; filename="{}.xls"'.format(rec.bill_no)
  wb.save(response)
  return response

相关要点

这其中有三个要点需要注意：

• 实现修改后的 Excel 文件的下载
• 直接指定下载文件要保存的文件名
• 确保下载的文件格式与模板一致

实现下载

要实现下载只要在输出的前面加上一个 head 头就可以了，这里的实现是

response = HttpResponse(content_type='application/vnd.ms-excel')
response['Content-Disposition'] = 'attachment; filename="{}.xls"'.format(rec.bill_no)
wb.save(response)
return response

• 第一行指明输出为 html 的内容输出，类型为微软的 Excel 文件类型
• 第二行指明这个输出的是一个附件，文件名是指定的那个
• 第三行输出内容
• 第四行返回输出

很好理解！

实现下载的文件为指定的文件名

看上述代码的第二行，指定 filename 即可，这里我们将它指定为 bill_no 字段的内容。

确保下载的 Excel 文件格式与模板一致

为什么要有这一说呢，因为我实现了下载后发现下载下来的文件，虽然里面的数据与模板一样，但是居然：

没有格式，没有格式，没有格式！

为什么呀？？回到原来的代码，是这样的：

rb = xlrd.open_workbook(filename)
wb = xlutils.copy.copy(rb)

几经测试，必应，终于发现是在于 open_workbook 的问题，添加了 formatting_info=True 参数，

耶，带出格式了，

但是…，但是整个表格看着就是与模板文件不一样，

再查，再查，又添加了 encoding_override='utf_8' ，原来是字符集不一致搞的鬼，这下，终于一个样了～～

接下来，继续实现 Excel 文件数据的修改操作

修改单元格数据

上面我们得到了一个 wb ，这是个 Workbook ，下面来取得一个 Worksheet ：

ws = wb.get_sheet(0)  # 第一张工作表

这里就可以用

worksheet.write(r,c,value,style)

的方式来对指定行列 r,c 的单元格写入数据和设置格式

但是坑爹的是，如果你不指定格式，它就会用一个默认的格式设置它。

拜托，我是不想改到格式好不好。

各种方式的试，终于在 stackoverflow 找到
一个解决方法。这个网站不错呀，好多问题都在这里找到了解决方案。

用一个函数来扩展 worksheet.write() 的功能

函数的功能大概就是修改单元格数据前先保存一下这个单元格的数据？设置？

然后修改，

修改完后看看这个单元格原来是一个全新的空单元格还是有保存数据？做过设置？

如果有做过设置，那就恢复设置到修改后的单元格上。

def setOutCell(outSheet, row, col, value):
  """ Change cell value without changing formatting.
   修改单元格数据而不改变单元格格式。
  """
  def _getOutCell(outSheet, rowIndex, colIndex):
    """ HACK: Extract the internal xlwt cell representation. """
    row = outSheet._Worksheet__rows.get(rowIndex)
    if not row:
      return None
    cell = row._Row__cells.get(colIndex)
    return cell

  # HACK to retain cell style.
  previousCell = _getOutCell(outSheet, row, col)
  # END HACK, PART I
  outSheet.write(row, col, value)
  # HACK, PART II
  if previousCell:
    newCell = _getOutCell(outSheet, row, col)
    if newCell:
      newCell.xf_idx = previousCell.xf_idx
  # END HACK

def get_dispatch_report(request)
  ...
  ws = wb.get_sheet(0)  # 第一张工作表
  # 填充数据到单元格
  setOutCell(ws, 3, 2, rec.dispatcher)  # C4：派发人
  setOutCell(ws, 3, 4, rec.bill_no)  # E4：编号
  ...