【附代码】Python Excel合并单元格（OpenPyXL） Pandas.DataFrame groupby样式保存xlsx

本文链接：https://blog.csdn.net/ymzhu385/article/details/136177410

文章介绍了如何使用openpyxl库在Python中操作Excel，包括合并单元格和居中，以及针对大型数据集优化的Pandas.DataFramegroupby功能应用，以实现类似Excel的分组样式保存。

摘要由CSDN通过智能技术生成

文章目录

作者：小猪快跑

基础数学&计算数学，从事优化领域5年+，主要研究方向：MIP求解器、整数规划、随机规划、智能优化算法

如有错误，欢迎指正。如有更好的算法，也欢迎交流！！！——@小猪快跑

Excel合并单元格并居中

如果我们想合并单元格A1:B1并居中：

from openpyxl.styles import Alignment
from openpyxl import load_workbook

# 读取Excel文件
wb = load_workbook("excel.xlsx")
# 选择表名为 Sheet1 的表
ws = wb["Sheet1"]
# 合并单元格
ws.merge_cells('A1:B1')
# 单元格居中
ws['A1'].alignment = Alignment(horizontal="center", vertical="center")
# 保存Excel
wb.save("excel.xlsx")

Pandas.DataFrame groupby样式保存Excel

假设原始表格是这样的

a	b	c
A	1	0
A	1	1
A	2	2
B	2	3
B	2	4
C	3	5
X	3	6
X	3	7

然后想类似 groupby([‘a’,‘b’])样式保存成Excel

a	b	c
A	1	0
		1
	2	2
B	2	3
		4
C	3	5
X	3	6
		7

import numpy as np
from openpyxl import Workbook
from openpyxl.styles import Alignment
from openpyxl.utils.dataframe import dataframe_to_rows
import pandas as pd
from openpyxl.worksheet.cell_range import CellRange
from openpyxl.worksheet.merge import MergedCellRange


def merge_cells(df, key, output_path=None):
    """
    key 列去重并合并单元格并居中
    Args:
        df: DataFrame输入表
        key: （多个）列名
        output_path: 保存路径

    Returns: Workbook 工作簿

    """
    wb = Workbook()  # 创建工作簿
    ws = wb.active  # 获取第一个工作表

    # 把 key 列 调整到最前面，并进行排序
    col = key if isinstance(key, list) else [key]
    set_col = set(col)
    columns = [*col, *(i for i in df.columns if i not in set_col)]
    _df = df[columns]
    _df.sort_values(key, inplace=True)

    # 将每行数据写入工作表中
    for row in dataframe_to_rows(_df, index=False, header=True):
        ws.append(row)

    align = Alignment(horizontal="center", vertical="center")  # 居中样式

    idx = {-1, _df.shape[0] - 1}
    for i, _ in enumerate(col):
        c = _df[_].values
        idx.update(np.where(c[1:] != c[:-1])[0])
        sorted_idx = sorted(idx)
        for start, end in zip(sorted_idx[:-1], sorted_idx[1:]):
            # OpenPyXL 序号从1开始，所以行序号需要+2
            ws.merge_cells(start_row=start + 3, end_row=end + 2, start_column=i + 1, end_column=i + 1)
            # 仅需对合并单元格后的一个 cell 居中即可
            ws.cell(start + 3, i + 1).alignment = align

    if output_path:
        # 保存writer中的数据至excel
        wb.save(output_path)

    return wb


def merge_cells_big_data(df, key, output_path=None):
    """
    合并数据过大导致原本函数性能过低

    key 列去重并合并单元格并居中
    Args:
        df: DataFrame输入表
        key: （多个）列名
        output_path: 保存路径

    Returns: Workbook 工作簿

    """
    wb = Workbook()  # 创建工作簿
    ws = wb.active  # 获取第一个工作表

    # 把 key 列 调整到最前面，并进行排序
    col = key if isinstance(key, list) else [key]
    set_col = set(col)
    columns = [*col, *(i for i in df.columns if i not in set_col)]
    _df = df[columns]
    _df.sort_values(key, inplace=True)

    # 将每行数据写入工作表中
    for row in dataframe_to_rows(_df, index=False, header=True):
        ws.append(row)

    align = Alignment(horizontal="center", vertical="center")  # 居中样式

    merged_cells = []
    idx = {-1, _df.shape[0] - 1}
    for i, _ in enumerate(col):
        c = _df[_].values
        idx.update(np.where(c[1:] != c[:-1])[0])
        sorted_idx = sorted(idx)
        for start, end in zip(sorted_idx[:-1], sorted_idx[1:]):
            # OpenPyXL 序号从1开始，所以行序号需要+2
            cr = CellRange(min_col=i + 1, min_row=start + 3, max_col=i + 1, max_row=end + 2)
            range_string = cr.coord
            mcr = MergedCellRange(ws, range_string)
            merged_cells.append(mcr)
            ws._clean_merge_range(mcr)
            # 仅需对合并单元格后的一个 cell 居中即可
            ws.cell(start + 3, i + 1).alignment = align
    ws.merged_cells = merged_cells

    if output_path:
        # 保存writer中的数据至excel
        wb.save(output_path)

    return wb


if __name__ == '__main__':
    df = pd.DataFrame(
        {
            "a": ["A", "A", "A", "B", "B", "C", "X", "X"],
            "b": [1, 1, 2, 2, 2, 3, 3, 3],
            "c": range(8),
        }
    )
    df.to_csv('input.csv', index=False)
    wb = merge_cells_big_data(df, ['a', 'b'], 'out.xlsx')