python自动化办公

一，xlwings的安装与使用

1，xlwings是什么

在日常生活中我们或多或少的都会跟Excel打交道，比如做销售统计，人力的考勤，学生的考试成绩等等，甚至在某些领域会涉及到批量操作Excel表格，那对于非程序员来说，可能使用Excel中的函数，但是在某种场景下这些又是不好使的，只能熬夜加班啦！但是对于程序员来说，可能就是分分钟的事，轻松搞定。
那在我们Python中有哪些模块（或者第三方库）可以轻松处理Excel 呢？给大家列出来学习一下
详细请看https://mp.weixin.qq.com/s?__biz=MzI3NzI1MzY4Mw==&mid=2247493580&idx=1&sn=3bb272e1f187b1993a88f2578aa0d470&chksm=eb6bbc90dc1c358687b47c05a34df9343b70ab2e3365915ba94c2890f81c6a11ed5d423391d2&scene=21#wechat_redirect
2.xlwings基本介绍

xlwings是一个可以实现从Excel调用Python，也可在python中调用Excel的库。开源免费，一直在更新。特点：
1）xlwings支持.xls读，支持.xlsx文件读写。
2）支持Excel操作。
3）支持VBA。
4）强大的转换器可以处理大部分数据类型，包括在两个方向上的numpy array和pandas DataFrame。
文档链接：https://docs.xlwings.org/en/stable/index.html
3.xlwings的安装

pip install xlwings
注意：在安装过程中，xlwings也是有依赖项的，但是依赖项通过conda或pip自动安装
如果以前已经安装过的，使用下面命令更新版本
pip install -upgrade xlwings
验证
xlwings addin install
卸载xlwings

先卸载软件包
xlwings addin remove
pip uninstall xlwings
最后，手动删除个人文件夹中的.xlwings目录
4.xlwings的使用

在xlwings中

• Excel程序用App来表示，多个Excel程序集合用Apps表示；
  
• 单个工作簿用Book表示，工作簿集合用Books表示；
  
• 单个工作表用Sheet表示，工作表集合用Sheets表示；
  
• 区域用Range表示，既可以是一个单元格，也可以是一片单元格区域。
  
  

对Excel进行操作主要使用如下三个类：

1. import xlwings as xw
3. xw.App 打开一个excel应用
5. xw.Book 创建一个工作薄
7. xw.Sheet 创建一个工作表

复制代码

牛刀小试：创建一个excel表格并保存

1. import xlwings as xw
3. #打开excel,参数visible表示处理过程是否可视,add_book表示是否要打开新的Excel程序
4. with xw.App(visible=True,add_book=False) as app:
5. # 创建一个工作薄
6. book = app.books.add()
7. # 工作薄中创建一个sheet表
8. sht = book.sheets.add()
9. # 向表格的A1单元格写入“Hello Python”
10. sht.range('A1').value = 'Hello Python'
11. # 保存
12. book.save('./test.xlsx')

复制代码

App

App就是我们打开的一个Excel应用，在我们程序员看来一个App对象就是一个Excel的实例，在此实例下创建工作薄。因此我们要创建工作簿，就必须先创建App实例。一个App实例可以创建多个工作簿Book。
使用xlwings可以创建一个或者多个App，而每个App中又可以创建多个工作薄Book，并且多个App之间是相互独立的。
要使用xlwings就需要先引用该库import xlwings as xw
引入之后，我们可以查看xw下所有的app

1. Apps = xw.apps
2. count = Apps.count
3. print(count) # 打印个数是1，是指当前打开的这个app
4. #此时要保证已经打开一个excel表格，count才能为1

复制代码

每个App对应一个PID值，可以看作是数字编号，可以用来识别不同的App。

1. keys = xw.apps.keys()
3. print(keys) # 打印结果[13156]，当前只有一个app

复制代码

创建App
我们可以通过xw.app()创建一个新的app实例

