71 lines
3.0 KiB
Python
71 lines
3.0 KiB
Python
#!/usr/bin/env python3
|
|
"""只读分析 xlsx 每个 sheet 的长内容单元格：按合并宽度+字号估算所需视觉行数与建议行高，
|
|
与当前行高对比，标出可能截断的行。不修改文件。
|
|
|
|
用法: python3 xlsx-rowheight-analyze.py <文件.xlsx> [最小字符阈值,默认80]
|
|
|
|
背景见 references/onlyoffice-xlsx-rowheight-rendering.md：
|
|
- 409.5/409.6pt 是 OnlyOffice 网页编辑器的 clamp 值，不是 xlsx 格式天花板
|
|
- openpyxl 从文件层可写 >409.5 且 x2t 引擎不 clamp
|
|
经验系数：中文每字≈2.1 宽度单位（西文≈1.05），每视觉行≈15.5pt(10pt字)。
|
|
"""
|
|
import sys, math
|
|
import openpyxl
|
|
from openpyxl.utils import get_column_letter, range_boundaries
|
|
|
|
DEFAULT_WIDTH = 8.43
|
|
|
|
def col_width(ws, col_letter):
|
|
dim = ws.column_dimensions.get(col_letter)
|
|
return dim.width if (dim and dim.width) else DEFAULT_WIDTH
|
|
|
|
def merged_info(ws, coord):
|
|
for m in ws.merged_cells.ranges:
|
|
if coord in m:
|
|
c0, r0, c1, r1 = range_boundaries(str(m))
|
|
total = sum(col_width(ws, get_column_letter(c)) for c in range(c0, c1 + 1))
|
|
return total, str(m)
|
|
col = ''.join(filter(str.isalpha, coord))
|
|
return col_width(ws, col), None
|
|
|
|
def estimate_height(text, total_width, font_sz):
|
|
cap = max(total_width, 1)
|
|
visual_rows = 0
|
|
for line in text.split("\n"):
|
|
w = sum((2.1 if ord(ch) > 0x2000 else 1.05) for ch in line)
|
|
visual_rows += max(1, math.ceil(w / cap))
|
|
per_row = 15.5 if (font_sz or 11) <= 11 else (font_sz * 1.4)
|
|
return visual_rows, math.ceil(visual_rows * per_row + 8)
|
|
|
|
def main():
|
|
if len(sys.argv) < 2:
|
|
print(__doc__); sys.exit(1)
|
|
path = sys.argv[1]
|
|
threshold = int(sys.argv[2]) if len(sys.argv) > 2 else 80
|
|
wb = openpyxl.load_workbook(path)
|
|
print(f"FILE: {path}\nSHEETS: {wb.sheetnames}\n")
|
|
for ws in wb.worksheets:
|
|
heights = {r: round(d.height, 1) for r, d in ws.row_dimensions.items() if d.height}
|
|
flagged = []
|
|
for row in ws.iter_rows():
|
|
for cell in row:
|
|
if cell.value and isinstance(cell.value, str) and len(cell.value) > threshold:
|
|
tw, mrange = merged_info(ws, cell.coordinate)
|
|
fsz = cell.font.sz or 11
|
|
vr, sug = estimate_height(cell.value, tw, fsz)
|
|
cur = heights.get(cell.row)
|
|
short = cur is not None and cur < sug
|
|
flagged.append((cell.coordinate, mrange, len(cell.value),
|
|
cell.value.count(chr(10)) + 1, round(tw, 1), vr, sug, cur, short))
|
|
if not flagged:
|
|
continue
|
|
print(f"===== {ws.title} (max_row={ws.max_row}) =====")
|
|
for co, mr, ln, ll, tw, vr, sug, cur, short in flagged:
|
|
mark = " ⚠️可能截断" if short else ""
|
|
print(f" {co} merge={mr} chars={ln} lines={ll} w={tw} "
|
|
f"=> est_rows={vr} SUGGEST={sug}pt current={cur}{mark}")
|
|
print()
|
|
|
|
if __name__ == "__main__":
|
|
main()
|