python动态导出示例

from openpyxl import Workbook
import jsonpath
from openpyxl.utils import get_column_letter, column_index_from_string
from openpyxl.styles import Border, Side


def get_title():
    return {"facets": [{"facet_id": "1", "facet_name": "主流交付场景",
                        "cards": [{"card_id": "1", "card_name": "卡片1"},
                                  {"card_id": "2", "card_name": "卡片2"}]
                        },
                       {"facet_id": "2", "facet_name": "基本能力",
                        "cards": [{"card_id": "3", "card_name": "卡片3"},
                                  {"card_id": "4", "card_name": "卡片4"}
                                  ]}]}


def get_data():
    return {"results": [
        {"staff_id": "001", "staff_name": "张三", "facet1_card1": 1, "facet1_card2": 1, "facet2_card3": 1, "facet2_card4": 1},
        {"staff_id": "002", "staff_name": "李四", "facet1_card1": 0, "facet1_card2": 1, "facet2_card3": 0, "facet2_card4": 1},
        {"staff_id": "003", "staff_name": "王五", "facet1_card1": 1, "facet1_card2": 1, "facet2_card3": 0, "facet2_card4": 1}
        ]}


def export_demo():
    # 初始化
    wb = Workbook()
    # 创建一个新的sheet
    sheet = wb.active
    # 在这个新的sheet中第2行第2列的单元格写入222
    sheet['A1'] = '序号'
    sheet['B1'] = '工号'
    sheet['C1'] = '姓名'
    sheet.merge_cells('A1:A2')
    sheet.merge_cells('B1:B2')
    sheet.merge_cells('C1:C2')
    index_map = {}
    index_map[2] = "staff_id"
    index_map[3] = "staff_name"

    title1_index = 4
    title2_index = 4
    index_map_start = 4
    title = get_title()
    for index1, facet_item in enumerate(title['facets']):
        title1_length = jsonpath.jsonpath(facet_item, f'$.cards[*].card_name').__len__()
        merge_str = f'{get_column_letter(title1_index)}1:{get_column_letter(title1_index + title1_length - 1)}1'
        sheet.merge_cells(merge_str)
        sheet.cell(1, title1_index, facet_item["facet_name"])
        title1_index += title1_length

        for index2, card_item in enumerate(facet_item["cards"]):
            sheet.cell(2, title2_index, card_item["card_name"])
            index_map[index_map_start]=f'facet{facet_item["facet_id"]}_card{card_item["card_id"]}'
            index_map_start +=1
            title2_index += 1

    data = get_data()
    for row_index,row in enumerate(data["results"]):
        sheet.cell(row_index + 3, 1, row_index)
        for col_index in range(2,index_map_start):
            filed_name = index_map[col_index]
            print(filed_name)
            sheet.cell(row_index+3,col_index,row[filed_name])

    # 设置边框样式
    thin_border = Border(left=Side(style='thin'),
                         right=Side(style='thin'),
                         top=Side(style='thin'),
                         bottom=Side(style='thin'))
    # 设置单元格边框
    for row_index in range(1, sheet.max_row + 1):
        for col_index in range(1, sheet.max_column + 1):
            sheet[get_column_letter(col_index) + str(row_index)].border = thin_border


    # 保存本地excel文件中
    wb.save("test.xlsx")


if __name__ == '__main__':
    export_demo()