Python操作EXCEL

表格分组聚合

import pandas as pd

# 分组聚合
def group(path, name):
    wb = pd.read_excel(path)  # 打开excel表格
    grade_df1 = wb.groupby(name)  # 按name分组
    n = 1
    for i in grade_df1:
        writer = "{}.xlsx".format(n)
        i[1].to_excel(writer, header=True, index=None)  # 生成表格
        n += 1


path = r"data.xlsx"
group(path, "years")  # 单个标签分组
group(path, ["code", "years"])  # 多个标签分组

复制表格样式

import copy
import openpyxl
from openpyxl.utils import get_column_letter

path = input('输入你需要复制格式的表格')
save_path = input('输入你需要复制格式后的表格路径')

wb = openpyxl.load_workbook(path)
wb2 = openpyxl.Workbook()

sheetnames = wb.sheetnames
for sheetname in sheetnames:
    print(sheetname)
    sheet = wb[sheetname]
    sheet2 = wb2.create_sheet(sheetname)

    # 复制tab颜色
    sheet2.sheet_properties.tabColor = sheet.sheet_properties.tabColor

    # 开始处理合并单元格形式为“(<CellRange A1：A4>,)，替换掉(<CellRange 和 >,)' 找到合并单元格
    wm = list(sheet.merged_cells)
    if len(wm) > 0:
        for i in range(0, len(wm)):
            cell2 = str(wm[i]).replace('(<CellRange ', '').replace('>,)', '')
            sheet2.merge_cells(cell2)

    # 遍历后，先写入数据
    for i, row in enumerate(sheet.iter_rows()):
        sheet2.row_dimensions[i+1].height = sheet.row_dimensions[i+1].height
        for j, cell in enumerate(row):
            sheet2.column_dimensions[get_column_letter(
                j+1)].width = sheet.column_dimensions[get_column_letter(j+1)].width
            sheet2.cell(row=i + 1, column=j + 1, value=cell.value)

            # 接着逐一设置单元格格式
            source_cell = sheet.cell(i+1, j+1)
            target_cell = sheet2.cell(i+1, j+1)
            target_cell.fill = copy.copy(source_cell.fill)

            # 默认样式是 Normal，如果是默认样式，返回False，不触发if，反之则进行复制
            if source_cell.has_style: 

                # 该StyleableObject实现将样式存储在单个列表中_style，并且单元格上的样式属性实际上是该数组的 getter 和 setter，所以你可以使用下方的写法，克隆样式更快
                target_cell._style = copy.copy(source_cell._style)

                # 复制字号
                target_cell.font = copy.copy(source_cell.font)

                # 复制边框
                target_cell.border = copy.copy(source_cell.border)

                # 复制填充样式
                target_cell.fill = copy.copy(source_cell.fill)

                # 复制字体样式
                target_cell.number_format = copy.copy(
                    source_cell.number_format)

                # 复制样式保护
                target_cell.protection = copy.copy(source_cell.protection)

                # 复制对齐样式
                target_cell.alignment = copy.copy(source_cell.alignment)

if 'Sheet' in wb2.sheetnames:
    del wb2['Sheet']
wb2.save(save_path)

wb.close()
wb2.close()