Python通过openpyxl、xlrd、xlwt和Pandas库实现Excel读写与数据处理，结合使用可高效操作.xlsx和.xls文件，并利用Pandas进行数据清洗、类型转换、缺失值处理及分块读取大型文件以避免内存溢出。
Python读写Excel文件，核心在于使用合适的库，并理解Excel文件的结构。Pandas库则更侧重于高效地处理表格数据，两者结合能让你在数据处理方面事半功倍。
解决方案
Python主要通过 openpyxl、xlrd、xlwt 等库来读写Excel文件，而Pandas则提供了更高级、更便捷的表格数据操作接口。下面分别介绍如何使用它们。
1. 使用 openpyxl 读写Excel（.xlsx格式）
openpyxl 是一个用于读取和写入 Excel 2010 xlsx/xlsm/xltx/xltm 文件的 Python 库。
立即学习“Python免费学习笔记（深入）”；
- 读取Excel文件:
from openpyxl import load_workbook
# 加载工作簿
workbook = load_workbook(filename="example.xlsx")
# 选择工作表
sheet = workbook["Sheet1"] # 或者 sheet = workbook.active
# 读取单元格数据
cell_value = sheet["A1"].value
print(cell_value)
# 遍历所有行
for row in sheet.iter_rows(values_only=True):
print(row)
# 遍历特定范围的单元格
for row in sheet["A1":"C3"]:
for cell in row:
print(cell.value)- 写入Excel文件:
from openpyxl import Workbook # 创建一个新的工作簿 workbook = Workbook() # 选择工作表 sheet = workbook.active # 写入数据 sheet["A1"] = "Hello" sheet["B2"] = "World" # 保存工作簿 workbook.save(filename="output.xlsx")
2. 使用 xlrd 和 xlwt 读写Excel（.xls格式）
xlrd 用于读取旧版本的 .xls 文件，xlwt 用于写入 .xls 文件。
- 读取Excel文件 (
xlrd):
import xlrd
# 打开工作簿
workbook = xlrd.open_workbook("example.xls")
# 选择工作表
sheet = workbook.sheet_by_name("Sheet1") # 或者 sheet = workbook.sheet_by_index(0)
# 读取单元格数据
cell_value = sheet.cell_value(0, 0) # 行, 列 (从0开始)
print(cell_value)
# 获取行数和列数
num_rows = sheet.nrows
num_cols = sheet.ncols
# 遍历所有行
for row_idx in range(num_rows):
print(sheet.row_values(row_idx))- 写入Excel文件 (
xlwt):
import xlwt
# 创建一个新的工作簿
workbook = xlwt.Workbook()
# 添加工作表
sheet = workbook.add_sheet("Sheet1")
# 写入数据
sheet.write(0, 0, "Hello") # 行, 列, 值
sheet.write(1, 1, "World")
# 保存工作簿
workbook.save("output.xls")3. 使用 Pandas 读写Excel
Pandas 提供了更简洁的接口来读写Excel文件，并且可以方便地进行数据处理。
- 读取Excel文件:
import pandas as pd
# 读取Excel文件
df = pd.read_excel("example.xlsx", sheet_name="Sheet1") # 或者 sheet_name=0
# 打印DataFrame
print(df)
# 访问特定列
print(df["Column1"])
# 访问特定行
print(df.iloc[0]) # 第一行- 写入Excel文件:
import pandas as pd
# 创建一个DataFrame
data = {"Name": ["Alice", "Bob", "Charlie"], "Age": [25, 30, 28]}
df = pd.DataFrame(data)
# 写入Excel文件
df.to_excel("output.xlsx", sheet_name="Sheet1", index=False) # index=False 避免写入索引如何处理大型Excel文件，避免内存溢出？
对于大型Excel文件，一次性加载到内存中可能会导致内存溢出。可以采用以下策略：
-
使用
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.iter_rows(values_only=True): # 处理每一行数据 print(row)read_only=True可以显著减少内存占用，但只能读取，不能修改。 -
使用
chunksize参数分块读取Pandas:import pandas as pd for chunk in pd.read_excel("large_file.xlsx", sheet_name="Sheet1", chunksize=1000): # 处理每一块数据 (chunk 是一个 DataFrame) print(chunk.head()) # 查看每一块的前几行chunksize参数指定每次读取的行数，将大文件分成小块处理。 -
使用
xlrd的on_demand模式 (仅适用于 .xls):import xlrd workbook = xlrd.open_workbook("large_file.xls", on_demand=True) sheet = workbook.sheet_by_name("Sheet1") for row_idx in range(sheet.nrows): row = sheet.row(row_idx) # 只在需要时加载行数据 print([cell.value for cell in row]) workbook.unload_sheet("Sheet1") # 释放内存on_demand=True允许按需加载sheet，workbook.unload_sheet()可以手动释放内存。
如何处理Excel中的日期和时间数据？
Excel中的日期和时间通常存储为数字，需要进行转换才能得到可读的日期时间对象。
-
使用
openpyxl:openpyxl会自动将Excel中的日期和时间转换为Python的datetime对象。from openpyxl import load_workbook from datetime import datetime workbook = load_workbook(filename="date_example.xlsx") sheet = workbook.active date_value = sheet["A1"].value if isinstance(date_value, datetime): print(date_value.strftime("%Y-%m-%d %H:%M:%S")) else: print("Not a datetime object") -
使用
xlrd:xlrd提供xlrd.xldate_as_datetime()函数将Excel日期数字转换为datetime对象。import xlrd from datetime import datetime workbook = xlrd.open_workbook("date_example.xls") sheet = workbook.sheet_by_name("Sheet1") date_value = sheet.cell_value(0, 0) if sheet.cell_type(0, 0) == xlrd.XL_CELL_DATE: date_object = xlrd.xldate_as_datetime(date_value, workbook.datemode) print(date_object.strftime("%Y-%m-%d %H:%M:%S")) else: print("Not a date cell") -
使用 Pandas:
Pandas 可以自动解析日期，或者使用
pd.to_datetime()函数进行转换。import pandas as pd df = pd.read_excel("date_example.xlsx") # 自动解析日期 (如果Excel中的日期格式标准) print(df["DateColumn"]) # 如果日期没有被自动解析，可以使用 pd.to_datetime() df["DateColumn"] = pd.to_datetime(df["DateColumn"], unit='D', origin='1899-12-30') # Excel的起始日期 print(df["DateColumn"])
如何在Pandas中进行数据清洗和转换？
Pandas 提供了丰富的功能用于数据清洗和转换。
-
处理缺失值:
-
df.isnull(): 检测缺失值。 -
df.fillna(value): 使用指定值填充缺失值。 -
df.dropna(): 删除包含缺失值的行或列。
import pandas as pd import numpy as np data = {"A": [1, 2, np.nan, 4], "B": [5, np.nan, 7, 8]} df = pd.DataFrame(data) # 填充缺失值 df_filled = df.fillna(0) # 用0填充 print(df_filled) # 删除包含缺失值的行 df_dropped = df.dropna() print(df_dropped) -
-
数据类型转换:
-
df["Column"].astype(dtype): 转换列的数据类型。
import pandas as pd data = {"A": ["1", "2", "3"], "B": [4.5, 5.6, 6.7]} df = pd.DataFrame(data) # 转换为整数类型 df["A"] = df["A"].astype(int) print(df.dtypes) -
-
字符串处理:
Pandas 提供了
.str属性，用于对字符串列进行各种操作。import pandas as pd data = {"Name": [" Alice ", "Bob", "Charlie "]} df = pd.DataFrame(data) # 去除字符串两端的空格 df["Name"] = df["Name"].str.strip() print(df["Name"]) # 转换为小写 df["Name"] = df["Name"].str.lower() print(df["Name"]) -
数据过滤和选择:
使用布尔索引可以方便地过滤和选择数据。
import pandas as pd data = {"Name": ["Alice", "Bob", "Charlie"], "Age": [25, 30, 28]} df = pd.DataFrame(data) # 选择年龄大于27岁的人 df_filtered = df[df["Age"] > 27] print(df_filtered) -
数据排序:
-
df.sort_values(by="Column"): 按照指定列排序。
import pandas as pd data = {"Name": ["Alice", "Bob", "Charlie"], "Age": [25, 30, 28]} df = pd.DataFrame(data) # 按照年龄排序 df_sorted = df.sort_values(by="Age") print(df_sorted) -
总的来说，Python 通过 openpyxl、xlrd、xlwt 和 Pandas 库提供了强大的 Excel 文件读写和数据处理能力。 选择合适的库取决于你的具体需求，例如文件格式、文件大小、数据处理复杂度等。Pandas 尤其擅长处理结构化数据，可以显著提高数据处理的效率。记住，在处理大型文件时，要考虑内存占用问题，并采取相应的优化策略。
