xlwings模块操作指引

这是一篇关于xlwings模块操作指引的思维导图，主要内容有学习途径、工作簿、工作表、单元格等操作方法。

xlwings模块

• xlwings依赖于pywin32，需要安装有excel软件，支持xls和xlsx格式（这是与openpyxl最大区别，openpyxl不需要安装excel，但仅支持xlsx格式） xlwings与Excel VBA无缝对接，如学习过VBA，用xlwings将十分顺手，可以调用 Excel 文件中 VBA 写好的程序； 与openpyxl相比，xlwings可以在原文件上写入内容（但牺牲了读写速度，没有后者快），但openpyxl不支持原文件写入 和 matplotlib 以及 pandas 的兼容性强。
• 学习路径
  • 官网中文说明文档
  • 其他参考学习文档
  • 导入模块
    • pip install xlwings #安装xlwings模块
    • import xlwings as xw #导入模块
      • 在xlwings中是不区分大小写的，SHEET1和sheet1的含义一样
  • 核心思想
    • 关系图
      • 可以看到，和 xlwings 直接对接的是 apps，也就是 Excel 应用程序，然后才是 工作簿 books 和工作表 sheets，这点和 openpyxl 有较大区别，也正是因为此，xlwings 需要依然安装有 Excel 应用程序的环境
• 工作簿
  • 打开Excel程序
    • app=xw.App(visible=True,add_book=False) #启动excel程序设置
      • Excel程序用App来表示，多个Excel程序集合用Apps表示 我们要创建工作簿，首先要创建App。一个App可以创建多个工作簿。也就是说，我们可以通过xlwings创建多个App，每个App又可以创建多个工作薄，这些App是相互独立的，只能管理它自己创建的工作簿。需要注意的是，当我们手动打开一个工作簿的时候，系统也会自动帮我们创建一个App。 visible用来设置程序是否可见，True表示可见（默认），Flase不可见。 add_book用来设置是否自动创建工作簿，True表示自动创建（默认），False不创建。 当设置成add_book=False时，可以创建App，但是还未生成PID，只有当这个App创建了工作簿后，才会生成自己的PID。
    • app.display_alerts = False # 警告关闭
    • app.screen_updating = False # 屏幕更新关闭
  • 新建工作簿
    • wb = xw.Book() # 在新的app下创建一个新的工作簿
    • wb = app.books.add()# 在当前的app下创建一个新的工作簿
    • 两者都是新建工作簿，但有区别
  • 打开工作簿
    • wb = xw.Book('test.xlsx') # 连接到当前工作目录中的现有文件
      • 参数设置Book(fullname=None, update_links=None, read_only=None, format=None, password=None, write_res_password=None, ignore_read_only_recommended=None,origin=None, delimiter=None, editable=None, notify=None, converter=None,add_to_mru=None, local=None, corrupt_load=None, impl=None)
    • wb = app.books.open('test.xlsx') # 打开当前工作目录中的现有文件
    • wb = xw.Book(r'C:\path\to\file.xlsx') # 在Windows上：使用原始字符串来转义反斜杠，打开绝对路径工作簿
    • wb = app.books.open(r'C:UserszhouxDesktoptest.xlsx')
  • 引用工作簿
    • 引用已打开的工作簿
    • wb = xw.Book('test.xlsx') #引用工作簿
      • xw.Book('test.xlsx')既可以打开工作薄也可以引用工作簿。也就是说，当工作簿未打开时，它可以打开工作薄，当工作簿处于打开状态时，它可以引用工作簿。而app.books('test1.xlsx')只可以引用工作簿。 xw.Book 提供了连接到工作簿的最简单的方法： 它在所有的app实例中查找指定的工作簿，如果同一个工作簿在多个app实例中存在，就会返回一个错误信息。每个App都可以打开属于自己的工作簿，两个不同的App可以打开同一个工作簿。如使用App1和App2都打开了test.xlsx工作簿时，使用app.books('test1.xlsx')的方式引用test1.xlsx当然没有问题，因为每个App都知道哪个test1是属于自己的，但是当使用xw.Book('test1.xlsx')的方式引用工作簿就会产生问题，因为这种方式会从App1和App2中找到两个test1，然而它并不知道想要引用的是哪一个，此时就会报错。
    • wb = app.books('test.xlsx') #引用工作簿
      • 也可以用数字来代替文件名，比如wb = app.books(1)表示当前app打开的第一个工作簿，此处有小括号和中括号之分，小括号的序号是从1开始的，中括号（切片）是从0开始计数，也就是说，app.books(1) 等同于 app.books[0]
    • wb = app.books.active #获取当前活动的工作簿
  • 激活工作簿
    • wb.activate()
    • wb.activate(steal_focus=True)
      • 如果steal_focus=True, 则把窗口显示到最上层，并且把焦点从Python切换到Excel
  • 保存工作簿
    • wb.save() #默认保存在工作薄所在的路径
      • 同名文件会在没有提示的情况下被直接覆盖
    • wb.save(r'D:test.csv') #另存为时，我们不仅能够指定保存的位置，还可以指定保存的格式
  • 关闭工作簿
    • wb.close() #直接关闭工作簿，不会进行保存，如需保存，可在之前wb.save()
    • app.quit() #退出excel程序，关闭工作簿后一般需要退出程序
  • 通用代码
    • import xlwings as xw # 导入xlwings模块 app=xw.App(visible=True,add_book=False) # 启动Excel程序 app.display_alerts = False # 警告关闭 app.screen_updating = False # 屏幕更新关闭 path=r"E:\python" wb=app.books.open(path+r'\test.xlsx') #打开工作簿 核心处理代码（此处省略） wb.save() #保存工作簿 wb.close() #关闭工作簿 app.quit() #退出Excel程序
  • 工作簿属性
    • wb.fullname # 获取工作簿的绝度路径
    • wb.name # 获取工作薄名称（带扩展名）
    • wb.app # 获取创建工作簿的App
    • wb.names # 返回工作薄中定义过的所有命名区域
      • 命名区域可在公式 - 名称管理器中查看
    • wb.app.calculation = 'manual' # 更改计算模式
      • calculation的值表示了工作簿的计算模式，有: manual(手动) , automatic(自动) , semiautomatic(半自动)三种方式
