代码收藏家技术教程 2022-07-19

Python模块之xlwings模块学习笔记（一）

前言

可以处理Excel文件的Python模块有很多，xlwings模块的功能是最齐全的。它不仅能读写和修改，而且能批量处理多个Excel文件。此外，xlwing模块还能与Exce VBA结合使用。

一、模块的安装

cmd>pip install xlwings

安装xlwings模块后，若提示No module named 'pywintype'，则还需要安装pypiwin32模块

cmd>pip install pypiwin32

二、xlwings的基本使用

1、对APP进行操作

excel基本结构分为 Application ——> Workbooks ——> Worksheets ——> Range. 即应用程序 ——> 工作簿 ——> 工作表 ——> 单元格。所以，我们先对APP进行操作。

(1)启动Excel程序

import xlwings as xw
app=xw.App(visible=True, add_book=False) #启动Excel程序窗口，但不新建工作簿。
#visible是否可见。False表示后台运行。 add_book 是否新建一个工作簿

(2)关闭程序

import xlwings as xw
app.kill() #终止进程，强制退出。
app.quit() #在不保存的情况下，退出excel程序。

（a）每个App对应一个PID值，这个PID值可以认为是一个标签，用来识别不同的App。

（b）创建工作簿之前要先创建App: app=xw.App(visible=Ture,add_book=False)

（c）通过xlwings可以创建多个App，每个App又可以创建多个工作簿，每一个工作簿中又可以创建多个Sheet。

（d）需要注意的是这些App之间是相互独立的，也就是操作不同的工作簿的时候就要找到对应的App。

2、对WorkBook进行操作

(1)新建Workbook

wb=app.books.add()  #在当前app下创建工作簿
wb=xw.Book()  #创建一个新的App，并在新App中新建一个Book
wb.display_alerts=False #是否开启提示，如保存提示等。
wb.screen_updating=True #是否更新显示变动内容，若设为False则看不到文档的打开或变化

(2)返回所有book对象集合

import xlwings as xw
books = xw.books #当前活动App的工作簿集合
books = app.books #指定的App的所有工作簿的集合，返回一个列表。
#Python打开的和手动打开的是不互通的

(3)保存Workbook

wb.save([path])  #path省略，则保存；不省略，则可另存为。
#另存为时，我们不仅能够指定保存的位置，还可以指定保存的格式
#同名文件会在没有提示的情况下被直接覆盖

(4)打开已有Workbook

运行xlwings打开excel文件时，如果该文件已经打开则会报错

'''
#判断要打开的excel是否存在
if not os.path.exists('./a.xlsx'):
    print("第一次创建excel文件")
    excelFile= app.books.add()
    excelFile.save("./a.xlsx")
'''
wb=app.books.open(path)  #path为相对或绝对路径
wb=xw.Book('test.xlsx')

(5)引用

wb = app.books('test.xlsx') 
wb = xw.Book('test.xlsx')
wb = app.books.active  #引用当前app活动工作簿

也可以用数字来代替文件名，比如wb = app.books(1)表示当前app打开的第一个工作簿。还可以使用中括号的引用方式，那么中括号和小括号有什么区别呢？

小括号的序号是从1开始的，中括号（切片）是从0开始计数，也就是说，app.books(1) 等同于 app.books[0]，后面在工作表和区域引用的时候会经常遇到这种方式。

