Python脚本 - 多表提取列合并到同一Excel

日常工作中想将几个Excel中包含同一列的字段整合到一起，以下Python脚本实现了，按要求取对应的列名，合并到同一个Excel中，参考如下：
最新版本代码：可以根据汇总表的文件名称去适配原表中的字段名称。
准备：需要一个Excel模板，包含预置的几个sheet页，具体的每个sheet页的名称需符合脚本SHEETLIST中的命名。

#!/usr/bin/python3.8
#coding:utf-8

import os
import sys
import logging
import openpyxl
from openpyxl import Workbook
from openpyxl import load_workbook
from openpyxl.utils import get_column_letter, column_index_from_string
from openpyxl.styles import Font, PatternFill, Color, colors
from openpyxl.styles import Side, Alignment, Border


SHEETLIST = [
    '源数据-L2.5',
    '源数据-SUPPORT',
    '源数据-线下非缺陷',
    '源数据-线下非需求',
    '源数据-专有云需求',
    '源数据-汇总表',
]

DEFECTS = [
    '源数据-L2.5',
    '源数据-SUPPORT',
    '源数据-线下非缺陷',
]

ALL = {
    "问题类型":"A",
    "ID":"B",
    "标题":"C",
    "状态":"D",
    "作者":"E",
    "作者工号":"F",
    "作者的主管":"G",
    "指派给":"H",
    "指派给工号":"I",
    "指派给的主管":"J",
    "创建时间":"K",
    "优先级":"L",
    "模块":"M",
    "归属项目":"N",
    "标签":"O",
    "版本":"P",
    "更新时间":"Q",
    "状态更新时间":"R",
    "验收版本":"S",
    "修复于时间":"T",
    "关闭于":"U",
    "版本号":"V",
    "缺陷来源":"W",
    "客户":"X",
    "工单号":"Y",
    "客户名称":"Z",
    "项目名称":"AA",
    "改善机会":"AB",
    "潜在影响":"AC",
    "是否国产化":"AD",
    "专有云形态":"AE",
    "评论":"AF",
    "响应时间(天)":"AG",
    "修复时间(天)":"AH",
    "关闭时间(天)":"AI",
    "周次":"AJ",
    "所属行业":"AK",
    "重点产品":"AL",
    "提交团队":"AM",
    "重要客户":"AN",
    "最终修复版本":"AO",
    "hotfix修复版本":"AP",
    "严重程度": "AQ",
    "问题分类": "AR",
}


def read_as_dict(workbook, sheetname):
    ws = workbook.get_sheet_by_name(sheetname)
    #ws = workbook["sheetname"]
    data = list(ws.values)
    keys = data[0]
    return [dict(zip(keys, values)) for values in data[1:]]


def save_to_excel(filename, data, sheetname):
    if os.path.exists(filename):
        wb = openpyxl.load_workbook(filename)
        if sheetname in wb.sheetnames:
            wb.remove(wb[sheetname])

        wb.save(filename)

    wb = openpyxl.load_workbook(filename)
    wb.create_sheet(sheetname, -1)
    ws = wb.get_sheet_by_name(sheetname)
    #ws = wb["sheetname"]
    titles = list(data[0].keys())

    for col in range(len(titles)):
        title = titles[col]
        wc = ws.cell(row=1, column=col + 1, value=title)
        wc.fill = PatternFill('solid', fgColor='008B8B')
        wc.font = Font(bold=True)
        wc.alignment = Alignment(horizontal='center',vertical='center')
        wc.border = Border(left=Side(style='thin', color=colors.BLACK), right=Side(style='thin', color=colors.BLACK),)
        for r in range(len(data)):
            wc = ws.cell(row=r + 2, column=col + 1, value=data[r][title])

    wb.save(filename)
    msg = 'save the quality inspection results to: filename: {}; sheetname:{}'.format(filename, sheetname)
    print(msg)


def main():
    if len(sys.argv) == 1:
        print("python3", sys.argv[0], "excel file path")
        sys.exit()
    else:
        filename = sys.argv[1]

    wb = openpyxl.load_workbook(filename)
    data = []
    for sn in SHEETLIST:
        if sn == '源数据-汇总表':
            continue
        rst = read_as_dict(wb, sn)
        if sn in DEFECTS:
            for d in rst:
                d['问题类型'] = '缺陷Aone'
                d['状态更新时间'] = d['更新时间']
                try:
                    d['工单号'] = d['工单ID']
                except Exception as e:
                    pass
        else:
            for d in rst:
                d['问题类型'] = '需求Aone'
        data += rst

    rst = []
    rst_title = list(ALL.keys())
    for d in data:
        nd = {}
        for k in rst_title:
            try:
                nd[k] = d[k]
            except Exception as e:
                nd[k] = ''
        rst.append(nd)

    save_to_excel(filename, rst, '源数据-汇总表')

if __name__ == '__main__':
    main()

上一版本代码：

#!/usr/bin/python3.8
#coding:utf-8

from openpyxl import Workbook
from openpyxl import load_workbook
from openpyxl.utils import get_column_letter, column_index_from_string
import sys


if len(sys.argv) == 1:
    print("python3",sys.argv[0],"Execl_Path")
    sys.exit()
else:
    Execl = sys.argv[1]

wb = load_workbook(Execl)
ws1 = wb.create_sheet("源数据-汇总表")