• 工作表
  • 所有工作表
    • sheets = xw.sheets # 当前活动工作簿
    • sheets = wb.sheets # 指定工作簿
    • 包括隐藏工作表及深度隐藏工作表
  • 新建工作表
    • sht = wb.sheets.add('test',after='sheet2') # 在sheet2之后插入工作表test
      • 参数1为工作表名称，省略的话为Excel默认名称 参数2为插入位置，可选before或者after，示例中的代码表示插入到sheet2之后，省略的话插入到当前活动工作表之前工作表名称重复的话会报错 两个参数均省略，如sht=wb.sheeets.add() # 在最前面新建工作表
  • 引用工作表
    • sht = wb.sheets('sheet1') # 指定表名为sheet1的工作表
    • sht = wb.sheets(1) # 第一个工作表
    • sht = wb.sheets[1] # 获取索引为1的工作表
      • 索引号从0开始
    • sht = xb.sheets.active # 当前工作簿活动工作表
    • sht = xw.sheets.active # 当前活动工作表
  • 选定工作表
    • sht.select()
      • 只能在活动工作簿中选择
  • 激活工作表
    • sht.activate()
  • 清除工作表
    • sht.clear() # '清除工作表所有内容和格式
      • clear()不仅可以清除背景色等格式，还可以清除数据有效性和条件格式等，可以清除受保护的工作表的内容
    • sht.clear_contents() # '清除工作表的所有内容但是保留原有格式
  • 删除工作表
    • sht.delete()
      • 可以删除隐藏的工作表，但是不能删除深度隐藏的工作表
  • 工作表属性
    • sht.name # 返回工作表名称
    • sht.name = 'rename' # 重命名工作表名称
    • sht.book # 返回指定工作表所属的工作簿
    • sht.cells # 工作表上所有单元格的区域对象
      • 包括所有的单元格，不仅仅是那些正在使用中的单元格
    • sht.index # 返回工作表的索引值
      • 按照Excel的方式，从1开始的
    • sht.names # 返回所有与本工作表有关的命名区域
    • sht.used_range # 工作表中用过的区域
      • UsedRange属性返回工作表中所有已使用范围的单元格区域，而不管该区域数据间是否有空行或空格。特别注意：UsedRange属性返回工作表中所有已使用范围的单元格区域是指：单元格中有数值、公式、单元格格式化设置（例如：单元格字体设置、边框设置等等）
  • 调整工作表
    • sht.autofit('c') # 自动调整列宽
      • 要做行自适应，用 rows 或 r； 要做列自适应，用 columns 或 c； 同时做行和列的自适应，不需要参数。
