Excel排版美化工具

import subprocess
import sys

def ensure_openpyxl():
    """确保 openpyxl 已安装，未安装则自动安装。"""
    try:
        import openpyxl
        return True
    except ImportError:
        print("⚠️ openpyxl 未安装，正在自动安装...")
        try:
            subprocess.check_call([sys.executable, '-m', 'pip', 'install', 'openpyxl'])
            import openpyxl
            print("✅ openpyxl 安装成功")
            return True
        except Exception as e:
            print(f"❌ openpyxl 安装失败: {e}")
            print("请手动执行: pip install openpyxl")
            return False

import platform
import subprocess

# ===== 跨平台字体映射表 =====
FONT_MAP = {
    '黑体': {
        'windows': ['黑体', 'SimHei'],
        'darwin':  ['Heiti SC', 'PingFang SC', 'STHeiti', 'Hiragino Sans GB'],
        'linux':   ['Noto Sans CJK SC', 'WenQuanYi Micro Hei', 'Source Han Sans SC'],
    },
    '仿宋': {
        'windows': ['仿宋', 'FangSong', 'FangSong_GB2312'],
        'darwin':  ['STFangsong', 'Songti SC', 'PingFang SC'],
        'linux':   ['Noto Serif CJK SC', 'WenQuanYi Micro Hei Mono', 'FangSong'],
    },
    '宋体': {
        'windows': ['宋体', 'SimSun'],
        'darwin':  ['Songti SC', 'STSong', 'PingFang SC'],
        'linux':   ['Noto Serif CJK SC', 'WenQuanYi Zen Hei'],
    },
    'Times New Roman': {
        'windows': ['Times New Roman'],
        'darwin':  ['Times New Roman', 'Times'],
        'linux':   ['Times New Roman', 'Liberation Serif', 'FreeSerif'],
    },
    'Calibri': {
        'windows': ['Calibri'],
        'darwin':  ['Calibri', 'Helvetica Neue'],
        'linux':   ['Carlito', 'Liberation Sans'],
    },
}

def resolve_font_name(font_name, os_name=None):
    """
    根据当前操作系统，将字体名映射为可用字体。
    返回 (实际使用的字体名, 是否为替代字体)

    示例：
      Windows: resolve_font_name('黑体') → ('黑体', False)
      macOS:   resolve_font_name('黑体') → ('Heiti SC', True)  # 替代
    """
    if os_name is None:
        os_name = platform.system().lower()

    candidates = FONT_MAP.get(font_name, {}).get(os_name, [font_name])

    if os_name == 'windows':
        return candidates[0], False  # Windows 直接信任字体名

    # macOS/Linux: 尝试验证字体
    for name in candidates:
        try:
            from openpyxl import Workbook
            wb = Workbook()
            ws = wb.active
            ws['A1'].font = Font(name=name, size=12)
            ws['A1'] = 'test'
            # 不实际保存，只验证是否能设置
            return name, (name != font_name)
        except Exception:
            continue
    # 全部验证失败，用第一个候选兜底
    return candidates[0], True

# ===== 使用示例 =====
os_name = platform.system().lower()
title_font_name, _ = resolve_font_name('黑体', os_name)
body_font_cn_name, is_alt_cn = resolve_font_name('仿宋', os_name)
body_font_en_name, _ = resolve_font_name('Times New Roman', os_name)

# 如果使用了替代字体，在表格备注中标注
if is_alt_cn:
    print(f"⚠️ 当前系统({os_name})无'仿宋'字体，已替代为: {body_font_cn_name}")

# 生成时使用解析后的字体名
title_font = Font(name=title_font_name, size=20, bold=True)
body_font_cn = Font(name=body_font_cn_name, size=12)
body_font_en = Font(name=body_font_en_name, size=12)

def safe_open_workbook(filepath):
    """安全打开 Excel 文件，处理常见错误场景。"""
    from openpyxl import load_workbook

    if not filepath.endswith(('.xlsx', '.xlsm', '.xltx', '.xltm')):
        raise ValueError(
            f"❌ 不支持的文件格式: '{filepath}'n"
            f"仅支持 .xlsx / .xlsm / .xltx / .xltm 格式。"
        )

    try:
        wb = load_workbook(filepath)
        return wb
    except FileNotFoundError:
        raise FileNotFoundError(
            f"❌ 文件不存在: '{filepath}'n"
            f"请确认文件路径是否正确。"
        )
    except Exception as e:
        # 文件可能损坏或格式异常
        raise RuntimeError(
            f"❌ 无法打开文件: '{filepath}'n"
            f"文件可能已损坏或不是有效的 Excel 文件。n"
            f"原始错误: {e}n"
            f"建议：请尝试用 Excel 手动打开该文件确认是否正常。"
        )