All = {
    "问题类型":"A",
    "ID":"B",
    "标题":"C",
    "状态":"D",
    "作者":"E",
    "作者的主管":"F",
    "指派给":"G",
    "指派给的主管":"H",
    "创建时间":"I",
    "优先级":"J",
    "模块":"K",
    "归属项目":"L",
    "标签":"M",
    "版本":"N",
    "更新时间":"O",
    "状态更新时间":"P",
    "验收版本":"Q",
    "修复于时间":"R",
    "关闭于":"S",
    "版本号":"T",
    "缺陷来源":"U",
    "客户":"V",
    "工单号":"W",
    "客户名称":"X",
    "项目名称":"Y",
    "改善机会":"Z",
    "潜在影响":"AA",
    "是否国产化":"AB",
    "专有云形态":"AC",
    "评论":"AD",
    "响应时间(天)":"AE",
    "修复时间(天)":"AF",
    "关闭时间(天)":"AG",
    "周次":"AH",
    "所属行业":"AI",
    "重点产品":"AJ",
    "提交团队":"AK",
    "重要客户":"AL",
}


sheet_name = {
    'Aone25_dict':[{'源数据-L2.5':
		["ID",
		"标题",
		"状态",
		"作者",
		"作者的主管",
		"指派给",
		"指派给的主管",
		"创建时间",
		"优先级",
		"模块",
		"归属项目",
		"标签",
		"版本",
		"更新时间",
		"修复于时间",
		"关闭于",
             "工单号",
		"评论",
		"响应时间(天)",
		"修复时间(天)",
		"关闭时间(天)"],
		'Aone25':{}},'缺陷Aone'],
    'Support_dict':[{'源数据-SUPPORT':
		["ID",
    	"标题",
    	"状态",
    	"作者",
    	"作者的主管",
    	"指派给",
    	"指派给的主管",
    	"创建时间",
    	"优先级",
    	"模块",
    	"归属项目",
    	"标签",
    	"版本",
    	"更新时间",
    	"修复于时间",
    	"关闭于",
    	"版本号",
    	"缺陷来源",
    	"客户",
    	"工单号",
    	"客户名称",
    	"是否国产化",
    	"专有云形态",
    	"评论",
    	"响应时间(天)",
    	"修复时间(天)",
    	"关闭时间(天)"
      "所属行业",
      "重点产品",
      "提交团队",
      "重要客户"],
    	'Support':{}},"缺陷Aone"],
    'XQ_demand_dict':[{'源数据-专有云需求':
		["ID",
    	"标题",
    	"状态",
    	"作者",
    	"指派给",
    	"创建时间",
    	"更新时间",
    	"状态更新时间",
    	"优先级",
    	"模块",
    	"归属项目",
    	"标签",
    	"版本",
    	"验收版本",
    	"项目名称",
    	"改善机会",
    	"潜在影响",
    	"是否国产化",
    	"专有云形态",
    	"评论",
      "重要客户",
      "所属行业",
      "重点产品",
      "提交团队",
      "重要客户"],
    	'XQ_demand':{}},'需求Aone'],
    'No_demand_dict':[{'源数据-线下非需求':
    	["ID",
    	"标题",
    	"状态",
    	"作者",
    	"指派给",
    	"创建时间",
    	"优先级",
    	"模块",
    	"归属项目",
    	"标签",
    	"版本",
    	"更新时间",
    	"状态更新时间",
    	"验收版本",
    	"项目名称",
    	"改善机会",
    	"潜在影响",
    	"评论",
      "重要客户",
      "所属行业",
      "重点产品",
      "提交团队"],
    	'No_demand':{}},'需求Aone'],
    'Off_defect_dict':[{'源数据-线下非缺陷':
    	["ID",
    	"标题",
    	"状态",
    	"作者",
    	"作者的主管",
    	"指派给",
    	"指派给的主管",
    	"创建时间",
    	"优先级",
    	"模块",
    	"归属项目",
    	"标签",
    	"版本",
    	"更新时间",
    	"修复于时间",
    	"关闭于",
    	"工单号",
    	"客户名称",
    	"评论",
    	"响应时间(天)",
    	"修复时间(天)",
    	"关闭时间(天)"
      "重要客户",
      "所属行业",
      "重点产品",
      "提交团队"],
    	'Off_defect':{}},'缺陷Aone']
}


# 写入Title
for Id,title in enumerate(All.keys(),1):
    ws1.cell(row=1, column=Id, value=title)


def acquire_sheel(sheet_name,*sheet_var):
    # 获取所有sheet列表
    var = sheet_var[0].keys()
    if list(var)[0] in wb.sheetnames:
        cur_sheel = list(var)[0]
    else:
        return False,False,False
    ws = wb[cur_sheel]
    Max_col = ws.max_column
    return ws,Max_col,sheet_var

def generate_Dict(*sheet_var):
    # 自动生成字段与单元的对应关系
    for i in range(1,Max_col):
        Cell = sheet_var[0]
        if ws.cell(1,int(i)).value in list(Cell.values())[0]:
            new_dict = list(Cell.keys())[1]
            Cell[new_dict][get_column_letter(i)] = ws.cell(1,int(i)).value
    return Cell[new_dict]


def writeData(Id,ws,sheet_var,**sheet):
    for key,value in sheet.items():
        # 取值
        for line,data in enumerate(ws[key],1):
            line += Id
            # 只要是开头行信息就跳过
            if data.value in sheet.values():
                continue
            # 设置写入方式
            ws1[All.get("问题类型") + str(line)] = sheet_var[1]
            ws1[All.get("周次") + str(line)] = '=WEEKNUM(' + All.get("创建时间") + str(line) + ')'
            ws1.cell(row=line,column=column_index_from_string(All.get(value)),value=data.value)
    Id = line - 1
    return Id


if __name__ == '__main__':
    # 写入
    Id = 0
    for sheet_key,sheet_var in sheet_name.items():
        ws,Max_col,sheet_var = acquire_sheel(sheet_name,*sheet_var)
        if not sheet_var:continue
        sheet = generate_Dict(*sheet_var)
        Id = writeData(Id,ws,sheet_var,**sheet)
        wb.save(Execl)