Python之Xlwings操作excel

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

一、xlwings简介

python操作Excel的模块，python中能操作Excel的库，网上提到的模块大致有：xlwings、xlrd、xlwt、openpyxl等，一共九个库

所以我综合了一下就打算用xlwings
他的特点：

1. xlwings能够非常方便的读写Excel文件中的数据，并且能够进行单元格格式的修改
2. 可以和matplotlib以及pandas无缝连接
3. 可以调用Excel文件中VBA写好的程序，也可以让VBA调用用Python写的程序。
4. 开源免费，一直在更新

二、安装与使用

1.安装

pip install xlwings
1

中文使用文档说明：https://www.kancloud.cn/gnefnuy/xlwings-docs/1127450

2.使用

app=xw.App()
wb=app.books.open(r'文件地址') # 这里的r指的是不转义特殊字符
# 1.打开Excel
app=xw.App(visible=True,add_book=False)
# visible是否可见,False表示在后台运行,add_book是否新增一张Excel操作,在新增wb上运行
# 2.对excel操作时候屏幕是否更新
app.screen_updating=True   #默认True
# 3.返回所有book对象集合
print(app.books)    #只返回通过xw打开的workbook,手动打开的不返回
# 4.退出excel
app.kill()  #终止进程
app.quit()  #退出excel程序

1
2
3
4
5
6
7
8
9
10
11
12
13

3.xlwings结构说明

根据我们写的代码也可以看出
excel基本结构分为 Application ——> Workbooks ——> Worksheets ——> Range
即应用程序 ——> 工作簿 ——> 工作表 ——> 单元格。

其他的没什么不好理解的（如果用过excel）这里的range的含义其实指的是范围也称为区域，（你所需要进行操作的范围）比如a1到f1这样

二、xlwings对App常见的操作

App基础操作

import xlwings as xw
 
app = xw.App(visible=True, add_book=False)
app.display_alerts = False    # 关闭一些提示信息，可以加快运行速度。 默认为 True。
app.screen_updating = True    # 更新显示工作表的内容。默认为 True。关闭它也可以提升运行速度。
wb = app.books.add()
sht = wb.sheets.active
1
2
3
4
5
6
7

工作簿的基础操作

 wb = app.books.add()                   # 新建工作簿。
 wb = app.books.open(r'file_path')      # 打开现有的工作簿
 wb = app.books.active                  # 获取当前活动的工作簿
1
2
3

工作表的基础操作

sht = wb.sheets.active                 # 获取当前活动的工作表
sht = wb.sheets[0]                     # 按索引获取工作表
sht = wb.sheets['Sheet1']              # 按表名获取工作表
sht1 = wb.sheets.add()                 # 新建工作表，默认新建的放在最前面。
sht1 = wb.sheets.add('新建工作表', after=sht)   # 新建工作表，放在sht工作表后面。
1
2
3
4
5

工作表其他操作

app=xw.App()
wb=app.books.open(r'C:\Users\Administrator\Desktop\1test.xlsm')
sht=wb.sheets[0]
1.sheets返回所有的工作表
print(wb.sheets)
2.active返回正在被操作的sht,
sht=wb.sheets[0]
print(sht.activate)
3.add(name=None,before=None,after=None)添加sht
wb.sheets.add(name='hello_xlwings')
4.autofit(axis=None)自动调整行和列
栗子
import xlwings as xw
wb=xw.Book()
wb.sheets[0].range('a1').value='hello xlwings'  #对第一张表a1赋值
wb.sheets[0].autofit('c')   #columns=c
wb.sheets[0].autofit('r')   #rows=r
wb.sheets[0].autofit()
5.book sht所属的workbook
sht=wb.sheets[0]
print(sht.book) #属于workbook
6.cells返回一个range对象,表示sheet上的所有单元格
print(sht.cells)
print(sht.cells.shape)
7.charts返回sheet中所有图标的集合
print(sht.charts)
8.clear()
print(sht.clear())  #清除内容颜色格式...
9.clear_contents()
print(sht.clear_contents()) #清除内容
10.delete() 删除sheet
sht.delete()
11.pictures 返回所有pictures对象的集合
print(sht.pictures)
12.index 返回工作表的索引
print(sht.index)    #第一个为1
13.name,sheet name的操作
print(sht.name) #获得sht的名字
sht.name='xlwingstest'  #修改sht的名字
print(sht.name)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40

读取单元格的基础操作

cell1 = sht.range('cell1')
# 获取 cell1 中的值
v = cell1.value
# 也可以根据行列号读取
cell1_value = sht.range(3,2).value
# 读取一段区间内的值
a1_c4_value = sht.range('a1:c4').options(ndim=2).value       # 加上 option 读取二维的数据
a1_c4_value = sht.range((1,1),(4,3)).options(ndim=2).value   # 和上面读取的内容一样。
1
2
3
4
5
6
7
8

Range其他操作

