openpyxl 读取 execl

按列读取数据

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#/