使用Python对Excel数据读取与保存的全面指南

目录

• 1. Pandas读写Excel核心函数解析
• 1.1 数据读取函数：read_excel()
• 1.2 数据写入函数：to_excel()
• 2. 数据读取与保存的常见场景
• 2.1 单工作表读取
• 2.2 工作表级别精细读取
• 2.2.1 读取单个工作表的基本信息
• 2.2.2 多工作表处理
• 2.2.3 读取特定工作表的特定区域
• 2.3 批量处理：读取文件夹中的多个Excel文件
• 2.4 数据导出与格式控制
• 3. 实战演示：完整Excel数据处理流程
• 4. 常见问题与解决方案
• 4.1 编码问题导致读取失败
• 4.2 大型文件内存不足
• 4.3 数据类型自动识别错误
• 4.4 批量处理中的错误处理
• 4.5 保存时的格式丢失

Pandas提供了完善的Excel文件支持，可以处理.xlsx、.xls等多种格式的电子表格文件。

pd.read_excel()函数是Pandas中用于读取Excel数据的核心方法，支持丰富的参数配置：

def read_single_sheet(file_path: str | Path, sheet_name: str | int = 0,
                      usecols: Optional[List] = None, skiprows: int = 0,
                      nrows: Optional[int] = None) -> pd.DataFrame:
    """
    读取单个Excel文件的指定工作表
    """

关键参数详解：

• io：文件路径，支持字符串、路径对象或文件流
• sheet_name：工作表指定，可以是名称(str)、索引(int)或列表(多工作表)
• header：指定作为列名的行号，默认为0（第一行）
• usecols：选择特定列读取，提高读取效率
• nrows：限制读取行数，适合探索大型文件
• dtype：指定列数据类型，优化内存使用

将DataFrame写入Excel文件同样简单直观：

def save_to_excel(data: Union[pd.DataFrame, Dict[str, pd.DataFrame]],
                  output_path: str | Path, index: bool = False, **kwargs) -> bool:
    """
    将数据保存到Excel文件，支持单个或多个工作表
    """

最基本的应用场景，读取指定Excel文件的特定工作表：

# 读取单个工作表
df = pd.read_excel('data.xlsx', sheet_name='Sheet1')

在实际数据处理中，我们经常需要先了解工作表的结构再决定如何读取：

def get_excel_info(file_path: str | Path) -> Dict:
    """
    获取Excel文件的基本信息，包括工作表数量、名称、数据范围等
    """
    try:
        wb = load_workbook(file_path, read_only=True)
        info = {
            'file_name': os.path.basename(file_path),
            'sheet_count': len(wb.sheetnames),
            'sheet_names': wb.sheetnames,
            'active_sheet': wb.active.title  # 获取默认打开时选中的工作表
        }
        
        # 获取每个工作表的详细数据范围
        sheets_info = {}
        for sheet_name in wb.sheetnames:
            ws = wb[sheet_name]
            sheets_info[sheet_name] = {
                'max_row': ws.max_row,        # 工作表的最大数据行数
                'max_column': ws.max_column   # 工作表的最大数据列数
            }
        
        info['sheets_info'] = sheets_info
        wb.close()
        return info
    except Exception as e:
        print(f"获取文件信息失败: {e}")
        return {}

当Excel文件包含多个工作表时，可以一次性读取所有工作表：

# 读取所有工作表，返回字典{sheet_name: DataFrame}
all_sheets = pd.read_excel('data.xlsx', sheet_name=None)

# 或者读取特定多个工作表
selected_sheets = pd.read_excel('data.xlsx', sheet_name=['Sheet1', 'Sheet2'])

对于大型工作表，可以只读取需要的区域：

# 只读取A到C列，跳过前两行标题，只读取100行数据
df = pd.read_excel('large_file.xlsx', 
                   sheet_name='Sheet1',
                   usecols='A:C',
                   skiprows=2,
                   nrows=100)

在实际项目中，经常需要处理整个文件夹中的多个Excel文件。思路如下：

将处理结果保存为Excel文件时，可以精细控制输出格式：

# 保存单个DataFrame
df.to_excel('output.xlsx', index=False, sheet_name='处理结果')

# 保存多个DataFrame到同一文件的不同工作表
with pd.ExcelWriter('multi_sheet_output.xlsx') as writer:
    df1.to_excel(writer, sheet_name='汇总', index=False)
    df2.to_excel(writer, sheet_name='详情', index=False)

以下是一个完整的实际应用示例，展示从数据读取、处理到保存的全过程：

import pandas as pd
import glob
from pathlib import Path
from typing import List, Union, Dict, Optional
from openpyxl import load_workbook, Workbook
from file_io import FileIO


