python读写excel利器：xlwings 从入门到精通

xlwings简介：
python操作Excel的模块，网上提到的模块大致有：xlwings、xlrd、xlwt、openpyxl等，
他们提供的功能归纳起来有两种：

一、用python读写Excel文件，实际上就是读写有格式的文本文件，操作excel文件和操作text、csv文件没有区别，Excel文件只是用来储存数据。
二、除了操作数据，还可以调整Excel文件的表格宽度、字体颜色等。

xlwings和目前流行的其它xlsxwriter, openpyxl的区别：

特点：

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

安装和使用

pip install xlwings
conda install xlwings
conda install -c conda-forge xlwings

xlwings中文文档
https://www.kancloud.cn/gnefnuy/xlwings-docs/1127450

英文文档：
https://docs.xlwings.org/en/stable/quickstart.html

excel基本结构分为 Application ——> Workbooks ——> Worksheets ——> Range.
即应用程序 ——> 工作簿 ——> 工作表 ——> 单元格。

在xlwings中

Excel程序用App来表示，多个Excel程序集合用Apps表示；
单个工作簿用Book表示，工作簿集合用Books表示；
单个工作表用Sheet表示，工作表集合用Sheets表示；
区域用Range表示，既可以是一个单元格，也可以是一片单元格区域。

明确几个概念：

新建：创建一个不存在的工作薄或者工作表
打开：打开一个已经存在的工作薄
引用：就是告诉程序，你要操作哪个对象。比如你打开了A、B、C三个工作薄，现在你想操作B工作薄，就要先引用B
激活：我们可以同时打开多个工作薄，但是一次只能操作一个工作簿，我们正在操作的这个工作薄称为当前活动工作薄，激活的意思就是将某一个对象（工作薄或工作表等）变成当前活动对象

基础操作

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

操作工作簿

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

操作工作表

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

读取单元格

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   # 和上面读取的内容一样。

写入(单元格赋值)

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

前面操作的是单个单元格，接下来我们来操作一行或一列

sheet.range('A1').value = [1, 2, 3, 4, 5]  #向 A1:E1 写入数据print(sheet.range('A1:E1').value)

xlwings 还提供了另外一种更加方便的方式来操作一个区域块，通过 expand 或 options 中的 expand 参数，expand 使用的是当前已获取的区域对象，而 options 中的 expand 参数在调用时才计算区域对象，推荐使用 options 中的 expand 参数，是你可以在更改区域后及时获取区域的变化。下面的代码，可以清楚的表达两种方式的不同。

sheet.range('A1').value = [[1,2], [3,4]]
rng1 = sheet.range('A1').expand('table')
rng2 = sheet.range('A1').options(expand='table')
print(rng1.value)
# [[1.0, 2.0], [3.0, 4.0]]
print(rng2.value)
# [[1.0, 2.0], [3.0, 4.0]]
sheet.range('A3').value = [5, 6]
print(rng1.value)
# [[1.0, 2.0], [3.0, 4.0]]
print(rng2.value)
# [[1.0, 2.0], [3.0, 4.0], [5.0, 6.0]]

