Python中各类Excel表格批量合并问题的实现思路与案例

from glob import glob
glob("[!~r]*.xlsx")

glob("[!~r]*.xls*")

glob("**/[!~r]*.xls*", recursive=True)

['test.xls',
 '合并结果.xlsx',
 '多sheet表格合并.xlsx',
 '带表头样式合并.xlsx',
 'Excel多sheet合并\\excel3.xlsx',
 'Excel多sheet合并\\excel4.xlsx',
 'Excel多sheet合并\\新建文件夹\\excel3.xlsx',
 'Excel多sheet合并\\新建文件夹\\excel4.xlsx',
 'Excel多sheet合并\\新建文件夹\\新建文件夹\\excel3.xlsx',
 'Excel多sheet合并\\新建文件夹\\新建文件夹\\excel4.xlsx',
 '带样式合并\\HB区.xlsx',
 '带样式合并\\HN区.xlsx',
 '带样式合并\\XN区.xlsx',
 '带样式合并\\汇总表.xlsx']

import os 
path = os.path.expanduser("~/Documents/WeChat Files")
glob(f"{path}[!~r]*.xls*", recursive=True)

from glob import glob
import pandas as pd

dfs = [pd.read_csv(file, skiprows=1, sep="|", compression="gzip")
       for file in glob("gzip[!~]*.xls*", recursive=True)]
df = pd.concat(dfs, ignore_index=True)
df.to_excel("合并结果.xlsx", index=False)

import pandas as pd
from glob import glob

path = "Excel多sheet合并"
data = {}
for file in glob(f"{path}[!~]*.xls*", recursive=True):
    for name, df in pd.read_excel(file, sheet_name=None).items():
        data.setdefault(name, []).append(df)

with pd.ExcelWriter("多sheet表格合并.xlsx") as write:
    for name, dfs in data.items():
        pd.concat(dfs).to_excel(write, name, index=False)

import pandas as pd
from glob import glob

path = "带样式合并"
# 定义被读取的sheet名和跳过的行数
sheet_start_num = {'03': 3, '06': 2, '07': 3, '08': 3}
data = {}
for file in glob(f"{path}[!~r汇]*.xls*", recursive=True):
    for sheet_name, skiprows in sheet_start_num.items():
        excel = pd.ExcelFile(file)
        df = excel.parse(sheet_name=sheet_name, skiprows=skiprows, header=None)
        data.setdefault(sheet_name, []).append(df.values)

from openpyxl import load_workbook

workbook = load_workbook(filename="带样式合并/汇总表.xlsx")

for sheet_name, sheet_data in data.items():
    sheet = workbook[sheet_name]
    sheet.delete_rows(sheet_start_num[sheet_name] + 1, sheet.max_row)
    for row in np.vstack(sheet_data).tolist():
        sheet.append(row)
workbook.save(filename="带表头样式合并.xlsx")

from tkinter import filedialog

filedialog.askdirectory(initialdir=".")

filedialog.asksaveasfilename(title="保存",
                             initialdir=".",
                             defaultextension="xlsx",
                             filetypes=[("Excel 工作簿", "*.xlsx"),
                                        ("Excel 97-2003 工作簿", "*.xls")])

from tkinter import filedialog
import pandas as pd
from glob import glob

path = filedialog.askdirectory(initialdir=".")
data = {}
for file in glob(f"{path}[!~]*.xls*", recursive=True):
    for name, df in pd.read_excel(file, sheet_name=None).items():
        data.setdefault(name, []).append(df)

save_name = filedialog.asksaveasfilename(title="保存",
                             initialdir=".",
                             defaultextension="xlsx",
                             filetypes=[("Excel 工作簿", "*.xlsx"),
                                        ("Excel 97-2003 工作簿", "*.xls")])
with pd.ExcelWriter(save_name) as write:
    for name, dfs in data.items():
        pd.concat(dfs).to_excel(write, name, index=False)

from glob import glob
import pandas as pd

from gooey import Gooey, GooeyParser


def combine_excel(path, save_name):
    data = {}
    for file in glob(f"{path}[!~]*.xls*", recursive=True):
        for name, df in pd.read_excel(file, sheet_name=None).items():
            data.setdefault(name, []).append(df)
    with pd.ExcelWriter(save_name) as write:
        for name, dfs in data.items():
            pd.concat(dfs).to_excel(write, name, index=False)

@Gooey
def main():
    parser = GooeyParser(description="多Excel多Sheet合并程序 - @小小明")
    parser.add_argument('path', help="被合并的Excel文件目录", widget="DirChooser")
    parser.add_argument('save_name', help="合并后保存的文件(以Excel文件形式保存)", widget="FileSaver")
    args = parser.parse_args()
    print("输入路径：", args.path)
    print("保存位置：", args.save_name)
    combine_excel(args.path, args.save_name)
    print("合并完成！")


if __name__ == '__main__':
    main()

from glob import glob
import pandas as pd

from gooey import Gooey, GooeyParser


def combine_excel(path, save_name):
    data = {}
    files = glob(f"{path}[!~]*.xls*", recursive=True)
    for i, file in enumerate(files, 1):
        for name, df in pd.read_excel(file, sheet_name=None).items():
            data.setdefault(name, []).append(df)
        yield f"合并进度：{i}/{len(files)}"
    with pd.ExcelWriter(save_name) as write:
        items = data.items()
        for i, (name, dfs) in enumerate(items, 1):
            pd.concat(dfs).to_excel(write, name, index=False)
            yield f"保存进度：{i}/{len(items)}"


@Gooey(progress_regex=r"^..进度：(?P<current>\d+)/(?P<total>\d+)$",
       progress_expr="current / total * 100",
       timing_options={
           'show_time_remaining': False,
           'hide_time_remaining_on_complete': True,
       })
def main():
    parser = GooeyParser(description="多Excel多Sheet合并程序 - @小小明")
    parser.add_argument('path', help="被合并的Excel文件目录", widget="DirChooser")
    parser.add_argument('save_name', help="合并后保存的文件(以Excel文件形式保存)", widget="FileSaver")
    args = parser.parse_args()
    print("输入路径：", args.path)
    print("保存位置：", args.save_name)
    for msg in combine_excel(args.path, args.save_name):
        print(msg)
    print("合并完成！")


if __name__ == '__main__':
    main()