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

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

import pandas as pd
from openpyxl import load_workbook
from xlrd import open_workbookdef 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.boundsbase_value = item.start_cell.value# 1-based index转为0-based indextop_row -= 1top_col -= 1# 由于前面的几行被设为了header，所以这里要对坐标进行调整if header is not None:top_row -= header + 1bottom_row -= header + 1df.iloc[top_row:bottom_row, top_col:bottom_col] = base_valuereturn dfdef 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 indexfor 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 + 1bottom_row -= header + 1df.iloc[top_row:bottom_row, top_col:bottom_col] = base_valuereturn 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 warningswarnings.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.boundsbase_value = item.start_cell.value# 1-based index转为0-based indextop_row -= 1top_col -= 1# 由于前面的几行被设为了header，所以这里要对坐标进行调整if header is not None:top_row -= header + 1bottom_row -= header + 1df.iloc[top_row:bottom_row, top_col:bottom_col] = base_valuereturn dfexcept 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 indexfor 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 + 1bottom_row -= header + 1df.iloc[top_row:bottom_row, top_col:bottom_col] = base_valuereturn dfexcept 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 dfexcept Exception as e:raise ValueError(f"Error reading Excel file: {e}")