Python：使用xlwings处理Excel文档

茫茫宇宙

参考：《Python+Excel办公自动化一本通》官方使用文档：xlwings-MakeExcelFly!Python中处理Excel文档的常见库对比功能库xlwingsxlrdxlwtopenpyxlxlutils读√√×√√写√×√√√修改√××√√xls格式√√√×√xlsx格式√√√√×批量操作√××××由表可见，xlwings可以同时完成读、写、修改，并且支持xls和xlsx格式，此外还有批量操作的功能。因此，xlwings成为当前主流的处理Excel文档的库。1、xlwings的安装和简介pipinstallxlwings或pip3installxlwings1xlwings中的基础概念包括apps、books、sheets和ranges，其关系如图所示其中，apps代表的是应用，相当于打开操作系统中的Excel软件（常见的有微软的Office、金山的WPS等）。一个APP可以操作多份Excel工作簿，books代表Excel工作簿，books就相当于一个个Excel文件；sheets则代表一个工作簿可以分为多份独立的工作表（Sheet），每个工作表由多个单元格组成，多个单元格可以组成区域（ranges）。2、举例说明xlwings的使用defcreate_xlsx():"""1、创建xlsx表格并写入数据"""std_array=[['学生编号','学生姓名','出生日期','班级编号','性别','籍贯'],['0001','张三','1990/12/5','1001','男','广东'],['0002','李四','1991/2/25','1001','女','山东'],['0003','王五','1991/8/16','1002','男','江苏'],['0004','赵六','1992/6/21','1002','女','四川']] #开启Excel应用，参数visible表示处理过程是否可视，如果add_book为False则代表默认不创建空白工作簿app=xw.App(visible=False,add_book=False)#添加工作簿，相当于一个Excel文档wb=app.books.add()#添加名为'学生信息表'的sheet，准备写入数据sheet=wb.sheets.add('学生信息表')#在单元格A1中开始写入数组sheet.range('A1').value=std_array#保存工作簿wb.save(file_path)#关闭工作簿wb.close()#退出Excel编辑应用app.quit()if__name__=="__main__":file_path='./学生信息表.xlsx'create_xlsx()12345678910111213141516171819202122232425在当前目录下打开生成的xlsx表格，查看保存结果。说明：将xlwings简写作xw，这也是通用的约定。创建APP对象，就等同于开启Excel应用，参数visible设置为False表示不打开Excel文档观看操作过程，默认是观看操作过程的；而add_book为False代表打开APP时不自动默认创建一个工作表，这样就可以准备打开Excel工作簿。3、读/写Excel文档3.1读取单元格数据使用xlwings读取Excel文档的数据很简单，使用open方法即可，但还需要注意一些读取的技巧。有时可能需要读取Excel中的单个单元格或多个单元格，这里以上面创建的‘学生信息表.xlsx’为例进行示范。defread_xlsx(file_path):"""2、读Excel文档"""app=xw.App(visible=False,add_book=False)wb=app.books.open(file_path)#获取工作表的两种方法#sheet=wb.sheets('学生信息表')sheet=wb.sheets[0]#获取单个单元格内容cell_val=sheet.range(1,1).valuecell_A2=sheet.range('A2').valueprint(cell_val,"\n",cell_A2)#获取多个单元格内容row2_col2to4=sheet.range((2,2),(2,4)).valueC5_TO_F5=sheet.range('C5:F5').valueprint(row2_col2to4,'\n',C5_TO_F5)#获取多行数据title=sheet.range('A1:F1').valuedata=sheet.range('A2:F5').valueprint(f"title:{title}\ndata:")forrowindata:print(row)if__name__=="__main__":file_path='./学生信息表.xlsx'read_xlsx(file_path)1234567891011121314151617181920212223242526打印结果：说明：使用open函数来打开对应的Excel文档，这样就可以读取一个工作簿（WorkBook）；通过工作簿打开一个工作表（Sheet），有两种方法，即使用工作表的名称或使用整数下标（下标是从0开始的）；使用Sheet的range方法读取单元格，方法也有两种，即sheet.range(1,1)和sheet.range(‘A1’)，都表示读取第一行第一个单元格，需要注意的是，在xlwings中对Excel单元格的索引是从1开始的，而不是从0开始；使用Sheet的range方法还可以连续读取数个单元格以及读取多行数据，如下：sheet.range((2,2),(2,4)).value #第二行第二个单元格到第二行第四个单元格sheet.range('C5:F5').value #C5单元格到F5单元格sheet.range('A2:F5').value #读取以A2单元格和F5单元格为对角的长方形中的数据1233.2获取工作表的行数和列数有时需要读取多份相同格式的Excel，是固定的，而数据的行数是变化的，这个时候就不能写成‘A2:F5’读取数据，而是需要自行判断读取多少行，为了解决这个问题，可以使用工作表（Sheet）的应用程序接口（API）来获取工作表的行数和列数，这里仍然以‘学生信息表.xlsx’为例defget_cols_rows(file_path):"""获取表格总的行数与列数"""app=xw.App(visible=False,add_book=False)wb=app.books.open(file_path)sheet=wb.sheets('学生信息表')#Excel数据的总行数rows=sheet.api.UsedRange.Rows.count#列数cols=sheet.api.UsedRange.Columns.countprint(f'行数:{rows}\n列数:{cols}')if__name__=="__main__":file_path='./学生信息表.xlsx'#read_xlsx(file_path)get_cols_rows(file_path)12345678910111213141516打印结果这样，我们就可以通过下面的方法来分别获取表格的title和data部分内容：title=sheet.range((1,1),(1,cols)).valuedata=sheet.range((2,1),(rows,cols)).value123.3将数据写入Excel使用xlwings将数据写入Excel也不难，使用工作簿的save方法就可以，在上面的示例：‘学生信息表.xlsx’中已经展示了这一过程，下面再以‘学生科目成绩’为内容，向表格写入数据。defwrite_to_Excel(file_path):"""将数据写入Excel"""#title=['编号','学号','姓名','科目','年份','分数']#分数scores=[[1,1,'张三','数学',2018,98],[2,1,'张三','数学',2019,96],[3,1,'张三','数学',2020,96],[4,1,'张三','语文',2018,98],[5,1,'张三','语文',2019,92],[6,1,'张三','语文',2020,91],[7,2,'李四','语文',2018,85],[8,2,'李四','语文',2019,82],[9,2,'李四','语文',2020,84],[10,2,'李四','数学',2018,86],[11,2,'李四','数学',2019,88],[12,2,'李四','数学',2020,90]]apps=xw.App(visible=False,add_book=False)wb=apps.books.add()sheet=wb.sheets.add('分数')sheet.range('A1').value=titlesheet.range('A2').value=scoreswb.save(file_path)wb.close()apps.quit()if__name__=="__main__":file_path='./学生信息表.xlsx'file_path_score='./学生科目成绩表.xlsx'#read_xlsx(file_path)#get_cols_rows(file_path)write_to_Excel(file_path_score)123456789101112131415161718192021222324252627282930313233查看保存结果有关注释不再重复赘述。上面的代码只涉及为单元格赋值的基本方法，其他常见的方法通过下面的例子进行说明：defwrite_to_Excel2():"""其它的为单元格赋值的方法"""apps=xw.App(visible=False,add_book=False)wb=apps.books.add()sheet=wb.sheets.add('写入测试')#写入一个列表sheet.range('A1').value=[1,2,3]#从纵向写入一个列表sheet.range('A2').options(transpose=True).value=[4,7]#在B2:C3单元格写入一个二维数组sheet.range('B2').value=[[5,6],[8,9]]#在单元格内写入Excel公式sheet.range('A4').formula='=sum(A1:A3)'sheet.range('B4').formula='=sum(B1:B3)'sheet.range('C4').formula='=sum(C1:C3)'wb.save('./写入测试.xlsx')wb.close()apps.quit()if__name__=="__main__":file_path='./学生信息表.xlsx'file_path_score='./学生科目成绩表.xlsx'#read_xlsx(file_path)#get_cols_rows(file_path)#write_to_Excel(file_path_score)write_to_Excel2()1234567891011121314151617181920212223242526272829查看写入结果有关注释已经写到代码里，结合保存结果不难理解。4、设置单元格格式对于单元格来说，还可以设置字体、表框，以及合并单元格等。在使用Excel时也常常会处理这类问题。4.1字体和格式化有时需要设置字体，以满足不同的需求，如需要使用粗字体，而一些重要的内容应该使用红色加以强调等。此外，还有可能涉及时间和数字的问题，这个时候就需要考虑格式化的问题。下面使用xlwings来处理这些问题，在单元格中可以设置文字的字体、格式和对齐方式。fromdatetimeimportdatetimeimportxlwingsasxwdefformat_test():"""设置单元格的格式"""apps=xw.App(visible=False,add_book=False)wb=apps.books.add()sheet=wb.sheets.add('字体')cell_A1=sheet.range('A1')cell_A1.value='字符串'#获取单元格的字体属性font_name=cell_A1.api.Font.Name #获取字体名称font_size=cell_A1.api.Font.Size #获取字号font_bold=cell_A1.api.Font.Bold #获取是否加粗，True表示加粗，False表示不加粗font_color=cell_A1.api.Font.Color #获取字体颜色print(f'字体:{font_name},字号:{font_size},加粗:{font_bold},颜色:{font_color}')#设置字体属性cell_A1.api.Font.Name='华文仿宋' #设置字体：华文仿宋cell_A1.api.Font.Size=15 #设置字号为15cell_A1.api.Font.Bold=True #加粗cell_A1.api.Font.Color=0x0000FF #设置为红色RGB（255,0，0）#添加下划线cell_A1.api.Font.Underline=2#金额(数字)格式化cell_B1=sheet.range('B1')cell_B1.value=3000000cell_B1.api.NumberFormat='￥#,###.00'#水平方向对齐方式：-4108居中,-4131靠左,-4152靠右cell_B1.api.HorizontalAlignment=-4152 #日期格式化cell_C1=sheet.range('C1')date=datetime.today()cell_C1.value=datecell_C1.api.NumberFormat='yyyy-mm-ddhh:MM:ss'cell_C1.api.HorizontalAlignment=-4108#垂直方向对齐方式:-4108居中(默认),-4160靠上,-4107靠下,-4130自动换行对齐cell_C1.api.VerticalAlignment=-4107wb.save('格式化测试.xlsx')wb.close()apps.quit()if__name__=="__main__":format_test()1234567891011121314151617181920212223242526272829303132333435363738394041424344454647代码中已经有了详细的说明，供读者参考，查看保存结果如下4.2边框格式除了可以设置单元格的字体，有时还需要设置边框，在制作表格时，这是常常使用到的，在xlwings中也可以做到。在单元格的API中存在边框（Border）属性需要设置，只是需要指定使用哪条边或对角线画边框，下面通过代码展示这个过程importxlwingsasxwapp=xw.App(visible=False,add_book=False)wb=app.books.add()sht=wb.sheets.add('方格边框')#Borders(9)底部边框，LineStyle=1直线cell_A1=sht.range('A1')cell_A1.api.Borders(9).LineStyle=1#设置边框粗细cell_A1.api.Borders(9).Weight=3#设置边框颜色cell_A1.api.Borders(9).Color=0x0000FF#Borders(7)左边框，LineStyle=2虚线cell_C1=sht.range('C1')cell_C1.api.Borders(7).LineStyle=2cell_C1.api.Borders(7).Weight=3#Borders(10)右边框，LineStyle=4点画线cell_E1=sht.range('E1')cell_E1.api.Borders(10).LineStyle=4cell_E1.api.Borders(10).Weight=3#Borders(8)顶部线，LineStyle=5双点画线cell_A3=sht.range('A3')cell_A3.api.Borders(8).LineStyle=5cell_A3.api.Borders(8).Weight=3#Borders(5)单元格从左上角到右下角的对角线cell_C3=sht.range('C3')cell_C3.api.Borders(5).LineStyle=1cell_C3.api.Borders(5).Weight=3#Borders(6)单元格从左下角到右上角的对角线cell_E3=sht.range('E3')cell_E3.api.Borders(6).LineStyle=1cell_E3.api.Borders(6).Weight=3wb.save('./边框测试.xlsx')wb.close()app.quit()12345678910111213141516171819202122232425262728293031323334353637383940414243上述代码中有详尽的注释供读者参考，查看保存结果除了可以设置单个单元格，还可以设置多个单元格，如下所示importxlwingsasxwapp=xw.App(visible=False,add_book=False)wb=app.books.add()sht=wb.sheets.add('方格边框')#选择多个单元格进行操作area=sht.range('B2:E5')#底部边框，LineStyle=1直线area.api.Borders(9).LineStyle=1#设置边框粗细area.api.Borders(9).Weight=3#左边框area.api.Borders(7).LineStyle=1area.api.Borders(7).Weight=3#右边框area.api.Borders(10).LineStyle=1area.api.Borders(10).Weight=3#顶部边框area.api.Borders(8).LineStyle=1area.api.Borders(8).Weight=3#内部垂直方向的直线，采用虚线area.api.Borders(11).LineStyle=2area.api.Borders(11).Weight=3#内部水平方向的直线，采用虚线area.api.Borders(12).LineStyle=2area.api.Borders(12).Weight=3wb.save('./边框测试2.xlsx')wb.close()app.quit()123456789101112131415161718192021222324252627282930313233343536374.3合并单元格和拆分单元格在制作表格的过程中，合并单元格和拆分单元格是经常遇到的应用，如在当前目录下有文件：合并单元格和拆分单元格.xlsx，内容如下表格中B2和C2单元格已经合并，假设需要将B2单元格的“人生苦短，我用Python”赋值到B4单元格，然后将B4单元格和C4单元格合并，最后将B2单元格拆分为B2和C2单元格。importxlwingsasxwapp=xw.App(visible=False)#打开Excel文档file_path='./合并单元格和拆分单元格.xlsx'wb=app.books.open(file_path)sht=wb.sheets['Sheet1']#获取B2单元格的值val_B2=sht.range('B2').value#将B2单元格拆分为B2单元格和C2单元格sht.range('B2:C2').api.UnMerge()#清空B2单元格的值sht.range('B2').value=''#合并B4单元格和C4单元格sht.range('B4:C4').api.Merge()#为B4单元格赋值sht.range('B4').value=val_B2wb.save(file_path)wb.close()app.quit()12345678910111213141516171819202122拆分和合并各自调用API：UnMerge和Merge,查看修改后的文件：4.4调整单元格的宽度和高度调整单元格的高度和宽度的代码如下importxlwingsasxwapp=xw.App(visible=False)#新添加Excel文档wb=app.books.add()sht=wb.sheets['Sheet1']#设置高度和宽度cell=sht.range('A1')cell.column_width=60cell.row_height=35#设置字体cell.api.Font.Name='黑体'#设置字体：黑体cell.api.Font.Size=30cell.api.Font.Bold=Truecell.api.Font.Color=0xFF00FFcell.api.HorizontalAlignment=-4108cell.value='人生苦短，我用Python'wb.save('./高度和宽度.xlsx')wb.close()app.quit()123456789101112131415161718192021查看合并结果5、处理一些常见的Excel场景前面介绍了如何使用xlwings来读/写Excel文档，接下来需要考虑的是一些常见的Excel的场景处理，如同时处理多个Excel文档，在一个文档中同时写多个工作表，将一个工作表中的内容复制到其他的工作表。5.1新建和读/写多个Excel文档使用xlwings可以很方便地保存或读取多个Excel文档，下面举例说明如何新建和保存多个Excel文档，代码如下：importxlwingsasxwapp=xw.App(visible=False,add_book=False)foriinrange(1,6):#添加一个工作簿，相当于一个Excel文档wb=app.books.add()#文件名file_name=f'新建文件{i}.xlsx'#保存文件wb.save(file_name)#选择第一个新建的工作簿，下标是从0开始的wb=app.books[0]sht=wb.sheets[0]#给A1单元格赋值sht.range('A1').value='test1'wb.save()app.quit()12345678910111213141516171819上述代码使用for循环来创建工作簿，并且保存文件，此处循环了5次，因此保存了5份Excel文档。随后通过下标0来读取创建的工作簿，然后在其第一个工作表的A1单元格写入“test1”，最后进行保存。这样就能同时新建多个Excel文档，同时选择具体的Excel文档进行操作。当然，也可以打开多个Excel文档，如下：importxlwingsasxwapp=xw.App(visible=False,add_book=False)foriinrange(1,6):#文件名file_name=f'新建文件{i}.xlsx'#打开工作簿wb=app.books.open(file_name)#选择第一个新建的工作簿wb=app.books[0]#选择该工作簿的工作表sht=wb.sheets[0]#获取A1单元格的值val=sht.range('A1').valueprint(val)wb.save()app.quit()12345678910111213141516171819205.2在同一个Excel文档中写入多个工作表有时需要在同一个Excel文档中写入多个工作表，下面通过代码来举例：importxlwingsasxwapp=xw.App(visible=False,add_book=False)#打开工作簿wb=app.books.open('./新建文件2.xlsx')foriinrange(1,6):#工作表的名称sht_name=f'sheet_{i}'#新增一个工作表sht=wb.sheets.add(sht_name)#在最后一个新建的工作表中写入内容wb.sheets[0].range('B2:C3').value=[[1,3],[2,4]]wb.save()app.quit()1234567891011121314151617首先通过for循环来添加5个工作表，接着通过下标0来获取最后一个工作表，然后写入内容，需要注意的是，0代表最后一个新建的工作表，而不是第一个新建的工作表。5.3复制数据为了保存原始数据，有时人们会更倾向于复制数据，然后在新的文档或同一个文件的不同的工作表中来分析数据。将数据赋值到新的地方也很简单，下面通过举例来说明：importxlwingsasxwapp=xw.App(visible=False,add_book=False)#打开第一个工作簿filepath1='./新建文件2.xlsx'wb1=app.books.open(filepath1)#选择工作表sht1=wb1.sheets[0]#需要复制的内容content=sht1.range('B2:C3').value#新建工作表，用于存放复制的内容new_sht=wb1.sheets.add('复制内容')#填入复制的内容new_sht.range('B2').value=contentwb1.save(filepath1)wb1.close()#打开第二个Excel文档filepath2='./新建文件3.xlsx'wb2=app.books.open(filepath2)sht2=wb2.sheets[0]sht2.range('B2:C3').value=contentwb2.save(filepath2)wb2.close()app.quit()1234567891011121314151617181920212223242526上述代码第一部分是在同一个Excel文档中新建一个工作表，然后将原有的数据复制到新建的工作表中；第二部分代码是打开了新的文档，然后将拷贝内容写入新文档的第一个工作表中。这样就能够将对应的内容复制到其他的文件中，然后进行数据分析，从而保证原始数据不被修改。