Python模块之xlwings模块学习笔记（二）

您所在的位置：网站首页 › excel列宽自动调整设置 › Python模块之xlwings模块学习笔记（二）

Python模块之xlwings模块学习笔记（二）

2024-06-20 15:12| 来源: 网络整理| 查看: 265

前言

一、 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)

【本文地址】

Python模块之xlwings模块学习笔记（二）

Python模块之xlwings模块学习笔记（二）

今日新闻

推荐新闻