python中导出功能

模板导出

文件中（word/excel）

在模板中使用jinja2模板语言取变量

模板中取变量:{{key}}
判断： {% if fwyt_id =='cyl_ncpjg' %}☑{% else %}□{% endif %}
图片： {% img variable %} ，同时单元格里需要插入一张图片
for循环：{% for item in items %}{{item.year5_tqjesjnhs}}{% endfor %}

python代码

excel

import os
from decimal import Decimal

from xltpl.writerx import BookWriter
import openpyxl


def export(data, src, dest, sheet_name="Sheet1", need_xh=False, del_col_list=None):
    """
    del_col_list:需要删除的列的下标列表 eg:[2,3]
    """
    if isinstance(data, list):
        data = {"items": data}
    # sheet名
    data.update({"sheet_name": sheet_name})
    # 将空的字段置为空字符串（不然在导出的excel中是null）
    data = {d: v if v is not None else '' for d, v in data.items()}
    # 列表字段添加序号
    items = data.get("items")
    if items:
        for index, item in enumerate(items, 1):
            if need_xh:
                item["xh"] = index
            # 把decimal的字段转换成字符串,以便在导出的文档保留原有的小数点
            for k, v in item.items():
                if isinstance(v, Decimal):
                    item[k] = str(v)
    data["items"] = items

    document = BookWriter(src)
    payloads = [data]
    document.render_book(payloads)
    if not os.path.exists(os.path.dirname(dest)):
        os.makedirs(os.path.dirname(dest))
    document.save(dest)
    if del_col_list:
        wb = load_workbook(dest)
        ws = wb[sheet_name]
        # 获取之前的格式, 清空格式
        col_style = {i: v.width for i, v in ws.column_dimensions.items()}
        row_height = ws.row_dimensions[1].height
        # 先将选项取出来 并清空
        # get_column_letter 将数字列转换为字符
        # column_index_from_string 将字母列转换为数字
        choices_list = []
        for items in ws.data_validations.dataValidation:
            for c in items.ranges:
                for c_num in range(c.min_col, c.max_col + 1):
                    choices_list.append({'col': c_num, 'val': items.formula1})
        ws.data_validations.dataValidation.clear()

        del_col_list.sort(reverse=True)

        rmv_list = list()
        for del_col in del_col_list:
            ws.delete_cols(del_col)
            # 将对应的格式删除
            col_style.pop(get_column_letter(del_col))
            # 将被删除的列对应的下拉选项放入清空列表，并将后边的顺延
            for i, v in enumerate(copy.deepcopy(choices_list)):
                if v.get('col') == del_col:
                    rmv_list.append(i)
                elif v.get('col') > del_col:
                    choices_list[i]['col'] -= 1
        # 将被删除的列对应的下拉选项清空
        for r in rmv_list:
            choices_list.pop(r)

        # 设置选项
        for choices in choices_list:
            # 创建DataValidation对象，并设置下拉选项的范围
            data_validation = DataValidation(type="list", formula1='%s' % choices.get('val'), allow_blank=True)
            ws.add_data_validation(data_validation)
            # 将DataValidation对象应用到指定的单元格
            data_validation.add("%(col)s2:%(col)s1048576" % {'col': get_column_letter(choices.get('col'))})

        # 设置格式
        col_style = {get_column_letter(list(col_style.keys()).index(col) + 1): weight for col, weight in
                     col_style.items()}

        for r in ws.iter_rows():
            for cell in r:
                if cell.row == 1:
                    continue
                else:
                    cell.value = cell.value
                    cell.style = 'Normal'
        ws.row_dimensions[1].height = row_height
        for col, weight in col_style.items():
            ws.column_dimensions[col].width = weight
        wb.save(dest)
    return True

word

import os

from django.utils.timezone import localtime
from docxtpl import DocxTemplate
from utils.exceptions import APIException



def export(src, dest, context, pdf=False):
    document = DocxTemplate(src)
    document.render(context)
    if not os.path.exists(os.path.dirname(dest)):
        os.makedirs(os.path.dirname(dest))
    document.save(dest)
    if pdf:
        pdf_path = os.path.dirname(dest)
        cmd = 'libreoffice  --display :0 --headless --invisible --convert-to  pdf  {}  --outdir  {} '.format(dest, pdf_path)
        p = subprocess.Popen(cmd, stderr=subprocess.PIPE, stdout=subprocess.PIPE, shell=True)
        p.wait(timeout=10)
        stdout, stderr = p.communicate()
        if not stderr:
            return os.path.join(pdf_path, os.path.basename(dest).replace('.docx', '.pdf'))
        raise APIException(message="转换失败,请联系管理员")
    return dest

