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)
06-10
4534
