485 lines
15 KiB
Python
485 lines
15 KiB
Python
import os
|
||
|
||
from openpyxl import load_workbook
|
||
from openpyxl.cell import MergedCell
|
||
from openpyxl.cell.cell import Cell
|
||
from openpyxl.styles import Alignment
|
||
from openpyxl.utils import get_column_letter
|
||
from openpyxl.utils.cell import range_boundaries
|
||
from openpyxl.worksheet import worksheet
|
||
from openpyxl.worksheet.merge import MergedCellRange
|
||
from PIL import ImageFont
|
||
|
||
try:
|
||
import win32com.client
|
||
except ImportError:
|
||
win32com = None
|
||
|
||
# openpyxl 未设置时的默认列宽/行高
|
||
DEFAULT_COLUMN_WIDTH = 8.43
|
||
DEFAULT_ROW_HEIGHT_PT = 15.0
|
||
MAX_HEIGHT_PT = 825.0
|
||
MAX_WIDTH_PT = 1702.0
|
||
|
||
|
||
def get_char_size(
|
||
|
||
font_path: str, font_size_pt: float, char: str = "中"
|
||
|
||
) -> tuple[float, float]:
|
||
"""返回指定字体和字号下单个字符的宽度和高度，单位为磅(pt)。"""
|
||
dpi = 96
|
||
pixel_size = max(1, round(font_size_pt * dpi / 72))
|
||
|
||
# 如果不是完整路径，尝试在 Windows 字体目录中查找
|
||
if not os.path.isabs(font_path):
|
||
windows_fonts_dir = "C:\\Windows\\Fonts\\"
|
||
full_path = os.path.join(windows_fonts_dir, font_path)
|
||
if os.path.exists(full_path):
|
||
font_path = full_path
|
||
|
||
font = ImageFont.truetype(font_path, pixel_size)
|
||
bbox = font.getbbox(char)
|
||
if bbox is None:
|
||
return 0.0, 0.0
|
||
|
||
width_px = bbox[2] - bbox[0]
|
||
height_px = bbox[3] - bbox[1]
|
||
width_pt = width_px * 72.0 / dpi
|
||
height_pt = height_px * 72.0 / dpi
|
||
return width_pt, height_pt
|
||
|
||
|
||
def base_width():
|
||
return get_char_size("Calibri.ttf", 11.0, "0")[0]
|
||
|
||
|
||
# 宋体字符宽度规则
|
||
def char_width_pt(c: str, base_pt: float = 8.0) -> float:
|
||
if "\u4e00" <= c <= "\u9fff":
|
||
return base_pt
|
||
return 0.5 * base_pt
|
||
|
||
|
||
def is_chinese_char(c: str) -> bool:
|
||
return "\u4e00" <= c <= "\u9fff"
|
||
|
||
|
||
def is_ascii_alnum(c: str) -> bool:
|
||
return c.isascii() and (c.isalnum() or c == ".")
|
||
|
||
|
||
# 这些符号通常不应出现在折行后的行首，倾向于“粘在前一个词后面”
|
||
NON_LEADING_PUNCT = {
|
||
")",
|
||
"]",
|
||
"}",
|
||
"）",
|
||
"】",
|
||
"》",
|
||
"〉",
|
||
"”",
|
||
"’",
|
||
"，",
|
||
"。",
|
||
"、",
|
||
",",
|
||
".",
|
||
";",
|
||
":",
|
||
"!",
|
||
"?",
|
||
}
|
||
|
||
|
||
def iter_words(line_txt: str) -> list[str]:
|
||
"""把一行文本切成“单词”数组。
|
||
|
||
规则：
|
||
- 一个汉字算一个单词
|
||
- 连续的数字或字母（ASCII alnum）算一个单词
|
||
- 其他字符（空格/标点等）按单字符单词处理
|
||
"""
|
||
|
||
words: list[str] = []
|
||
i = 0
|
||
n = len(line_txt)
|
||
while i < n:
|
||
c = line_txt[i]
|
||
|
||
# 右括号/收尾标点尽量不单独成词，避免被折到新行行首
|
||
if c in NON_LEADING_PUNCT and words:
|
||
words[-1] += c
|
||
i += 1
|
||
continue
|
||
|
||
if is_chinese_char(c):
|
||
words.append(c)
|
||
i += 1
|
||
continue
|
||
|
||
if is_ascii_alnum(c):
|
||
j = i + 1
|
||
while j < n and is_ascii_alnum(line_txt[j]):
|
||
j += 1
|
||
words.append(line_txt[i:j])
|
||
i = j
|
||
continue
|
||
|
||
words.append(c)
|
||
i += 1
|
||
|
||
# print(" | ".join(words))
|
||
return words
|
||
|
||
|
||
def wrap_line_words(line_txt: str, max_width_pt: float) -> list[str]:
|
||
"""把一行文本按最大宽度折行，返回折行后的每一行内容。"""
|
||
|
||
if not line_txt.strip():
|
||
return [line_txt]
|
||
|
||
words = iter_words(line_txt)
|
||
wrapped_lines: list[str] = []
|
||
|
||
curr_line = ""
|
||
curr_width = 0.0
|
||
|
||
def flush_line() -> None:
|
||
nonlocal curr_line, curr_width
|
||
if curr_line != "":
|
||
wrapped_lines.append(curr_line)
|
||
curr_line = ""
|
||
curr_width = 0.0
|
||
|
||
def append_char(ch: str) -> None:
|
||
nonlocal curr_line, curr_width
|
||
ch_w = char_width_pt(ch)
|
||
if curr_line and (curr_width + ch_w > max_width_pt):
|
||
flush_line()
|
||
curr_line += ch
|
||
curr_width += ch_w
|
||
|
||
for word in words:
|
||
word_w = sum(char_width_pt(c) for c in word)
|
||
|
||
# 单词本身就超过最大宽度：退化到逐字符切分
|
||
if word_w > max_width_pt:
|
||
for ch in word:
|
||
append_char(ch)
|
||
continue
|
||
|
||
if curr_line and (curr_width + word_w > max_width_pt):
|
||
flush_line()
|
||
|
||
curr_line += word
|
||
curr_width += word_w
|
||
|
||
flush_line()
|
||
return wrapped_lines or [""]
|
||
|
||
|
||
# ------------- 获取合并/普通单元格 真实可用列宽（原生列宽单位） -------------
|
||
def cell_available_col_width(cell: Cell) -> float:
|
||
sheet: worksheet.Worksheet = cell.parent # pyright: ignore[reportAssignmentType]
|
||
|
||
target: list[MergedCellRange] = list(
|
||
filter(lambda x: cell.coordinate in x, sheet.merged_cells.ranges)
|
||
)
|
||
if target and len(target) > 0:
|
||
cell_range = target[0]
|
||
return sum(
|
||
sheet.column_dimensions[get_column_letter(col)].width
|
||
if row == cell.row
|
||
else 0
|
||
for (row, col) in cell_range.cells
|
||
)
|
||
|
||
# 普通单元格
|
||
col_idx = cell.column
|
||
col_letter = get_column_letter(col_idx)
|
||
return sheet.column_dimensions[col_letter].width or DEFAULT_COLUMN_WIDTH # pyright: ignore[reportAttributeAccessIssue]
|
||
|
||
|
||
# ------------- 获取合并/普通单元格 真实总高度（pt） -------------
|
||
def cell_total_height_pt(cell: Cell) -> float:
|
||
sheet = cell.parent
|
||
row_idx = cell.row
|
||
|
||
for merged_range in sheet.merged_cells.ranges: # pyright: ignore[reportAttributeAccessIssue]
|
||
_, min_row, _, max_row = merged_range.bounds
|
||
if min_row <= row_idx <= max_row:
|
||
total_h = 0.0
|
||
for r in range(min_row, max_row + 1):
|
||
row_height = sheet.row_dimensions[r].height or DEFAULT_ROW_HEIGHT_PT # pyright: ignore[reportAttributeAccessIssue]
|
||
total_h += row_height
|
||
return total_h
|
||
|
||
return sheet.row_dimensions[row_idx].height or DEFAULT_ROW_HEIGHT_PT # pyright: ignore[reportAttributeAccessIssue]
|
||
|
||
|
||
# ------------- 计算单行文本在指定宽度下折成几行 -------------
|
||
def wrap_line_count(line_txt: str, max_width_pt: float) -> int:
|
||
return len(wrap_line_words(line_txt, max_width_pt))
|
||
|
||
|
||
def can_fit_chinese_chars(col_width_chars: float, zh_font_pt: float) -> int:
|
||
"""
|
||
根据Excel列宽值（字符单位），计算在宋体8号下能容纳多少个汉字
|
||
像素 = 点 × (DPI / 72)
|
||
px = pt * dpi / 72
|
||
pt = px * 72 / dpi
|
||
基准字符宽度：Calibri 11pt '0' = 5.83
|
||
"""
|
||
return int((col_width_chars - 0.5) * base_width() / zh_font_pt)
|
||
|
||
|
||
def default_row_height_pt(sheet) -> float:
|
||
"""获取 sheet 默认行高（pt）。"""
|
||
|
||
return float(
|
||
getattr(getattr(sheet, "sheet_format", None), "defaultRowHeight", None)
|
||
or DEFAULT_ROW_HEIGHT_PT
|
||
)
|
||
|
||
|
||
def set_rows_height_for_hanzi(
|
||
sheet,
|
||
row_idxs: tuple[int, ...],
|
||
hanzi_count: int = 1,
|
||
zh_font_pt: float = 8.0,
|
||
) -> None:
|
||
"""设置指定行高，使其仅能容纳指定数量的汉字高度。"""
|
||
|
||
row_height_pt = hanzi_count * zh_font_pt
|
||
for row_idx in row_idxs:
|
||
sheet.row_dimensions[row_idx].height = row_height_pt
|
||
|
||
|
||
def set_column_width_for_hanzi(
|
||
sheet,
|
||
column_letters: tuple[str, ...],
|
||
hanzi_count: int = 1,
|
||
zh_font_pt: float = 8.0,
|
||
) -> None:
|
||
"""设置指定列宽，使其仅能容纳指定数量的汉字宽度。"""
|
||
han_width = get_char_size("simsun.ttc", zh_font_pt)[0]
|
||
width = hanzi_count * han_width / base_width()
|
||
|
||
for column_letter in column_letters:
|
||
print(column_letter, "设置宽度：", width)
|
||
sheet.column_dimensions[column_letter].width = width + 0.5
|
||
sheet.column_dimensions[column_letter].bestFit = True
|
||
sheet.column_dimensions[column_letter].hidden = False
|
||
|
||
|
||
def parse_print_area(print_area_str: str | None) -> list[str]:
|
||
"""解析打印区域字符串，移除工作表前缀，返回区域列表"""
|
||
if not print_area_str:
|
||
return []
|
||
areas = str(print_area_str).split()
|
||
return [area.split("!", 1)[-1] if "!" in area else area for area in areas]
|
||
|
||
|
||
def get_print_area(sheet: worksheet.Worksheet) -> str:
|
||
"""
|
||
获取打印区域的单元格范围
|
||
|
||
返回打印区域的字符串表示，如 "A1:U4"。
|
||
如果未设置打印区域，则返回工作表使用的最大区域。
|
||
"""
|
||
print_area = getattr(sheet, "print_area", None)
|
||
areas = parse_print_area(print_area)
|
||
if areas:
|
||
return areas[0]
|
||
return sheet.calculate_dimension()
|
||
|
||
|
||
def print_area_size_landscape(sheet) -> tuple[float, float]:
|
||
"""计算打印区域尺寸（横向），返回 (宽度pt, 高度pt)。
|
||
|
||
优先使用 sheet.print_area；未设置时使用 sheet.calculate_dimension()。
|
||
宽度按列宽 * BASE_WIDTH 估算为 pt，高度按行高 pt 累加。
|
||
"""
|
||
|
||
print_area = getattr(sheet, "print_area", None)
|
||
areas = parse_print_area(print_area) or [sheet.calculate_dimension()]
|
||
|
||
min_col = 10**9
|
||
min_row = 10**9
|
||
max_col = 0
|
||
max_row = 0
|
||
|
||
for area in areas:
|
||
left_col, top_row, right_col, bottom_row = range_boundaries(area)
|
||
if (
|
||
left_col is None
|
||
or top_row is None
|
||
or right_col is None
|
||
or bottom_row is None
|
||
):
|
||
continue
|
||
min_col = min(min_col, left_col)
|
||
min_row = min(min_row, top_row)
|
||
max_col = max(max_col, right_col)
|
||
max_row = max(max_row, bottom_row)
|
||
|
||
if max_col == 0 or max_row == 0:
|
||
return 0.0, 0.0
|
||
|
||
width_pt = 0.0
|
||
for col_idx in range(min_col, max_col + 1):
|
||
col_letter = get_column_letter(col_idx)
|
||
col_width = sheet.column_dimensions[col_letter].width or DEFAULT_COLUMN_WIDTH # pyright: ignore[reportAttributeAccessIssue]
|
||
width_pt += col_width * base_width()
|
||
|
||
height_pt = 0.0
|
||
default_height = default_row_height_pt(sheet)
|
||
for row_idx in range(min_row, max_row + 1):
|
||
height_pt += sheet.row_dimensions[row_idx].height or default_height # pyright: ignore[reportAttributeAccessIssue]
|
||
|
||
return width_pt, height_pt
|
||
|
||
|
||
# ------------- 主函数：判断单元格内容展示/打印是否溢出 -------------
|
||
def is_cell_content_overflow(cell: Cell) -> bool:
|
||
if not cell.value:
|
||
return False
|
||
|
||
text = str(cell.value).replace("\r", "")
|
||
align = cell.alignment
|
||
|
||
# 缩小字体填充 直接不溢出
|
||
if align.shrink_to_fit:
|
||
return False
|
||
|
||
# 1. 拿到真实可用列宽、单元格总高度(pt)
|
||
avail_col_width = cell_available_col_width(cell)
|
||
chinese_capacity = can_fit_chinese_chars(avail_col_width, 8.0)
|
||
print("列宽为", avail_col_width, "可容纳汉字", chinese_capacity, "个")
|
||
cell_height_pt = cell_total_height_pt(cell)
|
||
# print("高度：", cell_height_pt)
|
||
|
||
# 2. 不开自动换行：只判宽度溢出
|
||
if not align.wrap_text:
|
||
total_w = sum(char_width_pt(c) for c in text)
|
||
return total_w > avail_col_width
|
||
|
||
# 3. 开自动换行：先按 \n 拆分，再逐行自动折行
|
||
lines = text.split("\n")
|
||
total_wrap_lines = 0
|
||
for line in lines:
|
||
wrapped = wrap_line_words(line, avail_col_width * base_width())
|
||
print(line, "->", len(wrapped), "行")
|
||
for i, wrapped_line in enumerate(wrapped, start=1):
|
||
print(f" {i:02d}: {wrapped_line}")
|
||
total_wrap_lines += len(wrapped)
|
||
|
||
# 文本总所需高度 pt
|
||
# 注意：font_size 不是“行高”。这里改为用 sheet 默认行高来估算每一行换行文本的占用高度。
|
||
required_height_pt = total_wrap_lines * default_row_height_pt(cell.parent)
|
||
# 超出单元格高度即溢出
|
||
return required_height_pt > cell_height_pt
|
||
|
||
|
||
def print_excel_file(
|
||
file_path: str,
|
||
printer_name: str | None = None,
|
||
copies: int = 1,
|
||
preview: bool = False,
|
||
) -> None:
|
||
"""使用 Excel COM 打印指定文件。"""
|
||
if win32com is None:
|
||
raise ImportError(
|
||
"win32com.client 未安装，无法通过 Excel 执行打印。请安装 pywin32 或使用 pip install pywin32"
|
||
)
|
||
|
||
abs_path = os.path.abspath(file_path)
|
||
excel = win32com.client.Dispatch("Excel.Application")
|
||
excel.Visible = False
|
||
wb = excel.Workbooks.Open(abs_path)
|
||
try:
|
||
if printer_name:
|
||
excel.ActivePrinter = printer_name
|
||
wb.PrintOut(Preview=preview, Copies=copies)
|
||
finally:
|
||
wb.Close(SaveChanges=False)
|
||
excel.Quit()
|
||
|
||
|
||
def fix_alignment(sheet: worksheet.Worksheet):
|
||
area = get_print_area(sheet)
|
||
dims = area.split(":")
|
||
|
||
for row in sheet[dims[0] : dims[1]]:
|
||
for cell in row:
|
||
# if isinstance(cell, MergedCell):
|
||
# continue
|
||
if not isinstance(cell.value, str):
|
||
continue
|
||
|
||
avail_col_width = cell_available_col_width(cell)
|
||
print("处理对齐", cell.coordinate, "宽度", avail_col_width)
|
||
chinese_capacity = can_fit_chinese_chars(avail_col_width, 8.0)
|
||
if chinese_capacity == 1:
|
||
print(
|
||
"单元格",
|
||
cell.coordinate,
|
||
"只能容纳1个汉字",
|
||
cell.value,
|
||
avail_col_width,
|
||
)
|
||
cell.alignment = Alignment(
|
||
horizontal="left", vertical="center", wrapText=True
|
||
)
|
||
cell.value = str(cell.value).replace("\r", "").replace("\n", "").strip()
|
||
else:
|
||
cell.alignment = Alignment(
|
||
horizontal="center", vertical="center", wrapText=True
|
||
)
|
||
|
||
|
||
# ===================== 使用示例 =====================
|
||
if __name__ == "__main__":
|
||
# 加载你的Excel文件
|
||
wb = load_workbook("测试.xlsx")
|
||
sheet = wb.active
|
||
if sheet is None:
|
||
raise ValueError("无法获取工作表")
|
||
# print(sheet.merged_cells)
|
||
|
||
pa = get_print_area(sheet)
|
||
print("打印区域范围：", pa)
|
||
set_rows_height_for_hanzi(sheet, (1,), 1, 12)
|
||
set_rows_height_for_hanzi(sheet, (2,), 3, 12)
|
||
set_rows_height_for_hanzi(sheet, (4, 5), 30)
|
||
cols_width_1hanzi = "ABCDEFGHIJKLMNOPQRSTU"
|
||
|
||
set_column_width_for_hanzi(sheet, tuple((x for x in cols_width_1hanzi)), 1)
|
||
|
||
print_area_width_pt, print_area_height_pt = print_area_size_landscape(sheet)
|
||
print("打印区域（横向）宽度：", print_area_width_pt)
|
||
print("打印区域（横向）高度：", print_area_height_pt)
|
||
#
|
||
# # 测试：普通单元格 / 合并单元格 都可以
|
||
# test_cells = ws["A4":"U4"]
|
||
# for row in test_cells:
|
||
# for cell in row:
|
||
# overflow = is_cell_content_overflow(cell)
|
||
# print(f"单元格 {cell.coordinate}:")
|
||
# print(f" 内容：{cell.value}")
|
||
# print(f" 是否溢出：{overflow}")
|
||
fix_alignment(sheet)
|
||
output_path = "测试_结果.xlsx"
|
||
wb.save(output_path)
|
||
#
|
||
# # 调用 Excel 打印的能力打印修改后的文件
|
||
# # try:
|
||
# # print_excel_file(output_path)
|
||
# # print("已发送打印任务到 Excel。")
|
||
# # except Exception as exc:
|
||
# # print("Excel 打印失败：", exc)
|
||
# print("Calibri 11pt 0", get_char_size("Calibri.ttf", 11, "0"))
|
||
# print("simsun.ttc 8pt 0", get_char_size("simsun.ttc", 8, "0"))
|
||
# print("simsun 8pt 中", get_char_size("simsun.ttc", 8, "中"))
|