自学教程：Python自动化之批量处理工作簿和工作表

import xlwings as xw     # 导入库# 启动Excel程序，但不新建工作簿app = xw.App(visible = True, add_book = False) for i in range(6):    workbook = app.books.add()   # 新建工作簿    workbook.save(f'e://file//test{i}.xlsx')    # 保存新建的多个工作簿    workbook.close()     # 关闭当前工作簿app.quit()     # 退出Excel程序

import osimport xlwings as xw file_path = 'e://table'     # 给出工作簿所在的文件夹路径file_list = os.listdir(file_path)     # 列出路径下所有文件和子文件夹的名称app = xw.App(visible = True, add_book = False)    # 启动Excel程序for i in file_list:    if os.path.splitext(i)[1] == '.xlsx':       # 判断文件夹下文件的扩展名是否为“.xlsx”        app.books.open(file_path + '//' + i)    # 打开工作簿

import osfile_path = 'table'file_list = os.listdir(file_path)for i in file_list:    print(i)

import xlwings as xwapp = xw.App(visible = False, add_book = False)workbook = app.books.open('e://table//统计表.xlsx')worksheets = workbook.sheets     # 获取工作簿中所有的工作表 for i in range(len(worksheets)):   # 遍历获取到的工作表    worksheets[i].name = worksheets[i].name.replace('销售', '')     # 重命名工作表workbook.save('e://table//统计表1.xlsx')       # 另存重命名工作表后的工作簿app.quit()

import xlwings as xwapp = xw.App(visible = False, add_book = False)workbook = app.books.open('e://table//统计表.xlsx')worksheets = workbook.sheets    # 获取工作簿中所有的工作表 for i in range(len(worksheets))[:5]:   # 通过切片来选中部分工作表    worksheets[i].name = worksheets[i].name.replace('销售', '')   # 重命名工作表workbook.save('e://table//统计表1.xlsx')    # 另存重命名工作表后的工作簿app.quit()

import os    # 导入库 file_path = 'e://table//产品销售表'   # 给出待重命名工作簿所在文件夹的路径file_list = os.listdir(file_path)    # 列出文件夹下所有文件和子文件夹的名称 old_book_name = '销售表'           # 给出工作簿名中需要替换的旧关键字new_book_name = '分部产品销售表'    # 给出工作簿名中要替换的新关键字 for i in file_list:    if i.startswith('~$'):   # 判断是否有文件夹名以“~$”开头的临时文件        continue     # 如果有，则跳过这种类型的文件            new_file = i.replace(old_book_name, new_book_name)    # 执行查找和替换，生成新的工作簿名    old_file_path = os.path.join(file_path, i)            # 构造需要重命名工作簿的完整路径    new_file_path = os.path.join(file_path, new_file)     # 构造重命名后工作簿的完整路径    os.rename(old_file_path, new_file_path)     # 执行重命名

import osimport xlwings as xw file_path = 'e://table//信息表'file_list = os.listdir(file_path) old_sheet_name = 'Sheet1'    # 给出需要修改的工作表名new_sheet_name = '员工信息'   # 列出修改后的工作表名 app = xw.App(visible = False, add_book = False) for i in file_list:    if i.startswith('~$'):        continue     old_file_path = os.path.join(file_path, i)    workbook = app.books.open(old_file_path)     for j in workbook.sheets:        if j.name == old_sheet_name:    # 判断工作表名是否为“sheet1”            j.name = new_sheet_name     # 如果是，则重命名工作表                workbook.save()app.quit()

import osimport xlwings as xw file_path = 'e://table//销售表'file_list = os.listdir(file_path) sheet_name = '产品销售区域'    # 给出新增的工作表名称app = xw.App(visible = False, add_book = False) for i in file_list:    if i.startswith('~$'):        continue            file_paths = os.path.join(file_path, i)    workbook = app.books.open(file_paths)    sheet_names = [j.name for j in workbook.sheets]    if sheet_name not in sheet_names:        workbook.sheets.add(sheet_name)        workbook.save()app.quit()