1. app=xw.App(visible=True,add_book=False)  
2. # 当然也可以通过app.visible = True设置可见性

复制代码

其中可以设置参数visible：用来设置程序是否可见，True表示可见（默认），Flase不可见。add_book用来设置是否自动创建工作簿，True表示自动创建（默认），False不创建。当设置成add_book=False时，可以创建App，但是还未生成PID，只有当这个App创建了工作簿后，才会生成自己的PID 。
创建成果后可以查看pid

1. import xlwings as xw
3. app=xw.App()
4. pid = app.pid
5. print(pid) # 6260就是这个App的PID

复制代码

总代码：

1. import xlwings as xw
3. app=xw.App()
4. pid = app.pid
5. # 就是这个App的PID
6. app1=xw.App()
7. pid1 = app1.pid
8. print(pid,pid1) # 6260
10. count = xw.apps.count
11. print(count)
12. print(xw.apps.keys())

复制代码

结果：

可以引用某个app实例进行操作并激活

1. app = xw.apps[992]
2. app.activate() # 或者app.activate(steal_focus=True)
3. # 当steal_focus=True时, Excel程序变为最前台的应用，并且把焦点从Python切换到Excel

复制代码

注意：激活需要在新python文件中，并且保持生成PID的程序一直在运行状态，因为PID的值每次重新运行就会变化
app.screen_updating：打开屏幕更新，我们可以看到xlwings对Excel进行操作的过程，关闭更新可以加速脚本运行。默认是打开的。
常用的属性有：
app.display_alerts：在使用Excel的过程中，经常会遇到一些提醒信息，比如关闭前的保存提示、数据有效性的警告窗口，若想隐藏这些窗口可以设置成False。如果提醒信息是需要反馈的，Excel会选择默认的方式True
其中关闭app有两种方式，通过测试使用kill()函数更快些。

1. app.kill():通过杀掉进程,强制Excel app退出
3. app.quit():退出excel程序,不保存任何工作簿

复制代码

工作簿Book与Books

一个app可以包含多个工作薄，如何在app中创建工作薄呢？
创建Book对象


两种方式的区别：方式1是创建一个新的App，并在新App中新建一个Book，方式2是在当前App下新建一个Book
如果是打开一个已经存在的则使用：

1. wb = app.books.open('绝对或者相对路径的excel文件')
2. '或者
3. wb = xw.Book('绝对或者相对路径的excel文件')
4. 其中创建Book对象的参数如下：
6. 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)

复制代码

详细情况可以参考文档：
https://docs.microsoft.com/zh-cn/office/vba/api/excel.workbooks.open
xw.Book('绝对或者相对路径的excel文件')既可以打开工作薄也可以引用工作簿。
激活与保存

1. wb.activate()
3. # 如果steal_focus=True, 则把窗口显示到最上层，并且把焦点从Python切换到Excel
4. wb.activate(steal_focus=True)

复制代码

保存工作薄：

1. wb.save()
2. # 或者使用指定路径保存
3. wb.save('存储路径')

复制代码

关闭
关闭工作薄也很简单，就是使用wb.close(),注意：wb.close()只是关闭并不会保存，所以在关闭之前必须要使用save()进行一下保存才可以。可以考虑使用with搭建上下文，实现关闭资源。

1. import xlwings as xw
3. app=xw.App(visible=True,add_book=False)
4. app.display_alerts=False
5. app.screen_updating=False
6. # 文件位置：filepath，打开test文档，然后保存，关闭，结束程序
7. filepath=r'test.xlsx'
8. wb=app.books.open(filepath)
10. wb.save()
11. wb.close()
12. app.quit()

复制代码

若想获取当前活动App中的所有books，可以直接通过下列方式

1. import xlwings as xw
3. # 当前活动App的工作簿集合
4. books = xw.books
6. # 或者使用app.books获取
7. # books = app.books

复制代码

工作表Sheet与查看所有Sheets
新建Sheet