举例(二维数据写入）：

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

# 将A1：B3的值赋给二维列表list1
list1=sht.range('A1:B3').value

Excel中区域的选取表格(按行或按列选取)

# 选取第一列
rng=sht. range('A1').expand('down')
rng.value=['a1','a2','a3']
# 选取第一行
rng=sht.range('A1').expand('right')
#rng=['a1','b1']

# 选取表格
rng.sht.range('A1').expand('table')
rng.value=[['a1','a2','a3'],['b1','b2','b3']]

range的操作（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()

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

注意点：这里的sheet参数必须是已经存在的sheet表，不能新建

import xlwings as xw
def write_col(io, sheet, col='A1', data=None):
    """
    写入一列数据
    :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()
一次写多列
注意点：此方法所需的data参数必须是list嵌套，如：[[1, 9], [2, 8], [3, 7], [4, 6]]，并且里面的每个list的长度必须一致

def write_col(io, sheet, col='A1', data=None):
    """
    写入多列数据
    :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(col).value = data
    wb.save()
    wb.app.quit()

写入效果如下：

写入行，一次写一行
注意点：此方法所需的data参数是list类型，如：[1, 2, 3, 4]

def write_row(io, sheet, row='A1', data=None):
    """
    写入一行数据
    :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()
一次写多行
注意点：此方法所需的data参数必须是list嵌套，如：[[1, 2], [3, 4], [5, 6]]，并且里面的每个list的长度必须一致

def write_row(io, sheet, row='A1', data=None):
    """
    写入多行数据
    :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(row).options(transpose=True).value = data
    wb.save()
    wb.app.quit()
写入效果如下：

使用range('A1').api.AddComment('comments')给单元格加注释

ws1 = wb.sheets.add('原始整理')
ws1.range('A1').value = 'name_list_bank'
ws1.range('A1').api.AddComment('该表是汇总大家的待授信银行机构名单')
ws1.range('A2').options(transpose=True).value = sorted(name_list_bank)
ws1.range('B1').value = 'name_none_bank'
ws1.range('B1').api.AddComment('该表是汇总数人的待授信非银行机构名单')
ws1.range('B2').options(transpose=True).value =  sorted(name_none_bank)

格式设置

使用xlwings模块进行excel表格操作时，难免会用到对单元格进行格式设置，比如常用到的对单元格设置为文本格式、日期时间格式、小数和百分数，下面列出常用：
使用range().api.NumberFormat = XXX即可修改格式

比如：

range('A1').api.NumberFormat = "@"  #设置为文本格式
range('A2').api.NumberFormat = "0.0"  #设置为小数格式
range('A3').api.NumberFormat = "yyyy-mm-dd"  #设置为"-"连接的日期格式
range('A4').api.NumberFormat = "0%"  #设置为百分比

设置单元格大小

sht.autofit()    # 自动调整单元格大小。注：此方法是在单元格写入内容后，再使用，才有效。
sht.range(1,4).column_width = 5    # 设置第4列 列宽。（1,4）为第1行第4列的单元格
sht.range(1,4).row_height = 20     # 设置第1行 行高

设置单元格字体格式

cell1.color = 255,200,255         # 设置单元格的填充颜色
cell1.api.Font.ColorIndex = 3     # 设置字体的颜色，具体颜色索引见下方。
cell1.api.Font.Size = 24          # 设置字体的大小。
cell1.api.Font.Bold = True        # 设置为粗体。
cell1.api.HorizontalAlignment = -4108    # -4108 水平居中。 -4131 靠左，-4152 靠右。
cell1.api.VerticalAlignment = -4130      # -4108 垂直居中（默认）。 -4160 靠上，-4107 靠下， -4130 自动换行对齐。
cell1.api.NumberFormat = "0.00"          # 设置单元格的数字格式。

设置边框

# Borders(9) 底部边框，LineStyle = 1 直线。
cell1.api.Borders(9).LineStyle = 1
cell1.api.Borders(9).Weight = 3                # 设置边框粗细。

# Borders(7) 左边框，LineStyle = 2 虚线。
cell1.api.Borders(7).LineStyle = 2
cell1.api.Borders(7).Weight = 3

# Borders(8) 顶部框，LineStyle = 5 双点划线。
cell1.api.Borders(8).LineStyle = 5
cell1.api.Borders(8).Weight = 3

# Borders(10) 右边框，LineStyle = 4 点划线。
cell1.api.Borders(10).LineStyle = 4
cell1.api.Borders(10).Weight = 3

# Borders(5) 单元格内从左上角 到 右下角。
cell1.api.Borders(5).LineStyle = 1
cell1.api.Borders(5).Weight = 3

# Borders(6) 单元格内从左下角 到 右上角。
cell1.api.Borders(6).LineStyle = 1
cell1.api.Borders(6).Weight = 3

如果是一个区域的单元格，内部边框设置如下

# # Borders(11) 内部垂直边线。
# cell1.api.Borders(11).LineStyle = 1
# cell1.api.Borders(11).Weight = 3
# 
# # Borders(12) 内部水平边线。
# cell1.api.Borders(12).LineStyle = 1
# cell1.api.Borders(12).Weight = 3

单元格操作

合并拆分单元格

sht.range('C8:D8').api.merge()      # 合并单元格 C8 到 D8
sht.range('C8:D8').api.unmerge()    # 拆分单元格。

插入、删除一行

sht1.range('a3').api.EntireRow.Delete()     # 会删除 ’a3‘ 单元格所在的行。
sht1.api.Rows(3).Insert()                   # 会在第3行插入一行，原来的第3行下移。

插入、删除一列

sht1.range('c2').api.EntireColumn.Delete()  # 会删除 ’c2‘ 单元格所在的列。
sht1.api.Columns(3).Insert()                # 会在第3列插入一列，原来的第3列右移。(也可以用列的字母表示)

选择sheet页面最右下角的单元格，获取最大行数，和列数

# 区别 expand(), expand()只选中与之连续的单元格。
cell = sht1.used_range.last_cell
rows = cell.row
columns = cell.column

# cell = sht1.range("a1").expand("down")
# max_rows = cell.rows.count              # 获取最大行数

排序，删除重复值

排序使用方法：
1、选择需要排序的区域。这里用 'a2' 是因为排序的数据送从第二行开始的，第一行是标题，不应该参与排序。
2、选择按那一列进行排序 Key1=sht.range('c2').api，这里选择的是按第 C 列排序，所以这里选择 c1 和 c2 都可以。
3、Order1=1 为升序，2为降序。

sht1.range('a2',(rows,columns)).api.Sort(Key1=sht.range('c2').api, Order1=1)

删除重复值使用方法：

# RemoveDuplicates(3) 为按第3列内容进行删除重复项。
sht1.range('a2',(rows,columns)).api.RemoveDuplicates(3)

插入、读取公式

sht1.range('d1').formula = '=sum(e1+f1)'    # 插入公式
print(sht1.range('d1').formula)

同个表格复制、粘贴

# 复制 a2 到 a6 之间单元格的值，粘贴到'a15'中
sht.range('a2','a6').api.Copy(sht.range('a15').api)

跨表格复制、粘贴

my_values = sht_1.range('a2：d4').options(ndim=2).value    # 读取二维的数据
sht_2.range('a1').value = my_values

关闭文件

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

其它功能

清除单元格内容和格式 sheet.range('A1').clear()
单元格的列标 sheet.range('A1').column
单元格的行标 sheet.range('A1').row
单元格的行高 sheet.range('A1').row_height
单元格的列宽 sheet.range('A1').column_width
列宽自适应 sheet.range('A1').columns.autofit()
行高自适应 sheet.range('A1').rows.autofit()
单元格背景色(RGB) sheet.range('A1').color=(34,139,34)
清除单元格颜色 sheet.range('A1').color=None
输入公式，相应单元格会出现计算结果 sheet.range('A1').formula='=SUM(A1:E1)
获取单元格公式 sheet.range('A1').formula_array

踩过的坑

解决xlwings写入长数字型字符串时变成科学计数法的问题

解决要点：要先把要设置的那一列，设置为文本格式，再给那个区域赋值（次序倒过来是不行的）

sht = wb.sheets.add('买入清单')
sht.range('B:B').api.NumberFormat ="@"
sht.range('A1').value = df_gz_buy
sht.autofit('c')

#或者写成：
sht2 = wb.sheets.add('卖出清单',after =sht)
range2 = sht2.range('B:B')
range2.api.NumberFormat ="@"
sht2.range('A1').value = df_gz_sell
sht2.autofit('c')

参考资料：

官网：https://docs.xlwings.org/en/stable/
官网中文：https://docs.xlwings.org/zh_CN/latest/quickstart.html
国外视频教程（USD99）https://training.xlwings.org/p/xlwings
利用xlwings库对excel进行字体(font)、边框(border)、合并单元格(merge)等格式设置：http://www.dszhp.com/xlwings-format.html
不一样的xlwings用法：https://blog.csdn.net/lly1122334/article/details/99706504
10秒搞定Xlwings全套操作：https://zhuanlan.zhihu.com/p/237583143
Python操作Excel的Xlwings教程（1-5）（强烈推荐）：
https://blog.csdn.net/th1522856954/article/details/107850947
https://blog.csdn.net/th1522856954/article/details/107923265
https://blog.csdn.net/th1522856954/article/details/108111058
https://blog.csdn.net/th1522856954/article/details/108269219

posted on 2020-12-08 12:14 chengjon 阅读(21512) 评论(3) 编辑收藏举报

刷新页面返回顶部

金融量化学习---Python, MySQL, Pandas

导航

公告

python读写excel利器：xlwings 从入门到精通

安装和使用

基础操作

操作工作簿

操作工作表

读取单元格

写入(单元格赋值)

range的操作（range常用的api）

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

使用range('A1').api.AddComment('comments')给单元格加注释

格式设置

设置单元格大小

设置单元格字体格式

设置边框

单元格操作

合并拆分单元格

插入、删除一行

插入、删除一列

排序，删除重复值

删除重复值使用方法：

插入、读取公式

同个表格复制、粘贴

跨表格复制、粘贴

关闭文件

其它功能

踩过的坑

解决xlwings写入长数字型字符串时变成科学计数法的问题

参考资料：

金融量化学习---Python, MySQL, Pandas

导航

公告

python读写excel利器：xlwings 从入门到精通

安装和使用

基础操作

操作工作簿

操作工作表

读取单元格

写入(单元格赋值)

range的操作（range常用的api）

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

使用range('A1').api.AddComment('comments')给单元格加注释

格式设置

设置单元格大小

设置单元格 字体格式

设置边框

单元格操作

合并拆分单元格

插入 、删除 一行

插入 、删除 一列

排序，删除重复值

删除重复值使用方法：

插入、读取公式

同个表格复制、粘贴

跨表格复制、粘贴

关闭文件

其它功能

踩过的坑

解决xlwings写入长数字型字符串时变成科学计数法的问题

参考资料：

设置单元格字体格式

插入、删除一行

插入、删除一列