import osimport xlwings as xw file_path = 'e://table//销售表1'file_list = os.listdir(file_path) sheet_name = '产品销售区域'   # 给出要删除的工作表名称app = xw.App(visible = False, add_book = False) for i in file_list:    if i.startswith('~$'):        continue            file_paths = os.path.join(file_path, i)    workbook = app.books.open(file_paths)    for j in workbook.sheets:        if j.name == sheet_name:            j.delete()            break    workbook.save()app.quit()

import osimport xlwings as xw file_path = 'e://table//公司'file_list = os.listdir(file_path)app = xw.App(visible = False, add_book = False) for i in file_list:    if i.startswith('~$'):        continue            file_paths = os.path.join(file_path, i)   # 获取需要打印的工作簿的文件路径    workbook = app.books.open(file_paths)    # 打开要打印的工作簿        workbook.api.PrintOut()   # 打印工作簿app.quit()

import osimport xlwings as xw file_path = 'e://table//公司1'file_list = os.listdir(file_path) sheet_name = '产品分类表'     # 给出要打印的工作表的名称app = xw.App(visible = False, add_book = False) for i in file_list:    if i.startswith('~$'):        continue            file_paths = os.path.join(file_path, i)    workbook = app.books.open(file_paths)        for j in workbook.sheets:        if j.name == sheet_name:     # 判断工作簿中是否存在名为“产品分类表”的工作表            j.api.PrintOut()    # 如果存在，则打印该表            breakapp.quit()   # 退出Excel程序

import osimport xlwings as xw app = xw.App(visible = False, add_book = False) file_path = 'e://table//销售表'   file_list = os.listdir(file_path)   workbook = app.books.open('e://table//信息表.xlsx')  worksheet = workbook.sheets for i in file_list:      if os.path.splitext(i)[1] == '.xlsx':      # 判断文件是否有工作簿        workbooks = app.books.open(file_path + '//' + i)     # 如果是工作簿则将其打开                for j in worksheet:                contents = j.range('A1').expand('table').value     # 读取来源工作簿中要赋值的工作表数据            name = j.name        # 获取来源工作簿中的工作表名称            workbooks.sheets.add(name = name, after = len(workbooks.sheets))     # 在目标工作簿中新增同名工作表            workbooks.sheets[name].range('A1').value = contents      # 将从来源工作簿中读取的工作表数据写入新增工作表        workbooks.save()        # 保存目标工作簿app.quit()

import osimport xlwings as xwapp = xw.App(visible = False, add_book = False) file_path = 'e://table//销售表1'  file_list = os.listdir(file_path)   workbook = app.books.open('e://table//新增产品表.xlsx')  worksheet = workbook.sheets['新增产品']           # 选中工作表“新增产品”value = worksheet.range('A1').expand('table')   # 读取工作表“新增产品”中的所有数据start_cell = (2, 1)                             # 给出要复制数据的单元格区域的起始单元格end_cell = (value.shape[0], value.shape[1])    # 给出要复制数据的单元格区域的结束单元格cell_area = worksheet.range(start_cell, end_cell).value   # 根据前面设定的单元格区域选取要复制的数据 for i in file_list:      if os.path.splitext(i)[1] == '.xlsx':         try:            workbooks = xw.Book(file_path + '//' + i)              sheet = workbooks.sheets['产品分类表']       # 选中要粘贴数据的工作表“产品分类表”            scope = sheet.range('A1').expand()          # 选中要粘贴数据的单元格区域            sheet.range(scope.shape[0] + 1, 1).value = cell_area     # 粘贴数据            workbooks.save()     # 保存目标工作簿                    finally:            workbooks.close()     # 关闭目标工作簿workbook.close()                  # 关闭来源工作簿app.quit()

