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

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 xlwingsconda install xlwingsconda 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 xwapp = 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的值赋给二维列表list1list1=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.addressrng.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_regionrng.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# 行的高度，所有行一样高返回行高，不一样返回Nonerng.row_height# 返回range的总高度rng.height# 返回range的行数和列数rng.shape# 返回range所在的sheetrng.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 xwdef 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 = datawb.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 = datawb.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 = datawb.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 = datawb.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 = 1cell1.api.Borders(9).Weight = 3# 设置边框粗细。# Borders(7) 左边框，LineStyle = 2 虚线。cell1.api.Borders(7).LineStyle = 2cell1.api.Borders(7).Weight = 3# Borders(8) 顶部框，LineStyle = 5 双点划线。cell1.api.Borders(8).LineStyle = 5cell1.api.Borders(8).Weight = 3# Borders(10) 右边框，LineStyle = 4 点划线。cell1.api.Borders(10).LineStyle = 4cell1.api.Borders(10).Weight = 3# Borders(5) 单元格内从左上角 到 右下角。cell1.api.Borders(5).LineStyle = 1cell1.api.Borders(5).Weight = 3# Borders(6) 单元格内从左下角 到 右上角。cell1.api.Borders(6).LineStyle = 1cell1.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 到 D8sht.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_cellrows = cell.rowcolumns = 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_buysht.autofit(\'c\')#或者写成：sht2 = wb.sheets.add(\'卖出清单\',after =sht)range2 = sht2.range(\'B:B\')range2.api.NumberFormat ="@"sht2.range(\'A1\').value = df_gz_sellsht2.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