目录
前言
一、 xlwings的批量操作
1、批量处理工作簿和工作表
(1)批量新建并保存关闭工作簿
(2)批量打开一个文件夹下的所有工作簿
(3)批量重命名一个工作簿中的部分工作表
(4)批量重命名多个工作簿
(5)在多个工作簿中批量新增工作表
(6)批量打印工作簿
(7)将一个工作簿中所有工作表批量复制到其他工作簿中
(8)按条件将一个工作表拆分为多个工作簿
(9)批量合并多个工作簿中的同名工作表
2、批量处理行、列和单元格
(2)批量更改数据格式
(3)批量替换行列数据
(4)批量对工作表数据进行分列
(5)批量合并指定列
前言
本次学习笔记主要记录xlwing的批量操作及其与Excel VBA相互调用。
一、 xlwings的批量操作
1、批量处理工作簿和工作表
(1)批量新建并保存关闭工作簿
import xlwings as xw
app = xw.App(visible=True, add_book=False) # 启动excel，但不新建
for i in range(6):
workbook = app.books.add() # 新建工作簿
#worksheet = workbook.sheets.add() # 新建工作表
workbook.save(f'e:\\file\\test{i}.xlsx') # 保存
workbook.close() # 关闭工作簿
app.quit() # 退出excel程序
#f-string方法：以f或F修饰符引领字符串，字符串中以大括号{}标明将被替换的内容。(2)批量打开一个文件夹下的所有工作簿
import os
import xlwings as xw
file_path = 'e:\\table'
file_list = os.listdir(file_path)
app = xw.App(visible = True, add_book = False)
for i in file_list:
#判断文件是否为excel文件
if os.path.splitext(i)[1] == '.xlsx' or os.path.splitext(i)[1] == '.xls':
app.books.open(file_path+'\\'+i)(3)批量重命名一个工作簿中的部分工作表
import xlwings as xw
app = xw.App(visible=True, add_book=False) # 启动excel，但不新建
workbook = app.books.open('e:\\data.xlsx')
worksheets = workbook.sheets # 获取工作簿中的所有工作表
for i in range(len(worksheets))[:5]: # 通过切片获取前面的5个工作表
worksheets[i].name = worksheets[i].name.replace('sale', ' ') #将工作表名中的sale替换掉
workbook.save('e:\\data1.xlsx') # 将修改后的工作簿另存为data1，若不想另存，则省略
app.quit()(4)批量重命名多个工作簿
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) # 执行重命名(5)在多个工作簿中批量新增工作表
import os
file_path = 'e:\\table'
file_list = os.listdir(file_path)
sheet_name = 'a' # 新增的工作表名称
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()
for m in workbook.sheets:
if m.name == sheet_name2:
m.delete() # 批量删除工作表
workbook.save()
app.quit()(6)批量打印工作簿
xlwings模块中打印命令语法：workbook.api.PrintOut() # 打印工作簿
xlwings中没有提供打印工作簿的函数，所以利用工作簿的api属性调用VBA的PrintOut()函数来打印。
PrintOut(From, To, Copies, Preview, ActivePrinter, PrintToFile, Collate, PrToFileName)
From指定打印开始页；to打印终止页；Copies打印份数；Preview为True打印前显示打印预览，False为立即打印；Active Printer使用的打印机名称；PrintToFile为True则表示不打印到打印机，而是打印成pm文件；Collate为True表示逐份打印；PrToFileName当PrintTofile为True时指定pm文件的文件名。
(7)将一个工作簿中所有工作表批量复制到其他工作簿中
#将一个工作薄中所有工作表批量复制到多个工作簿中
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\\out.xlsx') # 打开信息源工作簿
worksheet = workbook.sheets # 获取信息源工作簿的所有工作表
for i in file_list: # 遍历目标文件夹中的所有目标工作簿
if os.path.splitext(i)[1] == '.xlsx': # 判断是否是excel工作簿
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()(8)按条件将一个工作表拆分为多个工作簿
import xlwings as xw
file_path = 'G:\\D盘\\2- 检测报告\\计量仪器校准'
sheet_name = '内部校准清单'
app = xw.App(visible = True, add_book = False)
workbook = app.books.open(file_path+'\\2021内部校准计划表.xlsx')
worksheet = workbook.sheets[sheet_name]
value = worksheet.range('A3').expand('table').value
data = dict() # 创建空字典
for i in range(len(value)): # 按行遍历数据
product_name = value[i][6] # 筛选第6列的名称
if product_name not in data: # 判断第6列的名称是否在字典中，不存在则写入
data[product_name] = []
data[product_name].append(value[i]) # 按第6列的名称将数据筛选汇总到该键-值对中
for key, value in data.items():
n_workbook = app.books.add() #新增工作簿
n_worksheet = n_workbook.sheets.add(key) #在新增以键名为表名的新工作表
n_worksheet['A1'].value = worksheet['A2:J2'].value # 设置新表格的标题行
n_worksheet['A2'].value = value
n_worksheet.range('C2').column_width = 20 # 设置列宽
n_worksheet.range('D2').column_width = 13
n_worksheet.range('F2').column_width = 12
n_worksheet.autofit(r) # 自动调整行高
n_workbook.save(file_path+'\\split\\{}计量工具清单.xlsx'.format(key))
n_workbook.close()
app.quit()(9)批量合并多个工作簿中的同名工作表
import os
import xlwings as xw
file_path = 'e:\\table'
file_list = os.listdir(file_path)
sheet_name = 'aa'
app = xw.App(visible=True, add_book=False)
header = None # 定义列标题
all_data = [] #创建空白列表，用于存储要合并的数据
for i in file_list:
if i.startswith('~$'):
continue
if os.path.splitext(i)[1] != '.xlsx'
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 #将多个工作簿中同名工作表的数据汇总到一个列表中
n_workbook = app.books.add()
n_worksheet = n_workbook.sheets.add(sheet_name)
n_worksheet['A1'].value = header
n_worksheet['A2'].value = all_data
n_worksheet.autofit(r)
n_workbook.save(file_path+'\\split\\{}计量工具清单.xlsx'.format(key))
app.quit()2、批量处理行、列和单元格
(1)调整行高列宽
xlwings设置行高列宽的方法：.row_height、.column_width、.autofit()
import os
import xlwings as xw
file_path = r'G:\D盘\2- 检测报告\计量仪器校准\split'
file_list = os.listdir(file_path)
app = xw.App(visible = True, add_book = False)
for i in file_list:
if i.startswith('~$'):
continue
if os.path.splitext(i)[1] != '.xlsx':
continue
file_paths = os.path.join(file_path,i)
workbook = app.books.open(file_paths)
worksheet = workbook.sheets[0]
worksheet.range('C2').column_width = 20 # 设置C列列宽20
worksheet.range('D2').column_width = 13
worksheet.range('F2').column_width = 12
worksheet.range('A1').row_height = 12 # 设置A1行行高
worksheet.autofit('r')
workbook.save()
workbook.close()
app.quit()(2)批量更改数据格式
-获取某列有数据的最后一行行号：sht['A1'].current_region.last_cell.row
-设置数据格式：sht['A2:A200'].number_format='￥#,##0.00'
-设置单元格填充颜色：sht['A2:H2'].Color=xw.utils.rgb_to_int((0,0,0))
-设置字体：.api.Font.size、.api.Font.name、.api.Font.Bold、.api.Font.Color
-设置水平对齐方式：.api.HorizontalAlignment=xw.constants.HAlign.xlHAlignCenter/Left/Right
-设置垂直对齐方式：.api.VerticalAlignment=xw.constants.Valign.xlVAlignCenter/Left/Right
-设置边框：cell.api.Borders(b).LineStyle=1 #设置线型; cell.api.Borders(b).Weight=1 #设置线宽
row_num = j['A1'].current_region.last_cell.row # 获取工作表中数据区域最后一行的行号
j['A2:A{}'.format(row_num)].number_format = 'm/d' #将A列数据更改为“月/日”的格式
j['B2:B{}}'.format(row_num)].number_format = '￥#,##0.00' #将B列改为会计货币符号和小数2位
j['A2:H2'].api.Font.Size = 10 # 字体大小
j['A2:H2'].api.Font.Name = '宋体'
j['A2:H2'].api.Font.Bold = True # 加粗
j['A2:H2'].api.Font.Color = xw.utils.rgb_to_int((255,255,255)) # 字体颜色白色
j['A2:H2'].Color = xw.utils.rgb_to_int((0,0,0)) # 单元格填充颜色为黑色
j['A2:H2'].api.HorizontalAlignment = xw.constants.HAlign.xlHAlignCenter # 水平对齐方式居中
j['A2:H2'].api.HorizontalAlignment = xw.constants.HAlign.xlHAlignLeft # 水平对齐方式靠左
j['A2:H2'].api.VerticalAlignment = xw.constants.Valign.xlVAlignCenter # 垂直对齐方式居中
for cell in j['A1'].expand('table'):
for b in range(7,12)
cell.api.Borders(b).LineStyle = 1 # 设置边框线型
cell.api.Borders(b).Weight = 2 # 设置边框粗细(3)批量替换行列数据
for j in workbook.sheets:
value = j['A2'].expand('table').value
for index, val in enumerate(value):
val[2] = val[2]*(1+0.05) # 批量修改列数据
if val == ['背包', 16, 65]:
value[index] = ['双肩包', 36, 79] #将行数据背包，16，65替换为双肩包，36，79
j['A2'].expand('table').value = value #将替换后的数据重新写入工作表
知识延伸：
enumerate是python 的内置函数，用于将一个可遍历的数据对象（如列表、元组或字符串）组合为一个索引序列。enumerate(sequence[, start=0])(4)批量对工作表数据进行分列
values = worksheet.range("A1").options(pd.DataFrame, header=1, index=False, expand='table').value
n_val = values['规格'].str.split('*', expand = True) # 根据*号拆分
values['长(mm)'] = n_val[0] #将拆分出的长写入长列
values['高(mm)'] = n_val[1] #将拆分出的长写入长列
values.drop(columns = ['规格'], inplace = True) # 将规格列删除
worksheet['A1'].options(index = False).value = values # 将拆分后的数据重新写入工作表中
知识延伸：
split()函数是pandas模块中Series对象的一个字符串函数，用于根据指定的分隔符拆分字符串。语法格式如下：
Series.str.split(pat=None, n=-1, expand=False) #pat为分隔符，省略则以空格作为分隔符。n为拆分次数省略或者为0或-1则无限拆分。expand指定拆分结果的格式，True为DATa Frame格式，False为Series格式。
drop()函数是pandas模块中DataFrame对象的函数，用于删除DataFrame对象的某一行或某一列。语法如下：
DataFrame.drop(label=None, axis=0, index=None, columns=None, inplace=False)(5)批量合并指定列
values = worksheet.range("A1").options(pd.DataFrame, header=1, index=False, expand='table').value
values['规格'] = values['长(mm)']+'*'+values['高(mm)']
知识延伸：
insert()函数是python列表对象的函数，用于在列表的指定位置插入元素。语法格式如下：
insert(index,obj)本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
