Python openpyxl打开工作簿教程 - load_workbook使用详解

from openpyxl import load_workbook

wb = load_workbook('filename.xlsx', read_only=False, data_only=False)

from openpyxl import Workbook, load_workbook

# 先创建一个测试文件
wb = Workbook()
ws = wb.active
ws['A1'] = '姓名'
ws['B1'] = '年龄'
ws.append(['张三', 25])
ws.append(['李四', 30])
wb.save('test_load.xlsx')

# 打开已有文件
wb2 = load_workbook('test_load.xlsx')
ws2 = wb2.active

print(f"工作表名: {ws2.title}")
print(f"A1值: {ws2['A1'].value}")
print(f"B2值: {ws2['B2'].value}")
print(f"最大行: {ws2.max_row}")
print(f"最大列: {ws2.max_column}")

工作表名: Sheet
A1值: 姓名
B2值: 25
最大行: 3
最大列: 2

from openpyxl import Workbook, load_workbook

# 创建含公式的文件
wb = Workbook()
ws = wb.active
ws['A1'] = 10
ws['A2'] = 20
ws['A3'] = '=A1+A2'  # 公式
wb.save('formula_test.xlsx')

# 普通模式：读取公式
wb1 = load_workbook('formula_test.xlsx', data_only=False)
ws1 = wb1.active
print(f"data_only=False: A3 = {ws1['A3'].value}")

# data_only模式：读取计算结果
# 注意：需要先用Excel打开并保存过，data_only才能读到计算值
# 未用Excel保存过的文件，data_only模式下公式返回None
wb2 = load_workbook('formula_test.xlsx', data_only=True)
ws2 = wb2.active
print(f"data_only=True: A3 = {ws2['A3'].value}")

data_only=False: A3 = =A1+A2
data_only=True: A3 = None

from openpyxl import Workbook, load_workbook

# 创建测试文件
wb = Workbook()
ws = wb.active
ws.append(['编号', '名称', '价格'])
for i in range(1, 6):
    ws.append([i, f'商品{i}', i * 10.5])
wb.save('readonly_test.xlsx')

# 只读模式打开
wb_ro = load_workbook('readonly_test.xlsx', read_only=True)
ws_ro = wb_ro.active

print("只读模式遍历数据:")
for row in ws_ro.iter_rows(values_only=True):
    print(f"  {row}")

# 查看工作表信息
print(f"\n工作表列表: {wb_ro.sheetnames}")
print(f"最大行: {ws_ro.max_row}")
print(f"最大列: {ws_ro.max_column}")

# 关闭只读工作簿（重要！）
wb_ro.close()
print("只读工作簿已关闭")

只读模式遍历数据:
  ('编号', '名称', '价格')
  (1, '商品1', 10.5)
  (2, '商品2', 21.0)
  (3, '商品3', 31.5)
  (4, '商品4', 42.0)
  (5, '商品5', 52.5)

工作表列表: ['Sheet']
最大行: 6
最大列: 3
只读工作簿已关闭