Python操作Excel表格

本文介绍如何通过轻量级、零依赖（仅使用标准库）的 pylightxl 库操作Excel表格。

官网：Welcome to pylightxl documentation — pylightxl 2019 documentation

目录

一、入门

1. 读写CSV文件

2. 读Excel文件

3. 获取工作表和单元格数据

3.1 通过单元格地址 

3.2 通过单元格索引

3.3 通过单元格范围

3.4 获取整个行或列

3.5 通过行/列进行迭代

3.6 更新单元格的值

3.7 更新单元格公式

3.8 获取命名范围

3.9 基于键值获取行/列

4. 读取半结构化的数据

5. 将 pylightxl.Database 写入excel 文件

6. 将 Python 数据写入新的 Excel 文件

二、进阶

---

一、入门

1. 读写CSV文件

import pylightxl as xl
 
# 设置CSV文件路径
# 设置CSV文件的分隔符
# 设置默认工作表，将读入的CSV数据写入其中
db = xl.readcsv(fn='input.csv', delimiter='/', ws='sh2')
 
# 然后对其进行修改
 
 
# 现在把它写成csv文件；或者写成excel文件，见xl.writexl()。
xl.writecsv(db=db, fn='new.csv', ws=('sh2'), delimiter=',')

2. 读Excel文件

import pylightxl as xl
 
# readxl返回一个持有所有工作表及其数据的pylightxl数据库
db = xl.readxl(fn='folder1/folder2/excelfile.xlsx')
 
# pylightxl也支持pathlib
my_pathlib = pathlib.Path('folder1/folder2/excelfile.xlsx')
db = xl.readxl(my_pathlib)
 
# pylightxl也支持django用户的类文件对象。
with open('excelfile.xlsx', 'rb') as f:
    db = xl.readxl(f)
 
# 仅读取选择的工作表
db = xl.readxl(fn='folder1/folder2/excelfile.xlsx', ws=('Sheet1','Sheet3'))
 
# 返回所有工作表的名字
db.ws_names
>>> ['Sheet1', 'Sheet3']

3. 获取工作表和单元格数据

假设表格的数据内容如下图所示：

3.1 通过单元格地址 

# 访问指定单元格的数据
db.ws(ws='Sheet1').address(address='A1')
>>> 10
# 访问该单元格的公式（如果有的话）
db.ws(ws='Sheet1').address(address='A1', output='f')
>>> ''
# 访问该单元格的注释（如果有的话）
db.ws(ws='Sheet1').address(address='A1', output='c')
>>> 'this is a comment on cell A1!'
# 注意：空单元格的索引将返回一个空字符串。
db.ws(ws='Sheet1').address(address='A100')
>>> ''
# 默认的空值可以覆盖每个工作表。
db.ws(ws='Sheet1').set_emptycell(val=0)
db.ws(ws='Sheet1').address(address='A100')
>>> 0

3.2 通过单元格索引

# 访问指定单元格的数据
db.ws(ws='Sheet1').index(row=1, col=2)
>>> 20
# 访问该单元格的公式（如果有的话）
db.ws(ws='Sheet1').index(row=1, col=2, output='f')
>>> '=A1+10'
# 注意：空单元格的索引将返回一个空字符串。
db.ws(ws='Sheet1').index(row=100, col=1)
>>> ''
# 默认的空值可以覆盖每个工作表。
db.ws(ws='Sheet1').set_emptycell(val=0)
db.ws(ws='Sheet1').index(row=100, col=1)
>>> 0

3.3 通过单元格范围

db.ws(ws='Sheet1').range(address='A1')
>>> 10
# 获取指定范围的数据
db.ws(ws='Sheet1').range(address='A1:C2')
>>> [[10, 20, ''], ['', 30, 40]]
# 获取指定范围的公式
db.ws(ws='Sheet1').range(address='A1:B1', output='f')
>>> [['=10', '=A1+10']]
# 用指定值更新指定范围
db.ws(ws='Sheet1').update_range(address='A1:B1', val=10)

3.4 获取整个行或列

db.ws(ws='Sheet1').row(row=1)
>>> [10,20,'']
 
db.ws(ws='Sheet1').col(col=1)
>>> [10,'']

3.5 通过行/列进行迭代

for row in db.ws(ws='Sheet1').rows:
    print(row)
 
>>> [10,20,'']
>>> ['',30,40]
 
for col in db.ws(ws='Sheet1').cols:
    print(col)
 
>>> [10,'']
>>> [20,30]
>>> ['',40]

3.6 更新单元格的值

db.ws(ws='Sheet1').address(address='A1')
>>> 10
db.ws(ws='Sheet1').update_address(address='A1', val=100)
db.ws(ws='Sheet1').address(address='A1')
>>> 100
 
