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

  行业动态     |      2024-01-22 01:04

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

目录

  • 安装和使用
    • 基础操作
        • 操作工作簿
        • 操作工作表
        • 读取单元格
        • 写入(单元格赋值)
        • range的操作(range常用的api)
            • 写入一行或一列Excel数据(函数式)
          • 使用range(\'A1\').api.AddComment(\'comments\')给单元格加注释
    • 格式设置
        • 设置单元格大小
        • 设置单元格 字体格式
        • 设置边框
    • 单元格操作
        • 合并拆分单元格
        • 插入 、删除 一行
        • 插入 、删除 一列
        • 排序,删除重复值
        • 删除重复值使用方法:
        • 插入、读取公式
        • 同个表格复制、粘贴
        • 跨表格复制、粘贴
    • 关闭文件
    • 其它功能
    • 踩过的坑
        • 解决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表示,既可以是一个单元格,也可以是一片单元格区域。

明确几个概念:

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

基础操作

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