Python操作Excel表格

傷城~ 2024-03-31 15:43 72阅读 0赞

> 本文介绍如何通过轻量级、零依赖（仅使用标准库）的 pylightxl 库操作Excel表格。
> 
> 官网：[Welcome to pylightxl documentation — pylightxl 2019 documentation][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. 获取工作表和单元格数据 ###

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

![d6e1e8f183e0473a86f248d3909bc749.png][]

#### 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. 读取半结构化的数据 ###

![2833c9d7d9e894fd89b276eee6a7d7be.png][]

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博客][xlrd_xlwt_QQVQQ..._-CSDN]

[教程 — openpyxl 3.0.7 文档][_ openpyxl 3.0.7]

[openpyxl使用教程\_Cold autumn的博客][openpyxl_Cold autumn]

[Python 操作 Excel 库 xlrd与xlwt 常用操作详解][Python _ Excel _ xlrd_xlwt]

[Excel 神器 —— OpenPyXl - 知乎][Excel _ _ OpenPyXl -]

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

[Welcome to pylightxl documentation _ pylightxl 2019 documentation]: https://pylightxl.readthedocs.io/en/latest/
[d6e1e8f183e0473a86f248d3909bc749.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/0fb9eb5aed9d4bbc87fd4419a5a4e2b6.png
[2833c9d7d9e894fd89b276eee6a7d7be.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/d2d210c3d3e1417ebb6106f0f8312527.png
[xlrd_xlwt_QQVQQ..._-CSDN]: https://blog.csdn.net/hfy1237/article/details/129463891
[_ openpyxl 3.0.7]: https://openpyxl-chinese-docs.readthedocs.io/zh_CN/latest/tutorial.html
[openpyxl_Cold autumn]: https://blog.csdn.net/qq_43428376/article/details/120307771?ops_request_misc=%257B%2522request%255Fid%2522%253A%2522167048577416782395327705%2522%252C%2522scm%2522%253A%252220140713.130102334..%2522%257D&request_id=167048577416782395327705&biz_id=0&utm_medium=distribute.pc_search_result.none-task-blog-2~all~sobaiduend~default-2-120307771-null-null.142%5Ev68%5Econtrol,201%5Ev4%5Eadd_ask,213%5Ev2%5Et3_esquery_v3&utm_term=openpyxl%E4%BD%BF%E7%94%A8%E6%95%99%E7%A8%8B&spm=1018.2226.3001.4187
[Python _ Excel _ xlrd_xlwt]: https://cloud.tencent.com/developer/article/1839447
[Excel _ _ OpenPyXl -]: https://zhuanlan.zhihu.com/p/409954748
[Python_Excel_xlrd]: https://blog.csdn.net/zh6526157/article/details/121947764?ops_request_misc=%257B%2522request%255Fid%2522%253A%2522167767180916800227430239%2522%252C%2522scm%2522%253A%252220140713.130102334..%2522%257D&request_id=167767180916800227430239&biz_id=0&utm_medium=distribute.pc_search_result.none-task-blog-2~all~top_positive~default-2-121947764-null-null.142%5Ev73%5Econtrol,201%5Ev4%5Eadd_ask,239%5Ev2%5Einsert_chatgpt&utm_term=xlrd&spm=1018.2226.3001.4187