按列读取数据
from openpyxl import Workbook
# 创建一个新的Excel工作簿
wb = Workbook()
# 获取第一个工作表
worksheet = wb.active
# 多行数据
data = [
['Bob', 25, 'Male'],
['Charlie', 35, 'Male'],
['Alice', 30, 'Engineer']
]
# 逐行添加数据
for row in data:
worksheet.append(row)
# A 列
cells = worksheet["A"]
# 获取第一列所有单元格的内容
for cell in cells:
print(cell.value)
# 输出: Bob
# 输出: Charlie
# 输出: Alice
# 打印A列所有内容
for cols in worksheet.iter_cols(min_col=1, max_col=1, values_only=True):
print(cols) # 输出: ('Bob', 'Charlie', 'Alice')
# 按A列的索引读取
for cols in worksheet.iter_cols(min_col=1, max_col=1, values_only=True):
for col in cols:
print(col)
# 输出: Bob
# 输出: Charlie
# 输出: Alice
# 读取多列内容
for cols in worksheet.iter_cols(min_col=1, max_col=3, values_only=True):
print(cols)
# 读取多列内容
for cols in worksheet["A:C"]:
print(cols.values)
# 保存工作簿到指定的文件名
wb.save("execl_test.xlsx")
# 关闭工作簿
wb.close()
按行读取数据
from openpyxl import Workbook
# 创建一个新的Excel工作簿
wb = Workbook()
# 获取第一个工作表
worksheet = wb.active
# 多行数据
data = [
['Bob', 25, 'Male'],
['Charlie', 35, 'Male'],
['Alice', 30, 'Engineer']
]
# 逐行添加数据
for row in data:
worksheet.append(row)
# 按行打印整个工作表内容
for row in worksheet.iter_rows(values_only=True):
print(type(row), row)
# 输出: <class 'tuple'> ('Bob', 25, 'Male')
# 输出: <class 'tuple'> ('Charlie', 35, 'Male')
# 输出: <class 'tuple'> ('Alice', 30, 'Engineer')
# 按行打印整个第一行的内容
for row in worksheet.iter_rows(min_row=1,max_row=1,values_only=True):
print(type(row), row)
# 输出: <class 'tuple'> ('Bob', 25, 'Male')
# 按行获取每个单元格的内容
for row in worksheet.iter_rows(min_row=1,max_row=1,values_only=True):
for cell in row:
print(cell)
# 输出: Bob
# 输出: 25
# 输出: Male
# 打印第一行
print(worksheet[1]) # (<Cell 'Sheet'.A1>, <Cell 'Sheet'.B1>, <Cell 'Sheet'.C1>)
for i in worksheet[1]:
print(i.value)
# 打印多行
for rows in worksheet[2:3]:
for row in rows:
print(type(row), row.value)
# 保存工作簿到指定的文件名
wb.save("execl_test.xlsx")
# 关闭工作簿
wb.close()
读取某个单元格
from openpyxl import Workbook
# 创建一个新的Excel工作簿
wb = Workbook()
# 获取第一个工作表
worksheet = wb.active
# 多行数据
data = [
['Bob', 25, 'Male'],
['Charlie', 35, 'Male'],
['Alice', 30, 'Engineer']
]
# 逐行添加数据
for row in data:
worksheet.append(row)
# 读取A2数据
print(worksheet["A2"].value) # 输出: Charlie
#
for cells in worksheet["A1":"C2"]:
for cell in cells:
print(cell.value)
# 保存工作簿到指定的文件名
wb.save("execl_test.xlsx")
# 关闭工作簿
wb.close()
示例
from openpyxl import Workbook
# 创建一个新的Excel工作簿
wb = Workbook()
# 获取第一个工作表
worksheet = wb.active
# 多行数据
data = [
['Bob', 25, 'Male'],
['Charlie', 35, 'Male'],
['Alice', 30, 'Engineer']
]
# 逐行添加数据
for row in data:
worksheet.append(row)
cell = worksheet['A1']
cell_range = worksheet['A1':'C3']
colC = worksheet['C']
col_range = worksheet['B:C']
row1 = worksheet[1]
row_range = worksheet[2:3]
print(type(cell), cell) # 输出: <class 'openpyxl.cell.cell.Cell'> <Cell 'Sheet'.A1>
print(type(cell_range), cell_range) # 输出: <class 'tuple'> ((<Cell 'Sheet'.A1>, <Cell 'Sheet'.B1>, <Cell 'Sheet'.C1>), (<Cell 'Sheet'.A2>, <Cell 'Sheet'.B2>, <Cell 'Sheet'.C2>), (<Cell 'Sheet'.A3>, <Cell 'Sheet'.B3>, <Cell 'Sheet'.C3>))
print(type(colC), colC) # 输出: <class 'tuple'> (<Cell 'Sheet'.C1>, <Cell 'Sheet'.C2>, <Cell 'Sheet'.C3>)
print(type(col_range), col_range) # 输出: <class 'tuple'> ((<Cell 'Sheet'.B1>, <Cell 'Sheet'.B2>, <Cell 'Sheet'.B3>), (<Cell 'Sheet'.C1>, <Cell 'Sheet'.C2>, <Cell 'Sheet'.C3>))
print(type(row1), row1) # 输出: <class 'tuple'> (<Cell 'Sheet'.A1>, <Cell 'Sheet'.B1>, <Cell 'Sheet'.C1>)
print(type(row_range), row_range) # 输出: <class 'tuple'> ((<Cell 'Sheet'.A2>, <Cell 'Sheet'.B2>, <Cell 'Sheet'.C2>), (<Cell 'Sheet'.A3>, <Cell 'Sheet'.B3>, <Cell 'Sheet'.C3>))
# 遍历第一列
for col in worksheet["A"]:
print(col.value)
# 输出: Bob
# 输出: Charlie
# 输出: Alice
# 保存工作簿到指定的文件名
wb.save("execl_test.xlsx")
# 关闭工作簿
wb.close()
参考文档
https://openpyxl.readthedocs.io/en/stable/tutorial.html#/
标签:execl,输出,wb,读取,openpyxl,worksheet,print,col,row From: https://www.cnblogs.com/wangguishe/p/18196486