class ExcelIO:
    """
    Excel文件处理工具类
    封装常见的Excel数据读取和保存操作
    """

    def __init__(self):
        pass
    @staticmethod
    def read_single_sheet(file_path: str | Path, sheet_name: str | int = 0,
                          usecols: Optional[List] = None, skiprows: int = 0,
                          nrows: Optional[int] = None) -> pd.DataFrame:
        """
        读取单个Excel文件的指定工作表[1,4](@ref)

        Args:
            file_path: Excel文件路径
            sheet_name: 工作表名称或索引，默认为第一个工作表
            usecols: 指定读取的列
            skiprows: 跳过的行数
            nrows: 读取的行数限制

        Returns:
            pd.DataFrame: 读取的数据框
        """
        try:
            df = pd.read_excel(
                file_path,
                sheet_name=sheet_name,
                usecols=usecols,
                skiprows=skiprows,
                nrows=nrows
            )
            print(f"成功读取文件: {file_path}, 工作表: {sheet_name}")
            return df
        except Exception as e:
            print(f"读取文件失败: {e}")
            return pd.DataFrame()
    @staticmethod
    def read_all_sheets(file_path: str | Path) -> Dict[str, pd.DataFrame]:
        """
        读取单个Excel文件的所有工作表[1](@ref)

        Args:
            file_path: Excel文件路径

        Returns:
            Dict[str, pd.DataFrame]: 工作表名到数据框的映射字典
        """
        try:
            all_sheets = pd.read_excel(file_path, sheet_name=None)
            print(f"成功读取文件的所有工作表: {file_path}, 共{len(all_sheets)}个工作表")
            return all_sheets
        except Exception as e:
            print(f"读取所有工作表失败: {e}")
            return {}
    @staticmethod
    def save_to_excel(data: Union[pd.DataFrame, Dict[str, pd.DataFrame]],
                      output_path: str | Path, index: bool = False, **kwargs) -> bool:
        """
        将数据保存到Excel文件[1,4](@ref),可以保存一个sheet的数据或者多个sheet的数据

        Args:
            data: 要保存的数据，可以是单个DataFrame或工作表字典
            output_path: 输出文件路径
            index: 是否保存索引
            **kwargs: 其他pandas参数

        Returns:
            bool: 保存是否成功
        """
        try:
            if isinstance(data, pd.DataFrame):
                # 保存单个数据框
                data.to_excel(output_path, index=index, **kwargs)
                print(f"成功保存单个数据框到: {output_path}")

            elif isinstance(data, dict):
                # 保存多个工作表
                with pd.ExcelWriter(output_path) as writer:
                    for sheet_name, df in data.items():
                        # 确保工作表名称有效
                        valid_sheet_name = str(sheet_name)[:31]  # Excel工作表名最大31字符
                        df.to_excel(writer, sheet_name=valid_sheet_name, index=index, **kwargs)
                print(f"成功保存{len(data)}个工作表到: {output_path}")

            return True

        except Exception as e:
            print(f"保存文件失败: {e}")
            return False

    @staticmethod
    def get_excel_info(file_path: str | Path) -> Dict:
        """
        获取Excel文件的基本信息

        Args:
            file_path: Excel文件路径，可以是字符串或Path对象

        Returns:
            Dict: 包含Excel文件信息的字典
        """

        try:
            # 确保文件路径为Path对象（兼容str格式）
            file_path = Path(file_path)
            # 以只读模式加载Excel工作簿（提高大文件读取效率）
            wb = load_workbook(file_path, read_only=True)

            # 构建信息字典，包含文件基本属性[7](@ref)
            info = {
                'file_name': file_path.name,  # 提取文件名（不含路径）
                'sheet_count': len(wb.sheetnames),  # 工作表总数[7](@ref)
                'sheet_names': wb.sheetnames,  # 所有工作表名称列表[7](@ref)
                'active_sheet': wb.active.title  # 默认激活的工作表名称[6](@ref)
            }

            # 遍历每个工作表，获取详细信息[7](@ref)
            sheets_info = {}
            for sheet_name in wb.sheetnames:
                ws = wb[sheet_name]
                sheets_info[sheet_name] = {
                    'max_row': ws.max_row,  # 工作表的最大数据行数（从1开始）
                    'max_column': ws.max_column  # 工作表的最大数据列数（从1开始）
                }

            # 将各工作表详细信息添加到返回字典
            info['sheets_info'] = sheets_info
            wb.close()  # 关闭工作簿释放资源

            return info

        except Exception as e:
            print(f"获取文件信息失败: {e}")
            return {}  # 发生异常时返回空字典