1. sht = wb.sheets.add()
2. # 或者
3. sht = wb.sheets.add('test',after='sheet2')
4. 参数1为工作表名称，省略的话为Excel默认名称，参数2为插入位置，可选before或者after

复制代码

若想引用某一个Sheet，可以通过下面方式

1. sht = wb.sheets('sheet1') # 指定名称获取sheet工作表
3. sht = wb.sheets(1) # 根据序号获取
5. sht = xw.sheets.active #获取当前活动的工作表

复制代码

1. import xlwings as xw
3. app=xw.App(visible=True,add_book=False)
4. app.display_alerts=False
5. app.screen_updating=False
6. # 文件位置：filepath，打开test文档，然后保存，关闭，结束程序
7. filepath=r'test.xlsx'
8. wb=app.books.open(filepath)
10. # add()是在现有的sheets集合列表中追加新的Sheet
11. sht1 = wb.sheets.add()
12. sht2 = wb.sheets.add()
13. print(wb.sheets.count)
15. sht3 = wb.sheets(1)
16. # sht1.activate()
17. sht3.range('A1').value = 'Hello Running'
19. wb.save('test1.xlsx')
20. wb.close()
21. app.quit()

复制代码

sheet对象可以调用的方法有：

1.         sheet.activate       sheet.charts         sheet.index
2.          sheet.api            sheet.clear          sheet.name
3.          sheet.autofit        sheet.clear_contents sheet.names
4.          sheet.book           sheet.delete         sheet.pictures
5.          sheet.cells          sheet.impl           sheet.range
6.          ......

复制代码

常用的有：

1. 清除工作表所有内容和格式
2. sht.clear()
3. # 清除工作表的所有内容但是保留原有格式
4. sht.clear_contents()
5. # 删除工作表
6. sht.delete()
7. # 自动调整行高列宽
8. sht.autofit('c')
9. # 在活动工作簿中选择
10. sht.select()

复制代码

可以通过属性获取获取工作表的名称、所有单元格的区域对象、当前工作表的索引值

1. sht.name      sht.cells      sht.index     sht.names

复制代码

引用区域与单元格操作
在操作区域或者单元格之前，首先就要引用他们，其实就是表明你要操作的区域或者单元格是哪些。可以认为区域是多个单元格。
引用区域的方式有很多种，下面列举一下常见的引用方式：

1. xw.Range('A1:D4')
2. xw.Range((1,1), (4，4))
3. xw.Range(xw.Range('A1'),xw.Range('D4'))
4. xw.Range(xw.Range('A1:E6'),xw.Range('C3:D7'))
5. xw.Range('NamedRange')
6. app.range("A1")  # 注意是小写的range
7. sht.range('A1')
8. xw.books['MyBook.xlsx'].sheets[0].range('A1')
9. sht['A1']
10. sht['A1:D4']
11. sht[0,5]
12. sht[:5,:5]

复制代码

区域管理可以通过如下方式：

1.         range.offset(row_offset=5,column_offset=2) 表示偏移，row_offset行偏移量（正数表示向下偏移，负数相反），column_offset列偏移量（正数表示向右偏移，负数相反）
3. 注意：是将选区范围进行偏移，内容不进行偏移
5. range.expand(mode='down') 扩展区域，参数可选取 'down' , 'right' ,'table' ，类似我们使用向下、向右或者下右方的区域扩展操作。
7. range.resize(row_size=4, column_size=2) 表示调整选中区域的大小，参数表示调整后区域的行、列的数量。
9. range.current_region 表示全选 类似Ctrl + A

复制代码

对区域或单元格进行操作：
1）存储数据

1. 储存单个值
2. # ".value“属性
3. sht.range('A1').value=1
4. 储存列表
5. # 将列表[1,2,3]储存在A1：C1中
6. sht.range('A1').value=[1,2,3]
7. # 将列表[1,2,3]储存在A1:A3中
8. sht.range('A1').options(transpose=True).value=[1,2,3]
9. # 将2x2表格，即二维数组，储存在A1:B2中，如第一行1，2，第二行3，4
10. sht.range('A1').options(expand='table').value=[[1,2],[3,4]]

