处理Excel 的模块 xlwings

转载好文章：

可以处理Excel文件的python模块很多，各模块功能对比如下

功能	XlsxWriter	xlrd	xlwt	xlutils	openpyxl	xlwings	Pandas
读	×	√	×	√	√	√
写	√	×	√	√	√	√
修改	×	×	×	√	√	√
支持xls格式	×	√	√	√	×	√	√
支持xlsx格式	√	√	√	×	√	√	√
支持批量操作	×	×	×	×	×	√
支持VBA	√	×	×	×	×	√

可以发现 xlwings功能是最齐全的。而且他可以与EXCEL VBA结合使用，实现强大的数据输入和分析功能。

这个模块会经常和os,pandas,Numpy,Matplotlib等模块结合使用，让我们可以轻松应对数据处理和分析工作。

App相当于Excel程序(前文的进程截图)；
Book相当于Excel的工作簿；
Range相当于单元格，也可以是单元格区域；
多个Excel程序则由Apps表示，单个为App；
多个工作簿用Books表示，单个为Book；
多个工作表用Sheets表示，单个为Sheet。

1.创建工作簿

# 方法1：
# 创建一个新的App，并在新App中新建一个Book
wb = xw.Book()
wb.save('1.xlsx')
wb.close()

# 方法2：
# 当前App下新建一个Book
# visible参数控制创建文件时可见的属性
app=xw.App(visible=False,add_book=False)
wb=app.books.add()
wb.save('1.xlsx')
wb.close()
app.quit() #结束进程

下图展示了xlwings.mian.app的 __init__方法

2.打开已有的Excel文件

import xlwings as xw

app=xw.App(visible=True,add_book=False)
app.display_alerts=False   #不显示Excel消息框
app.screen_updating=False  #关闭屏幕更新,可加快宏的执行速度
wb=app.books.open('1.xlsx')
# print(wb.fullname)       # 输出打开的excle的绝对路径
wb.save()
wb.close()
app.quit()  # 退出excel程序，
# app.kill() 通过杀掉进程强制Excel app退出


# 以第一种方式创建Book时，打开文件的操作可如下

import xlwings as xw

app=xw.App(visible=True,add_book=False)
app.display_alerts=False   #不显示Excel消息框
app.screen_updating=False  #关闭屏幕更新,可加快宏的执行速度
wb = xw.Book('1.xlsx')
print(wb.fullname)       # 输出打开的excle的绝对路径
wb.save()
wb.close()
app.quit()  # 退出excel程序
# app.kill() 通过杀掉进程强制Excel app退出

xw.Book()打开文件传入的参数可选，具体如下：

官网中有一句提醒:

If you have the same file open in two instances of Excel, you need to fully qualify it and include the app instance. You will find your app instance key (the PID) via xw.apps.keys():
xw.apps[10559].books['FileName.xlsx']

也是就是说：

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

（2）创建工作簿之前要先创建App:

app=xw.App(visible=Ture,add_book=False)

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

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

建议使用：xw.Book('filename.xlsx') 来打开工作薄或引用工作簿，不容易出错

官网中有一句提醒:

If you have the same file open in two instances of Excel, you need to fully qualify it and include the app instance. You will find your app instance key (the PID) via xw.apps.keys():
xw.apps[10559].books['FileName.xlsx']

也是就是说：

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

（2）创建工作簿之前要先创建App:

app=xw.App(visible=Ture,add_book=False)

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

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

建议使用：xw.Book('filename.xlsx') 来打开工作薄或引用工作簿，不容易出错

官网中有一句提醒:

If you have the same file open in two instances of Excel, you need to fully qualify it and include the app instance. You will find your app instance key (the PID) via xw.apps.keys():
xw.apps[10559].books['FileName.xlsx']

也是就是说：

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

（2）创建工作簿之前要先创建App:

app=xw.App(visible=Ture,add_book=False)

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

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

建议使用：xw.Book('filename.xlsx') 来打开工作薄或引用工作簿，不容易出错。 xw.Book 和 xw.books使用差异

3.读取和写入值

