Python openpyxl批量操作详解：高效处理大量Excel数据教程

# 批量追加行
for row_data in data_list:
    ws.append(row_data)

# 批量读取
rows = list(ws.iter_rows(values_only=True))

# 批量写入（循环+cell）
for r, row_data in enumerate(data, 1):
    for c, value in enumerate(row_data, 1):
        ws.cell(row=r, column=c, value=value)

# 整行/整列操作
ws.insert_rows(idx, amount)
ws.insert_cols(idx, amount)
ws.delete_rows(idx, amount)
ws.delete_cols(idx, amount)

from openpyxl import Workbook

wb = Workbook()
ws = wb.active

# 表头
ws.append(['编号', '姓名', '部门', '工资'])

# 批量追加数据
employees = [
    [1, '张三', '技术部', 15000],
    [2, '李四', '市场部', 12000],
    [3, '王五', '技术部', 18000],
    [4, '赵六', '财务部', 13000],
    [5, '钱七', '技术部', 16000],
]
for emp in employees:
    ws.append(emp)

print(f"总行数: {ws.max_row}")
print(f"总列数: {ws.max_column}")

# 读取所有数据
for row in ws.iter_rows(values_only=True):
    print(f"  {row}")

wb.save('batch_append.xlsx')
print("批量追加文件已保存")

总行数: 6
总列数: 4
  (1, '张三', '技术部', 15000)
  (2, '李四', '市场部', 12000)
  (3, '王五', '技术部', 18000)
  (4, '赵六', '财务部', 13000)
  (5, '钱七', '技术部', 16000)
批量追加文件已保存

from openpyxl import Workbook

wb = Workbook()
ws = wb.active

# 使用cell()批量写入
data = [
    ['A1', 'B1', 'C1'],
    ['A2', 'B2', 'C2'],
    ['A3', 'B3', 'C3'],
]
for r, row_data in enumerate(data, 1):
    for c, value in enumerate(row_data, 1):
        ws.cell(row=r, column=c, value=value)

print("原始数据:")
for row in ws.iter_rows(values_only=True):
    print(f"  {row}")

# 插入行
ws.insert_rows(2, 2)  # 在第2行插入2行
print(f"\n插入2行后(max_row={ws.max_row}):")
for row in ws.iter_rows(values_only=True):
    print(f"  {row}")

# 插入列
ws.insert_cols(2, 1)  # 在第2列插入1列
print(f"\n插入1列后(max_column={ws.max_column}):")
for row in ws.iter_rows(min_row=1, max_row=1, values_only=True):
    print(f"  {row}")

# 删除行
ws.delete_rows(2, 1)  # 删除第2行
print(f"\n删除1行后(max_row={ws.max_row}):")
for row in ws.iter_rows(values_only=True):
    print(f"  {row}")

wb.save('batch_insert_delete.xlsx')
print("行列操作文件已保存")

原始数据:
  ('A1', 'B1', 'C1')
  ('A2', 'B2', 'C2')
  ('A3', 'B3', 'C3')

插入2行后(max_row=5):
  ('A1', 'B1', 'C1')
  (None, None, None)
  (None, None, None)
  ('A2', 'B2', 'C2')
  ('A3', 'B3', 'C3')

插入1列后(max_column=4):
  ('A1', None, 'B1', 'C1')

删除1行后(max_row=4):
  ('A1', None, 'B1', 'C1')
  (None, None, None, None)
  ('A2', None, 'B2', 'C2')
  ('A3', None, 'B3', 'C3')
行列操作文件已保存

from openpyxl import Workbook

wb = Workbook()
ws = wb.active

# 创建测试数据
ws.append(['姓名', '部门', '工资'])
employees = [
    ['张三', '技术部', 15000],
    ['李四', '市场部', 12000],
    ['王五', '技术部', 18000],
    ['赵六', '财务部', 13000],
    ['钱七', '技术部', 16000],
]
for emp in employees:
    ws.append(emp)

# 批量修改：技术部员工工资加2000
for row in ws.iter_rows(min_row=2):
    if row[1].value == '技术部':
        row[2].value += 2000
        print(f"  {row[0].value}: {row[2].value - 2000} -> {row[2].value}")

# 批量添加公式列
ws['D1'] = '年薪'
for row in range(2, ws.max_row + 1):
    ws.cell(row=row, column=4, value=f'=C{row}*12')

# 添加汇总行
ws.append(['合计', '', f'=SUM(C2:C{ws.max_row})', f'=SUM(D2:D{ws.max_row})'])

print("\n修改后数据:")
for row in ws.iter_rows(values_only=True):
    print(f"  {row}")

wb.save('batch_update.xlsx')
print("批量更新文件已保存")

  张三: 15000 -> 17000
  王五: 18000 -> 20000
  钱七: 16000 -> 18000

修改后数据:
  ('姓名', '部门', '工资', '年薪')
  ('张三', '技术部', 17000, '=C2*12')
  ('李四', '市场部', 12000, '=C3*12')
  ('王五', '技术部', 20000, '=C4*12')
  ('赵六', '财务部', 13000, '=C5*12')
  ('钱七', '技术部', 18000, '=C6*12')
  ('合计', '', '=SUM(C2:C6)', '=SUM(D2:D6)')
批量更新文件已保存