Python操作Excel之xlwings教程

Python中有哪些模块（或者第三方库）可以轻松处理Excel:

xlwings是一个可以实现从Excel调用Python，也可在python中调用Excel的库。开源免费，一直在更新。特点：

1）xlwings支持.xls读，支持.xlsx文件读写。

2）支持Excel操作。

3）支持VBA。

4）强大的转换器可以处理大部分数据类型，包括在两个方向上的numpy array和pandas DataFrame

xlwings的特色

xlwings能够非常方便的读写Excel文件中的数据，并且能够进行单元格格式的修改

可以和matplotlib以及pandas无缝连接

可以调用Excel文件中VBA写好的程序，也可以让VBA调用用Python写的程序。

开源免费，一直在更新

文档:https://docs.xlwings.org/en/stable/index.html

安装

使用pip安装:

pip install xlwings


conda安装:

conda install xlwings


使用conda-forge通道安装:

conda install -c conda-forge xlwings


注意：在安装过程中，xlwings也是有依赖项的，但是依赖项通过conda或pip自动安装

常用API

基本操作

    打开已保存的Excel文档
    # 导入xlwings模块，打开Excel程序，默认设置：程序可见，只打开不新建工作薄，屏幕更新关闭
    import xlwings as xw
    app=xw.App(visible=True,add_book=False)
    app.display_alerts=False
    app.screen_updating=False
    # 文件位置：filepath，打开test文档，然后保存，关闭，结束程序
    filepath=r'g:\Python Scripts\test.xlsx'
    wb=app.books.open(filepath)
    wb.save()
    wb.close()
    app.quit()

    新建Excel文档，命名为test.xlsx，并保存在D盘。
    import xlwings as xw
    app=xw.App(visible=True,add_book=False)
    wb=app.books.add()
    wb.save(r'd:\test.xlsx')
    wb.close()
    app.quit()

    在单元格输入值
    新建test.xlsx，在sheet1的第一个单元格输入 “人生” ，然后保存关闭，退出Excel程序。
    import xlwings as xw
    app=xw.App(visible=True,add_book=False)
    wb=app.books.add()
    # wb就是新建的工作簿(workbook)，下面则对wb的sheet1的A1单元格赋值
    wb.sheets['sheet1'].range('A1').value='人生'
    wb.save(r'd:\test.xlsx')
    wb.close()
    app.quit()
    打开已保存的test.xlsx，在sheet2的第二个单元格输入“苦短”，然后保存关闭，退出Excel程序
    import xlwings as xw
    app=xw.App(visible=True,add_book=False)
    wb=app.books.open(r'd:\test.xlsx')
    # wb就是新建的工作簿(workbook)，下面则对wb的sheet1的A1单元格赋值
    wb.sheets['sheet1'].range('A1').value='苦短'
    wb.save()
    wb.close()
    app.quit()
    掌握以上代码，已经完全可以把Excel当作一个txt文本进行数据储存了，也可以读取Excel文件的数据，进行计算后，并将结果保存在Excel中。