xw.Book('test.xlsx')既可以打开工作薄也可以引用工作簿。也就是说，当工作簿未打开时，它可以打开工作薄，当工作簿处于打开状态时，它可以引用工作簿。而app.books('test.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，然而它并不知道我们想要引用的是哪一个，此时就会报错。

(6)激活工作簿

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

(7)Workbook属性

a=wb.fullname #获取工作簿的绝对路径
a=wb.name  #获取工作薄名称（带扩展名）
a=wb.app  #获取创建工作簿的App

(8)关闭Workbook

wb.close() #关闭工作簿(只是python不能编辑了)，显示的文件不关闭

3、对WorkSheet进行操作

(1)选中工作表

sh1=wb.sheets['Sheet1'] #根据工作表名称选中工作表
sh1=wb.sheets[0]  #根据工作表序号选中工作表
sh1=wb.sheets.active  #引用当前工作簿活动工作表
sh1=xw.sheets.active  #引用当前app活动工作表

worksheet1=xw.sheets  #返回当前活动工作簿的所有工作表
worksheet1=wb.sheets  #返回指定工作簿中所有工作表

(2)新增工作表

sh1=wb.sheets.add('new sheet', after='sheet2') 
#before或after表示插入到sheet2之前或之后，省略的话插入到当前活动工作表之前。
#工作表名称重复的话会报错

(3)激活工作表

sh1.activate()   #激活为当前工作表

(4)清除

sht.clear()  #清除工作表所有内容和格式
sht.clear_contents()  #清除工作表的所有内容但是保留原有格式

clear()不仅可以清除背景色等格式，还可以清除数据有效性和条件格式等。

特别提醒：可以清除受保护的工作表的内容

(5)删除

sht.delete() #可以删除隐藏的工作表，但是不能删除深度隐藏的工作表

(6)属性

#工作表名称
sht.name  #返回工作表名称
sht.name = 'rename'  #重命名工作表名称

#返回指定工作表所属的工作簿
sht.book

#工作表上所有单元格的区域对象
sht.cells

#返回工作表的索引值
sht.index

#工作表中用过的区域
sht.used_range  #如果整张表为空，则返回A1单元格

4、对单元格进行操作

(1)引用单元格

rng = wb.sheets['sheet1'].range('a1')
rng = sht.range('a1')  #引用单元格
rng = sht['b1']   #引用单元格,参数可以大写，可以小写
rng = sht[0,0]   #引用单元格，第一行的第一列即a1
rng = xw.Range('c1') #引用活动sheet上的单元格
rng = sht.cells(1,2) #cells引用单元格，单元格行/列均从1开始索引

#引用某一行、一列
rng = sht.range('a1').expand('table')
nrows = rng.rows.count
ncols = rng.columns.count
a = sht.range(f'a1:a{nrows}').value
b = sht[0,:ncols].value #用切片

rng = sht.range('A'+str(r))  #多用于循环语句中

(2) 引用单元格区域

rng = sht.range('a1:b5')  #引用区域
rng = sht['a1:b5']   #引用区域
rng = sht[:5,0]  #引用区域（切片）

value=sht.range('A1').expand('table')
start_cell=(2,1)  #指定选择区域的起始单元格
end_cell=(value.shape[0],value.shape[1])  #指定选择区域的结束单元格，利用单元格的shape属性
cell_area=sht.range(start_cell,end_cell).value

(3)读、写单元格

#读取单元格值
a=sheet1.range(A2').value  #返回指定单元格的值，数值默认是浮点数

#写入单元格值
sheet1.range('A2').value='ab'
sheet2.cells(1,2).value='ab''ab''ab'

#按行插入
sheet1.range('A3').value=['a','b','c'] 
sht.range('a1:d4').value = [10,20,30,40]  #指定区域插入数据---按行

#按列插入
sht.range('a2').options(transpose=True).value = [5, 6, 7, 8]  

#区域赋值
sht.range('a6').expand('table').value = [['a','b','c'],['d','e','f'],['g','h','i']]  

sheet1[:3,:2].value     #指从第一行第一列到第三行第二列范围内的单元格即A1~C2

#取值
sheet.cells(行号,列号).value

(4)合并拆分单元格

xw.Range("A4:C4").api.merge()  # 合并单元格通过pywin32的api调用merge
xw.Range("A4:C4").api.unmerge()# 拆分单元格

(5)移动单元格

# 选定单元格进行移动
xw.Range('B2:C4').offset(row_offset=0,column_offset=0) 
# 将剪贴板中的范围粘贴到指定范围
xw.Range('A1:A3').paste()

(6) 单元格链接

rng = sht.range('a1')
rng.add_hyperlink(r'www.baidu.com','百度','提示：点击即链接到百度')  # 指定单元格加入超链接
a=rng.hyperlink  #获得range的超链接

(7)单元格内写入Excel公式

rng = sht.range('b6')
rng.formula='=SUM(B1:B5)'   #输入公式
a=rng.formula  #获取公式
#=SUM(B1:B5)
a=rng.get_address(row_absolute=True, column_absolute=True,include_sheetname=False, external=False)  # 获得单元格的绝对地址
# $B$6

(8)单元格最后一行

#引用最后一行/列
import xlwings as xw 
book=xw.books["XXXX.xlsx"]
sht=book.sheets['Sheet Name']
UsedRange = sht.used_range  #已经被使用的单元格区域
LastRow = UsedRange.last_cell.row  #有值单元格最后一行
LastCol = UsedRange.last_cell.column #有值单元格最后一列
row_num = sht['A1'].current_region.last_cell.row  #获取工作表中数据区域最后一行的行号

# 工作表sheet中有数据区域最大的行数，法2
sheet['a1048576'].end('up').row      
# 工作表sheet中有数据区域最大的列数，法2
sheet['xfd1'].end('left').column

# 返回工作表中已经使用的单元格区域 
sheet.api.UsedRange 或sheet.used_range

(9)插入删除行

#插入行、删除行
#插入行
sht.range('2:2').api.Insert()  # 插入行
sheet.api.row('2:4').insert    # 插入行，在第2-4行插入空白行

#删除行
sheet.api.row('2:4').delete    # 删除行

#插入列
sht.range('a:a').api.Insert()

#插入单元格
sht.range('b4').api.Insert()  # 插入单元格
xw.Range ('A3').insert(shift='right')  # 插入单元格

(10)其它

1.add_hyperlink(address, text_to_display=None, screen_tip=None):在单元格上添加超链接。
address:'超链接地址'。text_to_display:在单元格上显示的文本。screen_tip:当鼠标悬停时，出现的提示。
2.clear():删除Range里的内容和格式。
3.clear_contents():删除Range里的内容，保留格式。
4.color:获取或者设置范围里单元额的背景颜色。
设置颜色时，可以使用RGB元组。例如：range.color = (125,125,125) 
rng.color=None  #清除单元格背景色
5.column:返回Range对象的第一列编号
6.column_width:获取或设置Range的宽度。
7.columns:返回一个RangeColumns对象,该对象表示指定范围内的列。
8.a=rng.count #返回rng中单元格数量。
9.end(direction):返回一个Range对象。相当于window中的Ctrl+Up,Ctrl+down,Ctrl+left, 或Ctrl+right.
range.end('down')  #跳到该列最后一个有值单元格
10.formula:获取或设置Range的公式。
11.formula_array:获取或设置给定Range的数组公式。
12.get_address(row_absolute=True, column_absolute=True, include_sheetname=False, external=False):返回地址
13.height:返回Range的高度。
14.hyperlink: 返回指定Range的超链接地址(仅限单个单元格)。如无，则报错。
15.last_cell:返回指定Rnage右下角的单元格(只读)
16.left:返回从第一列到Range第一列的距离。
17.name:设置或获取Range的名称。
18.number_format:获取或设置Range的内容格式。
19.offset(row_offset=0, column_offset=0):返回一个偏移后的Range。
20.options(convert=None, **options): 允许设置转换器及其选项. 返Range对象。
21.raw_value:直接获取或设置传递的值。
22.row:返回指定Range第一行编号。
23.row_height:获取或设置Range的高度(以磅为单位).如果Range中的所有行具有相同的高度,则返回高度,否则返回None。
24.rows:返回所有行。
25.rows.count #返回行数
26.rows[0] #返回第一行
27.columns： #返回所有列
28.select():选择范围，相当于用鼠标选取范围。
29.shape:返回Range的维数即行数列数，以元组的形式。
30.size:Range中元素数量。
31.top:返回第一行到Range第一行的距离，跟left很像。
32.value:获取和设置给定Range的值。
32.width:返回Range的宽度。

5、设置表格格式

(1)设置行高和列宽

import xlwings as xw
wb = xw.Book()
sht = wb.sheets.active
value = sht.range('A1').expand('table') #选中整个工作表
value.column_width = 12 #将列宽设为12个字符的宽度，列宽值单位为字符数
value.row_height = 12  #将行高设为12磅，行高值单位为磅

wb.sheets['Sheet1'].autofit(axis='c')  #自动调整列宽columns或c
wb.sheets['Sheet1'].autofit(axis='r')  #自动调整行高rows 或 r，
wb.sheets['Sheet1'].autofit()  #自动调整行和列
sheet.range('A1:B2').columns.autofit()  #自动调整某几列列宽
sheet.range('A1:B2').rows.autofit()  #自动调整某几行行高

(2)设置字体格式及对齐设置

#设置字体格式
Range = sheet[:LastRow,:LastCol]  #选取区域
Range.api.color = (255,255,255)	#背景色RGB
Range.api.Font.Name = "Arial"	#设置字体为Arial
Range.api.Font.ColorIndex = 3	#设置字体颜色，颜色表详见图“ColorIndex 颜色索引”
Range.api.Font.Color = 0x00ff00 #设置字体颜色 十六进制数表示RGB，低2位表红色，中2位表绿色，高2位表蓝色
Range.api.Fonts.Size = 24        # 字号
Range.api.Fonts.Bold = True      # 粗体
Range.api.NumberFormat = '0'     # 数字格式，详见 NumberFormat-Microsoft 帮助。
Range.api.NumberFormat = 'm/d'   # 设置为月/日格式
Range.api.NumberFormat = '￥#,##0.00'   # 设置为带货币符合和保留2为小数格式

#设置字体对齐格式
Range.api.HorizontalAlignment = -4108   # 水平位置：-4108 居中 -4131 靠左  -4152 靠右。
Range.api.VerticalAlignment = -4130   	# 垂直位置：-4108 居中 -4160 靠上  -4107 靠下 -4130 自动换行对齐。
Range.api.HorizontalAlignment = xw.constants.HAlign.xlHAlignLeft  #设置水平对齐方式，靠左
Range.api.VerticalAlignment = xw.constants.VAlign.xlVAlignCenter  #设置垂直对齐方式，居中

(3)设置边框样式

import xlwings as xw 
book=xw.books["XXXX.xlsx"]
sheet=book.sheets['Sheet Name']
Range=sheet[:5,:5]
Range.api.Border(11).LineStyle = -4115 #设置前5行5列的表格内部全部垂直边框为虚线
#详见表‘引用边框 Border’ 及 表‘指定边框的线条样式 Line Style’
for i in range(7,11) #循环设置上下左右表框，使外表框加粗
	Range.api.Borders(i).Weight = 4 #边框线粗，1最细，4最粗

引用边框 Border

指定边框的线条样式 Line Style

三、插入图片

1、使用绝对路径插入

使用相对路径插入会报错。

import xlwings as xw

app=xw.App()
wb=app.books.open(r'C:\Users\Administrator\Desktop\2.xlsx')
sht=wb.sheets[0]

'''
add(image, link_to_file=False, save_with_document=True, left=0, top=0, 
    width=None, height=None, name=None, update=False, scale=1)
image 文件地址
left 距离左边距离
top 距离上边距离
width 宽
height 高
name 图片名字 默认‘Picture 1’
update (bool, default False) – Replace an existing picture with the same name. Requires name to be set
'''
sht.pictures.add(r'C:\Users\Administrator\Desktop\1.jpg',link_to_file=True,left=60,top=60)
sht.pictures.add(r'C:\Users\Administrator\Desktop\2.jpeg')
print(sht.pictures[0].height)
print(sht.pictures[0].left)
print(sht.range('a1').width)
# sht.pictures[0].left=sht.range('a1').width
# sht.pictures[0].width=sht.range('a1').width
# sht.pictures[0].top=0
# sht.pictures[0].height=sht.range('a1').height
# print(sht.pictures)
# sht.pictures[1].left=sht.range('a1').width*2

2、指定位置和大小

import os
import xlwings as xw
 
wb = xw.Book()
sht = wb.sheets['Sheet1']
fileName = os.path.join(os.getcwd(), '1.jpg')
sht.pictures.add(fileName, left=sht.range('B5').left, top=sht.range('B5').top, width=100, height=100)
wb.save('test.xlsx')
wb.close()

3、居中插入

import os
import xlwings as xw
 
wb = xw.Book('test.xlsx') # 打开已存在的Excel文件
sht = wb.sheets['Sheet1']
rng = sht.range('B2') # 目标单元格
fileName = os.path.join(os.getcwd(), '1.jpg')
width, height = 80, 80 # 指定图片大小
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()
wb.close()

4、使用Matplotlib制作图表并插入Excel工作表中

figure=plt.figure()  #调用Matplotlib模块制作的图表
sht.pictures.add(figure, name='图片1', update=True,left=200)
#name用于指定图表的名称，并不显示在图表上，是在绘制多个图表时使用的
#update设置为True，则在后续通过pictures.add()调用具有相同名称的图表时，可以只更新图表数据而不更改其位置或大小
#left用于设置图表与左侧边界的距离，单位像素。还可设置top、width、height

参考文章：

全网最详细的xlwings库解析(3) — Python操作工作簿&表 – 知乎

Excel Python之Xlwings学习笔记

Python操作Excel的Xlwings教程（三）

超全整理｜Python 操作 Excel 库 xlwings 常用操作详解！_pdcfighting的博客-CSDN博客

Python xlwings插入Excel图片的实现方法

python xlwings Pictures

来源：python-小卒