def check_content_overflow(text, col_width_units, font_size=12):
    """
    检测文本是否会因列宽太窄而严重溢出。
    返回 (是否会溢出, 建议最小列宽, 预计换行行数)
    """
    if not text:
        return False, col_width_units, 1

    total_width = 0
    for ch in str(text):
        cp = ord(ch)
        if cp > 127:
            total_width += 2.0
        elif cp in (ord('('), ord(')'), ord('《'), ord('》')):
            total_width += 0.8
        else:
            total_width += 0.55

    effective_width = max(col_width_units * 0.92, 3)
    lines = max(1, int(total_width / effective_width) + (1 if total_width % effective_width > 0 else 0))

    # 换行超过5行 = 溢出警告
    if lines > 5:
        suggested_width = total_width / (4 * 0.92)  # 目标不超过4行
        return True, suggested_width, lines
    return False, col_width_units, lines

def safe_merge_cells(ws, start_row, start_col, end_row, end_col):
    """
    安全合并单元格，避免与已有合并区域冲突。
    如果目标区域与已有合并区域重叠，先取消已有合并。
    """
    target_range = f'{get_column_letter(start_col)}{start_row}:{get_column_letter(end_col)}{end_row}'

    # 检查冲突
    conflicts = []
    for merged_range in list(ws.merged_cells.ranges):
        if (start_row <= merged_range.max_row and end_row >= merged_range.min_row and
            start_col <= merged_range.max_col and end_col >= merged_range.min_col):
            conflicts.append(str(merged_range))

    if conflicts:
        print(f"⚠️ 合并区域冲突，先取消已有合并: {conflicts}")
        for merged_range in ws.merged_cells.ranges:
            if (start_row <= merged_range.max_row and end_row >= merged_range.min_row and
                start_col <= merged_range.max_col and end_col >= merged_range.min_col):
                ws.unmerge_cells(str(merged_range))

    ws.merge_cells(target_range)

┌─────────────────────────────────────┐
│ ① 检查 openpyxl 可用性              │ → 失败则安装重试，安装失败则中止
├─────────────────────────────────────┤
│ ② 检测操作系统 + 字体映射           │ → 生成解析后的字体名
├─────────────────────────────────────┤
│ ③ 如果是修改已有文件：              │
│    安全打开 + 检测文件损坏           │ → 损坏则报错中止
├─────────────────────────────────────┤
│ ④ 生成数据，设置样式                │ → 使用解析后的字体名
├─────────────────────────────────────┤
│ ⑤ 列宽溢出检测                     │ → 如有溢出，增加列宽或告知用户
├─────────────────────────────────────┤
│ ⑥ 行高自适应计算                   │ → 逐行估算
├─────────────────────────────────────┤
│ ⑦ 保存文件                         │ → 输出到指定路径
└─────────────────────────────────────┘

from openpyxl.styles import Font, Alignment, PatternFill

title_font = Font(name='黑体', size=20, bold=True)
title_align = Alignment(horizontal='center', vertical='center', wrap_text=True)
# 合并标题行覆盖所有数据列
ws.merge_cells(start_row=1, start_column=1, end_row=1, end_column=last_col)
ws['A1'].font = title_font
ws['A1'].alignment = title_align

# ===== 第一步：全表兜底 —— 数据区所有单元格统一设仿宋 =====
body_font_cn = Font(name='仿宋', size=12)
body_font_en = Font(name='Times New Roman', size=12)

for row in ws.iter_rows(min_row=header_row+1, max_row=last_row, min_col=1, max_col=last_col):
    for cell in row:
        cell.font = body_font_cn  # 先全部设仿宋，兜底

# ===== 第二步：按列覆盖 —— 纯数字/日期列覆盖为 Times New Roman =====
# 提前定义哪些列是数字/日期列（按列索引，1-based）
numeric_cols = [1, 5, 6, 7, 8, 9]  # 如：序号、入职日期、工龄、月薪、股份数、占比
for col_idx in numeric_cols:
    for row_idx in range(header_row+1, last_row+1):
        ws.cell(row=row_idx, column=col_idx).font = body_font_en

header_font = Font(name='黑体', size=12)
header_align = Alignment(horizontal='center', vertical='center')
header_border = Border(
    left=Side(style='thin'), right=Side(style='thin'),
    top=Side(style='thin'), bottom=Side(style='thin')
)