复制代码

2）读取数据

1. 读取单个值
2. # 将A1的值，读取到a变量中
3. a=sht.range('A1').value
5. 将值读取到列表中
6. #将A1到A2的值，读取到a列表中
7. a=sht.range('A1:A2').value
9. # 将第一行和第二行的数据按二维数组的方式读取
10. a=sht.range('A1:B2').value

复制代码

3）清除与删除

1. # 清除range的内容
2. rng.clear_contents()
3. # 清除格式和内容
4. rng.clear()
5. # 删除
6. rng.delete(shift=None)

复制代码

4）其他设置

1. # 获取数字格式
2. rng.number_format
4. # 设置数字格式
5. rng.number_format = '0.00%'
7. rng.insert(shift=None, copy_origin='format_from_left_or_above')
9. # 返回区域第一行的行号
10. rng.row
12. # 返回区域的第一列的号，注意返回的列号不是ABCD，而是1234
13. rng.column
15. # 获取行高 或者设置行高
16. rng.row_height  
17. rng.row_height = 20
19. # 获取列宽或设置列宽
20. rng.column_width
21. rng.column_width = 20
23. # 自适应行高列宽
24. rng.autofit()
25. rng.columns.autofit()
26. rng.rows.autofit()
28. # 合并单元格
29. rng.merge(across=False)
30. rng.merge_area # 返回合并单元格区域
31. rng.merge_cells # 返回True或者False，测试是否在合并单元格区域
32. rng.unmerge() # 取消单元格合并
34. # 背景色
35. rng.color # 获取指定区域的背景色
36. xw.Range('A1').color = (255,255,255) # 设置背景色
37. xw.Range('A2').color = None # 去除背景色

复制代码

其他参考

1. range.add_hyperlink  range.clear_contents range.count
2. range.address        range.color          range.current_region
3. range.api            range.column         range.end
4. range.autofit        range.column_width   range.expand
5. range.clear          range.columns        range.formula
6. ...等等
8. range.add_hyperlink('https://www.baidu.com','百度')
10. range.color = (128,128,128) RGB通道颜色，可获取or设置
12. range.row/column　　获取第几行/列，注意是第几而不是下标
14. range.formula　　可以设置计算表达式，用来进行表内计算
16. range.current_region　　返回当前range所在区域的区域表达，这个比较难描述，好比一个Excel中互相连接的单元格都是连城一片，两个片之间没有任何相邻就是互相独立的。
18. range.count　　返回这个range中共有多少单元格，合并单元格仍然按未合并的算
20. range.offset(a,b)　　获取到当前range向右a格，向下移动b格同样大小的那片区域，ab可以为负值
22. range.rows/columns　　返回行/列的各个range对象
24. range.expand

复制代码

参考案例代码：
1）批量写入并读取数据

1. import xlwings as xw
3. wb = xw.Book()
4. sht = wb.sheets.active
5. # 向工作表中写入行列值
6. for i in range(1, 6):
7.     for j in range(1, 6):
8.         sht.range(i, j).value = '({}, {})'.format(i, j)
9. print(sht.range((1, 1), (5, 5)).expand().value)  # 批量读取
10. print(sht.range(1, 1).expand('right').value)  # 按行读
11. print(sht.range(1, 1).expand('down').value)  # 按列读
12. wb.close()

复制代码

2）提前设置好表格的颜色，如图

1. import xlwings as xw
2. from itertools import product
4. app = xw.App(visible=False)  # 隐藏Excel
5. wb = app.books.open('test.xlsx')  # 打开工作簿
6. sht = wb.sheets['Sheet1']  # 实例化工作表
7. for cell in list(map(''.join, product('ABCDEFGH', '1'))):  # A1 B1 C1 D1 E1 F1 G1 H1
8.     print(cell, sht.range(cell).color)  # 填充颜色
9. wb.close()

