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") 二、进阶

待完成部分

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

Excel 神器 —— OpenPyXl - 知乎