一，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中

1. Excel程序用App来表示，多个Excel程序集合用Apps表示；

2. 单个工作簿用Book表示，工作簿集合用Books表示；

3. 单个工作表用Sheet表示，工作表集合用Sheets表示；

4. 区域用Range表示，既可以是一个单元格，也可以是一片单元格区域。
   

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

import xlwings as xw

xw.App 打开一个excel应用

xw.Book 创建一个工作薄

xw.Sheet 创建一个工作表

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

import xlwings as xw

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

App

App就是我们打开的一个Excel应用，在我们程序员看来一个App对象就是一个Excel的实例，在此实例下创建工作薄。因此我们要创建工作簿，就必须先创建App实例。一个App实例可以创建多个工作簿Book。

使用xlwings可以创建一个或者多个App，而每个App中又可以创建多个工作薄Book，并且多个App之间是相互独立的。

要使用xlwings就需要先引用该库import xlwings as xw

引入之后，我们可以查看xw下所有的app

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

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

keys = xw.apps.keys() 

print(keys) # 打印结果[13156]，当前只有一个app

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

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

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

创建成果后可以查看pid

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

总代码：

import xlwings as xw

app=xw.App()
pid = app.pid
# 就是这个App的PID
app1=xw.App()
pid1 = app1.pid
print(pid,pid1) # 6260

count = xw.apps.count
print(count)
print(xw.apps.keys())

结果：

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

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

注意：激活需要在新python文件中，并且保持生成PID的程序一直在运行状态，因为PID的值每次重新运行就会变化

app.screen_updating：打开屏幕更新，我们可以看到xlwings对Excel进行操作的过程，关闭更新可以加速脚本运行。默认是打开的。

常用的属性有：
app.display_alerts：在使用Excel的过程中，经常会遇到一些提醒信息，比如关闭前的保存提示、数据有效性的警告窗口，若想隐藏这些窗口可以设置成False。如果提醒信息是需要反馈的，Excel会选择默认的方式True

其中关闭app有两种方式，通过测试使用kill()函数更快些。

app.kill():通过杀掉进程,强制Excel app退出

app.quit():退出excel程序,不保存任何工作簿

工作簿Book与Books

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

创建Book对象

两种方式的区别：方式1是创建一个新的App，并在新App中新建一个Book，方式2是在当前App下新建一个Book

如果是打开一个已经存在的则使用：

wb = app.books.open('绝对或者相对路径的excel文件')
'或者
wb = xw.Book('绝对或者相对路径的excel文件')
其中创建Book对象的参数如下：

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文件')既可以打开工作薄也可以引用工作簿。

激活与保存

wb.activate()

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

保存工作薄：

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

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

import xlwings as xw

app=xw.App(visible=True,add_book=False)
app.display_alerts=False
app.screen_updating=False
# 文件位置：filepath，打开test文档，然后保存，关闭，结束程序
filepath=r'test.xlsx'
wb=app.books.open(filepath)

wb.save()
wb.close()
app.quit()

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

import xlwings as xw

# 当前活动App的工作簿集合
books = xw.books

# 或者使用app.books获取
# books = app.books

工作表Sheet与查看所有Sheets

新建Sheet

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

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

sht = wb.sheets('sheet1') # 指定名称获取sheet工作表

sht = wb.sheets(1) # 根据序号获取

sht = xw.sheets.active #获取当前活动的工作表

import xlwings as xw

app=xw.App(visible=True,add_book=False)
app.display_alerts=False
app.screen_updating=False
# 文件位置：filepath，打开test文档，然后保存，关闭，结束程序
filepath=r'test.xlsx'
wb=app.books.open(filepath)

# add()是在现有的sheets集合列表中追加新的Sheet
sht1 = wb.sheets.add()
sht2 = wb.sheets.add()
print(wb.sheets.count)

sht3 = wb.sheets(1)
# sht1.activate()
sht3.range('A1').value = 'Hello Running'

wb.save('test1.xlsx')
wb.close()
app.quit()

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

	sheet.activate       sheet.charts         sheet.index
	 sheet.api            sheet.clear          sheet.name
	 sheet.autofit        sheet.clear_contents sheet.names
	 sheet.book           sheet.delete         sheet.pictures
	 sheet.cells          sheet.impl           sheet.range
	 ......

常用的有：

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

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

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

引用区域与单元格操作

在操作区域或者单元格之前，首先就要引用他们，其实就是表明你要操作的区域或者单元格是哪些。可以认为区域是多个单元格。

引用区域的方式有很多种，下面列举一下常见的引用方式：

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

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

	range.offset(row_offset=5,column_offset=2) 表示偏移，row_offset行偏移量（正数表示向下偏移，负数相反），column_offset列偏移量（正数表示向右偏移，负数相反）

注意：是将选区范围进行偏移，内容不进行偏移

range.expand(mode='down') 扩展区域，参数可选取 'down' , 'right' ,'table' ，类似我们使用向下、向右或者下右方的区域扩展操作。

range.resize(row_size=4, column_size=2) 表示调整选中区域的大小，参数表示调整后区域的行、列的数量。

range.current_region 表示全选 类似Ctrl + A

对区域或单元格进行操作：

1）存储数据

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

2）读取数据

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

3）清除与删除

