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程序

二、批量打开一个文件夹下的所有工作簿

1.批量打开一个文件夹下的所有工作簿

import os
import 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)    # 打开工作簿

2.列出文件夹下所有文件和子文件夹的名称

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

三、批量重命名一个工作簿中所有工作表

1.批量重命名一个工作簿中的所有工作表

import xlwings as xw
app = 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()

2.批量重命名一个工作簿中的部分工作表

import xlwings as xw
app = 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()

四、批量重命名工作簿

1.批量重命名多个工作簿

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)     # 执行重命名

2.批量重命名多个工作簿中的同名工作表

import os
import 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()

五、在多个工作簿中批量新增/删除工作表

1.批量新增工作表

import os
import 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()

2.批量删除工作表

import os
import 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()

六、批量打印工作簿

1.批量打印工作簿

import os
import 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()

2.批量打印多个工作簿中的指定工作表

import os
import 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()    # 如果存在，则打印该表
           break
app.quit()   # 退出Excel程序

七、将一个工作簿的所有工作表批量赋值到其他工作簿

1.将一个工作簿的所有工作表批量赋值到其他工作簿

import os
import 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()

2.将指定工作表的数据批量复制到其他工作簿的指定工作表中

import os
import xlwings as xw
app = 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()

八、按条件将一个工作表批量赋值到其他工作簿

1.将多个工作表拆分为多个工作簿

import xlwings as xw
workbook_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()

2.按条件将一个工作表拆分为多个工作表

import xlwings as xw
import pandas as pd
app = 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()

3.按条件将一个工作表拆分为多个工作簿

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').value
data = 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()

九、批量合并多个工作簿中的同名工作表

1.批量合并多个工作簿中的同名工作表

import os
import xlwings as xw

file_path = 'e:\\table\\销售统计'
file_list = os.listdir(file_path)
sheet_name = '产品销售统计'
app = xw.App(visible = False, add_book = False)
header = None
all_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 + values
new_workbook = xw.Book()
new_worksheet = new_workbook.sheets.add(sheet_name)  
new_worksheet['A1'].value = header
new_worksheet['A2'].value = all_data
new_worksheet.autofit()
new_workbook.save('e:\\table\\上半年产品销售统计表.xlsx')
app.quit()

2.将工作簿中名称有规律的工作表合并到一个工作表

import os
import xlwings as xw
workbook_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 = None
all_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 = False
for 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()

来源：https://blog.csdn.net/weixin_44940488/article/details/117409356