from openpyxl.styles import PatternFill

# 突出色
highlight_font = Font(name='仿宋', size=12, bold=True, color='C00000')
highlight_fill = PatternFill(start_color='FFF2CC', end_color='FFF2CC', fill_type='solid')
# 注意：theme色在openpyxl中不易直接使用，用近似RGB替代
# theme 7 tint 0.8 ≈ FFF2CC（浅黄）
# 暗红 = C00000

# 分类标题行示例
category_font = Font(name='仿宋', size=12, bold=True, color='C00000')
category_fill = PatternFill(start_color='FFF2CC', end_color='FFF2CC', fill_type='solid')
category_align = Alignment(horizontal='left', vertical='center')

# 假设第5行是分类标题行
ws.merge_cells(start_row=5, start_column=1, end_row=5, end_column=last_col)
cell = ws.cell(row=5, column=1, value='一、市场部')
cell.font = category_font
cell.fill = category_fill
cell.alignment = category_align

# 不要写：ws['F2'] = '=A2/B2'
# 要写：
ws['F2'] = '=IFERROR(A2/B2,"-")'

from openpyxl.styles import Border, Side

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

import math

def calc_row_height(text, col_width_units, font_size=12):
    """
    计算单元格所需行高（pt）—— v3精确版

    关键原理：
    - openpyxl 的 column_width 单位是"默认字体(Calibri 11)的字符0宽度"
    - 仿宋12号的中文字符实际占用约 2.0 个单位宽度
    - 英文/数字约占用 0.55 个单位宽度，括号/书名号约 0.8
    - 每行实际高度 ≈ 字号 × 1.4（含行间距）
    - 安全系数 1.06（仅6%余量，避免行高过大）
    """
    if not text:
        return 20

    total_width = 0
    for ch in str(text):
        cp = ord(ch)
        if cp > 127:       # 中文字符 / 全角符号 → 宽2.0单位
            total_width += 2.0
        elif cp in (ord('('), ord(')'), ord('《'), ord('》')):
            total_width += 0.8
        else:               # 半角英文/数字/标点 → 宽0.55单位
            total_width += 0.55

    effective_width = max(col_width_units * 0.92, 3)  # 留左右padding
    lines = math.ceil(total_width / effective_width)
    line_height = font_size * 1.4   # 每行高度含行距
    height = lines * line_height + 2  # 上下安全边距
    return max(18, int(height * 1.06))  # 安全系数1.06，最小18pt

# ===== 设置列宽（先设列宽，才能算行高） =====
col_widths = {'A': 6, 'B': 12, 'C': 15, 'D': 15, 'E': 14, 'F': 12,
              'G': 14, 'H': 16, 'I': 12, 'J': 20}
for col, width in col_widths.items():
    ws.column_dimensions[col].width = width

# ===== 设置行高 =====
# 固定行高（标题、表头等）
ws.row_dimensions[1].height = 30  # 标题行
ws.row_dimensions[2].height = 20  # 副标题行
ws.row_dimensions[3].height = 22  # 表头行

# 数据行：逐行估算，取该行所有单元格的最大所需高度
for row_idx in range(header_row + 1, last_row + 1):
    max_height = 20  # 最小行高
    for col_idx in range(1, last_col + 1):
        cell = ws.cell(row=row_idx, column=col_idx)
        if cell.value is not None:
            col_letter = get_column_letter(col_idx)
            col_width = col_widths.get(col_letter, 12)
            h = calc_row_height(str(cell.value), col_width)
            max_height = max(max_height, h)
    ws.row_dimensions[row_idx].height = max_height

ws_output['B2'] = "=Working!B10"

# 设置打印区域
ws.print_area = f'A1:{get_column_letter(last_col)}{last_row}'

# 页面设置
ws.page_setup.orientation = 'landscape'  # 或 'portrait'，视表格宽度
ws.page_setup.paperSize = ws.PAPERSIZE_A4
ws.page_setup.fitToWidth = 1
ws.page_setup.fitToHeight = 0  # 不限制高度
ws.sheet_properties.pageSetUpPr.fitToPage = True

# 页边距（厘米转英寸）
ws.page_margins.left = 0.75
ws.page_margins.right = 0.75
ws.page_margins.top = 1.0
ws.page_margins.bottom = 1.0

# 冻结首行（表头）
ws.freeze_panes = 'A2'  # 或表头下一行

import openpyxl
import math
import platform
import subprocess
import sys
from openpyxl.styles import Font, Alignment, PatternFill, Border, Side
from openpyxl.utils import get_column_letter

