使用Python读取excel中的数据方法

《使用Python读取Excel中的数据方法》

在数据分析与处理的日常工作中，Excel文件因其直观的表格结构和广泛的应用场景，成为存储结构化数据的重要工具。Python作为一门功能强大的编程语言，通过丰富的第三方库（如openpyxl、pandas、xlrd等），能够高效地读取、解析和操作Excel文件中的数据。本文将系统介绍使用Python读取Excel数据的多种方法，涵盖基础操作到高级应用，帮助读者根据实际需求选择最适合的方案。

一、准备工作：环境配置与依赖安装

在开始读取Excel数据前，需确保Python环境已安装相关库。推荐使用虚拟环境管理依赖，避免版本冲突。以下是常用库的安装方法：

# 通过pip安装常用库
pip install openpyxl pandas xlrd xlwt

# 安装conda环境（可选）
conda create -n excel_env python=3.9
conda activate excel_env
conda install pandas openpyxl

各库的用途：

• openpyxl：读写.xlsx格式文件，支持公式、图表等高级功能。
• pandas：基于DataFrame的高效数据处理工具，依赖openpyxl或xlrd读取Excel。
• xlrd：读取旧版.xls文件（1.2.0版本后不再支持.xlsx）。
• xlwt：写入.xls文件（不支持.xlsx）。

二、使用openpyxl读取.xlsx文件

openpyxl是处理.xlsx文件的推荐库，尤其适合需要操作单元格样式、公式等场景。

1. 基本读取操作

from openpyxl import load_workbook

# 加载Excel文件
wb = load_workbook('example.xlsx')

# 获取所有工作表名
print(wb.sheetnames)  # 输出: ['Sheet1', 'Sheet2']

# 选择工作表
sheet = wb['Sheet1']

# 读取单元格数据
cell_value = sheet['A1'].value  # 读取A1单元格
print(cell_value)

# 遍历行数据
for row in sheet.iter_rows(values_only=True):
    print(row)  # 输出每行数据（元组形式）

2. 指定范围读取

通过参数控制读取的行/列范围：

# 读取A1到C10的数据
for row in sheet.iter_rows(min_row=1, max_row=10, min_col=1, max_col=3, values_only=True):
    print(row)

3. 处理空值与数据类型

Excel中的空单元格会被读取为None，数字、日期等需手动转换：

from datetime import datetime

# 假设B列包含日期
for row in sheet.iter_rows(min_row=2, values_only=True):
    date_str = row[1]  # B列数据
    if isinstance(date_str, datetime):
        print(f"日期: {date_str}")
    elif date_str is None:
        print("空值")

三、使用pandas读取Excel文件

pandas提供了更简洁的DataFrame接口，适合快速数据分析和清洗。

1. 读取整个工作表

import pandas as pd

# 读取第一个工作表
df = pd.read_excel('example.xlsx', sheet_name=0)

# 显示前5行
print(df.head())

# 读取指定工作表
df_sheet2 = pd.read_excel('example.xlsx', sheet_name='Sheet2')

2. 参数详解

pd.read_excel()支持丰富参数：

df = pd.read_excel(
    'example.xlsx',
    sheet_name='Sheet1',  # 工作表名或索引
    header=0,            # 表头行（默认第0行）
    usecols='A:C',       # 指定列范围
    skiprows=1,          # 跳过前1行
    nrows=100,           # 最多读取100行
    na_values=['NA', 'NULL']  # 自定义空值标识
)

3. 处理多工作表

# 读取所有工作表到字典（键为工作表名）
all_sheets = pd.read_excel('example.xlsx', sheet_name=None)

for sheet_name, df in all_sheets.items():
    print(f"工作表: {sheet_name}")
    print(df.head())

四、处理旧版.xls文件（xlrd库）

对于.xls文件（Excel 97-2003），需使用xlrd库（注意1.2.0后版本移除了.xlsx支持）：

import xlrd

# 打开.xls文件
book = xlrd.open_workbook('old_file.xls')

# 获取工作表
sheet = book.sheet_by_index(0)  # 第一个工作表

# 读取单元格数据
print(sheet.cell_value(0, 0))  # 第1行第1列（索引从0开始）

# 遍历行
for row_idx in range(sheet.nrows):
    print(sheet.row_values(row_idx))

五、性能优化与大文件处理

处理大型Excel文件时，需注意内存占用和读取速度。

1. 分块读取（pandas）

chunk_size = 5000
chunks = []

# 使用ExcelFile对象分块读取
with pd.ExcelFile('large_file.xlsx') as excel:
    for chunk in pd.read_excel(excel, sheet_name=0, chunksize=chunk_size):
        chunks.append(chunk)

# 合并分块
full_df = pd.concat(chunks, ignore_index=True)

2. 仅读取必要列

# 使用usecols参数减少内存占用
df = pd.read_excel('data.xlsx', usecols=['ID', 'Name', 'Score'])

3. 转换为CSV再处理（极端情况）

若Excel文件极大且仅需部分数据，可先转换为CSV再处理：

# 使用openpyxl导出为CSV（示例）
from openpyxl import load_workbook
import csv

wb = load_workbook('huge.xlsx')
sheet = wb.active

with open('output.csv', 'w', newline='') as f:
    writer = csv.writer(f)
    for row in sheet.iter_rows(values_only=True):
        writer.writerow(row)

六、常见问题与解决方案

1. 编码问题

中文Excel文件可能出现乱码，需指定编码（通常pandas会自动处理）：

# 显式指定编码（罕见情况）
df = pd.read_excel('file.xlsx', encoding='utf-8')  # 实际对.xlsx无效，仅作示例

2. 合并单元格处理

openpyxl可检测合并单元格，但pandas需手动处理：

# openpyxl检测合并区域
from openpyxl import load_workbook

wb = load_workbook('merged.xlsx')
sheet = wb.active

for merged_cell in sheet.merged_cells.ranges:
    print(f"合并区域: {merged_cell}")

3. 日期格式转换

Excel日期为数值（1900年1月1日为1），需用pandas转换：

df = pd.read_excel('dates.xlsx')
df['Date'] = pd.to_datetime(df['Date_Column'], origin='1899-12-30')  # Windows Excel基准日

七、完整案例：数据清洗与分析

综合应用前述方法，完成一个完整的数据处理流程：

import pandas as pd

# 1. 读取数据
df = pd.read_excel('sales_data.xlsx', sheet_name='2023', 
                   parse_dates=['OrderDate'])

# 2. 数据清洗
df = df.dropna(subset=['CustomerID', 'Amount'])  # 删除关键列空值
df['Amount'] = df['Amount'].astype(float)        # 转换金额为浮点数

# 3. 数据分析
monthly_sales = df.groupby(df['OrderDate'].dt.to_period('M'))['Amount'].sum()
print(monthly_sales)

# 4. 导出结果
monthly_sales.to_excel('monthly_sales.xlsx', sheet_name='Summary')

八、总结与选择建议

根据需求选择合适的库：

场景	推荐库
简单数据读取与分析	pandas
需要操作单元格样式/公式	openpyxl
处理旧版.xls文件	xlrd
超大型Excel文件	分块读取+pandas

关键词：Python、Excel读取、openpyxl、pandas、xlrd、数据处理、大文件处理、数据清洗

简介：本文详细介绍了使用Python读取Excel文件的多种方法，包括openpyxl、pandas和xlrd库的基础操作与高级应用，涵盖数据读取、性能优化、常见问题处理及完整案例分析，适合数据分析师和Python开发者参考。

《使用Python读取excel中的数据方法.doc》

将本文的Word文档下载到电脑，方便收藏和打印

推荐度：

点击下载文档