复制代码

3）局中插入图片

1. import os
2. import xlwings as xw
4. wb = xw.Book()
5. sht = wb.sheets['Sheet1']
6. rng = sht.range('A1')
7. fileName = os.path.join(os.getcwd(), 'aa.png')
8. width, height = 120, 100  # 指定图片大小
9. left = rng.left + (rng.width - width) / 2  # 居中
10. top = rng.top + (rng.height - height) / 2
11. sht.pictures.add(fileName, left=left, top=top, width=width, height=height)
12. wb.save('test2.xlsx')
13. wb.close()

复制代码

综合案例：

1. import xlwings as xw
3. wb = xw.Book()
4. sht = wb.sheets[0]
7. info_list = [['110202111111234','帐篷',5],
8. ['110202111118891','行李箱','16'],
9. ['110202111111004','微波炉','20'],
10. ['110202111132741','电冰箱','13'],
11. ['110202111109852','乐事薯片','30'],
12. ['110202111112030','鲁花花生油','12'],
13. ['110202111190391','羽绒服','9'],
14. ['110202111122319','防晒霜','18'],
15.                          ]
16. # 写入表头
17. titles = [['商品编号','商品名称','数量']]
18. sht.range('a1').value = titles
20. # 写入数据
21. sht.range('a2').value = info_list
23. # 保存数据
24. wb.save('goods.xlsx')

复制代码

若想更新里面的数据，由于有些商品被卖出，商品数量就会发生变化。另外还有一批货是新引入的。参考代码如下：

1. import xlwings as xw
3. wb = xw.Book()
4. sht = wb.sheets[0]
7. info_list = [['110202111111234','帐篷','5'],
8. ['110202111118891','行李箱','16'],
9. ['110202111111004','微波炉','20'],
10. ['110202111132741','电冰箱','13'],
11. ['110202111109852','乐事薯片','30'],
12. ['110202111112030','鲁花花生油','12'],
13. ['110202111190391','羽绒服','9'],
14. ['110202111122319','防晒霜','18'],
15.                          ]
16. # 写入表头
17. titles = [['商品编号','商品名称','数量']]
18. sht.range('a1').value = titles
20. # 写入数据
21. sht.range('a2').value = info_list
23. # 保存数据
24. wb.save('goods.xlsx')
26. # 读取数据
27. goods_list = sht.range('a2').expand('table').value
29. for goods in goods_list:
30.         goods[0] = str(int(goods[0]))
31.         goods[2] = int(goods[2])
32. print(goods_list)
33. new_info = [['110202111111234','帐篷',5],
34. ['110202111118891','行李箱',16],
35. ['110202111111004','微波炉',20],
36. ['110202111132741','电冰箱',10],
37. ['110202111124660','羊毛衫',8],
38. ['110202111109852','乐事薯片',10],
39. ['110202111112030','鲁花花生油',12],
40. ['110202111190391','羽绒服',0],
41. ['110202111122319','防晒霜',9],
42. ['110202111124560','牛仔裤',18],
43. ['110202111134798','老爹鞋',11]]
44. # 去重
45. extra = [i for i in new_info if i not in goods_list]
46. # print(extra)
48. # 读取extra每个商品的包裹号，判断是否存在并更新，然后添加
49. ids = sht.range(2, 1).expand('down').value
51. ids = [str(int(id)) for id in ids]
52. rows = len(sht.range('a2').expand('table').value)
53. # 更新已有数据的库存
54. for goods in extra:
55.         if goods[0] in ids:
56.                 row_number = ids.index(goods[0])
57.                 print(row_number,goods[1])
58.                 sht[row_number+1,2].value = goods[2]
59.         else:
60.                 for i in range(3):
61.                         sht[rows+1,i].value =goods[i]
62.                 rows+=1
64. wb.save('goods.xlsx')

复制代码

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！