# ===== 0. 前置检查 =====
# ① 检查 openpyxl 可用性
try:
    import openpyxl
except ImportError:
    print("⚠️ openpyxl 未安装，正在安装...")
    subprocess.check_call([sys.executable, '-m', 'pip', 'install', 'openpyxl'])
    import openpyxl

# ② 检测操作系统 + 字体映射
os_name = platform.system().lower()

FONT_MAP = {
    '黑体': {'windows': ['黑体', 'SimHei'], 'darwin': ['Heiti SC', 'PingFang SC'], 'linux': ['Noto Sans CJK SC', 'WenQuanYi Micro Hei']},
    '仿宋': {'windows': ['仿宋', 'FangSong'], 'darwin': ['STFangsong', 'Songti SC'], 'linux': ['Noto Serif CJK SC']},
    'Times New Roman': {'windows': ['Times New Roman'], 'darwin': ['Times New Roman', 'Times'], 'linux': ['Times New Roman', 'Liberation Serif']},
}

def resolve_font_name(name):
    """将字体名映射为当前系统可用字体"""
    candidates = FONT_MAP.get(name, {}).get(os_name, [name])
    return candidates[0], (candidates[0] != name)

title_fn, _ = resolve_font_name('黑体')
body_cn_fn, is_alt = resolve_font_name('仿宋')
body_en_fn, _ = resolve_font_name('Times New Roman')
if is_alt:
    print(f"⚠️ 字体替代: 仿宋 → {body_cn_fn}")

wb = openpyxl.Workbook()
ws = wb.active
ws.title = "股权分配表"

# === 样式定义 ===
title_font = Font(name=title_fn, size=20, bold=True)
title_align = Alignment(horizontal='center', vertical='center', wrap_text=True)

header_font = Font(name=title_fn, size=12)
header_align = Alignment(horizontal='center', vertical='center', wrap_text=True)

body_font_cn = Font(name=body_cn_fn, size=12)             # 中文兜底
body_font_en = Font(name=body_en_fn, size=12)              # 英文/数字
body_align_center = Alignment(horizontal='center', vertical='center', wrap_text=True)
body_align_left = Alignment(horizontal='left', vertical='center', wrap_text=True)

highlight_font = Font(name=body_cn_fn, size=12, bold=True, color='C00000')
highlight_fill = PatternFill(start_color='FFF2CC', end_color='FFF2CC', fill_type='solid')

category_font = Font(name=body_cn_fn, size=12, bold=True, color='C00000')
category_fill = PatternFill(start_color='FFF2CC', end_color='FFF2CC', fill_type='solid')
category_align = Alignment(horizontal='left', vertical='center')

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

# === 行高估算函数（v3精确版） ===
def calc_row_height(text, col_width_units, font_size=12):
    """计算单元格所需行高（pt）。中文字符2.0单位，英文/数字0.55，安全系数1.06。"""
    if not text:
        return 20
    total_width = 0
    for ch in str(text):
        cp = ord(ch)
        if cp > 127:
            total_width += 2.0
        elif cp in (ord('('), ord(')'), ord('《'), ord('》')):
            total_width += 0.8
        else:
            total_width += 0.55
    effective_width = max(col_width_units * 0.92, 3)
    lines = math.ceil(total_width / effective_width)
    line_height = font_size * 1.4
    height = lines * line_height + 2
    return max(18, int(height * 1.06))

# === 布局参数 ===
last_col = 10  # J列
header_row = 3
last_row = 20  # 假设数据到第20行

# === 1. 先设列宽（行高依赖列宽，必须先设） ===
col_widths = {'A': 6, 'B': 12, 'C': 15, 'D': 15, 'E': 14, 'F': 10,
              'G': 14, 'H': 18, 'I': 12, 'J': 25}
for col, width in col_widths.items():
    ws.column_dimensions[col].width = width

# === 2. 标题 ===
ws.merge_cells(start_row=1, start_column=1, end_row=1, end_column=last_col)
ws['A1'] = 'XX企业股权分配表'
ws['A1'].font = title_font
ws['A1'].alignment = title_align
ws.row_dimensions[1].height = 30

# === 3. 副标题行（公司名 + 日期） ===
ws.merge_cells(start_row=2, start_column=1, end_row=2, end_column=4)
ws['A2'] = '公司：XX有限公司'
ws['A2'].font = Font(name='仿宋', size=12, color='C00000')
ws['A2'].fill = highlight_fill
ws['A2'].alignment = body_align_left