# 使用示例
if __name__ == "__main__":
    # 创建处理器实例
    excel_io = ExcelIO()
    source_folder = Path(r"F:test")
    excel_name = "data.xlsx"
    # 读取单个文件，单个工作表
    df_single = excel_io .read_single_sheet(source_folder / excel_name , "Sheet1")
    print(df_single)
    array = df_single.to_numpy()# 转换为NumPy数组
    filtered_array = array[1,:] #通过切片读取特定区域的数据
    print( filtered_array)
    # 读取单个文件，所有工作表
    all_sheets = excel_io .read_all_sheets(source_folder / excel_name )
    print(all_sheets)
    print(all_sheets["Sheet1"])

    # 获取文件信息
    file_info = excel_io.get_excel_info(source_folder / excel_name)
    print(f"文件信息: {file_info}")

    #保存数据到Excel
    target_folder = Path(r"F:target")
    new_name = "new.xlsx"
    excel_io.save_to_excel(df_single, target_folder/new_name)

    #读取目标文件夹所有数据，并保存
    file_io = FileIO(source_folder, target_folder )
    file_paths = file_io.list_file_paths(True) #遍历所有文件，包括子文件夹里的
    excel_paths = file_io.filter_by_strings(file_paths ,[".xlsx"],True)#获取Excel文件
    for excel_path in excel_paths:
        #数据读取
        df = excel_io.read_single_sheet(excel_path,"Sheet1")

        ##数据处理

        #数据保存
        save_excel_name = Path(excel_path).name #可以通过修改名称进行重命名
        excel_io.save_to_excel(df, target_folder / save_excel_name) #保存至excel

问题：读取时出现UnicodeDecodeError等编码错误

解决方案：

# 指定编码格式读取
df = pd.read_excel('file.xlsx', encoding='utf-8')  # 或gbk、gb2312等

# 如仍失败，可先以二进制方式读取再处理
with open('problem_file.xlsx', 'rb') as f:
    df = pd.read_excel(f)

问题：读取大文件时内存占用过高

解决方案：

# 分块读取
chunk_size = 10000
chunks = pd.read_excel('large_file.xlsx', chunksize=chunk_size)

for i, chunk in enumerate(chunks):
    process_chunk(chunk)  # 逐块处理
    if i >= 10:  # 限制处理块数，避免无限循环
        break

# 或者只读取必要列
df = pd.read_excel('large_file.xlsx', usecols=['必要列1', '必要列2'])

问题：数值被识别为文本，日期格式错误等

解决方案：

# 明确指定列数据类型
df = pd.read_excel('file.xlsx', 
                   dtype={'电话': str, '数量': int},  # 明确指定类型
                   parse_dates=['日期列'])  # 明确解析日期列

问题：批量处理多个文件时，单个文件错误导致整个任务失败

解决方案：

def safe_batch_process(folder_path):
    """带错误处理的批量处理"""
    excel_files = glob.glob(os.path.join(folder_path, "*.xlsx"))
    success_count = 0
    error_files = []
    
    for file_path in excel_files:
        try:
            # 尝试读取和处理文件
            df = pd.read_excel(file_path)
            # ...处理逻辑...
            success_count += 1
            print(f"成功处理: {os.path.basename(file_path)}")
            
        except Exception as e:
            error_files.append((os.path.basename(file_path), str(e)))
            print(f"处理失败: {os.path.basename(file_path)}, 错误: {e}")
            continue  # 继续处理下一个文件
    
    print(f"处理完成: 成功 {success_count} 个, 失败 {len(error_files)} 个")
    if error_files:
        print("失败文件列表:")
        for file_name, error in error_files:
            print(f"  {file_name}: {error}")
    
    return success_count, error_files

问题：保存后数字格式、日期格式等丢失

解决方案：

# 使用ExcelWriter进行精细控制
with pd.ExcelWriter('output.xlsx', engine='openpyxl') as writer:
    df.to_excel(writer, sheet_name='数据', index=False)
    
    # 获取工作表对象进行格式设置
    worksheet = writer.sheets['数据']
    
    # 设置数字格式
    for column in worksheet.columns:
        column_name = column[0].value
        if column_name in ['金额', '价格']:
            for cell in column[1:]:  # 跳过标题行
                cell.number_format = '#,##0.00'

以上就是使用Python对Excel数据读取与保存的全面指南的详细内容，更多关于Python Excel数据读取与保存的资料请关注风君子博客其它相关文章！

您可能感兴趣的文章:

• python 读取txt中每行数据,并且保存到excel中的实例
• Python高效实现删除Excel重复数据的三种方法
• Python自动化操作Excel生成专业数据透视表的实战指南
• Python批量清洗Excel数据的操作指南(去重+补缺失值+可视化)
• Python实现Excel与TXT文本文件数据转换的完整指南