59 lines
2.3 KiB
Python
59 lines
2.3 KiB
Python
# utils/excel_utils.py
|
||
import re
|
||
from openpyxl.styles import Font, Alignment, Border, Side
|
||
from openpyxl.utils import get_column_letter
|
||
from openpyxl.worksheet.worksheet import Worksheet
|
||
|
||
class ExcelStyleUtils:
|
||
"""Excel样式工具类"""
|
||
@staticmethod
|
||
def set_style(
|
||
ws: Worksheet,
|
||
cell_range: str,
|
||
font: Font=Font(name='宋体', size=11),
|
||
align: Alignment=Alignment(horizontal='center', vertical='center', wrap_text=True),
|
||
border: Border=Border(left=Side(style='thin'), right=Side(style='thin'),top=Side(style='thin'), bottom=Side(style='thin'))):
|
||
"""设置单元格样式"""
|
||
if cell_range:
|
||
for row in ws[cell_range]:
|
||
for cell in row:
|
||
cell.font = font
|
||
cell.alignment = align
|
||
cell.border = border
|
||
|
||
@staticmethod
|
||
def auto_adjust_column_width(ws: Worksheet):
|
||
"""自动调整列宽"""
|
||
dims = {}
|
||
for row in ws.rows:
|
||
for cell in row:
|
||
if cell.value:
|
||
merged_range = next((range for range in ws.merged_cells.ranges if cell.coordinate in range), None)
|
||
if get_merge_type(merged_range) == 'column':
|
||
continue
|
||
cell_len = 0.7 * len(re.findall('([\u4e00-\u9fa5])', str(cell.value))) + len(str(cell.value))
|
||
dims[cell.column] = max(dims.get(cell.column, 0), cell_len)
|
||
# 设置列宽
|
||
for col, value in dims.items():
|
||
ws.column_dimensions[get_column_letter(int(col))].width = value + 5
|
||
|
||
# 判断单元格类型
|
||
def get_merge_type(merged_range):
|
||
"""
|
||
判断合并类型
|
||
返回: 'row'（行合并）, 'column'（列合并）, 'both'（行列合并）或 None（不是合并单元格）
|
||
"""
|
||
if not merged_range:
|
||
return None
|
||
|
||
min_row, max_row = merged_range.min_row, merged_range.max_row
|
||
min_col, max_col = merged_range.min_col, merged_range.max_col
|
||
|
||
if max_row > min_row and max_col > min_col:
|
||
return 'both' # 同时跨行和跨列
|
||
elif max_row > min_row:
|
||
return 'row' # 行合并（垂直合并）
|
||
elif max_col > min_col:
|
||
return 'column' # 列合并（水平合并）
|
||
else:
|
||
return None # 实际上不是合并单元格 |