如何使用Python操作Excel？openpyxl库高级技巧指南

openpyxl是python操作excel的核心库，支持读取、写入和修改文件。使用load_workbook()读取，workbook()创建新文件，save()保存修改。通过sheet["a1"].value读取单元格内容，append()添加行数据。样式可通过font、alignment、patternfill等类设置。处理大型文件时，应启用read_only或write_only模式，配合iter_rows()限制读取范围以优化性能。日期时间数据会自动转换为datetime对象，写入时若格式异常可设置number_format属性。此外，避免频繁样式设置、减少公式使用、及时释放资源及考虑使用pandas辅助处理均可提升效率。

使用Python操作Excel，关键在于openpyxl库。它允许你读取、写入和修改Excel文件，而高级技巧则集中在更高效的数据处理、样式定制和公式应用上。

解决方案：

1. 安装 openpyxl:

   立即学习“Python免费学习笔记（深入）”；

   

   pip install openpyxl

   登录后复制

2. 读取Excel文件:

   from openpyxl import load_workbook
   
   workbook = load_workbook(filename="example.xlsx")
   sheet = workbook.active  # 或者 workbook["Sheet1"]
   cell_value = sheet["A1"].value
   print(cell_value)
   
   # 遍历所有行
   for row in sheet.iter_rows(min_row=2, max_row=5, min_col=1, max_col=3, values_only=True):
       print(row)

   登录后复制

3. 写入Excel文件:

   

   from openpyxl import Workbook
   
   workbook = Workbook()
   sheet = workbook.active
   sheet["A1"] = "Hello"
   sheet["B1"] = 42
   
   # 添加一行数据
   sheet.append(["World", 99])
   
   workbook.save(filename="output.xlsx")

   登录后复制

4. 修改Excel文件:

   直接通过单元格坐标修改，然后保存。

5. 样式定制:

   from openpyxl.styles import Font, Color, Alignment, PatternFill
   
   cell = sheet["A1"]
   cell.font = Font(name="Arial", size=12, bold=True, color="FF0000")  # 红色加粗Arial字体
   cell.alignment = Alignment(horizontal="center", vertical="center")
   cell.fill = PatternFill(start_color="FFFF0000", end_color="FFFF0000", fill_type="solid") #红色背景

   登录后复制

6. 公式应用:

   sheet["C1"] = "=SUM(A1:B1)"

   登录后复制

7. 处理大型Excel文件:

   使用read_only=True和write_only=True模式，配合迭代器，可以显著降低内存占用。

openpyxl有哪些常用的函数和方法？

• 工作簿（Workbook）操作：

  • load_workbook(filename): 加载现有Excel文件。
  • Workbook(): 创建新的Excel文件。
  • save(filename): 保存Excel文件。
  • create_sheet(title=None, index=None): 创建新的工作表。
  • remove(worksheet): 删除工作表。
  • sheetnames: 获取所有工作表名称的列表。

• 工作表（Worksheet）操作：

  

  PPT.CN,PPTCN,PPT.CN是什么,PPT.CN官网,PPT.CN如何使用

  一键操作，智能生成专业级PPT

  37

  查看详情
  • title: 获取或设置工作表的标题。
  • active: 获取活动工作表。
  • iter_rows(min_row=None, max_row=None, min_col=None, max_col=None, values_only=False): 迭代指定范围内的行。values_only=True 可以只获取单元格的值。
  • iter_cols(min_col=None, max_col=None, min_row=None, max_row=None, values_only=False): 迭代指定范围内的列。
  • append(iterable): 在工作表的末尾添加一行数据。
  • merge_cells(range_string): 合并单元格。
  • unmerge_cells(range_string): 取消合并单元格。
  • row_dimensions[row_number]: 获取或设置行的属性（例如，高度）。
  • column_dimensions[column_letter]: 获取或设置列的属性（例如，宽度）。

• 单元格（Cell）操作：

  • value: 获取或设置单元格的值。
  • row: 获取单元格所在的行号。
  • column: 获取单元格所在的列号（数字）。
  • column_letter: 获取单元格所在的列字母。
  • coordinate: 获取单元格的坐标（例如，"A1"）。
  • font: 获取或设置单元格的字体样式。
  • fill: 获取或设置单元格的填充样式。
  • alignment: 获取或设置单元格的对齐方式。
  • number_format: 获取或设置单元格的数字格式。
  • border: 获取或设置单元格的边框样式。

• 样式（Style）操作：

  • Font(name=None, size=None, bold=None, italic=None, color=None): 创建字体样式。
  • PatternFill(fill_type=None, fgColor=None, bgColor=None): 创建填充样式。
  • Alignment(horizontal=None, vertical=None, wrap_text=None): 创建对齐方式。
  • Border(left=None, right=None, top=None, bottom=None): 创建边框样式。
  • Side(style=None, color=None): 创建边框的边。
  • Color(rgb=None): 创建颜色。

如何处理Excel中的日期和时间数据？

openpyxl 会自动将Excel中的日期和时间值转换为Python的datetime对象。

from openpyxl import load_workbook
from datetime import datetime

workbook = load_workbook(filename="dates.xlsx")
sheet = workbook.active

date_value = sheet["A1"].value
if isinstance(date_value, datetime):
    print(date_value.strftime("%Y-%m-%d"))  # 格式化日期
else:
    print("Not a datetime object")

#写入日期
from openpyxl import Workbook
from datetime import datetime

wb = Workbook()
ws = wb.active
ws['A1'] = datetime(2024, 1, 1)
wb.save("date_written.xlsx")

如果你需要将Python的datetime对象写入Excel，openpyxl会自动处理。 如果遇到日期显示不正确的情况，可能是Excel单元格的格式问题，可以在Excel中手动修改单元格的格式，或者使用openpyxl设置单元格的number_format属性。

如何优化openpyxl的性能，处理大型Excel文件？

• 使用 read_only 和 write_only 模式： 当处理大型文件时，使用只读或只写模式可以显著减少内存占用。

  from openpyxl import load_workbook
  
  workbook = load_workbook(filename="large_file.xlsx", read_only=True)
  for sheet_name in workbook.sheetnames:
      sheet = workbook[sheet_name]
      for row in sheet.iter_rows(max_row=100): #限制读取的行数
          for cell in row:
              print(cell.value)

  登录后复制

  对于写入：

  from openpyxl import Workbook
  
  workbook = Workbook(write_only=True)
  sheet = workbook.create_sheet()
  for i in range(1000):
      row = [i, i * 2, i * 3]
      sheet.append(row)
  workbook.save("large_output.xlsx")

  登录后复制

• 使用迭代器： iter_rows() 和 iter_cols() 方法允许你按行或按列迭代数据，而不是一次性加载整个工作表到内存中。

• 限制读取的范围： 使用 min_row, max_row, min_col, max_col 参数来限制读取的数据范围。

• 避免不必要的样式设置： 样式设置会增加内存占用和处理时间。尽量减少样式的使用，或者只在必要时应用样式。

• 使用公式时注意： 大量公式计算可能会影响性能。考虑是否可以在Python中预先计算结果，然后将结果写入Excel。

• 及时释放资源： 在处理完工作簿后，确保关闭文件，释放资源。

• 使用更高效的库（如果适用）： 对于非常大的文件，可以考虑使用其他更底层的库，例如csv 或 pandas，然后将数据导出到Excel。 pandas 在处理数据方面通常比直接使用 openpyxl 更高效。

以上就是如何使用Python操作Excel？openpyxl库高级技巧指南的详细内容，更多请关注php中文网其它相关文章！