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

说明：

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