Python 自动化批量设置 Excel 格式

用户11081884

发布于 2026-07-20 18:36:05

1070

文章被收录于专栏：用户11081884的专栏用户11081884的专栏

在日常工作中经常需要处理大量的 Excel 文件，并为它们设置统一的格式。手动操作不仅耗时耗力，还容易出错。Python 可以帮助实现 Excel 格式的自动化批量设置，大大提高工作效率。

使用场景

1、财务报告：为多个月份的财务报表统一设置数字格式、边框和颜色；

2、销售数据：批量高亮销售额超过特定值的单元格；

3、库存管理：为低于库存警戒线的商品自动标记红色；

4、学生成绩：为不同分数段的学生成绩设置不同的背景色；

5、项目管理：为即将到期的任务自动添加提醒格式；

工具准备

使用 openpyxl 库来处理 Excel 文件：

pip install openpyxl

代码示例

1、财务月报：数字千分位 + 表头蓝底白字 + 边框

fromopenpyxlimportload_workbook
fromopenpyxl.stylesimportFont, Alignment, PatternFill, Border, Side
frompathlibimportPath

deffinance_style(file: Path):
    wb = load_workbook(file)
    ws = wb.active
    # 千分位 & 两位小数
    forcolin ("B", "C", "D"):          # 假设金额在第 B-D 列
        forcellincol[1:]:             # 从第2行开始
            cell.number_format = "#,##0.00"
    # 表头
    header_fill = PatternFill("solid", fgColor="4F81BD")
    header_font = Font(name="微软雅黑", size=12, bold=True, color="FFFFFF")
    forcellinws[1]:
        cell.fill, cell.font = header_fill, header_font
    # 细边框
    thin = Border(*[Side(style="thin")]*4)
    forrowinws.iter_rows():
        forcinrow:
            c.border = thin
    wb.save(file.with_name(f"{file.stem}_finance{file.suffix}"))

if__name__ == "__main__":
    forfinPath(r"excel_folder").glob("*.xlsx"):
        finance_style(f)

2、销售台账：销售额 >10 万绿底，<5 万黄底

fromopenpyxlimportload_workbook
fromopenpyxl.formatting.ruleimportCellIsRule
fromopenpyxl.stylesimportPatternFill
frompathlibimportPath

defsales_highlight(file: Path):
    wb = load_workbook(file)
    ws = wb.active
    # 假设销售额在 C 列，数据区 C2:C1000
    green = PatternFill("solid", "C6EFCE")
    yellow = PatternFill("solid", "FFEB9C")
    ws.conditional_formatting.add(
        "C2:C1000",
        CellIsRule(operator="greaterThan", formula=[100000], fill=green)
    )
    ws.conditional_formatting.add(
        "C2:C1000",
        CellIsRule(operator="lessThan", formula=[50000], fill=yellow)
    )
    wb.save(file.with_name(f"{file.stem}_sales{file.suffix}"))

if__name__ == "__main__":
    forfinPath(r"sales_folder").glob("*.xlsx"):
        sales_highlight(f)

3、库存表：库存量 < 安全库存 → 整行红底

fromopenpyxlimportload_workbook
fromopenpyxl.formatting.ruleimportFormulaRule
fromopenpyxl.stylesimportPatternFill
frompathlibimportPath

defstock_alert(file: Path, safety=50):
    wb = load_workbook(file)
    ws = wb.active
    red = PatternFill("solid", "FFC7CE")
    # 假设库存量在 D 列，数据从第2行开始
    ws.conditional_formatting.add(
        f"A2:Z1000",                       # 整行范围
        FormulaRule(formula=[f"$D2<{safety}"], fill=red)
    )
    wb.save(file.with_name(f"{file.stem}_stock{file.suffix}"))

if__name__ == "__main__":
    forfinPath(r"stock_folder").glob("*.xlsx"):
        stock_alert(f, safety=100)          # 安全库存 100

4、学生成绩：≥90 绿色，<60 红色

fromopenpyxlimportload_workbook
fromopenpyxl.formatting.ruleimportCellIsRule
fromopenpyxl.stylesimportPatternFill
frompathlibimportPath

defscore_color(file: Path):
    wb = load_workbook(file)
    ws = wb.active
    green = PatternFill("solid", "C6EFCE")
    red = PatternFill("solid", "FFC7CE")
    # 假设成绩在 B 列
    ws.conditional_formatting.add(
        "B2:B1000",
        CellIsRule(operator="greaterThanOrEqual", formula=[90], fill=green)
    )
    ws.conditional_formatting.add(
        "B2:B1000",
        CellIsRule(operator="lessThan", formula=[60], fill=red)
    )
    wb.save(file.with_name(f"{file.stem}_score{file.suffix}"))

if__name__ == "__main__":
    forfinPath(r"score_folder").glob("*.xlsx"):
        score_color(f)

