使用 zipfile、openpyxl、flask 批量导出excel zip

使用 zipfile、openpyxl、flask 批量导出excel zip

---

前言

 一、python 使用 openpyxl 操作 excel https://blog.csdn.net/mtl1994/article/details/123349397 

---

一、环境

1. python3.10 (https://docs.python.org/3/library/zipfile.html)

二、使用

1.压缩本地文件

with ZipFile('spam.zip', 'w') as myzip:
    myzip.write('1.xls')
    myzip.write('app.py')

2.压缩流文件到zip

xls_byte = io.BytesIO()
xls_tmp = load_workbook('1.xlsx')
xls_tmp.save(xls_byte)
with ZipFile('spam.zip', 'w') as myzip:
    myzip.writestr('1.xlsx', xls_byte.getvalue())
xls_byte.seek(0)

3.压缩目录下 pyc

project = PyZipFile("project.zip", mode='w')
project.writepy("d:/datasets-work")

4.openpyxl 生成多个excel， excel zipfile 压缩 flask接口 导出

@admin.route("/export_all_tmplate", methods=["GET"])
def export_all_tmplate():
    all_zip = BytesIO()
    zf = ZipFile(all_zip, 'w')
    for i in range(10):
        output = BytesIO()
        wb_tmp = load_workbook(f'tmp-{i}.xlsx')
        wb_tmp.save(output)
        """
        操作excel...
        """
        zf.writestr(f"tmp-{i}.xlsx", output.getvalue())
        output.seek(0)
    zf.close()
    all_zip.seek(0)
    resp = make_response(all_zip.getvalue())
    basename = 'all.zip'
    # 转码，支持中文名称
    resp.headers["Content-Disposition"] = "attachment; filename*=UTF-8''{utf_filename}".format(
        utf_filename=basename)
    resp.headers['Content-Type'] = 'application/zip'
    return resp

5.实例

@admin.route("/equipment_standing_book/basic/export_all_tmplate", methods=["GET"])
# @need_login
# @cache.cached(timeout=600, key_prefix=make_cache_key)
def equipment_standing_book_export_all_tmplate():
    """
    #group  导出接口
    #name   导出接口
    #desc   导出excel
    #param  tmp_name #模板名称
    #priv   need_login
    #return data    <dict>   各个字段的注释
    #example
    {
        "code": 0,
        "msg": "成功",
        "data":{}
    }
    """
    tmp_name = str(g.request_data.get("tmp_name", "")).strip()
    time = str(g.request_data.get("time", "")).strip()
    filename = time
    tmplate = equip_standing_config.query({'tmp_name': tmp_name})[0]
    format = "%Y"
    if tmplate['type'] == '3':
        format = "%Y-%m"
    all_zip = BytesIO()
    zf = ZipFile(all_zip, 'w')
    record_list = equip_standing_record.query(where=f" and config_id='{tmplate['id']}' and date_format(create_time, '{format}') = '{time}'")
    last_record_time = ""
    for ri, record in enumerate(record_list):
        output = BytesIO()
        #如果相同日期合并到一个excel，多sheet
        if last_record_time != "" and last_record_time == record["create_time"].strftime('%Y-%m-%d'):
            ws = wb_tmp["next"]
            ws.title = record['code'].replace(":", "")
            target = wb_tmp.copy_worksheet(wb_tmp.active)
            target.title = "next"
        else:
            wb_tmp = load_workbook(f'./src/statics/{tmp_name}.xlsx')
            ws = wb_tmp[wb_tmp.sheetnames[0]]
            ws.title = record['code'].replace(":", "")
            target = wb_tmp.copy_worksheet(wb_tmp.active)
            target.title = "next"
        detail_list = eval(tmp_name).query({'record_id': record['id']})
        obj: {} = eval(tmp_name).get_dict()
        start, start_row, points = 0, [], {}
        #找到从哪一行赋值 excel中 配置和实体类对应的字段
        for i, row in enumerate(ws.values):
            print(type(row))
            start_row = row
            for j, value in enumerate(row):
                print(i,j,value)
                if value in obj.keys():
                    points[j] = value
                    start = i
                if value == 'serial_number':
                    points[j] = 'serial_number'
                    start = i
            if start > 0:
                break
        ws.delete_rows(start + 1)
        ws.insert_rows(start + 1, len(detail_list))
		#赋值
        for i, obj in enumerate(detail_list):
            for point in points.keys():
                cell = ws.cell(row=start + 1, column=point + 1)
                cell.value = i if points[point] == 'serial_number' else obj[points[point]]
                cell.border = Border(top=Side(border_style='thin', color='000000'),
                        bottom=Side(border_style='thin', color='000000'),
                        left=Side(border_style='thin', color='000000'),
                        right=Side(border_style='thin', color='000000'))
            start += 1
        #如果当前行和上一行相同，合并
        for i in range(start + 1, ws.max_row + 1):
            ws.merge_cells(start_row=i, start_column=1, end_row=i, end_column=ws.max_column)

        if (ri + 1 < len(record_list) and record_list[ri + 1]["create_time"].strftime('%Y-%m-%d') != record_list[ri]["create_time"].strftime('%Y-%m-%d'))\
                or (last_record_time != "" and last_record_time != record["create_time"].strftime('%Y-%m-%d')):
            wb_tmp.remove(wb_tmp["next"])
            wb_tmp.save(output)
            zf.writestr(record['create_time'].strftime('%Y-%m-%d') + ".xlsx", output.getvalue())
            output.seek(0)

        last_record_time = record["create_time"].strftime('%Y-%m-%d')
    zf.close()
    all_zip.seek(0)
    resp = make_response(all_zip.getvalue())
    basename = f'{filename}.zip'

    # 转码，支持中文名称
    resp.headers["Content-Disposition"] = "attachment; filename*=UTF-8''{utf_filename}".format(
        utf_filename=basename)
    resp.headers['Content-Type'] = 'application/zip'
    return resp

---

总结

~~~