Python中使用xlwings库处理Excel文档

参考：《Python+Excel办公自动化一本通》
官方使用文档：xlwings – Make Excel Fly!

Python中处理Excel文档的常见库对比

由表可见，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表格，查看保存结果。

说明：

3、读/写 Excel 文档

使用 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)

打印结果：

说明：

sheet.range((2,2), (2,4)).value	# 第二行第二个单元格到第二行第四个单元格
sheet.range('C5:F5').value	# C5 单元格到 F5 单元格
sheet.range('A2:F5').value	# 读取以 A2 单元格和 F5 单元格为对角的长方形中的数据

有时需要读取多份相同格式的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

使用 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时也常常会处理这类问题。

有时需要设置字体，以满足不同的需求，如标题需要使用粗字体，而一些重要的内容应该使用红色加以强调等。此外，还有可能涉及时间和数字的问题，这个时候就需要考虑格式化的问题。下面使用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()

代码中已经有了详细的说明，供读者参考，查看保存结果如下

除了可以设置单元格的字体，有时还需要设置边框，在制作表格时，这是常常使用到的，在 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()

在制作表格的过程中，合并单元格和拆分单元格是经常遇到的应用，如在当前目录下有文件：合并单元格和拆分单元格.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,查看修改后的文件：

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文档，在一个文档中同时写多个工作表，将一个工作表中的内容复制到其他的工作表。

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

有时需要在同一个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代表最后一个新建的工作表，而不是第一个新建的工作表。

为了保存原始数据，有时人们会更倾向于复制数据，然后在新的文档或同一个文件的不同的工作表中来分析数据。将数据赋值到新的地方也很简单，下面通过举例来说明：

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文档中新建一个工作表，然后将原有的数据复制到新建的工作表中；第二部分代码是打开了新的文档，然后将拷贝内容写入新文档的第一个工作表中。这样就能够将对应的内容复制到其他的文件中，然后进行数据分析，从而保证原始数据不被修改。