引用工作簿、工作表和单元格

    引用工作簿，注意工作簿应该首先被打开
    wb.=xw.books['工作簿的名字‘]

    引用活动工作簿
    wb=xw.books.active

    引用工作簿中的sheet
    sht=xw.books['工作簿的名字‘].sheets['sheet的名字']
    # 或者
    wb=xw.books['工作簿的名字']
    sht=wb.sheets[sheet的名字]

    引用活动sheet
    sht=xw.sheets.active

    引用A1单元格
    rng=xw.books['工作簿的名字‘].sheets['sheet的名字']
    # 或者
    sht=xw.books['工作簿的名字‘].sheets['sheet的名字']
    rng=sht.range('A1')

    引用活动sheet上的单元格
    # 注意Range首字母大写
    rng=xw.Range('A1')
    其中需要注意的是单元格的完全引用路径是：
    # 第一个Excel程序的第一个工作薄的第一张sheet的第一个单元格
    xw.apps[0].books[0].sheets[0].range('A1')
    迅速引用单元格的方式是
    sht=xw.books['名字'].sheets['名字']
    # A1单元格
    rng=sht[’A1']
    # A1:B5单元格
    rng=sht['A1:B5']
    # 在第i+1行，第j+1列的单元格
    # B1单元格
    rng=sht[0,1]
    # A1:J10
    rng=sht[:10,:10]

PS： 对于单元格也可以用表示行列的tuple进行引用

# A1单元格的引用

xw.Range(1,1)

#A1:C3单元格的引用

xw.Range((1,1),(3,3))
储存数据

    储存单个值
    # 注意".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')=[[1,2],[3,4]]

读取数据

    读取单个值
    # 将A1的值，读取到a变量中
    a=sht.range('A1').value

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

常用函数和方法

    Book 工作簿常用的api
    wb=xw.books[‘工作簿名称']

    wb.activate()激活为当前工作簿

    wb.fullname 返回工作簿的绝对路径

    wb.name 返回工作簿的名称

    wb.save(path=None) 保存工作簿，默认路径为工作簿原路径，若未保存则为脚本所在的路径
    -wb. close() 关闭工作簿
    代码例子：
    # 引用Excel程序中，当前的工作簿
    wb=xw.books.acitve
    # 返回工作簿的绝对路径
    x=wb.fullname
    # 返回工作簿的名称
    x=wb.name
    # 保存工作簿，默认路径为工作簿原路径，若未保存则为脚本所在的路径
    x=wb.save(path=None)
    # 关闭工作簿
    x=wb.close()

    sheet 常用的api
    # 引用某指定sheet
    sht=xw.books['工作簿名称'].sheets['sheet的名称']
    # 激活sheet为活动工作表
    sht.activate()
    # 清除sheet的内容和格式
    sht.clear()
    # 清除sheet的内容
    sht.contents()
    # 获取sheet的名称
    sht.name
    # 删除sheet
    sht.delete

    range常用的api
    # 引用当前活动工作表的单元格
    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()

    books 工作簿集合的api
    # 新建工作簿
    xw.books.add()
    # 引用当前活动工作簿
    xw.books.active

    sheets 工作表的集合
    # 新建工作表
    xw.sheets.add(name=None,before=None,after=None)
    # 引用当前活动sheet
    xw.sheets.active

使用示例

先了解如下内容：

新建：创建一个不存在的工作薄或者工作表

打开：打开一个已经存在的工作薄

引用：就是告诉程序，你要操作哪个对象。比如你打开了A、B、C三个工作薄，现在你想操作A工作薄，就要先引用A

激活：我们可以同时打开多个工作薄，但是一次只能操作一个工作簿，我们正在操作的这个工作薄称为**当前活动工作薄。

在xlwings中

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

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

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

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



入门示例:



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



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

示例:

import xlwings as xw
 
app=xw.App()
pid = app.pid
# 每个App对应一个PID值，可以看作是数字编号，可以用来识别不同的App。
app1=xw.App()
pid1 = app1.pid
print(pid,pid1)
 
count = xw.apps.count
 #是指当前打开的这个app
print(count)
#查看xw下所有的app的PID值
print(xw.apps.keys())


在操作一个app对象的时候要先引用工作薄，但是引用并不代表激活，激活就是当前操作的工作薄。

常用属性:

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

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



示例:

import xlwings as xw
 
app = xw.App(visible=False, add_book=False) # 界面设置
app.display_alerts = False  # 关闭提示信息
app.screen_updating = False  # 关闭显示更新
 
wb = app.books.add() # 创建新的工作簿
sht = wb.sheets['Sheet1'] # 实例化工作表
sht.range('A1').value = 'Hello World!'
print(sht.range('A1').value) # 读取
wb.close()
app.kill()#通过杀掉进程,强制Excel app退出
# app.quit()#退出excel程序,不保存任何工作簿


工作簿Book与Books

创建Book对象

官方给出的创建工作薄的方式如下：

方式1:wx.book()

方式2:wx.books.add()



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



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

wb = app.books.open('绝对或者相对路径的excel文件')
'或者
wb = xw.Book('绝对或者相对路径的excel文件')


激活与保存

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


保存工作薄：

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


关闭

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.close(),注意：wb.close()只是关闭并不会保存，所以在关闭之前必须要使用save()进行一下保存才可以。可以考虑使用with搭建上下文，实现关闭资源。
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()


引用区域与单元格操作:

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(‘ 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对象






示例

批量写入并读取数据:

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()


综合示例:

import xlwings as xw
import os

# 打开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')
    # ----------------------插入图片----------------------
    wb = xw.Book()
    sht = wb.sheets['Sheet1']
    rng = sht.range('A1')
    fileName = os.path.join(os.getcwd(), 'image.jpg')
    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()

    # ----------------------综合演示----------------------
    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('goodsNew.xlsx')

以上就是“Python操作Excel之xlwings教程”的详细内容，想要了解更多Python教程欢迎持续关注编程学习网。