python实现 提取excel内容并生成新的py文件

import openpyxl
from openpyxl.styles import PatternFill, Font, Alignment, Border, Side
from openpyxl.utils import get_column_letter


def extract_excel(file_path, output_py_file, start_row, end_row, start_col, end_col):
    # 加载工作簿
    wb = openpyxl.load_workbook(file_path)
    sheet = wb.active  # 默认激活的工作表，或指定 sheet = wb['Sheet1']

    # 存储数据和格式
    data = []
    merged_cells = []
    column_widths = {}
    row_heights = {}

    # 提取指定范围的内容
    for row in sheet.iter_rows(min_row=start_row, max_row=end_row, min_col=start_col, max_col=end_col, values_only=False):
        row_data = []
        for cell in row:
            cell_info = {
                "value": cell.value,
                "fill_color": cell.fill.start_color.index if cell.fill else None,
                "font_size": cell.font.size if cell.font else None,
                "font_name": cell.font.name if cell.font else None,
                "font_bold": cell.font.bold if cell.font else None,
                "font_italic": cell.font.italic if cell.font else None,
                "font_underline": cell.font.underline if cell.font else None,
                "horizontal_alignment": cell.alignment.horizontal if cell.alignment else None,
                "vertical_alignment": cell.alignment.vertical if cell.alignment else None,
                "wrap_text": cell.alignment.wrap_text if cell.alignment else None,
                "border_top": cell.border.top.style if cell.border else None,
                "border_right": cell.border.right.style if cell.border else None,
                "border_bottom": cell.border.bottom.style if cell.border else None,
                "border_left": cell.border.left.style if cell.border else None
            }
            row_data.append(cell_info)

            # 提取列宽
            col_letter = get_column_letter(cell.column)  # 将列索引转换为列字母
            if col_letter not in column_widths:
                column_widths[col_letter] = sheet.column_dimensions[col_letter].width

            # 提取行高
            if cell.row not in row_heights:
                row_heights[cell.row] = sheet.row_dimensions[cell.row].height

        if row_data:  # 只将有内容的行添加到数据中
            data.append(row_data)

    # 提取合并单元格信息
    for merged_cell in sheet.merged_cells.ranges:
        merged_cells.append(str(merged_cell))

    # 创建 Python 文件内容
    output_content = f"""# -*- coding: utf-8 -*-
import openpyxl
from openpyxl.styles import PatternFill, Font, Alignment, Border, Side

def recreate_excel(output_file):
    wb = openpyxl.Workbook()
    sheet = wb.active

    # 数据填充
    data = {data}  # 直接使用 Python 格式的字典和列表
    column_widths = {column_widths}  # 确保初始化 column_widths
    row_heights = {row_heights}  # 确保初始化 row_heights
    merged_cells = {merged_cells}  # 确保初始化 merged_cells

    for row_idx, row in enumerate(data, start={start_row}):
        for col_idx, cell_info in enumerate(row, start={start_col}):
            cell = sheet.cell(row=row_idx, column=col_idx)
            cell.value = cell_info.get("value")

            # 设置填充颜色
            fill_color = cell_info.get("fill_color")
            if fill_color and fill_color != "00000000":
                cell.fill = PatternFill(
                    start_color=fill_color, end_color=fill_color, fill_type="solid")

            # 设置字体
            font_size = cell_info.get("font_size")
            font_name = cell_info.get("font_name")
            font_bold = cell_info.get("font_bold")
            font_italic = cell_info.get("font_italic")
            font_underline = cell_info.get("font_underline")
            if font_size or font_name or font_bold or font_italic or font_underline:
                cell.font = Font(size=font_size, name=font_name, bold=font_bold,
                                 italic=font_italic, underline=font_underline)

            # 设置对齐方式
            horizontal_alignment = cell_info.get("horizontal_alignment")
            vertical_alignment = cell_info.get("vertical_alignment")
            wrap_text = cell_info.get("wrap_text")
            if horizontal_alignment or vertical_alignment or wrap_text is not None:
                cell.alignment = Alignment(
                    horizontal=horizontal_alignment, vertical=vertical_alignment, wrap_text=wrap_text)

            # 设置边框
            border_top = cell_info.get("border_top")
            border_right = cell_info.get("border_right")
            border_bottom = cell_info.get("border_bottom")
            border_left = cell_info.get("border_left")
            cell.border = Border(
                top=Side(style=border_top),
                right=Side(style=border_right),
                bottom=Side(style=border_bottom),
                left=Side(style=border_left)
            )

    # 设置列宽
    for col, width in column_widths.items():
        sheet.column_dimensions[col].width = width

    # 设置行高
    for row, height in row_heights.items():
        sheet.row_dimensions[row].height = height

    # 合并单元格
    for merged_range in merged_cells:
        sheet.merge_cells(merged_range)

    # 保存文件
    wb.save(output_file)

if __name__ == "__main__":
    recreate_excel("output.xlsx")
"""

    # 写入到新的 Python 文件
    with open(output_py_file, "w", encoding="utf-8") as f:
        f.write(output_content)


# 提取 Excel 文件并生成 Python 文件
extract_excel("多sku.xlsx", "recreate_excel.py", 1, 5, 1, 116)