Django 中基于 openpyxl 库实现以 Excel 文件为模板生成报表的方式

发布于 2024-06-28 22:39:37 字数 6744 浏览 14 评论 0

与 xlutils 库实现对 Excel 文件的读写比较：

xlutils 只支持到 Excel2003 的格式；而 openpyxl 支持 Excel2007 格式。
xlutils 通过复制的方式来实现读写操作，复制过程中 Excel 页面设置等信息丢失了；而 openpyxl 可以读出文件直接写操作，再保存（另存或输出），页面设置等设置保留。

实际测试过程中只发现 openpyxl 读取操作另存后边框格式不正常，其它如页面设置、行高、列宽、单元格除边框外的其它格式都保持正常，所以做为原模板文件不能设置边框格式，应该在读取文件后再把边框手工再绘制一遍。

原模板文件不能设置边框格式，否则下面程序会出错，无解～～

暂不说明，先上代码

文件： app/utils.py

import os
from django.http import HttpResponse
from openpyxl.writer.excel import save_virtual_workbook
from openpyxl import load_workbook
from openpyxl.styles import Border, Side

class XlsxReport(object):
    """采用 openpyxl 操作 Excel，基于模板文件生成报表。
    new_cells_data 的数据结构是：
    [...
        (A1, value, border_style),
    ...]
    A1, value, border_style 对应的是要修改的单元格的行/列，和要修改的值和边框样式
    A1：单元格范围，如 B3 的单元格 或 C4:D5 的范围，范围会被合并单元格
    value： 如果为 None 则不修改单元格的值
    border_style： 可以没 “ltrb” 的其中一个或多个，表示要绘制左 上 右 下边框
    ***注意***： 模板文件的边框要设为无，不然会出错
    """
    def __init__(self, templat_file, new_cells_data=list(), download_file='report'):
        self.template = templat_file
        self.new_cells_data = new_cells_data
        self.download_file = download_file

        if not os.path.exists(self.template):
            raise '找不到模板文件！'

        self.wb = load_workbook(self.template, keep_vba=True)
        self.act_ws = self.wb.active

        for cell, value, style in new_cells_data:
            if value is not None:
                self.act_ws[cell.split(':')[0]] = value
            set_border(self.act_ws, cell, str2border(style))

    def download_response(self):
        """返回一个用于下载的输出"""
        response = HttpResponse(content=save_virtual_workbook(self.wb),
                                content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
        response['Content-Disposition'] = 'attachment; filename="{}.xlsx"'.format(self.download_file)
        return response


def str2border(ltrb: str):
    """根据给出的边框设置串生成边框样式。
    ltrb：边框设置串，可以没 “ltrb” 的其中一个或多个，表示要绘制左 上 右 下边框"""
    ltrb = ltrb.lower().strip()
    thin = Side(border_style='thin', color='000000')
    none = Side(border_style=None)
    return Border(left=thin if 'l' in ltrb else none,
                  top=thin if 't' in ltrb else none,
                  right=thin if 'r' in ltrb else none,
                  bottom=thin if 'b' in ltrb else none)


def set_border(ws, cell_range, border=Border()):
    """对单元格或合并单元格设置边框。
    参数：
    ws： 工作表
    cell_range：单元格或单元格范围 B3 或 C5:E8 的形式
    border：边框样式
    ***注意***： ws 的边框必须设为无才不会出错，
    ***所以如果 ws 是从模板读来的，记得模板的边框要取消***
    """
    if len(cell_range.split(':')) == 1:
        # 单个单元格
        cell = ws[cell_range]
        cell.border = border
    else:
        top = Border(top=border.top)
        left = Border(left=border.left)
        right = Border(right=border.right)
        bottom = Border(bottom=border.bottom)

        if cell_range not in ws.merged_cell_ranges:
            ws.merge_cells(cell_range)

        rows = ws[cell_range]
        for cell in rows[0]:
            cell.border = cell.border + top  # 如果 cell 的边框默认不为无的话，在这里会出错
        for cell in rows[-1]:
            cell.border = cell.border + bottom

        for row in rows:
            l = row[0]
            r = row[-1]
            l.border = l.border + left
            r.border = r.border + right

"""
在实际的测试中发现，从模板中读出文件，工作簿、工作表、单元格的设置
及单元格内容基本正常，独边框出现了异常，模板绘制好的边框读出来后再保存，
边框会有缺失，而带有边框的情况下设置边框又会出错，所以模板只能：
1、调试好所有格式并打印无误
2、取消模板边框
3、在代码中生成数据及绘制边框
可以通过以下方式读取模板文件，生成数据及绘制边框，并提供报表下载

from .utils import XlsxReport
temp_file = os.path.join(settings.MEDIA_ROOT, 'templates/bill/dispatch.xlsx')  # 报表模板文件位置
a1_values = [
    ('E4:F4', '123456', ''),  # E4:F4：编号
    ('A5', None, 'ltrb'),  # 标题
    ('B5:C5', '信息班', 'ltb'),  # B5:C5：班组
]
xlreport = XlsxReport(temp_file, a1_values, 'report')
return xlreport.download_response()

# ('B5:C5', '信息班', 'ltb'), 分别代表要操作的单元格，要填充的数据及要绘制的表格
"""

文件： app/views.py

import os
from django.contrib.auth.decorators import login_required
from django.conf import settings
from django.shortcuts import get_object_or_404
from .models import *
from .utils import XlsxReport

# Create your views here.


@login_required(login_url='admin:login')
def get_dispatch_report(request, id):
    temp_file = os.path.join(settings.MEDIA_ROOT, 'templates/bill/dispatch.xlsx')  # 报表模板文件位置

    rec = get_object_or_404(Dispatch, pk=id)
    down_file = rec.bill_no  # 下载文件的默认文件名

    a1_values = [
        ('C4', rec.dispatcher, ''),  # C4：派发人
        ('E4:F4', rec.bill_no, ''),  # E4:F4：编号
        ('A5', None, 'ltrb'),  # 标题，目的是只绘制边框，不修改单元格内容
        ('B5:C5', rec.team, 'ltb'),  # B5:C5：班组
        ('D5', None, 'ltrb'),  # 标题
        ('E5:F5', rec.controller, 'ltrb'),  # E5:F5：负责人
        ('A6', None, 'ltrb'),  # 标题
        ('B6:E6', rec.team_members, 'ltb'),  # B6:E6：成员
        ('F6', '共 %s 人' % rec.total_members, 'trb'),  # F6：人数
        ('A7:A8', None, 'ltrb'),  # 标题
        ('B7:C7', rec.planned_time_start.strftime('自%Y 年%m 月%d 日%H 时%M 分'), 'lt'),  # B7:C7：开始时间
        ('B8:C8', rec.planned_time_end.strftime('至%Y 年%m 月%d 日%H 时%M 分'), 'lb'),  # B8:C8：结束时间
        ('D7:D8', None, 'ltrb'),  # 标题
        ('E7:F8', rec.workplace, 'ltrb'),  # E7:F8：地点
        ('A9', None, 'ltrb'),  # 标题
        ('B9:F9', rec.tasking, 'ltrb'),  # B9:F9：任务
        ('A10', None, 'ltrb'),  # 标题
        ('B10:F10', '；'.join([('[x] ' if key == rec.worktype else '[ ] ') + value for key, value in WORKTYPE]), 'ltRb'),
        # B10:F10：类型
        ('A11:F11', None, 'ltr'),  # 标题
        ('A12:F12', rec.attentions, 'lrb'),  # A12:F12：措施
        ('A13:F13', None, 'ltr'),  # 标题
        ('A14:F14', None, 'lrb'),  # 标题
    ]

    xlreport = XlsxReport(temp_file, a1_values, down_file)

    return xlreport.download_response()