python将Excel拆多表存放并批量运行宏文件

import os
import pandas as pd
import win32com.client
import openpyxl
import xlsxwriter
import datetime
from 转换pdf import PDFConverter

nowday = datetime.datetime.now().strftime("%m-%d")

def fenzu():
    filename = "读取大表数据.xlsx"
    filepath = "文件存储位置" + filename  # 源文件
    file = pd.read_excel(filepath, sheet_name='Shee1')
    i = 1
    for name, group in file.groupby("组名"):
        print(i, name, group['其他列'].iloc[0])
        i=i+1
        namefile = group['命名1'].iloc[0] + '-' + name + '-' + '命名2'
        group['图片'] = ''
        data = group[['组名', '字段1', '字段2', '字段3', '图片', '字段4', '字段5', '字段6', '字段7', '字段8']]


        filename = "拆多表存储位置" + namefile + ".xlsx"
        if not os.path.exists("拆多表存储位置"):
            os.makedirs("拆多表存储位置")
        writer = pd.ExcelWriter(filename, engine='xlsxwriter')
        data.to_excel(writer, sheet_name='Sheet1', index=False)  # 导入writer
        workbook = writer.book  # 提取workbook对象
        worksheet = writer.sheets['Sheet1']  # 添加工作表
        worksheet.set_default_row(73)  # 设置所有行高

        header_format = workbook.add_format({
            'text_wrap': True,  # 是否自动换行
            'valign': 'vcenter',  # 垂直对齐方式
            'align': 'center',  # 水平对齐方式
            'border': 1,
            # 'bold': True,  # 字体加粗
            # 'fg_color': '#D7E4BC',  # 单元格背景颜色
        })
        head_format = workbook.add_format({
            'text_wrap': True,  # 是否自动换行
            'valign': 'vcenter',  # 垂直对齐方式
            'align': 'center',  # 水平对齐方式
            'bold': True,  # 字体加粗
            'fg_color': '#D7E4BC',  # 单元格背景颜色
            'border': 1,

        })

        worksheet.set_row(0, 20, cell_format=header_format)  # 设置第一行行高

        worksheet.set_column("A:K", 18.75, header_format) 
        worksheet.set_column("A:A", 18, header_format)
        worksheet.set_column("B:B", 10, header_format)
        worksheet.set_column("C:C", 8, header_format)
        worksheet.set_column("D:D", 8, header_format)
        worksheet.set_column("E:E", 10, header_format)
        worksheet.set_column("F:F", 5, header_format)
        worksheet.set_column("G:G", 5, header_format) 
        worksheet.set_column("H:H", 5, header_format)
        worksheet.set_column("I:I", 5, header_format)
        worksheet.set_column("J:J", 5, header_format) 
        # worksheet.set_column("K:K", 4, header_format)
        # worksheet.set_column("L:L", 18.75, header_format)
        # worksheet.set_column("M:M", 4.5, header_format)

        writer.save()
        writer.close()
        # break

def useVBA(file_name):
    xlApp = win32com.client.DispatchEx("Excel.Application")
    xlApp.Visible = True
    xlApp.DisplayAlerts = 0
    file_path = file_name
    xlBook = xlApp.Workbooks.Open(os.path.abspath(file_path), False)
    xlBook.Application.Run("'插入图片2.xlsm'!CreateNewMacros.插入图片2")  # 宏
    xlBook.Close(True)
    xlApp.Quit()


def get_all_excel(dir):
    file_list = []
    for root_dir, sub_dir, files in os.walk(r'' + dir):
            if file.endswith('.xlsx')
                file_name = os.path.join(root_dir, file)
                useVBA(file_name)

                pdfConverter = PDFConverter(file_name)  # 也支持单个文件的转换
                pdfConverter.run_conver()

                # 把拼接好的文件目录信息添加到列表中
                file_list.append(file_name)
                # break
    # return file_list


if __name__ == '__main__':
    fenzu()  # 导出 & 分组
    dirpath = "拆多表存储位置"
    get_all_excel(dirpath)   # 插入图片
print("输出成功！")