ws.merge_cells(start_row=2, start_column=last_col-1, end_row=2, end_column=last_col)
ws[f'{get_column_letter(last_col-1)}2'] = '设计日期：2025年3月30日'
ws[f'{get_column_letter(last_col-1)}2'].font = Font(name='仿宋', size=12)
ws[f'{get_column_letter(last_col-1)}2'].alignment = body_align_left
ws.row_dimensions[2].height = 20

# === 4. 表头 ===
headers = ['序号', '姓名', '部门', '职位', '入职日期', '工龄（年）',
           '月薪（元）', '拟分配股份数（万股）', '股份占比', '备注']
for col_idx, h in enumerate(headers, 1):
    cell = ws.cell(row=header_row, column=col_idx, value=h)
    cell.font = header_font
    cell.alignment = header_align
    cell.border = thin_border
ws.row_dimensions[header_row].height = 22

# === 5. 数据行 ===
# 示例数据
data = [
    [1, '张三', '市场部', '市场总监', '2018-03-15', 7, 25000, 15.5, 0.0825, '核心骨干'],
    [2, '李四', '市场部', '高级经理', '2019-07-01', 6, 18000, 8.0, 0.0425, ''],
    # ... 更多数据
]

# ===== 第一步：全表兜底 —— 所有数据单元格先统一设仿宋 =====
for row_idx in range(header_row + 1, last_row + 1):
    for col_idx in range(1, last_col + 1):
        cell = ws.cell(row=row_idx, column=col_idx)
        cell.font = body_font_cn          # 兜底：先全部仿宋
        cell.border = thin_border
        cell.alignment = body_align_center

    # 姓名列(B)和备注列(J)左对齐
    ws.cell(row=row_idx, column=2).alignment = body_align_left
    ws.cell(row=row_idx, column=10).alignment = body_align_left

# ===== 第二步：按列覆盖 —— 数字/日期列覆盖为 Times New Roman =====
numeric_cols = [1, 5, 6, 7, 8, 9]  # 序号、入职日期、工龄、月薪、股份数、占比
for col_idx in numeric_cols:
    for row_idx in range(header_row + 1, last_row + 1):
        ws.cell(row=row_idx, column=col_idx).font = body_font_en

# ===== 填入数据 =====
for row_data in data:
    row_idx = row_data[0] + header_row
    for col_idx, value in enumerate(row_data, 1):
        cell = ws.cell(row=row_idx, column=col_idx, value=value)

# === 6. 数字格式 ===
for row_idx in range(header_row + 1, last_row + 1):
    ws.cell(row=row_idx, column=5).number_format = 'YYYY-MM-DD'   # 入职日期
    ws.cell(row=row_idx, column=7).number_format = '#,##0'        # 月薪
    ws.cell(row=row_idx, column=8).number_format = '#,##0.00'     # 股份数
    ws.cell(row=row_idx, column=9).number_format = '0.00%'        # 占比

# === 7. 合计行（示例：第 last_row 行） ===
sum_row = last_row
ws.cell(row=sum_row, column=2, value='合计')
for col_idx in [7, 8]:  # 月薪、股份数合计
    col_letter = get_column_letter(col_idx)
    ws.cell(row=sum_row, column=col_idx, value=f'=IFERROR(SUM({col_letter}{header_row+1}:{col_letter}{sum_row-1}),"-")')
    ws.cell(row=sum_row, column=col_idx).number_format = '#,##0.00'
# 合计行样式：高亮
for col_idx in range(1, last_col + 1):
    cell = ws.cell(row=sum_row, column=col_idx)
    cell.font = highlight_font
    cell.fill = highlight_fill
    cell.border = thin_border

# === 8. 行高自适应 ===
for row_idx in range(header_row + 1, last_row + 1):
    max_height = 18
    for col_idx in range(1, last_col + 1):
        cell = ws.cell(row=row_idx, column=col_idx)
        if cell.value is not None:
            col_letter = get_column_letter(col_idx)
            col_width = col_widths.get(col_letter, 12)
            h = calc_row_height(str(cell.value), col_width)
            max_height = max(max_height, h)
    ws.row_dimensions[row_idx].height = max_height

# === 9. 打印设置 ===
ws.print_area = f'A1:{get_column_letter(last_col)}{last_row}'
ws.page_setup.orientation = 'landscape'
ws.page_setup.paperSize = ws.PAPERSIZE_A4
ws.page_setup.fitToWidth = 1
ws.page_setup.fitToHeight = 0
ws.sheet_properties.pageSetUpPr.fitToPage = True
ws.freeze_panes = f'A{header_row + 1}'

wb.save('output.xlsx')