python读取Excel表合并单元格以及清除空格符

读取合并单元格并保留合并信息 当我们只是使用 pandas 的 read_excel 方法读取 Excel 文件时，我们可能会遇到一个很棘手的问题：合并单元格的信息将会丢失，从而导致我们的数据出现重复或缺失的情况。 在本篇文章中将介绍使用 pandas 正确地读取包含合并单元格的 Excel 表格，支持 xlsx 和 xls。

import pandas as pd
from openpyxl import load_workbook
from xlrd import open_workbook

def read_xlsx(file, sheet_name=None, header=None):
    """读取 xlsx 格式文件。"""
    excel = pd.ExcelFile(load_workbook(file, data_only=True), engine="openpyxl")
    sheet_name = sheet_name or excel.sheet_names[0]
    sheet = excel.book[sheet_name]
    df = excel.parse(sheet_name, header=header)

    for item in sheet.merged_cells:
        top_col, top_row, bottom_col, bottom_row = item.bounds
        base_value = item.start_cell.value
        # 1-based index转为0-based index
        top_row -= 1
        top_col -= 1
        # 由于前面的几行被设为了header，所以这里要对坐标进行调整
        if header is not None:
            top_row -= header + 1
            bottom_row -= header + 1
        df.iloc[top_row:bottom_row, top_col:bottom_col] = base_value
    return df

def read_xls(file, sheet_name=None, header=None):
    """读取 xls 格式文件。"""
    excel = pd.ExcelFile(open_workbook(file, formatting_info=True), engine="xlrd")
    sheet_name = sheet_name or excel.sheet_names[0]
    sheet = excel.book[sheet_name]
    df = excel.parse(sheet_name, header=header)

    # 0-based index
    for top_row, bottom_row, top_col, bottom_col in sheet.merged_cells:
        base_value = sheet.cell_value(top_row, top_col)
        # 由于前面的几行被设为了header，所以这里要对坐标进行调整
        if header is not None:
            top_row -= header + 1
            bottom_row -= header + 1
        df.iloc[top_row:bottom_row, top_col:bottom_col] = base_value
    return df

清除各单元格的空格和换行符，并去除列名中的空格和换行符
在数据处理过程中，字符串中的多余空格和换行符常常会影响数据的整洁性以及后续分析。使用 .replace(‘\n’, ‘’).strip() 可以有效地去除换行符和前后空格，但这并不能解决中间空格的问题。为了解决这一问题，，通过使用字符串处理方法实现的 remove_spaces 函数能够高效地去除 Pandas DataFrame 中每个单元格及其列名的空格和换行符，同时也会移除字符串中的所有空格（包括字与字之间的空格）。

def remove_spaces(df):
    """去除 DataFrame 中各单元格的空格和换行符，并去除列名中的空格和换行符。"""
    # 处理列名
    df.columns = [col.replace('\n', '').strip() if isinstance(col, str) else col for col in df.columns]

    # 处理各单元格，去掉所有空格，包括中间的空格和换行符
    return df.apply(lambda col: col.map(lambda x: x.replace('\n', '').replace(' ', '') if isinstance(x, str) else x))

封装后完整代码

# -*- coding: utf-8 -*-

import pandas as pd
from openpyxl import load_workbook
from xlrd import open_workbook
import warnings

warnings.simplefilter(action='ignore', category=FutureWarning)


def read_xlsx(file: str, sheet_name: str = None, header: int = None) -> pd.DataFrame:
    """读取 xlsx 格式文件。"""
    try:
        excel = pd.ExcelFile(load_workbook(file, data_only=True), engine="openpyxl")
        sheet_name = sheet_name or excel.sheet_names[0]
        sheet = excel.book[sheet_name]
        df = excel.parse(sheet_name, header=header)
        for item in sheet.merged_cells:
            top_col, top_row, bottom_col, bottom_row = item.bounds
            base_value = item.start_cell.value
            # 1-based index转为0-based index
            top_row -= 1
            top_col -= 1
            # 由于前面的几行被设为了header，所以这里要对坐标进行调整
            if header is not None:
                top_row -= header + 1
                bottom_row -= header + 1
            df.iloc[top_row:bottom_row, top_col:bottom_col] = base_value
        return df
    except Exception as e:
        raise ValueError(f"Error reading xlsx file: {e}")


def read_xls(file: str, sheet_name: str = None, header: int = None) -> pd.DataFrame:
    """读取 xls 格式文件。"""
    try:

        excel = pd.ExcelFile(open_workbook(file, formatting_info=True), engine="xlrd")
        sheet_name = sheet_name or excel.sheet_names[0]
        sheet = excel.book[sheet_name]
        df = excel.parse(sheet_name, header=header)

        # 0-based index
        for top_row, bottom_row, top_col, bottom_col in sheet.merged_cells:
            base_value = sheet.cell_value(top_row, top_col)
            # 由于前面的几行被设为了header，所以这里要对坐标进行调整
            if header is not None:
                top_row -= header + 1
                bottom_row -= header + 1
            df.iloc[top_row:bottom_row, top_col:bottom_col] = base_value

        return df
    except Exception as e:
        raise ValueError(f"Error reading xls file: {e}")


def remove_spaces(df: pd.DataFrame, strict: bool = True) -> pd.DataFrame:

    """
    去除 DataFrame 中各单元格的空格和换行符，并去除列名中的空格和换行符。
    param:
    strict (bool): 如果为 True，去除所有空格（包括中间空格和换行符）；如果为 False，仅去除两边的空格和换行符。
    """
    # 处理列名，去除换行符和两边空格
    df.columns = [col.replace('\n', '').strip() if isinstance(col, str) else col for col in df.columns]

    # 处理各单元格
    if strict:
        # 去掉所有空格，包括中间的空格和换行符
        return df.apply(
            lambda col: col.map(lambda x: x.replace('\n', '').replace(' ', '') if isinstance(x, str) else x))
    else:
        # 去掉两边的空格和换行符
        return df.apply(lambda col: col.map(lambda x: x.strip().replace('\n', '') if isinstance(x, str) else x))


def read_excel(file_path: str, sheet_name: str = None, header: int = None, strict: bool = True) -> pd.DataFrame:
    """
    提取单sheet表的数据，支持 .xls 和 .xlsx 格式。

    参数:
    file_path (str): Excel 文件路径。
    sheet_name (str, optional): 要读取的 sheet 名称，默认为 None，表示读取第一个 sheet。
    header (int, optional): 用于指定列名行数，默认为 None。
    strict (bool): 是否严格去除单元格中的空格。

    返回:
    pd.DataFrame: 读取并处理后的 DataFrame。
    """
    try:
        # 根据文件类型读取
        if file_path.endswith(".xlsx"):
            df = read_xlsx(file_path, sheet_name=sheet_name, header=header)
        elif file_path.endswith(".xls"):
            df = read_xls(file_path, sheet_name=sheet_name, header=header)
        else:
            raise ValueError("Unsupported file format. Please provide a .xls or .xlsx file.")

        # 去除各单元格的空格
        df = remove_spaces(df, strict=strict)
        return df
    except Exception as e:
        raise ValueError(f"Error reading Excel file: {e}")