gitsilence 的个人博客
Coding...
目录
python读取写入Excel
python读取写入Excel
读Excel
使用xlrd库
安装第三方库
pip install xlrd
xlrd 库的坑
示例代码
import xlrd
if __name__ == '__main__':
book = xlrd.open_workbook("./2021 Draft.xlsx")
print(book)
运行，报错信息
xlrd.biffh.XLRDError: Excel xlsx file; not supported
目前最新版的 xlrd 不支持读取 xlsx 格式
所以可以安装低版本的 xlrd 模块
卸载当前xlrd：pip uninstall xlrd
pip install xlrd==1.2.0
然后再次运行示例代码
File "/opt/homebrew/lib/python3.9/site-packages/xlrd/xlsx.py", line 266, in process_stream
for elem in self.tree.iter() if Element_has_iter else self.tree.getiterator():
AttributeError: 'ElementTree' object has no attribute 'getiterator'
又碰到个错误；因为当前Python 版本是 3.9.0 删除了getiterator
解决办法：修改源码
从报错信息可以看到是在xlsx.py 266行，我们找到那一行，将 getiterator 改成 iter
改完后再次运行代码
File "/opt/homebrew/lib/python3.9/site-packages/xlrd/xlsx.py", line 312, in process_coreprops
for elem in self.tree.iter() if Element_has_iter else self.tree.getiterator():
AttributeError: 'ElementTree' object has no attribute 'getiterator'
错误和上面一样，继续改；
改完后，再次运行，没有报错，则运行成功。
了解xlrd的常用方法
# 导入 模块 1.2.0
import xlrd
# 读取excel文件
book = xlrd.open_workbook('/Users/gitsilence/PycharmProjects/pythonProject/xlrd-study/2021 Draft.xlsx')
# 获取所有的sheet name
book.sheet_names()
['2021 Draft']
# 获取 所有的 sheet 对象, 返回一个list列表
book.sheets()
[<xlrd.sheet.Sheet at 0x1067bb400>]
# 根据 sheet name 获取 sheet对象
sheet = book.sheet_by_name('2021 Draft')
sheet
<xlrd.sheet.Sheet at 0x1067bb400>
# 也可以根据下标 获取
sheet = book.sheets()[0]
sheet
<xlrd.sheet.Sheet at 0x1067bb400>
# 获取 行数 和 列数
rows = sheet.nrows
print("rows: ", rows)
cols = sheet.ncols
print("cols: ", cols)
rows: 16
cols: 22
"""
获取指定行、指定范围列 的内容，结果返回list
"""
# 第一个参数：索引行 从0开始 代表第一行
# 第二个参数：索引列 开始索引，从0开始
# 第三个参数：索引列 结束索引 最大值为 (列数 - 1)
sheet.row_values(0, 0, 10) # 查询第一行，从第一列到第十列的内容 0 <= col < 10 在闭右开
['姓', '名字', '日期', 'dob', '年龄', '身高', 'Wingspan', '体重', 'GP', 'MP']
"""
获取指定列、指定范围行 的内容，结果返回list
"""
# 解释同上一个
sheet.col_values(0, 0, 10) # 查询第0列，从第一行到到第十行的内容 0 <= row < 10 左闭右开
['姓',
'Cunningham ',
'Mobley',
'Suggs',
'Green',
'Kuminga',
'Johnson',
'Mitchell',
'Wagner ',
'Sengun']
# 遍历 excel 文件的全部内容
for i in range(sheet.nrows):
print(sheet.row_values(i, 0, sheet.ncols))
['姓', '名字', '日期', 'dob', '年龄', '身高', 'Wingspan', '体重', 'GP', 'MP', 'TS%', 'eFG%', '3PAr', 'FTr', 'ORB%', 'DRB%', 'TRB%', 'AST%', 'STL%', 'BLK%', 'TOV%', 'USG%']
['Cunningham ', 'Cade', 39162.0, '9/25/01', 19.78, 79.0, 84.25, 220.0, 27.0, 956.0, 0.574, 0.515, 0.388, 0.39, 2.3, 16.3, 9.6, 20.4, 2.5, 2.3, 18.7, 29.1]
['Mobley', 'Evan', 39162.0, '6/18/01', 20.05, 84.0, 88.0, 215.0, 33.0, 1120.0, 0.624, 0.595, 0.117, 0.566, 9.6, 18.9, 14.5, 14.1, 1.4, 8.8, 14.6, 22.8]
['Suggs', 'Jalen', 39162.0, '6/23/01', 20.04, 76.0, 77.5, 205.0, 30.0, 868.0, 0.595, 0.56, 0.338, 0.37, 2.7, 18.0, 11.0, 23.7, 3.5, 1.1, 19.5, 24.8]
['Green', 'Jalen', 39162.0, 37501.0, 19.41, 78.0, 79.5, 180.0, 15.0, 480.0, 0.613, 0.537, 0.417, 0.172, 1.9, 11.6, 6.9, 13.5, 2.3, 0.8, 15.4, 23.4]
['Kuminga', 'Jonathan', 39162.0, 40331.0, 18.75, 79.0, 83.0, 210.0, 13.0, 426.0, 0.497, 0.43, 0.349, 0.258, 4.0, 19.2, 11.9, 12.2, 1.5, 2.4, 14.1, 24.5]
['Johnson', 'Keon', 39162.0, 37896.0, 19.33, 75.5, 79.25, 185.0, 27.0, 688.0, 0.519, 0.476, 0.194, 0.409, 4.8, 11.0, 7.9, 20.7, 2.5, 2.0, 19.4, 26.8]
['Mitchell', 'Davion', 39162.0, '9/5/98', 22.84, 72.0, 76.25, 202.0, 30.0, 991.0, 0.619, 0.613, 0.456, 0.207, 1.7, 8.0, 4.8, 27.7, 3.3, 1.2, 17.7, 20.3]
['Wagner ', 'Franz', 39162.0, '8/27/01', 19.86, 80.0, 82.0, 205.0, 28.0, 887.0, 0.587, 0.544, 0.392, 0.304, 2.9, 19.6, 12.1, 17.4, 2.3, 3.2, 10.8, 19.7]
['Sengun', 'Alperen', 39162.0, '7/25/02', 18.95, 82.0, 84.0, 243.0, 37.0, 1043.0, 0.697, 0.641, 0.088, 0.612, 17.5, 23.4, 20.5, 18.2, 2.6, 5.9, 14.6, 26.7]
['Jones', 'Kai', 39162.0, '1/18/01', 20.47, 82.0, 85.75, 221.0, 26.0, 594.0, 0.646, 0.626, 0.238, 0.517, 10.0, 13.5, 11.8, 5.4, 2.1, 4.2, 16.8, 18.0]
['Barnes', 'Scottie', 39162.0, 39448.0, 19.93, 79.0, 86.75, 225.0, 24.0, 595.0, 0.548, 0.531, 0.205, 0.338, 7.4, 11.1, 9.3, 31.6, 3.4, 2.1, 20.7, 23.7]
['Johnson', 'Jalen ', 39162.0, '12/18/01', 19.55, 79.75, 84.25, 210.0, 13.0, 278.0, 0.575, 0.56, 0.165, 0.349, 8.3, 25.2, 16.6, 20.5, 3.1, 6.0, 20.6, 28.6]
['Kispert', 'Corey', 39162.0, '3/3/99', 22.35, 78.0, 79.0, 224.0, 32.0, 1019.0, 0.674, 0.644, 0.524, 0.248, 3.8, 13.9, 9.3, 9.0, 1.5, 1.4, 8.7, 22.7]
['Giddey', 'Josh', 39162.0, 40453.0, 18.74, 80.0, 79.5, 205.0, 28.0, 900.0, 0.51, 0.479, 0.372, 0.256, 4.0, 20.8, 12.8, 36.3, 1.8, 1.4, 10.8, 19.6]
['Moody', 'Moses', 39162.0, '5/31/02', 19.1, 78.0, 84.75, 211.0, 32.0, 1082.0, 0.568, 0.503, 0.42, 0.482, 6.3, 11.8, 9.0, 8.2, 1.6, 2.0, 9.9, 22.3]
"""
获取指定 单元格的数据
"""
sheet.cell_value(0, 1) # 获取第一列、第二列 单元格对应的内容
'名字'
# 获取指定行
sheet.row(0) # 获取第一行的数据
[text:'姓',
text:'名字',
text:'日期',
text:'dob',
text:'年龄',
text:'身高',
text:'Wingspan',
text:'体重',
text:'GP',
text:'MP',
text:'TS%',
text:'eFG%',
text:'3PAr',
text:'FTr',
text:'ORB%',
text:'DRB%',
text:'TRB%',
text:'AST%',
text:'STL%',
text:'BLK%',
text:'TOV%',
text:'USG%']
# 遍历第一行的数据
for ct in sheet.row(0):
print(ct.value)
姓
名字
日期
dob
年龄
身高
Wingspan
体重
GP
MP
TS%
eFG%
3PAr
FTr
ORB%
DRB%
TRB%
AST%
STL%
BLK%
TOV%
USG%
# 遍历 所有数据，另一种方式
for i in range(sheet.nrows):
for col in sheet.row(i):
print(col.value, end="|")
print()
姓|名字|日期|dob|年龄|身高|Wingspan|体重|GP|MP|TS%|eFG%|3PAr|FTr|ORB%|DRB%|TRB%|AST%|STL%|BLK%|TOV%|USG%|
Cunningham |Cade|39162.0|9/25/01|19.78|79.0|84.25|220.0|27.0|956.0|0.574|0.515|0.388|0.39|2.3|16.3|9.6|20.4|2.5|2.3|18.7|29.1|
Mobley|Evan|39162.0|6/18/01|20.05|84.0|88.0|215.0|33.0|1120.0|0.624|0.595|0.117|0.566|9.6|18.9|14.5|14.1|1.4|8.8|14.6|22.8|
Suggs|Jalen|39162.0|6/23/01|20.04|76.0|77.5|205.0|30.0|868.0|0.595|0.56|0.338|0.37|2.7|18.0|11.0|23.7|3.5|1.1|19.5|24.8|
Green|Jalen|39162.0|37501.0|19.41|78.0|79.5|180.0|15.0|480.0|0.613|0.537|0.417|0.172|1.9|11.6|6.9|13.5|2.3|0.8|15.4|23.4|
Kuminga|Jonathan|39162.0|40331.0|18.75|79.0|83.0|210.0|13.0|426.0|0.497|0.43|0.349|0.258|4.0|19.2|11.9|12.2|1.5|2.4|14.1|24.5|
Johnson|Keon|39162.0|37896.0|19.33|75.5|79.25|185.0|27.0|688.0|0.519|0.476|0.194|0.409|4.8|11.0|7.9|20.7|2.5|2.0|19.4|26.8|
Mitchell|Davion|39162.0|9/5/98|22.84|72.0|76.25|202.0|30.0|991.0|0.619|0.613|0.456|0.207|1.7|8.0|4.8|27.7|3.3|1.2|17.7|20.3|
Wagner |Franz|39162.0|8/27/01|19.86|80.0|82.0|205.0|28.0|887.0|0.587|0.544|0.392|0.304|2.9|19.6|12.1|17.4|2.3|3.2|10.8|19.7|
Sengun|Alperen|39162.0|7/25/02|18.95|82.0|84.0|243.0|37.0|1043.0|0.697|0.641|0.088|0.612|17.5|23.4|20.5|18.2|2.6|5.9|14.6|26.7|
Jones|Kai|39162.0|1/18/01|20.47|82.0|85.75|221.0|26.0|594.0|0.646|0.626|0.238|0.517|10.0|13.5|11.8|5.4|2.1|4.2|16.8|18.0|
Barnes|Scottie|39162.0|39448.0|19.93|79.0|86.75|225.0|24.0|595.0|0.548|0.531|0.205|0.338|7.4|11.1|9.3|31.6|3.4|2.1|20.7|23.7|
Johnson|Jalen |39162.0|12/18/01|19.55|79.75|84.25|210.0|13.0|278.0|0.575|0.56|0.165|0.349|8.3|25.2|16.6|20.5|3.1|6.0|20.6|28.6|
Kispert|Corey|39162.0|3/3/99|22.35|78.0|79.0|224.0|32.0|1019.0|0.674|0.644|0.524|0.248|3.8|13.9|9.3|9.0|1.5|1.4|8.7|22.7|
Giddey|Josh|39162.0|40453.0|18.74|80.0|79.5|205.0|28.0|900.0|0.51|0.479|0.372|0.256|4.0|20.8|12.8|36.3|1.8|1.4|10.8|19.6|
Moody|Moses|39162.0|5/31/02|19.1|78.0|84.75|211.0|32.0|1082.0|0.568|0.503|0.42|0.482|6.3|11.8|9.0|8.2|1.6|2.0|9.9|22.3|
写入Excel
xlwt库的使用
# 导入依赖库
import xlwt
book = xlwt.Workbook(encoding='ascii') # 创建新的excel，建议使用ascii编码
book
<xlwt.Workbook.Workbook at 0x10603f490>
# 创建新的表单 test
sheet = book.add_sheet('test')
# 第一行 第一列 写入内容 'Hello'
sheet.write(0, 0, label='Hello')
sheet.write(0, 1, label='World')
sheet.write(1, 0, label='你好')
# 保存 文件
book.save('/Users/gitsilence/Desktop/test.xls')
修改Excel
# 修改Excel 表格
import xlrd
from xlutils.copy import copy
rb = xlrd.open_workbook('/Users/gitsilence/Desktop/test.xls')
wb = copy(rb)
ws = wb.get_sheet(0)
ws.write(0, 0, 'changed')
ws.write(2, 0, 'add 好')
wb.save('/Users/gitsilence/Desktop/test_update.xls')