excel常规导出

"""
导出excel，不通过模板 通过表头的方式
"""
from datetime import datetime, date
from decimal import Decimal
import os
from django.db import models
from django.utils.timezone import localtime
from openpyxl import Workbook
# choice的model 没有设置成None
from openpyxl.cell.cell import ILLEGAL_CHARACTERS_RE
from openpyxl.utils.exceptions import IllegalCharacterError
choice_model = None


def datetime_fmt(date_time, fmt='%Y-%m-%d %H:%M:%S'):
    if not date_time:
        return ''
    if isinstance(date_time, datetime):
        if date_time.tzinfo:
            r = localtime(date_time).strftime(fmt)
        else:
            r = date_time.strftime(fmt)
        return r
    else:
        return date_time


def get_model_choice(*mods):
    choice_dict = dict()  # 选项
    fk_list = list()  # 外键选项字段列表
    if choice_model:
        choice_dict['dict_choice'] = {c.code: c.name for c in choice_model.objects.all()}
    for m in mods:
        if not m:
            continue
        for f in m._meta.fields:
            if getattr(f, 'choices'):
                choice_dict[f.attname] = dict(f.choices)
            elif isinstance(f, models.ForeignKey) and choice_model:
                if f.remote_field.model == choice_model:
                    fk_list.append(f.attname)
    return choice_dict, fk_list


def export(dest: str,
           data: list,
           title: list,
           col: list,
           trans_dict: dict,
           trans_model: list,
           choice_col_split='pX$BP9',
           include_xh=True,
           t_fmt='%Y-%m-%d %H:%M:%S',
           float_round=None,
           float_round_dict=None):
    """
    dest: 目标文件
    data: 需要导出的数据 eg:[{"col1": 1, "col2: 2}, {"col1": 3, "col2": 4}]
    title: 表头 eg:["列1", "列2"]
    col: 和表头对应的字段名 eg:["col1", "col2"]
    trans_dict: 转换字典 eg: {"col1": {"1": '是', "2": '否'} }
    trans_model: 从model中的转换字典
    choice_col_split: 多选用choice_col_split隔开
    include_xh: 第一列是否为序号
    t_fmt: datetime格式化
    float_round: float保留几位小数
    float_round_dict: float保留几位小数 配置 和float_round搭配使用
                      eg:可以先把float_round保留2位  把需要保留4位小数的配置到loat_round_dict中
    """
    if not trans_dict:
        trans_dict = dict()
    choice_dict, fk_list = get_model_choice(*trans_model)
    trans_dict.update(choice_dict)
    if not os.path.exists(os.path.dirname(dest)):
        os.makedirs(os.path.dirname(dest))
    wb = Workbook()
    ws = wb.active
    for i, title in enumerate(title):
        ws.cell(row=1, column=i + 1).value = title
    row = 2
    start_col = 2 if include_xh else 1
    for j, item in enumerate(data):
        if include_xh:
            ws.cell(row=row, column=1).value = j + 1
        for k, value in enumerate(col, start=start_col):
            col_choices = value.split(choice_col_split)[-1]
            if col_choices in fk_list:
                item[value] = trans_dict.get('dict_choice').get(item.get(value))
            elif col_choices in trans_dict.keys():
                item[value] = trans_dict.get(col_choices).get(item.get(value))
            elif isinstance(item.get(value), Decimal):
                item[value] = str(item.get(value))
            elif isinstance(item.get(value), datetime):
                item[value] = datetime_fmt(item.get(value), fmt=t_fmt)
            elif isinstance(item.get(value), float):
                if float_round_dict:
                    if value in float_round_dict.keys():
                        item[value] = str(round(item.get(value), float_round_dict.get(value)))
                elif float_round:
                    item[value] = str(round(item.get(value), float_round))
            try:
                ws.cell(row=row, column=k).value = item.get(value)
            except IllegalCharacterError:
                ws.cell(row=row, column=k).value = ILLEGAL_CHARACTERS_RE.sub('', item.get(value))
        row = row + 1
    wb.save(dest)
    return True