裸數(shù)據(jù).xlsx → openpyxl 加載 → 應(yīng)用格式規(guī)則 → 保存格式化后的文件

pip install openpyxl

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

def format_excel_sheet(input_file, output_file=None):
    """
    為 Excel 文件添加統(tǒng)一的格式：標題行加粗、藍色背景、邊框、對齊

    參數(shù):
        input_file: 輸入的原始 Excel 文件
        output_file: 輸出的格式化文件（默認在原文件名后加_已格式化）
    """
    if output_file is None:
        base_name = os.path.splitext(input_file)[0]
        output_file = f"{base_name}_已格式化.xlsx"

    # 1. 加載工作簿
    wb = load_workbook(input_file)
    ws = wb.active

    # 2. 定義樣式對象
    # 標題行字體：微軟雅黑，12號，加粗，白色
    title_font = Font(name='微軟雅黑', size=12, bold=True, color='FFFFFF')

    # 標題行背景：藍色
    title_fill = PatternFill(start_color='4472C4', fill_type='solid')

    # 數(shù)據(jù)行字體：微軟雅黑，11號
    data_font = Font(name='微軟雅黑', size=11)

    # 細邊框
    thin_border = Border(
        left=Side(style='thin'),
        right=Side(style='thin'),
        top=Side(style='thin'),
        bottom=Side(style='thin')
    )

    # 居中對齊
    center_align = Alignment(horizontal='center', vertical='center')

    # 左對齊（適合文本列）
    left_align = Alignment(horizontal='left', vertical='center')

    # 3. 獲取數(shù)據(jù)范圍
    max_row = ws.max_row
    max_col = ws.max_column

    if max_row < 1:
        print(f"警告: {input_file} 沒有數(shù)據(jù)")
        return

    # 4. 格式化標題行（第一行）
    for col in range(1, max_col + 1):
        cell = ws.cell(row=1, column=col)
        cell.font = title_font
        cell.fill = title_fill
        cell.border = thin_border
        cell.alignment = center_align

    # 5. 格式化數(shù)據(jù)行（第二行到最后）
    for row in range(2, max_row + 1):
        for col in range(1, max_col + 1):
            cell = ws.cell(row=row, column=col)
            cell.font = data_font
            cell.border = thin_border
            # 文本列左對齊，數(shù)字列居中
            if isinstance(cell.value, (int, float)):
                cell.alignment = center_align
            else:
                cell.alignment = left_align

    # 6. 自動調(diào)整列寬
    for col in range(1, max_col + 1):
        max_length = 0
        column_letter = ws.cell(row=1, column=col).column_letter

        for row in range(1, max_row + 1):
            cell_value = ws.cell(row=row, column=col).value
            if cell_value:
                cell_length = len(str(cell_value))
                if cell_length > max_length:
                    max_length = cell_length

        # 設(shè)置列寬（最大 30，最小 10）
        adjusted_width = min(max(max_length + 2, 10), 30)
        ws.column_dimensions[column_letter].width = adjusted_width

    # 7. 凍結(jié)首行（滾動時標題行固定不動）
    ws.freeze_panes = 'A2'

    # 8. 保存
    wb.save(output_file)
    print(f"格式化完成: {output_file}")


# ==================== 批量格式化 ====================
def batch_format_excel_files(folder_path):
    """
    批量格式化文件夾中的所有 Excel 文件
    """
    count = 0
    for filename in os.listdir(folder_path):
        if filename.endswith('.xlsx') and '已格式化' not in filename:
            file_path = os.path.join(folder_path, filename)
            format_excel_sheet(file_path)
            count += 1
    print(f"\n批量格式化完成！共處理 {count} 個文件")


# ==================== 使用示例 ====================
if __name__ == "__main__":
    # 單個文件格式化
    # format_excel_sheet("原始數(shù)據(jù).xlsx")

    # 批量格式化文件夾
    batch_format_excel_files(r"D:\待格式化報表")

title_font = Font(name='微軟雅黑', size=12, bold=True, color='FFFFFF')

title_fill = PatternFill(start_color='4472C4', fill_type='solid')

thin_border = Border(
    left=Side(style='thin'),
    right=Side(style='thin'),
    top=Side(style='thin'),
    bottom=Side(style='thin')
)

adjusted_width = min(max(max_length + 2, 10), 30)

ws.freeze_panes = 'A2'

from openpyxl.formatting.rule import CellIsRule

# 高亮負數(shù)（紅色背景）
ws.conditional_formatting.add(
    f'B2:B{max_row}',
    CellIsRule(
        operator='lessThan',
        formula=['0'],
        fill=PatternFill(start_color='FFC7CE', fill_type='solid')
    )
)

light_fill = PatternFill(start_color='F2F2F2', fill_type='solid')

for row in range(2, max_row + 1):
    if row % 2 == 0:  # 偶數(shù)行
        for col in range(1, max_col + 1):
            ws.cell(row=row, column=col).fill = light_fill

import shutil

shutil.copy("公司標準模板.xlsx", output_file)
wb = load_workbook(output_file)
ws = wb.active

# 此時已有模板的所有格式，只需寫入數(shù)據(jù)即可
for row_idx, row_data in enumerate(data_rows, start=2):
    for col_idx, value in enumerate(row_data, start=1):
        ws.cell(row=row_idx, column=col_idx, value=value)

title_font = Font(name='Arial Unicode MS', size=12, bold=True)
# 或
title_font = Font(name='SimSun', size=12, bold=True)  # 宋體

# 設(shè)置第一行行高為 25
ws.row_dimensions[1].height = 25

# 批量設(shè)置所有數(shù)據(jù)行行高
for row in range(1, max_row + 1):
    ws.row_dimensions[row].height = 20