xlwings

xlwings

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

xlwings还可以和matplotlib、numpy以及pandas无缝连接，支持读写numpy、pandas数据类型，将matplotlib可视化图表导入到excel中。xlwings可以调用Excel文件中VBA写好的程序，也可以让VBA调用用Python写的程序。

 

xlwings安装和导入

本文python版本为3.6，系统环境为windows，在jupyter notebook中进行实验。
xlwings库使用pip安装：

pip install xlwings

xlwings导入：

import xlwings as xw

xlwings实操

• 建立excel表连接

wb = xw.Book("e:\example.xlsx")

• 实例化工作表对象

sht = wb.sheets["sheet1"]

• 返回工作表绝对路径

wb.fullname

• 返回工作簿的名字

sht.name

• 在单元格中写入数据

sht.range('A1').value = "xlwings"

• 读取单元格内容

sht.range('A1').value

• 清除单元格内容和格式

sht.range('A1').clear()

• 获取单元格的列标

sht.range('A1').column

• 获取单元格的行标

sht.range('A1').row

• 获取单元格的行高

sht.range('A1').row_height

• 获取单元格的列宽

sht.range('A1').column_width

• 列宽自适应

sht.range('A1').columns.autofit()

• 行高自适应

sht.range('A1').rows.autofit()

• 给单元格上背景色，传入RGB值

sht.range('A1').color = (34,139,34)

• 获取单元格颜色，RGB值

sht.range('A1').color

• 清除单元格颜色

sht.range('A1').color = None

• 输入公式，相应单元格会出现计算结果

sht.range('A1').formula='=SUM(B6:B7)'

• 获取单元格公式

sht.range('A1').formula_array

• 在单元格中写入批量数据，只需要指定其实单元格位置即可

sht.range('A2').value = [['Foo 1', 'Foo 2', 'Foo 3'], [10.0, 20.0, 30.0]]

• 读取表中批量数据，使用expand()方法

sht.range('A2').expand().value

• 其实你也可以不指定工作表的地址，直接与电脑里的活动表格进行交互

# 写入
xw.Range("E1").value = "xlwings"# 读取
xw.Range("E1").value

xlwings与numpy、pandas、matplotlib互动

• 支持写入numpy array数据类型

import numpy as np
np_data = np.array((1,2,3))
sht.range('F1').value = np_data

• 支持将pandas DataFrame数据类型写入excel

import pandas as pd
df = pd.DataFrame([[1,2], [3,4]], columns=['a', 'b'])
sht.range('A5').value = df

• 将数据读取，输出类型为DataFrame

sht.range('A5').options(pd.DataFrame,expand='table').value

• 将matplotlib图表写入到excel表格里

import matplotlib.pyplot as plt
%matplotlib inline
fig = plt.figure()
plt.plot([1, 2, 3, 4, 5])
sht.pictures.add(fig, name='MyPlot', update=True)

xlwings与VBA互相调用

xlwings与VBA的配合非常完美，你可以在python中调用VBA，也可以在VBA中使用python编程，这些通过xlwings都可以巧妙实现。这里不对该内容做详细讲解，感兴趣的童鞋可以去xlwings官网学习。

——Xlwings

1 Windows，Mac都能用 （Excel，WPS也都能用）

2 功能齐全，支持Excel的新建、打开、修改、保存（pandas和xlsxwriter去不能全做到）

3 语法简单（用过一次后我就记住了）

4 可以调用VBA，有丰富的API

 

先说基本操作：

引入库

import xlwings as xw 

打开Excel程序，默认设置：程序可见，只打开不新建工作薄

app = xw.App(visible=True,add_book=False)
#新建工作簿 (如果不接下一条代码的话，Excel只会一闪而过，卖个萌就走了）
wb = app.books.add()

打开已有工作簿（支持绝对路径和相对路径）

wb = app.books.open('example.xlsx')
#练习的时候建议直接用下面这条
#wb = xw.Book('example.xlsx')
#这样的话就不会频繁打开新的Excel

保存工作簿

wb.save('example.xlsx')

退出工作簿（可省略）

wb.close()

退出Excel

app.quit()

引用Excel工作表，单元格

引用工作表

sht = wb.sheets[0]
#sht = wb.sheets[第一个sheet名]

引用单元格

rng = sht.range('a1')
#rng = sht['a1']
#rng = sht[0,0] 第一行的第一列即a1,相当于pandas的切片

引用区域

rng = sht.range('a1:a5')
#rng = sht['a1:a5']
#rng = sht[:5,0]

重头戏：写入数据
(xlwings多个单元格的写入大多是以表格形式)

选择起始单元格A1,写入字符串‘Hello’

sht.range('a1').value = 'Hello'

默认按行插入：A1:D4分别写入1,2,3,4

sht.range('a1').value = [1,2,3,4]

等同于

sht.range('a1:d4').value = [1,2,3,4]

按列插入： A2:A5分别写入5,6,7,8

你可能会想

*sht.range('a2:a5').value = [5,6,7,8]

但是你会发现xlwings还是会按行处理的，上面一行等同于

*sht.range('a2').value = [5,6,7,8]

正确语法:

sht.range('a2').options(transpose=True).value = [5,6,7,8]

既然默认的是按行写入，我们就把它倒过来嘛（transpose），单词要打对，如果你打错单词，它不会报错，而会按默认的行来写入（别问我怎么知道的）

我们输入信息的时候往往不只是写入一行或一列，
多行输入就要用二维列表了：

sht.range('a6').expand('table').value = [['a','b','c'],['d','e','f'],['g','h','i']]

说完了写入就该讲读取了，记住了写入，读取就简单了

读取A1:D4（直接填入单元格范围就行了）

print(sht.range('a1:d4').value)

返回的值是列表形式，多行多列为二维列表，但有一点要注意，返回的数值默认是浮点数

a = sht.range('a1:d1').value
print(a)
for i in a:
  print(i)
  print(type(i))

读取excel的第一列怎么做？

*a = sht.range('a:a').value
print(len(a))

你将会得到一个1048576个元素的列表，也就是空值也包含进去了，所以这种方法不行

思路：先计算单元格的行数(前提是连续的单元格)

rng = sht.range('a1').expand('table')
nrows = rng.rows.count

接着就可以按准确范围读取了

a = sht.range(f'a1:a{nrows}').value

同理选取一行的数据也一样

ncols = rng.columns.count
#用切片
fst_col = sht[0,:ncols].value