# 在A1单元格写入值
# 实例化一个工作表对象
sheet1 = wb.sheets["sheet1"]
# 或者
# sheet1 =xw.books['1.xlsx'].sheets['sheet1']
# print(sheet1.name) 输出工作簿名称
# 写入值
sheet1.range('A1').value = 'python知识学堂'
# 读值并打印
print('value of A1:',sheet1.range('A1').value)
# 清空单元格内容,如果A1中是图片，此方法没有效果
sheet1.range('A1').clear()
# 传入列表写入多行值
sheet1.range('A1').value = [['a','b','c],[1,2,3]]
# 当然也可以将pandas的DataFrame数据写入
import pandas as pd
df = pd.DataFrame([[1,2], [3,4]], columns=['A', 'B'])
sheet1.range('A1').value = df
# 读取数据，输出类型为DataFrame
sheet1.range('A1').options(pd.DataFrame, expand='table').value
# 支持添加图片的操作
import matplotlib.pyplot as plt
fig = plt.figure()
plt.plot(x, np.log(x))
sheet1.pictures.add(fig, name='MyPlot', update=True)

n =65
n = chr(n)   # ASCII字符
pos = '%s%d' % (n, 1)
print(pos)  #A1

Tips: 对于A-Z的单元格可以这样进行访问，在进行循环读写的时候比较好用

4.其他

4.1 活动对象

#  存在活动工作表的时候（比如打开一个1.xlsx文件以后），可以直接操作
#  不存在的时候，就需要通过Book经sheet获取range
import xlwings as xw
xw.Range('A1').value = 'Python知识学堂'

Tips: xlwings中的对象层次结构为：

apps->books->sheets->range

4.2 range

假设现有的1.xlsx文件的数据如下：

# 传递字符串或索引/切片使得取值更加方便
app = xw.App(visible=False,add_book=False)
wb = app.books.open('1.xlsx')
range_1 = wb.sheets[0].range('A1:D3')
print(range_1)
# <Range [1.xlsx]Sheet1!$A$1:$D$3>
print(range_1.value)
# [[None, 'a', 'b', None], [0.0, 1.0, 2.0, None], [1.0, 3.0, 4.0, None]]
# 切片方式
range_2 = wb.sheets[0][:3, :3]
# <Range [1.xlsx]Sheet1!$A$1:$C$3>

# 写值的情况
# 使用列表将1,2,3,4写入A1,A2,A3,A4
# transpose=True进行转置写入
wb.sheets[0].range('A1').options(transpose=True).value=[1,2,3,4]
# 将二维数组，储存在A1:B3中
wb.sheets[0].range('A1').options(expand='table')=[[1,2],[3,4],[5,6]]

在Excel的读写中，经常需要获取当前打开的文件的数据行和列数，在Xlwings中的获取方式：

假设数据文件如下：

ws = wb.sheets['Sheet1']
shape = ws.used_range.shape
print(shape) #(2, 3)

nrow1 = ws.api.UsedRange.Rows.count
ncol1 = ws.api.UsedRange.Columns.count
print(nrow1) # 2
print(ncol1) # 3

rng = ws.range('A1').expand()
nrow2 = rng.last_cell.row
ncol2 = rng.last_cell.column
print(nrow2)  # 3
print(ncol2)  # 1

(I) 如果整张表为空，上述代码输出是怎样的呢？

(II）数据文件如下，那么上述代码的输出是怎样的呢？即返回结果是有数据矩阵的行数？

当然我们可以使用遍历的方式去寻找Excel文件中非空行的数量

# 可以多选几列进行一起判断
row = initial
while (ws.range('A'+str(row)).value !=None
    rownum += 1

UsedRange属性返回工作表中所有已使用范围的单元格区域是指：单元格中有数值、公式、单元格格式化设置（例如：单元格字体设置、边框设置等等）　　

可以对比Openpyxl和其他的库，看看计算Excel文件数据行数和列数的差异.　　

单元格还有其他一些属性和方法：

# 获取"AB2"单元格的行标和列标
print(ws.range('AB2').row)
print(ws.range('AB2').column)
# 高度和宽度
print(ws.range('AB2').row_height)
print(ws.range('AB2').column_width)
# 设置颜色,可根据RGB颜色表寻找自己想要的颜色
ws.range('AB2').color = (255,0,0)
# 获取颜色
print(ws.range('AB2').color)
# 清除颜色格式
ws.range('AB2').color = None
# 使用公式
ws.range('AB2').formula='=SUM(A1,A2)'
# 另外还可以获取某一个单元格的公式
print(ws.range('AB2').formula_array)
# 清除工作表的所有内容但是保留原有格式
ws.clear_contents()
# 当然了还有很多其他的属性
#range.address        range.current_region    range.end
#range.api            range.autofit          range.expand

import xlwings as xw
app1 = xw.App()
app2 = xw.App()
print(xw.apps)

第二、三行代码建立了两个创建工作簿实例，运行后发现：默认的打开了两个工作簿，这两个工作簿会显示的存在于你的桌面。

如果不想显示，我们在创建实例的时候可以使用参数：wx.App(visible=False)

第四行输出为：Apps([<Excel App 9244>, <Excel App 19476>])，其中[9244]和[19476]即为这两个实例的PID。

xw.apps.active          # 返回活动的应用程序
print(xw.apps.active)   # 输出<Excel App 9244>，活动应用程序的key
xw.apps.count           # 计算app的总数
xw.apps.add()           # 新增一个app
xw.apps.keys()          # 返回所有Excel实例的PID

Active：很多小伙伴可能对激活不是很清楚，举个例子：我们再使用Excel软件的时候，可以打开好几个工作簿，但是我们不能对打开的工作簿同时进行操作，只能操作其中一个。
这个Active的作用就是激活其中的一个你想使用的工作簿，将它变为活动工作簿。

for i in range(1,4):
    wookbook = xw.Book() #创建新的App并在创建的App新建Book
    print(wookbook)
print(xw.books)          #当前打开的所有Book对象的集合

xw.Book()会新建工作簿，这里默认会新建一个App，即打开Excel程序，并新建一个工作簿。而且Book这种方式则会打开多个窗口，之前所说的App.books.open方式则不会。

顺便提一下引用工作簿的方法：（使用工作簿前要引用工作簿）

xw.Book('工作簿1')   # 通过Book
xw.books['工作簿1']  # 通过books

（1）wb.app.calculate()               # Calculates all open books.
（2）wb.app.calculation = ‘manual’    # 设置计算模式
（3）wb.app.display_alerts = True     # 默认值为True。False的情况下执行操作的时候会忽略Excel的提示和警报消息(即弹窗信息)    
（4）wb.app.screen_updating = False   # 禁止屏幕更新(刷新)
（5）wb.app.range(cell1, cell2=None)  # 获取单元格
（6）wb.app.selection                 # 将所选单元格作为Range返回
（7）xw.App().version                 # 返回Excel程序版本号

首先要知道所有的Book构成Books,即Books是所有Book对象的集合,那么Books中有哪些常用的方法或属性呢？

import xlwings as xw
app1 = xw.App()
app2 = xw.App()
print(xw.books)
book_keys = xw.apps.keys()
first_book = book_keys[0]             # 取列表的第一个值
print(xw.apps[first_book].books)      # 应用
print(app1.books.active)              # 激活一个Book
print('count1:',app1.books.count)     # 先打印一下app1下Book的总数
app1.books.add()                      # 增加一个新的Book,此新建的Book将自动变成被激活的Book
print('count1:',app1.books.count)     # 再次打印app1下Book的总数
app1.books.open('1.xlsx')             # 打开操作，如果工作簿未打开则打开，若它已经打开，则返回工作簿对象。
# 或指定绝对路径打开文件  app1.books.open('D:\datafile\1.xlsx') 
print(app1.books.open('1.xlsx'))      
print('count1:',app1.books.count)　　 # 再一次打印app1下Book的总数

# 若想关闭某一个app下的全部工作簿的时候，可以使用kill()，如app1.kill()。

四、Book

官网中介绍到:A book object is a member of the books collection，即Book对象是Books集合的成员。下表显示了xw.Book()和xw.books三种情况下的操作：（上篇推文也有介绍）

Tips: 差别在于xw.Book()创建新的工作薄实例，xw.books是在活动工作薄中寻找一个实例

xw.Book()打开文件的时候，可选参数较多，具体的参数可以查看其构造函数，或者查看上篇文章

方法和属性

xw.Book.activate(steal_focus=True)   # True则激活最前面的窗口，并将焦点从python移交给excel
xw.Book.app                          # 获取创建工作簿的app对象
xw.Book.caller()                     # 当通过RunPython从Excel调用Python函数时引用调用的工作簿
xw.Book.set_mock_caller()            # 设置当前工作薄为Python代码的调用者即set_mock_caller让Python知道谁是调用者
xw.Book.close()                      # 关闭工作簿而不进行保存
xw.Book.selection                    # 返回选定的单元格作为Range
xw.Book.macro()                      # 在Excel VBA中运行Sub或Function
xw.Book.name                         # 返回工作簿的名称
xw.Book.names                        # 返回一个名称集合
xw.Book.save(path=None)              # 保存工作簿，可传入保存路径，同名会直接覆盖
xw.Book.sheets                       # 返回表示工作簿中所有工作表的工作表集

posted @ 2021-04-17 18:41 正在学Python 阅读(1987) 评论(0) 编辑收藏举报

刷新页面返回顶部

走python的路

处理Excel 的模块 xlwings

四、Book

公告