# 清除range的内容
rng.clear_contents()
# 清除格式和内容
rng.clear()
# 删除
rng.delete(shift=None)

4）其他设置

# 获取数字格式
rng.number_format
 
# 设置数字格式
rng.number_format = '0.00%'
 
rng.insert(shift=None, copy_origin='format_from_left_or_above')
 
# 返回区域第一行的行号
rng.row
 
# 返回区域的第一列的号，注意返回的列号不是ABCD，而是1234
rng.column
 
# 获取行高 或者设置行高
rng.row_height  
rng.row_height = 20
 
# 获取列宽或设置列宽
rng.column_width
rng.column_width = 20
 
# 自适应行高列宽
rng.autofit()
rng.columns.autofit()
rng.rows.autofit()
 
# 合并单元格
rng.merge(across=False)
rng.merge_area # 返回合并单元格区域
rng.merge_cells # 返回True或者False，测试是否在合并单元格区域
rng.unmerge() # 取消单元格合并
 
# 背景色
rng.color # 获取指定区域的背景色
xw.Range('A1').color = (255,255,255) # 设置背景色
xw.Range('A2').color = None # 去除背景色

其他参考

 range.add_hyperlink  range.clear_contents range.count
 range.address        range.color          range.current_region
 range.api            range.column         range.end
 range.autofit        range.column_width   range.expand
 range.clear          range.columns        range.formula
...等等

range.add_hyperlink('https://www.baidu.com','百度')

range.color = (128,128,128) RGB通道颜色，可获取or设置

range.row/column　　获取第几行/列，注意是第几而不是下标

range.formula　　可以设置计算表达式，用来进行表内计算

range.current_region　　返回当前range所在区域的区域表达，这个比较难描述，好比一个Excel中互相连接的单元格都是连城一片，两个片之间没有任何相邻就是互相独立的。

range.count　　返回这个range中共有多少单元格，合并单元格仍然按未合并的算

range.offset(a,b)　　获取到当前range向右a格，向下移动b格同样大小的那片区域，ab可以为负值

range.rows/columns　　返回行/列的各个range对象

range.expand

参考案例代码：

1）批量写入并读取数据

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

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

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

3）局中插入图片

import os
import xlwings as xw

wb = xw.Book()
sht = wb.sheets['Sheet1']
rng = sht.range('A1')
fileName = os.path.join(os.getcwd(), 'aa.png')
width, height = 120, 100  # 指定图片大小
left = rng.left + (rng.width - width) / 2  # 居中
top = rng.top + (rng.height - height) / 2
sht.pictures.add(fileName, left=left, top=top, width=width, height=height)
wb.save('test2.xlsx')
wb.close()

综合案例：

import xlwings as xw

wb = xw.Book()
sht = wb.sheets[0]


info_list = [['110202111111234','帐篷',5],
['110202111118891','行李箱','16'],
['110202111111004','微波炉','20'],
['110202111132741','电冰箱','13'],
['110202111109852','乐事薯片','30'],
['110202111112030','鲁花花生油','12'],
['110202111190391','羽绒服','9'],
['110202111122319','防晒霜','18'],
			 ]
# 写入表头
titles = [['商品编号','商品名称','数量']]
sht.range('a1').value = titles

# 写入数据
sht.range('a2').value = info_list

# 保存数据
wb.save('goods.xlsx')

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

import xlwings as xw

wb = xw.Book()
sht = wb.sheets[0]


info_list = [['110202111111234','帐篷','5'],
['110202111118891','行李箱','16'],
['110202111111004','微波炉','20'],
['110202111132741','电冰箱','13'],
['110202111109852','乐事薯片','30'],
['110202111112030','鲁花花生油','12'],
['110202111190391','羽绒服','9'],
['110202111122319','防晒霜','18'],
			 ]
# 写入表头
titles = [['商品编号','商品名称','数量']]
sht.range('a1').value = titles

# 写入数据
sht.range('a2').value = info_list

# 保存数据
wb.save('goods.xlsx')

# 读取数据
goods_list = sht.range('a2').expand('table').value

for goods in goods_list:
	goods[0] = str(int(goods[0]))
	goods[2] = int(goods[2])
print(goods_list)
new_info = [['110202111111234','帐篷',5],
['110202111118891','行李箱',16],
['110202111111004','微波炉',20],
['110202111132741','电冰箱',10],
['110202111124660','羊毛衫',8],
['110202111109852','乐事薯片',10],
['110202111112030','鲁花花生油',12],
['110202111190391','羽绒服',0],
['110202111122319','防晒霜',9],
['110202111124560','牛仔裤',18],
['110202111134798','老爹鞋',11]]
# 去重
extra = [i for i in new_info if i not in goods_list]
# print(extra)

# 读取extra每个商品的包裹号，判断是否存在并更新，然后添加
ids = sht.range(2, 1).expand('down').value

ids = [str(int(id)) for id in ids]
rows = len(sht.range('a2').expand('table').value)
# 更新已有数据的库存
for goods in extra:
	if goods[0] in ids:
		row_number = ids.index(goods[0])
		print(row_number,goods[1])
		sht[row_number+1,2].value = goods[2]
	else:
		for i in range(3):
			sht[rows+1,i].value =goods[i]
		rows+=1

wb.save('goods.xlsx')