5、项目排期：截止日期 < 今天 → 橙色提醒

fromopenpyxlimportload_workbook
fromopenpyxl.formatting.ruleimportFormulaRule
fromopenpyxl.stylesimportPatternFill
fromdatetimeimportdate
frompathlibimportPath

defdeadline_reminder(file: Path):
    wb = load_workbook(file)
    ws = wb.active
    orange = PatternFill("solid", "FFA500")
    today = date.today().strftime("%Y-%m-%d")
    # 假设截止日期在 E 列
    ws.conditional_formatting.add(
        f"A2:Z1000",
        FormulaRule(formula=[f'$E2<TODAY()'], fill=orange)
    )
    wb.save(file.with_name(f"{file.stem}_deadline{file.suffix}"))

if__name__ == "__main__":
    forfinPath(r"project_folder").glob("*.xlsx"):
        deadline_reminder(f)

6、批量设置单元格格式

以下示例展示了如何批量设置多个 Excel 文件的单元格格式：

import openpyxl
from openpyxl.styles import Font, Alignment, Border, Side, PatternFill
import glob
import os

def set_excel_format(file_path):
    try:
        # 打开工作簿
        wb = openpyxl.load_workbook(file_path)
        ws = wb.active  # 获取活动工作表
        
        # 定义样式
        header_font = Font(name='微软雅黑', bold=True, size=12, color='FFFFFF')
        header_fill = PatternFill(start_color='4F81BD', end_color='4F81BD', fill_type='solid')
        header_alignment = Alignment(horizontal='center', vertical='center')
        
        data_font = Font(name='微软雅黑', size=11)
        data_alignment = Alignment(horizontal='left', vertical='center')
        
        thin_border = Border(left=Side(style='thin'), 
                            right=Side(style='thin'), 
                            top=Side(style='thin'), 
                            bottom=Side(style='thin'))
        
        # 设置表头格式
        for row in ws.iter_rows(min_row=1, max_row=1):
            for cell in row:
                cell.font = header_font
                cell.fill = header_fill
                cell.alignment = header_alignment
                cell.border = thin_border
        
        # 设置数据区域格式
        for row in ws.iter_rows(min_row=2):
            for cell in row:
                cell.font = data_font
                cell.alignment = data_alignment
                cell.border = thin_border
        
        # 自动调整列宽
        for column in ws.columns:
            max_length = 0
            column_letter = column[0].column_letter
            for cell in column:
                try:
                    if len(str(cell.value)) > max_length:
                        max_length = len(str(cell.value))
                except:
                    pass
            adjusted_width = (max_length + 2) * 1.2
            ws.column_dimensions[column_letter].width = adjusted_width
        
        # 保存文件
        new_file_path = file_path.replace('.xlsx', '_formatted.xlsx')
        wb.save(new_file_path)
        print(f"已处理文件: {os.path.basename(file_path)}")
        
    except Exception as e:
        print(f"处理文件 {file_path} 时出错: {str(e)}")


# 批量处理文件夹中的所有Excel文件
folder_path = r'C:\path\to\your\excel\files'
for excel_file in glob.glob(os.path.join(folder_path, '*.xlsx')):
    set_excel_format(excel_file)

print("所有文件处理完成！")

7、条件格式设置

import openpyxl
from openpyxl.formatting.rule import CellIsRule, FormulaRule
from openpyxl.styles import PatternFill
from datetime import datetime

def set_conditional_formatting(file_path):
    wb = openpyxl.load_workbook(file_path)
    ws = wb.active
    
    # 设置销售额大于10000的高亮为绿色
    green_fill = PatternFill(start_color='C6EFCE', end_color='C6EFCE', fill_type='solid')
    ws.conditional_formatting.add('C2:C100', 
                                CellIsRule(operator='greaterThan', formula=['10000'], fill=green_fill))
    
    # 设置库存小于50的高亮为红色
    red_fill = PatternFill(start_color='FFC7CE', end_color='FFC7CE', fill_type='solid')
    ws.conditional_formatting.add('D2:D100', 
                                CellIsRule(operator='lessThan', formula=['50'], fill=red_fill))
    
    # 设置过期日期小于今天的为橙色
    today = datetime.today().strftime('%Y-%m-%d')
    orange_fill = PatternFill(start_color='FFA500', end_color='FFA500', fill_type='solid')
    ws.conditional_formatting.add('E2:E100', 
                                CellIsRule(operator='lessThan', formula=[f'"{today}"'], fill=orange_fill))
    
    # 保存文件
    wb.save(file_path)
    print(f"已为 {os.path.basename(file_path)} 设置条件格式")

# 批量设置条件格式
for excel_file in glob.glob(os.path.join(folder_path, '*.xlsx')):
    set_conditional_formatting(excel_file)

通过 Python 自动化批量设置 Excel 格式，可以：