db.ws(ws='Sheet1').update_index(row=1, col=1, val=10)
db.ws(ws='Sheet1').index(row=1, col=1)
>>> 10

3.7 更新单元格公式

db.ws(ws='Sheet1').update_address(address='A1', val='=B1+100')
db.ws(ws='Sheet1').update_index(row=1, col=1, val='=B1+100')

3.8 获取命名范围

# 定义一个范围
db.add_nr(name='Table1', ws='Sheet1', address='A1:B2')
# 获取命名范围的数据
db.nr(name='Table1')
>>> [[10, 20], ['', 30]]
# 获取命名范围的位置
db.nr_loc(name='Table1')
>>> ['Sheet1','A1:B2']
# 更新命名范围的值
db.update_nr(name='Table1', val=10)
# 查看所有的命名范围
db.nr_names
>>> {'Table1': 'Sheet1!A1:B2'}
# 删除一个指定的范围
db.remove_nr(name='Table1')

3.9 基于键值获取行/列

# 假设我们想返回 “行=1, 单元格值=20” 的那一列
db.ws(ws='Sheet1').keycol(key=20, keyindex=1)
>>> [20,30]
 
# 我们还可以指定一个自定义的键索引（不仅仅是row=1），注意我们现在是根据row=2来匹配的。
db.ws(ws='Sheet1').keycol(key=30, keyindex=2)
>>> [20,30]
 
# 同样，对于keyindex=1的keyrow也是如此（寻找col=1中的匹配项）。
db.ws(ws='Sheet1').keyrow(key='', keyindex=1)
>>> ['',30,40]

4. 读取半结构化的数据

import pylightxl
db = pylightxl.readxl(fn='Book1.xlsx')
 
# 要求半结构化数据（SSD）输出
ssd = db.ws(ws='Sheet1').ssd(keycols="KEYCOLS", keyrows="KEYROWS")
 
ssd[0]
>>> {'keyrows': ['r1', 'r2', 'r3'], 'keycols': ['c1', 'c2', 'c3'], 'data': [[1, 2, 3], [4, '', 6], [7, 8, 9]]}
ssd[1]
>>> {'keyrows': ['rr1', 'rr2', 'rr3', 'rr4'], 'keycols': ['cc1', 'cc2', 'cc3'], 'data': [[10, 20, 30], [40, 50, 60], [70, 80, 90], [100, 110, 120]]}

5. 将 pylightxl.Database 写入excel 文件

Pylightxl支持写excel而不需要在机器上安装excel。然而，它也不是没有限制。写入器只支持单元格数据的写入（即：不支持图形、格式化、图像、宏等），仅仅是单元格中的字符串/数字/方程。

import pylightxl as xl
 
# 读取现有工作表并改变其单元格的值（与上述工作表相同）。
db = xl.readxl(fn='excelfile.xlsx')
# 覆盖现有的数字值
db.ws(ws='Sheet1').index(row=1, col=1)
>>> 10
db.ws(ws='Sheet1').update_index(row=1, col=1, val=100)
db.ws(ws='Sheet1').index(row=1, col=1)
>>> 100
# 写入文本
db.ws(ws='Sheet1').update_index(row=1, col=2, val='twenty')
# 写入方程
db.ws(ws='Sheet1').update_address(address='A3', val='=A1')
 
# 将db写入本地Excel文件
xl.writexl(db=db, fn='updated.xlsx')

6. 将 Python 数据写入新的 Excel 文件

import pylightxl as xl
 
# 以这个列表为例，作为我们的输入数据，我们想把它放在A列中
mydata = [10,20,30,40]
 
# 创建一个空白数据库
db = xl.Database()
 
# 在数据库中添加一个空白工作表
db.add_ws(ws="Sheet1")
 
# 循环将我们的数据添加到工作表中
for row_id, data in enumerate(mydata, start=1)
    db.ws(ws="Sheet1").update_index(row=row_id, col=1, val=data)
 
# 将db写入新的本地Excel文件
xl.writexl(db=db, fn="output.xlsx")

二、进阶

待完成部分

三、其他读写Excel的库

除了pylightxl，还有其他很多库可以用于读写Excel文件，例如：xlrd，xlwd，OpenPyXl等

xlrd与xlwt的使用教程_QQVQQ...的博客-CSDN博客

教程 — openpyxl 3.0.7 文档

openpyxl使用教程_Cold autumn的博客

Python 操作 Excel 库 xlrd与xlwt 常用操作详解

Excel 神器 —— OpenPyXl - 知乎

Python操作Excel表格的模块xlrd的简单介绍