"""Excel操作工具类"""
import os
from typing import List
from openpyxl import Workbook
from openpyxl.worksheet.worksheet import Worksheet
from openpyxl.comments import Comment
from ..models import DataStruct
class ExcelHelper:
"""Excel文件操作工具类"""
def create_workbook(self) -> Workbook:
"""创建新的Excel工作簿"""
workbook = Workbook()
# 删除默认创建的sheet
if 'Sheet' in workbook.sheetnames:
workbook.remove(workbook['Sheet'])
# 创建新的worksheet
worksheet = workbook.create_sheet('Template')
return workbook
def add_headers(self, worksheet: Worksheet, headers: List[str]) -> None:
"""添加列标题行"""
for col_idx, header in enumerate(headers, start=1):
cell = worksheet.cell(row=1, column=col_idx)
cell.value = header
# 设置标题行样式
cell.font = cell.font.copy(bold=True)
def add_data_row(
self,
worksheet: Worksheet,
data_struct: DataStruct,
data_format: str,
row: int
) -> None:
"""添加数据行模板
对于有数据绑定的列，生成JXLS表达式
对于没有数据绑定的列（field和index都为空），保持空单元格
"""
for col_idx, field in enumerate(data_struct.dataFields, start=1):
cell = worksheet.cell(row=row, column=col_idx)
if data_format == "json":
# JSON格式: 如果有field则生成表达式，否则留空
if field.field:
cell.value = f"${{{data_struct.itemVariable}.{field.field}}}"
# 如果field为None，单元格保持为空
else: # array格式
# 数组格式: 如果有index则生成表达式，否则留空
if field.index is not None:
cell.value = f"${{{data_struct.itemVariable}[{field.index}]}}"
# 如果index为None，单元格保持为空
def add_comment_to_cell(
self,
worksheet: Worksheet,
row: int,
column: int,
comment_text: str
) -> None:
"""向单元格添加批注"""
cell = worksheet.cell(row=row, column=column)
comment = Comment(comment_text, "JXLS Generator")
cell.comment = comment
def save_workbook(self, workbook: Workbook, file_path: str) -> None:
"""保存工作簿"""
# 确保目录存在
directory = os.path.dirname(file_path)
if directory and not os.path.exists(directory):
os.makedirs(directory, exist_ok=True)
workbook.save(file_path)
def calculate_column_letter(self, column_index: int) -> str:
"""将列索引转换为Excel列字母"""
result = ""
while column_index > 0:
column_index -= 1
result = chr(column_index % 26 + ord('A')) + result
column_index //= 26
return result
def format_cell_reference(self, row: int, column: int) -> str:
"""格式化单元格引用（如A1, B2等）"""
column_letter = self.calculate_column_letter(column)
return f"{column_letter}{row}"