import xlwings as xwworkbook_name = 'e://table//产品销售表.xlsx'        # 指定要拆分的来源工作簿app = xw.App(visible = False, add_book = False) header = None    all_data = []  workbook = app.books.open(workbook_name) for i in workbook.sheets:                 # 遍历来源工作簿中的工作表    workbook_split = app.books.add()      # 新建一个目标工作簿    sheet_split = workbook_split.sheets[0]     # 选择目标工作簿中的第一个工作表    i.api.Copy(Before = sheet_split.api)       # 将来源工作簿中的当前工作表复制到目标工作簿的第一个工作表之前    workbook_split.save('{}'.format(i.name))   # 以当前工作表的名称作为文件名保存目标工作簿app.quit()

import xlwings as xwimport pandas as pdapp = xw.App(visible = True, add_book = False) workbook = app.books.open('e://table//产品统计表.xlsx')worksheet = workbook.sheets['统计表']# 读取要拆分的工作表数据value = worksheet.range('A1').options(pd.DataFrame, header = 1, index = False, expand = 'table').value data = value.groupby('产品名称')    # 将数据按照“产品名称”分组 for idx, group in data:     new_worksheet = workbook.sheets.add(idx)      # 在工作簿中新增工作表并命名为当前的产品名称    new_worksheet['A1'].options(index = False).value = group      # 将数据添加到新增的工作表workbook.save()workbook.close()app.quit()

import xlwings as xw file_path = 'e://table//产品统计表.xlsx'sheet_name = '统计表'app = xw.App(visible = True, add_book = False) workbook = app.books.open(file_path)worksheet = workbook.sheets[sheet_name]value = worksheet.range('A2').expand('table').valuedata = dict() for i in range(len(value)):    product_name = value[i][1]    if product_name not in data:        data[product_name] = []    data[product_name].append(value[i]) for key,value in data.items():    new_workbook = xw.books.add()    new_worksheet = new_workbook.sheets.add(key)    new_worksheet['A1'].value = worksheet['A1:H1'].value    new_worksheet['A2'].value = value    new_workbook.save('{}.xlsx'.format(key))app.quit()

import osimport xlwings as xw file_path = 'e://table//销售统计'file_list = os.listdir(file_path)sheet_name = '产品销售统计'app = xw.App(visible = False, add_book = False)header = Noneall_data = []for i in file_list:    if i.startswith('~$'):        continue       file_paths = os.path.join(file_path, i)    workbook = app.books.open(file_paths)    for j in workbook.sheets:        if j.name == sheet_name:            if header == None:                header = j['A1:I1'].value            values = j['A2'].expand('table').value            all_data = all_data + valuesnew_workbook = xw.Book()new_worksheet = new_workbook.sheets.add(sheet_name)   new_worksheet['A1'].value = headernew_worksheet['A2'].value = all_datanew_worksheet.autofit()new_workbook.save('e://table//上半年产品销售统计表.xlsx')app.quit()

import osimport xlwings as xwworkbook_name = 'e://table//采购表.xlsx'sheet_names = [str(sheet)+'月' for sheet in range(1,7)]new_sheet_name = '上半年统计表'app = xw.App(visible = False, add_book = False)header = Noneall_data = []workbook = app.books.open(workbook_name)for i in workbook.sheets:    if new_sheet_name in i.name:         i.delete()new_worksheet = workbook.sheets.add(new_sheet_name)title_copyed = Falsefor j in workbook.sheets:    if j.name in sheet_names:        if title_copyed == False:            j['A1'].api.EntireRow.Copy(Destination = new_worksheet["A1"].api)            title_copyed = True        row_num = new_worksheet['A1'].current_region.last_cell.row        j['A1'].current_region.offset(1, 0).api.Copy(Destination = new_worksheet["A{}".format(row_num + 1)].api)new_worksheet.autofit()workbook.save()app.quit()