• 单元格
  • 注意事项：Excel单元格不要处于编辑状态，不然代码会卡主，无法运行
  • 引用区域
    • xw.Range('A1:C3') # 区域操作符号表示法
    • xw.Range('A1)cells() # A1表示法
    • xw.Range((1,1), (3,3)) # 坐标元组表示法
    • xw.Range(xw.Range('A1'),xw.Range('C3'))
    • xw.Range(xw.Range('A1:F6'),xw.Range('B2:D5'))
    • xw.Range('NamedRange') # 名字对象表示法
    • app.range("A1")
    • sht.range('A1')
  • 返回区域
    • app.selection
    • wb.selection
  • 调整区域
    • 偏移区域
      • 将选区范围进行偏移，row_offset决定行偏移量(正数表示向下偏移，负数相反)，column_offset决定列偏移量(正数表示向右偏移，负数相反) 注意：是将选区范围进行偏移，而不是将选区内容进行偏移
      • rng.offset(row_offset=3,column_offset=5) #返回一个从指定单元格为起始点的区域对象
    • 扩展区域
      • 参数可选取 'down' , 'right' ,'table' ，效果分别等同于Ctrl+Shift+↓（选中整数据列），Ctrl+Shift+→（选中整数据行），Ctrl+Shift+↓→（选中整数据区域） 注意：实际效果与预期的可能并不相符，尤其是存在空白单元格的时候，看自己的需要
      • rng.expand(mode='table') #扩展到当前整个表
    • 调整区域大小
      • 参数:row_size (int > 0)新的区域的行数(如果为 None , 区域保持原来的行数不变) column_size (int > 0)新的区域的列数(如果为 None , 区域保持原来的列数不变)
      • rng.resize(row_size=6, column_size=4) #将区域调整成6*4
    • 全选区域
      • 与Ctrl + A效果相同
      • rng.current_region #获取当前数据区域
  • 区域属性
    • 名称管理
      • 如果没有名称，则返回空
      • rng.name #获取区域名称
      • rng.name = "区域1" #设置区域名称
    • 获取区域宽高
      • rng.height #返回区域高
      • rng.width #返回区域宽
    • 计数
      • rng.count #返回区域内单元格数量
      • rng.size #返回区域内单元格数量
    • 定位边界单元格
      • 参数可选：'up'，'down'，'right'，'left'。得到的结果与按 Ctrl+Up , Ctrl+down , Ctrl+left 或 Ctrl+right 组合键得到的结果相同
      • rng.end(direction) #返回区域内的边界单元格
      • rng.end('down') #返回区域内的下边界单元格
    • 定位右下角单元格
      • rng.last_cell # 返回指定区域的右下角单元格
    • 返回区域所属的工作表
      • rng.sheet
    • 返回区域引用字符串
      • rng.address #返回：'$C$1:$D$1'
      • rng.get_address(row_absolute=True, column_absolute=True, include_sheetname=False, external=False) #个性化获取区域字符串
        • 参数:row_absolute (bool, default True) – 设为 True 时，返回行部分的绝对引用。 column_absolute (bool, default True) – 设为 True 时，返回列部分的绝对引用。 include_sheetname (bool, default False) – 设为 True 时，返回的地址中包含工作表名。如果 external=True ，不管这里的设置如何，都带工作表名。 external (bool, default False) – 设为 True 时，返回带有工作簿名和工作表名的外部引用地址。
    • 返回A列的左边缘到区域的左边界的距离
      • rng.left
    • 返回从第一行的边缘到区域边缘的距离
      • rng.top
  • 区域方法
    • 1. 选中区域
      • 注意：只能在活动的工作表中选，如果rng区域并不属于当前活动工作表，就会报错。
      • rng.select()
    • 2. 赋值
      • rng.value #获取区域值
      • rng.value = 1 #设置区域的值
    • 3. 设置公式
      • rng.formula # 获取公式
      • rng.formula = "=1+5" #设置公式
      • rng.formula_array #获取数组公式
      • rng.formula_array='=sum(b1:b5)' #设置数组公式
      • rng.has_array # 检测区域内是否包含数组公式
    • 4. 设置数字格式
      • rng.number_format #获取数字格式
      • rng.number_format = '0.00%' #设置数字格式
    • 5. 复制
      • rng.copy(destination=None) #把一个区域拷贝到目的区域或者剪贴板
        • 参数：destination 设置目标区域，如果省略，rng区域会被拷贝到剪贴板上
    • 6. 粘贴
      • rng.paste(paste=None, operation=None, skip_blanks=False, transpose=False) # 将剪贴板里内容粘贴到指定区域
        • 参数：pasteall_merging_conditional_formats(将粘贴所有内容，并且将合并条件格式) all(粘贴全部内容) all_except_borders(粘贴除边框外的全部内容) all_using_source_theme(使用源主题粘贴全部内容) column_widths(粘贴复制的列宽) comments(粘贴批注) formats(粘贴复制的源格式) formulas(粘贴公式) formulas_and_number_formats(粘贴公式和数字格式) validation(粘贴有效性) values(粘贴值) values_and_number_formats(粘贴值和数字格式) operationadd(加) divide(除) multiply(乘) subtract(减) skip_blanks (bool, default False)设为 True 时忽略空白单元格 transpose (bool, default False)设为 True 时对行列转置
    • 7. 清除
      • rng.clear() #清除区域的内容和格式
      • rng.clear_contents() #清除区域的内容，保留格式
    • 8. 删除
      • rng.delete(shift=None)
        • 参数：shift (str, default None) – 使用 left 或 up 。如果省略，Excel根据区域的形状决定，可以理解为右侧单元格左移 或者 下方单元格上移 注意：区域删除后，会被其他单元格补上，但是此时xlwings中的rng已经没了，需要重新选定这个区域才能继续操作
    • 9. 插入
      • rng.insert(shift=None, copy_origin='format_from_left_or_above')
        • 参数:shift (str, default None)指定单元格的移动方向，可选：right 或者 down 。如果省略，Excel根据区域的形状决定。 copy_origin这个参数决定了**从何处复制插入单元格的格式，**可选 format_from_left_or_above 或 format_from_right_or_below
    • 10. 设置行高列宽
      • rng.row #返回区域第一行的行号
      • rng.column #返回区域的第一列的号
        • 注意返回的列号不是ABCD，而是1234
      • rng.row_height #获取行高
      • rng.row_height = 20 #设置行高
      • rng.column_width #获取列宽
      • rng.column_width = 20 #设置列宽
      • rng.rows #返回一个代表指定区域内的行的 RangeColumns 对象
      • rng.columns #返回一个代表指定区域内的行的 RangeColumns 对象
    • 11. 自适应行高列宽
      • rng.autofit() #调节行高列宽
      • rng.columns.autofit() #宽度自适应
      • rng.rows.autofit() #高度自适应
    • 12. 合并
      • rng.merge(across=False) #合并单元格
        • 参数across默认为False，如果为True，则实现 跨越合并 注意：执行合并单元格时，会出现如下提示框，需要手动点击：
      • rng.merge_area #返回所在合并单元格区域
        • 上面代码的返回的是rng所在的合并单元格区域，如果rng位于合并单元格内，则返回这个合并单元格，如果rng位于合并单元格外，则返回rng。 如果rng有一部分位于合并单元格内，有一部分位于合并单元格外，那返回什么？答案是：报错！
      • rng.merge_cells #检测是否包含合并单元格
        • 如果rng区域内包含合并的单元格，则返回True，否则返回False 注意：这里的包含是rng区域完全在合并单元格内
      • rng.unmerge() #取消合并单元格
        • rng可以是其中的一个是单元格，或者是整个区域
    • 13. 背景色
      • rng.color #获取指定区域的背景色
      • xw.Range('A1').color = (255,255,255) #设置背景色
      • xw.Range('A2').color = None #去除背景色
    • 14. 设置超链接
      • rng.add_hyperlink(r'www.baidu.com','百度','提示：点击即链接到百度')
        • 参数1：超链接地址 参数2：超链接的显示字符串，缺省为超链接地址本身 参数3：当鼠标停留在超链接上方是显示的屏幕提示
      • rng.hyperlink #返回指定区域的超链接(仅适合单个单元格)