Python:使用 xlwings 处理Excel文档
参考:《Python+Excel办公自动化一本通》
官方使用文档:xlwings - Make Excel Fly!
Python中处理Excel文档的常见库对比
功能库 | xlwings | xlrd | xlwt | openpyxl | xlutils |
---|---|---|---|---|---|
读 | √ | √ | × | √ | √ |
写 | √ | × | √ | √ | √ |
修改 | √ | × | × | √ | √ |
xls 格式 | √ | √ | √ | × | √ |
xlsx 格式 | √ | √ | √ | √ | × |
批量操作 | √ | × | × | × | × |
由表可见,xlwings 可以同时完成读、写、修改,并且支持 xls 和 xlsx 格式,此外还有批量操作的功能。因此,xlwings 成为当前主流的处理 Excel 文档的库。
1、xlwings 的安装和简介
pip install xlwings 或 pip3 install xlwings
xlwings 中的基础概念包括 apps、books、sheets 和 ranges,其关系如图所示
其中,apps代表的是应用,相当于打开操作系统中的Excel软件(常见的有微软的Office、金山的WPS等)。一个APP可以操作多份Excel工作簿,books代表Excel工作簿,books就相当于一个个Excel文件;sheets则代表一个工作簿可以分为多份独立的工作表(Sheet),每个工作表由多个单元格组成,多个单元格可以组成区域(ranges)。
2、举例说明 xlwings 的使用
def create_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()
在当前目录下打开生成的xlsx表格,查看保存结果。
说明:
- 将 xlwings 简写作 xw,这也是通用的约定。
- 创建APP对象,就等同于开启 Excel应用,参数visible设置为False表示不打开Excel文档观看操作过程,默认是观看操作过程的;而add_book为False代表打开APP时不自动默认创建一个工作表,这样就可以准备打开Excel工作簿。
3、读/写 Excel 文档
- 3.1 读取单元格数据
使用 xlwings 读取 Excel 文档的数据很简单,使用 open 方法即可,但还需要注意一些读取的技巧。
有时可能需要读取 Excel 中的单个单元格或多个单元格,这里以上面创建的 ‘学生信息表.xlsx’ 为例进行示范。
def read_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).value cell_A2 = sheet.range('A2').value print(cell_val, "\n", cell_A2) # 获取多个单元格内容 row2_col2to4 = sheet.range((2,2), (2,4)).value C5_TO_F5 = sheet.range('C5:F5').value print(row2_col2to4, '\n', C5_TO_F5) # 获取多行数据 title = sheet.range('A1:F1').value data = sheet.range('A2:F5').value print(f"title: {title} \n data:") for row in data: print(row) if __name__ == "__main__": file_path = './学生信息表.xlsx' read_xlsx(file_path)
打印结果:
说明:
- 使用 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 单元格为对角的长方形中的数据
- 3.2 获取工作表的行数和列数
有时需要读取多份相同格式的Excel,标题是固定的,而数据的行数是变化的,这个时候就不能写成 ‘A2:F5’ 读取数据,而是需要自行判断读取多少行,为了解决这个问题,可以使用工作表(Sheet)的应用程序接口(API)来获取工作表的行数和列数,这里仍然以 ‘学生信息表.xlsx’ 为例
def get_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.count print(f'行数: {rows} \n列数: {cols}') if __name__ == "__main__": file_path = './学生信息表.xlsx' # read_xlsx(file_path) get_cols_rows(file_path)
打印结果
这样,我们就可以通过下面的方法来分别获取表格的 title 和 data 部分内容:
title = sheet.range((1,1), (1,cols)).value data = sheet.range((2,1), (rows,cols)).value
- 3.3 将数据写入 Excel
使用 xlwings 将数据写入Excel也不难,使用工作簿的 save 方法就可以,在上面的示例:‘学生信息表.xlsx’ 中已经展示了这一过程,下面再以 ‘学生科目成绩’ 为内容,向表格写入数据。
def write_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 = title sheet.range('A2').value = scores wb.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)
查看保存结果
有关注释不再重复赘述。
上面的代码只涉及为单元格赋值的基本方法,其他常见的方法通过下面的例子进行说明:
def write_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()
查看写入结果
有关注释已经写到代码里,结合保存结果不难理解。
4、设置单元格格式
对于单元格来说,还可以设置字体、表框,以及合并单元格等。在使用Excel时也常常会处理这类问题。
- 4.1 字体和格式化
有时需要设置字体,以满足不同的需求,如标题需要使用粗字体,而一些重要的内容应该使用红色加以强调等。此外,还有可能涉及时间和数字的问题,这个时候就需要考虑格式化的问题。下面使用xlwings来处理这些问题,在单元格中可以设置文字的字体、格式和对齐方式。
from datetime import datetime import xlwings as xw def format_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 # 设置字号为15 cell_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 = 3000000 cell_B1.api.NumberFormat = '¥#,###.00' # 水平方向对齐方式:-4108 居中, -4131 靠左, -4152 靠右 cell_B1.api.HorizontalAlignment = -4152 # 日期格式化 cell_C1 = sheet.range('C1') date = datetime.today() cell_C1.value = date cell_C1.api.NumberFormat = 'yyyy-mm-dd hh:MM:ss' cell_C1.api.HorizontalAlignment = -4108 # 垂直方向对齐方式: -4108 居中(默认), -4160 靠上, -4107 靠下, -4130 自动换行对齐 cell_C1.api.VerticalAlignment = -4107 wb.save('格式化测试.xlsx') wb.close() apps.quit() if __name__ == "__main__": format_test()
代码中已经有了详细的说明,供读者参考,查看保存结果如下
- 4.2 边框格式
除了可以设置单元格的字体,有时还需要设置边框,在制作表格时,这是常常使用到的,在 xlwings 中也可以做到。在单元格的API中存在边框(Border)属性需要设置,只是需要指定使用哪条边或对角线画边框,下面通过代码展示这个过程
import xlwings as xw app = 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 = 2 cell_C1.api.Borders(7).Weight = 3 # Borders(10) 右边框,LineStyle = 4 点画线 cell_E1 = sht.range('E1') cell_E1.api.Borders(10).LineStyle = 4 cell_E1.api.Borders(10).Weight = 3 # Borders(8) 顶部线,LineStyle = 5 双点画线 cell_A3 = sht.range('A3') cell_A3.api.Borders(8).LineStyle = 5 cell_A3.api.Borders(8).Weight = 3 # Borders(5) 单元格从左上角到右下角的对角线 cell_C3 = sht.range('C3') cell_C3.api.Borders(5).LineStyle = 1 cell_C3.api.Borders(5).Weight = 3 # Borders(6) 单元格从左下角到右上角的对角线 cell_E3 = sht.range('E3') cell_E3.api.Borders(6).LineStyle = 1 cell_E3.api.Borders(6).Weight = 3 wb.save('./边框测试.xlsx') wb.close() app.quit()
上述代码中有详尽的注释供读者参考,查看保存结果
除了可以设置单个单元格,还可以设置多个单元格,如下所示
import xlwings as xw app = 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 = 1 area.api.Borders(7).Weight = 3 # 右边框 area.api.Borders(10).LineStyle = 1 area.api.Borders(10).Weight = 3 # 顶部边框 area.api.Borders(8).LineStyle = 1 area.api.Borders(8).Weight = 3 # 内部垂直方向的直线,采用虚线 area.api.Borders(11).LineStyle = 2 area.api.Borders(11).Weight = 3 # 内部水平方向的直线,采用虚线 area.api.Borders(12).LineStyle = 2 area.api.Borders(12).Weight = 3 wb.save('./边框测试2.xlsx') wb.close() app.quit()
- 4.3 合并单元格和拆分单元格
在制作表格的过程中,合并单元格和拆分单元格是经常遇到的应用,如在当前目录下有文件:合并单元格和拆分单元格.xlsx,内容如下
表格中 B2 和 C2 单元格已经合并,假设需要将 B2 单元格的“人生苦短,我用Python”赋值到 B4 单元格,然后将 B4 单元格和 C4 单元格合并,最后将 B2 单元格拆分为 B2 和 C2 单元格。
import xlwings as xw app = 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_B2 wb.save(file_path) wb.close() app.quit()
拆分和合并各自调用API:UnMerge和Merge,查看修改后的文件:
- 4.4 调整单元格的宽度和高度
调整单元格的高度和宽度的代码如下
import xlwings as xw app = xw.App(visible=False) # 新添加Excel文档 wb = app.books.add() sht = wb.sheets['Sheet1'] # 设置高度和宽度 cell = sht.range('A1') cell.column_width = 60 cell.row_height = 35 # 设置字体 cell.api.Font.Name = '黑体' # 设置字体:黑体 cell.api.Font.Size = 30 cell.api.Font.Bold = True cell.api.Font.Color = 0xFF00FF cell.api.HorizontalAlignment = -4108 cell.value = '人生苦短,我用Python' wb.save('./高度和宽度.xlsx') wb.close() app.quit()
查看合并结果
5、处理一些常见的 Excel 场景
前面介绍了如何使用xlwings来读/写Excel文档,接下来需要考虑的是一些常见的Excel的场景处理,如同时处理多个Excel文档,在一个文档中同时写多个工作表,将一个工作表中的内容复制到其他的工作表。
- 5.1 新建和读/写多个Excel文档
使用xlwings可以很方便地保存或读取多个Excel文档,下面举例说明如何新建和保存多个Excel文档,代码如下:
import xlwings as xw app = xw.App(visible=False, add_book=False) for i in range(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()
上述代码使用for循环来创建工作簿,并且保存文件,此处循环了5次,因此保存了5份Excel文档。
随后通过下标0来读取创建的工作簿,然后在其第一个工作表的A1单元格写入“test1”,最后进行保存。这样就能同时新建多个Excel文档,同时选择具体的Excel文档进行操作。
当然,也可以打开多个Excel文档,如下:
import xlwings as xw app = xw.App(visible=False, add_book=False) for i in range(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').value print(val) wb.save() app.quit()
- 5.2 在同一个Excel文档中写入多个工作表
有时需要在同一个Excel文档中写入多个工作表,下面通过代码来举例:
import xlwings as xw app = xw.App(visible=False, add_book=False) # 打开工作簿 wb = app.books.open('./新建文件2.xlsx') for i in range(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()
首先通过for循环来添加5个工作表,接着通过下标0来获取最后一个工作表,然后写入内容,需要注意的是,0代表最后一个新建的工作表,而不是第一个新建的工作表。
- 5.3 复制数据
为了保存原始数据,有时人们会更倾向于复制数据,然后在新的文档或同一个文件的不同的工作表中来分析数据。将数据赋值到新的地方也很简单,下面通过举例来说明:
import xlwings as xw app = 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 = content wb1.save(filepath1) wb1.close() # 打开第二个Excel文档 filepath2 = './新建文件3.xlsx' wb2 = app.books.open(filepath2) sht2 = wb2.sheets[0] sht2.range('B2:C3').value = content wb2.save(filepath2) wb2.close() app.quit()
上述代码第一部分是在同一个Excel文档中新建一个工作表,然后将原有的数据复制到新建的工作表中;第二部分代码是打开了新的文档,然后将拷贝内容写入新文档的第一个工作表中。这样就能够将对应的内容复制到其他的文件中,然后进行数据分析,从而保证原始数据不被修改。
- 5.3 复制数据
- 5.2 在同一个Excel文档中写入多个工作表
- 5.1 新建和读/写多个Excel文档
- 4.4 调整单元格的宽度和高度
- 4.3 合并单元格和拆分单元格
- 4.2 边框格式
- 4.1 字体和格式化
- 3.3 将数据写入 Excel
- 3.2 获取工作表的行数和列数
- 3.1 读取单元格数据