python安装excel库_Python最好的Excel第三方库

简介

xlwings 是一款操作 Excel 的开源库，其宗旨——让Excel飞起来！

用 Python 控制 Excel，就像 VBA 的脚本、宏、函数一样。

以下公司也在用：

accenture

NOKIA

AQR

Columbia Business

安装

pip install xlwings

初试

import xlwings as xw

wb = xw.Book() # 创建新的工作簿

sht = wb.sheets['Sheet1'] # 实例化工作表

sht.range('A1').value = 'Hello World!' # 写入

print(sht.range('A1').value) # 读取

wb.save('test.xlsx') # 保存

wb.close()

工作簿和工作表

连接工作簿(workbook)

import xlwings as xw

wb = xw.Book() # 创建新的工作簿

# wb = xw.Book('test.xlsx') # 连接当前路径下的工作簿

# wb = xw.Book(r'C:\test.xlsx') # Windows下绝对路径连接

连接工作表(sheet)

import xlwings as xw

wb = xw.Book()

sht = wb.sheets['Sheet1'] # 连接工作表

读写数据

指定位置是数据起始点，一行一行读写

写入单元格

import xlwings as xw

wb = xw.Book()

sht = wb.sheets['Sheet1'] # 连接工作表

sht.range('A1').value = 'Foo 1'

print(sht.range('A1').value)

# Foo 1

批量写入

import xlwings as xw

wb = xw.Book()

sht = wb.sheets['Sheet1']

sht.range('A1').value = [['Foo 1', 'Foo 2', 'Foo 3'], [10.0, 20.0, 30.0]] # 批量写入

print(sht.range('A1').expand().value) # 批量读取

wb.save('test.xlsx') # 保存

wb.close()

效果

数据兼容

强大的转换器能处理绝大多数数据类型，如 字典、Numpy Array 和 Pandas DataFrame

字典

import numpy as np

import xlwings as xw

wb = xw.Book()

sht = wb.sheets['Sheet1']

d = {'a': 1, 'b': 2}

sht.range('A1').value = d

print(sht.range('A1').options(np.array, expand='table').value)

# [['a' '1.0']

# ['b' '2.0']]

Numpy数组

import numpy as np

import xlwings as xw

wb = xw.Book()

sht = wb.sheets['Sheet1']

sht.range('A1').value = np.eye(3) # 对角矩阵

print(sht.range('A1').options(np.array, expand='table').value)

# [[1. 0. 0.]

# [0. 1. 0.]

# [0. 0. 1.]]

Pandas数据表

import pandas as pd

import xlwings as xw

wb = xw.Book()

sht = wb.sheets['Sheet1']

df = pd.DataFrame([[1, 2], [3, 4]], columns=['a', 'b'])

sht.range('A1').value = df # 写入DataFrame

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

# a b

# 0.0 1.0 2.0

# 1.0 3.0 4.0

插入图片

Matplotlib图像

import xlwings as xw

import matplotlib.pyplot as plt

wb = xw.Book()

sht = wb.sheets['Sheet1']

fig = plt.figure()

plt.plot([1, 2, 3, 4, 5])

sht.pictures.add(fig, name='MyPlot', update=True)

wb.save('test.xlsx')

wb.close()

效果

指定图片

import os

import xlwings as xw

wb = xw.Book()

sht = wb.sheets['Sheet1']

# sht.pictures.add('1.jpg') # 使用相对路径可能报错

sht.pictures.add(os.path.join(os.getcwd(), '1.jpg'))

wb.save('test.xlsx')

wb.close()

居中插入

import os

import xlwings as xw

wb = xw.Book()

sht = wb.sheets['Sheet1']

rng = sht.range('A1')

fileName = os.path.join(os.getcwd(), '1.jpg')

width, height = 10, 10 # 指定图片大小

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)

智能居中插入

效果

宏

1. 设置 Excel 信任对 VBA 工程对象模型的访问：

文件 → 选项 → 信任中心 → 信任中心设置 → 信任对 VBA 工程对象模型的访问

2. 安装加载项

xlwings addin install

3. 命令行客户端

快速构建项目

xlwings quickstart project

4. 打开project.xlsm

启用内容

5. 打开Visual Basic编辑器

快捷键： Alt+F11

Excel 的自定义功能区勾选上开发工具

点击 Visual Basic

出现 xlwings 则说明加载项安装成功

设置引用：工具 → 引用 → 勾选 xlwings

6. 运行宏

此宏运行的逻辑与同名.py的一致

import xlwings as xw

@xw.sub

def main():

wb = xw.Book.caller()

sheet = wb.sheets[0]

if sheet["A1"].value == "Hello xlwings!":

sheet["A1"].value = "Bye xlwings!"

else:

sheet["A1"].value = "Hello xlwings!"

7. 运行宏的其他两种方式

通过 Visual Basic

通过表单控件

UDFs: 用户定义函数

仅支持Windows

1. 命令行客户端

快速构建项目

xlwings quickstart hello

2. 打开hello.xlsm

启用内容

3. 打开Visual Basic编辑器

快捷键： Alt+F11

设置引用：工具 → 引用 → 勾选 xlwings

4. 运行

用户设置

Windows 用户的配置文件位于：%USERPROFILE%/.xlwings 的 xlwings.conf

遇到的坑

报错 pywintypes.com_error: (-2147023266, '这个类型的数据不受支持。', None, None)

解决方案：打开一个Excel

Excel 行高列宽单位不一致

Excel 行高 1单位 = 1像素

Excel 列宽 1单位 = 1个字符宽度(默认为6.107像素)

Excel 点击 Import Functions 报错 could not creat python process

确保 Error in parsing PYTHONPATH · Issue #574 中的步骤正确

其他

关闭Excel显示

封装

参考文献