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文件，一次性加载到内存中可能会导致内存溢出。可以采用以下策略：

1. 使用 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 可以显著减少内存占用，但只能读取，不能修改。

2. 使用 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 参数指定每次读取的行数，将大文件分成小块处理。

3. 使用 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中的日期和时间通常存储为数字，需要进行转换才能得到可读的日期时间对象。

代码小浣熊

代码小浣熊是基于商汤大语言模型的软件智能研发助手，覆盖软件需求分析、架构设计、代码编写、软件测试等环节

51

查看详情

1. 使用 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")

2. 使用 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")

3. 使用 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 提供了丰富的功能用于数据清洗和转换。

1. 处理缺失值:

   • 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)

2. 数据类型转换:

   • 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)

3. 字符串处理:

   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"])

4. 数据过滤和选择:

   使用布尔索引可以方便地过滤和选择数据。

   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)

5. 数据排序:

   • 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 尤其擅长处理结构化数据，可以显著提高数据处理的效率。记住，在处理大型文件时，要考虑内存占用问题，并采取相应的优化策略。