Python操作Excel及处理大文件技巧

处理Python操作Excel的核心是使用第三方库，如openpyxl、pandas等。1. openpyxl适合创建和修改.xlsx文件，支持流式读取以降低内存占用；2. pandas适合数据分析，通过chunksize分块读取大型文件提升效率；3. 优化读取速度可指定列、优化数据类型并使用SSD硬件；4. 中文乱码问题可通过读写时指定编码（如utf-8或gbk）解决。对于不同需求应选择合适的库：注重格式控制用openpyxl，侧重数据分析则选pandas。

Python操作Excel，核心在于借助第三方库，比如openpyxl、xlrd/xlwt（老版本，只支持.xls）以及pandas。处理大型Excel文件，就得考虑内存占用和效率，通常会采用流式读取或者分块处理。

解决方案

操作Excel，首推openpyxl，它支持.xlsx格式，功能也比较全面。

from openpyxl import load_workbook, Workbook

# 读取Excel文件
workbook = load_workbook(filename="example.xlsx")
sheet = workbook.active  # 获取当前活跃的sheet

# 访问单元格
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) # 输出每一行的数据，以元组形式

# 写入Excel文件
workbook = Workbook()
sheet = workbook.active
sheet["A1"] = "Hello"
sheet.append(["World", 123]) # 追加一行数据
workbook.save(filename="output.xlsx")

pandas库更适合数据分析，读取速度快，操作灵活。

import pandas as pd

# 读取Excel文件
df = pd.read_excel("example.xlsx", sheet_name="Sheet1") # 指定sheet名称

# 打印前5行
print(df.head())

# 写入Excel文件
df.to_excel("output.xlsx", sheet_name="Sheet1", index=False) # 不写入索引

对于大型Excel文件，直接加载到内存可能会爆掉。可以采用以下策略：

1. 分块读取（pandas）: pandas可以分块读取，通过chunksize参数指定每次读取的行数。

   import pandas as pd
   
   chunk_size = 10000
   for chunk in pd.read_excel("large_file.xlsx", chunksize=chunk_size):
       # 处理每个chunk
       print(f"Processing chunk with {len(chunk)} rows")
       # 例如，将chunk写入新的文件
       chunk.to_csv("output.csv", mode='a', header=False, index=False)

2. 流式读取（openpyxl）: openpyxl提供了read_only模式，可以逐行读取，减少内存占用。

   from openpyxl import load_workbook
   
   workbook = load_workbook(filename="large_file.xlsx", read_only=True)
   sheet = workbook.active
   
   for row in sheet.rows:
       row_values = [cell.value for cell in row]
       print(row_values)
       # 处理每一行的数据

如何选择合适的Python Excel库？

选择哪个库取决于你的需求：

• openpyxl: 适合创建、修改Excel文件，功能全面，但处理大型文件效率稍逊。
• xlrd/xlwt: 只支持.xls格式，功能较老，不推荐新项目使用。
• pandas: 适合数据分析，读取速度快，操作灵活，但对格式的控制不如openpyxl。如果你的主要任务是处理数据，而不是美化Excel，pandas是首选。

如何优化大型Excel文件的读取速度？

除了使用流式读取和分块读取，还可以考虑以下优化措施：

• 指定需要读取的列: 如果只需要Excel文件中的部分列，可以在读取时指定，减少不必要的数据加载。pandas可以通过usecols参数指定列。
• 优化数据类型: pandas可以自动推断数据类型，但有时会出错。手动指定数据类型可以提高读取速度和减少内存占用。
• 使用更高效的硬件: SSD比HDD读取速度更快，更大的内存可以减少磁盘IO。

如何解决Excel文件中的中文乱码问题？

Excel文件中的中文乱码通常是编码问题导致的。

• 读取时指定编码: pandas读取Excel文件时，可以通过encoding参数指定编码，常用的编码有utf-8、gbk、gb2312等。

  import pandas as pd
  
  df = pd.read_excel("example.xlsx", encoding="gbk")

• 写入时指定编码: 写入CSV文件时，也需要指定编码。

  df.to_csv("output.csv", encoding="utf-8")

• 检查Excel文件本身的编码: 有时Excel文件本身的编码就存在问题，需要用Excel软件打开，另存为UTF-8编码的CSV文件，再用Python读取。