app=xw.App()
wb=app.books.open(r'C:\Users\Administrator\Desktop\1test.xlsm')
sht=wb.sheets[0]
1.add_hyperlink(address,text_to_display=None,screen_tip=None)添加超链接
sht.range('a1').add_hyperlink(address='www.baidu.com',text_to_display='百度',screen_tip='我是百度哦')
2.clear()删除range里面的内容和格式
3.clear_contents()删除内容
4.color获取单元格的背景颜色
print(sht.range('a1').color)    #没有颜色的时候返回None
sht.range('a1').color=(100,100,100)   #设置颜色
print(sht.range('a1').color)
5.column返回range的列
6.column_width获取单元格的列宽
print(sht.range('a1').column_width)  #获取宽度
sht.range('a1').column_width=33 #设置宽度
print(sht.range('a1').column_width)
7.columns返回RangeColumns对象,表示指定范围内的列
print(sht.range('a1:c3').columns)
8.count返回单元格数量
9.end(direction),返回一个range对象,类似Ctrl+Up(down,left,right)
print(sht.range('a1:a65536').end('down').row)   #返回最后有值一行
10.formula 获取设置单元格的公式
sht.range('a1').formula='=b1+c1'    #设置
# print(sht.range('a1').formula)  #获取
11.formula_array 获取设置单元格的数组公式
sht.range('a1').formula_array='=b1+c1'    #设置
print(sht.range('a1').formula_array)  #获取的值并没有{}包裹
12.get_address(row_absolute=True,column_absolute=True,include_sheetname=False,external=False)返回地址
13.height返回高度 row_height
print(sht.range('a1').height) #获取行高
sht.range('a1').row_height=33   #设置行高
print(sht.range('a1').row_height)   #获取行高
14.hyperlink返回地址
15.last_cell返回指定range右下角的单元格
16.left返回从第一列到range第一列的距离
17.name设置获取range的名称
18.number_format获取设置range的内容格式
19.offset(row_offset=0,column_offset=0)
rng=sht.range('a1:c3')
rng=rng.offset(row_offset=2,column_offset=2)
print(rng)  #返回偏移后的array c3:e5
20.options(convert=None,)...
21.raw_value获取设置传递的值
22.row返回行
23.row_height 在上方height的时候已经举栗子
24.rows返回rangerows
25.select()
26.shape返回几行几列
27.size返回range中元素数量
print(sht.range('a1:d100').size)    #有400个cell
28.top返回第一行到range第一行的距离
29.value获取设置range的值
sht.range('a1').value='123'
print(sht.range('a1').value)



# 引用当前活动工作表的单元格
rng=xw.Range('A1')
# 加入超链接
# rng.add_hyperlink(r\'www.baidu.com\',\'百度\',‘提示：点击即链接到百度\')
# 取得当前range的地址
rng.address
rng.get_address()
# 清除range的内容
rng.clear_contents()
# 清除格式和内容
rng.clear()
# 取得range的背景色,以元组形式返回RGB值
rng.color
# 设置range的颜色
rng.color=(255,255,255)
# 清除range的背景色
rng.color=None
# 获得range的第一列列标
rng.column
# 返回range中单元格的数据
rng.count
# 返回current_region
rng.current_region
# 返回ctrl + 方向
rng.end('down')
# 获取公式或者输入公式
rng.formula='=SUM(B1:B5)'
# 数组公式
rng.formula_array
# 获得单元格的绝对地址
rng.get_address(row_absolute=True, column_absolute=True,include_sheetname=False, external=False)
# 获得列宽
rng.column_width
# 返回range的总宽度
rng.width
# 获得range的超链接
rng.hyperlink
# 获得range中右下角最后一个单元格
rng.last_cell
# range平移
rng.offset(row_offset=0,column_offset=0)
#range进行resize改变range的大小
rng.resize(row_size=None,column_size=None)
# range的第一行行标
rng.row
# 行的高度，所有行一样高返回行高，不一样返回None
rng.row_height
# 返回range的总高度
rng.height
# 返回range的行数和列数
rng.shape
# 返回range所在的sheet
rng.sheet
#返回range的所有行
rng.rows
# range的第一行
rng.rows[0]
# range的总行数
rng.rows.count
# 返回range的所有列
rng.columns
# 返回range的第一列
rng.columns[0]
# 返回range的列数
rng.columns.count
# 所有range的大小自适应
rng.autofit()
# 所有列宽度自适应
rng.columns.autofit()
# 所有行宽度自适应
rng.rows.autofit()
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128

写入单元格的基础操作

一维

sht.range(3,2).value = ='welcome'
sht.range('A1').value=[1,2,3]
# 将A1，B1，C1单元格的值存入list1列表中
list1=sht.range('A1:C1').value
# 将1，2，3分别写入了A1，A2，A3单元格中
sht.range('A1').options(transpose=True).value=[1,2,3]
# 将A1，A2，A3单元格中值存入list1列表中
list1=sht.range('A1:A3').value
1
2
3
4
5
6
7
8

二维

# 将a1,a2,a3输入第一列，b1,b2,b3输入第二列
list1=[['a1','a2','a3'],['b1','b2','b3']]
sht.range('A1').value=list1
1
2
3

写入一行或一列Excel数据(函数式）

import xlwings as xw
def write_col(io, sheet, col='A1', data=None):
    """
    一次写多列
	注意点：此方法所需的data参数必须是list嵌套，如：[[1, 9], [2, 8], [3, 7], [4, 6]]，并且里面的每个list的长度必须一致
    写入一列数据
    :param io: Excel文件
    :param sheet: sheet，int或者str类型
    :param col: 哪一列，如:'A1'
    :param data: 要写入的数据，list类型
    :return:
    """
    wb = xw.Book(io)
    if isinstance(sheet, str):
        sht = wb.sheets(sheet)
    else:
        sht = wb.sheets[sheet]
    sht.range(col).options(transpose=True).value = data
    wb.save()
    wb.app.quit()

 
def write_row(io, sheet, row=\'A1\', data=None):
    """
    写入一行数据
    一次写多行
	注意点：此方法所需的data参数必须是list嵌套，如：[[1, 2], [3, 4], [5, 6]]，并且里面的每个list的长度必须一致
    :param io: Excel文件
    :param sheet: sheet，int或者str类型
    :param row: 哪一行，如:'A1'
    :param data: 要写入的数据，list类型
    :return:
    """
    wb = xw.Book(io)
    if isinstance(sheet, str):
        sht = wb.sheets(sheet)
    else:
        sht = wb.sheets[sheet]
    sht.range(row).value = data
    wb.save()
	wb.app.quit()


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43