Python图片转Excel后数据错位怎么办？基于坐标重构行列逻辑

一、为什么直接按坐标排序必然导致数据错位？

常规OCR引擎（例如 PaddleOCR、EasyOCR 或 Tesseract）在识别图片中的文字块时，返回的核心数据包括文本内容及其对应的四个顶点坐标（或最小外接矩形 [x_min, y_min, x_max, y_max]）。初学者最直接的做法通常是编写如下逻辑：

# 常见但存在严重缺陷的朴素排序方式
boxes.sort(key=lambda b: (b['y_min'], b['x_min']))

这种“先按垂直高度（Y）从小到大排列，再按水平位置（X）排列”的朴素假设，前提是“同一行所有单元格的顶端Y坐标完全一致”。然而，在真实的扫描件、手机翻拍图甚至正规PDF渲染图中，这一假设几乎从未成立过：

• 微小倾斜（Skewing）：哪怕整张表格仅倾斜了0.5度，在宽度800像素的表格中，最右侧单元格的垂直偏移量就可能达到 800 * sin(0.5°) ≈ 7px。
• 字体基线与字号差异：同一表格行内，加粗标题、纯数字与中文字符的检测边界框高度往往参差不齐。
• 检测边界随机抖动（Y-Jitter）：检测模型在输出像素边界时，相邻单元格常常存在1~4像素的上下浮动。

只要相邻两个单元格的 y_min 仅仅相差 2 个像素，简单的排序函数就会认定“Y较小者属于上一行，Y较大者属于下一行”。原本属于同一行的3个数据项（如“工号”、“姓名”、“得分”），就会被横向撕裂成连续的3个阶梯单行，生成大量充斥着空白单元格的残破表格。

二、基于垂直投影重叠度的行重构算法原理

要让计算机像人眼一样准确判断“哪些文字块同处一行”，判定准则必须从绝对坐标比对升级为几何区间的相对垂直重叠度（Vertical Overlap Ratio）。

设基准文本框 A 的垂直区间为 [y1_A, y2_A]，高度为 h_A = y2_A - y1_A；待判定的相邻文本框 B 的垂直区间为 [y1_B, y2_B]，高度为 h_B = y2_B - y1_B。它们在Y轴上的有效交集高度 Δy 计算如下：

# 计算两个几何区间在垂直方向的交集长度
overlap_y = max(0, min(y2_A, y2_B) - max(y1_A, y1_B))

为了消除大字号单元格对小字号单元格尺度的干扰，通常以两者中较小的高度作为分母，定义垂直重叠比例：

overlap_ratio = overlap_y / min(h_A, h_B)

当 overlap_ratio >= 0.5（即交集高度达到较矮文本框高度的一半以上）时，即认定文本框 A 与 B 在视觉上属于同一物理行。此时，将 B 合并入 A 所在的行容器中，并动态扩展该行的包围盒：

# 动态更新当前行的整体纵向包围盒
row_y1 = min(row_y1, y1_B)
row_y2 = max(row_y2, y2_B)

若重叠比例低于设定阈值，则说明已经跨入视觉上的下一行，算法将闭合当前行，并以文本框 B 作为新行的起始锚点。

三、全流程操作步骤：从OCR结果到规整网格化

仅完成“行归类”还不足以直接输出到Excel，因为若表格中某一行存在空单元格（例如某员工无固定电话），直接按读取到的单元格依次填入表格，会导致该行后面的数据全部向左位移串列。完整的行列重构包含四个关键步骤：

1. 步骤一：数据标准化与初序预排。将各类OCR工具返回的多边形（4个点坐标）统一计算为外接矩形 [x_min, y_min, x_max, y_max]，并根据 y_min 进行一次粗排序，保证后续流式处理由上至下进行。
2. 步骤二：垂直重叠动态行聚类。依次遍历排好序的元素，应用垂直重叠度计算将元素逐一装入所属行；行划分完成后，对每一行内部的元素按照 x_min 从小到大升序排序，确保文本保持由左至右的阅读顺序。
3. 步骤三：全局列锚点推导与槽位对齐。统计全表中所有单元格的水平中心点或边界分布，利用一维聚类得到整张表格的全局标准列区间（Column Slots）。对于每一行，根据单元格的横向坐标将其精准放入对应的列槽位中。若某个槽位没有元素落入，则自动填充为空字符串 ""。
4. 步骤四：构建规整二维矩阵并导出。将填充好空缺的规则 M × N 矩阵转换为 pandas.DataFrame，直接调用 to_excel() 导出为具备正确行列关系的 .xlsx 文件。

四、完整可执行的Python实现代码

以下提供生产级别的纯Python实现代码。本段代码无需依赖重型深度学习库，仅基于内置模块与数据处理库，内置了一套带有微小抖动与空缺单元格的模拟OCR数据，可直接在本地运行验证。

import pandas as pd

def calculate_vertical_overlap_ratio(box_a, box_b):
    """
    计算两个包围盒在垂直方向的重叠比例
    包围盒格式: [x_min, y_min, x_max, y_max]
    """
    _, y1_a, _, y2_a = box_a
    _, y1_b, _, y2_b = box_b
    
    h_a = max(1.0, y2_a - y1_a)
    h_b = max(1.0, y2_b - y1_b)
    
    # 垂直交集
    overlap_y = max(0.0, min(y2_a, y2_b) - max(y1_a, y1_b))
    return overlap_y / min(h_a, h_b)

def cluster_rows(ocr_items, overlap_threshold=0.5):
    """
    步骤1与2: 基于垂直重叠度进行动态行聚类，并在行内按X升序排序
    """
    if not ocr_items:
        return []
    
    # 按照 y_min 进行初始升序排列
    sorted_items = sorted(ocr_items, key=lambda item: item['bbox'][1])
    
    rows = []
    current_row = [sorted_items[0]]
    # 记录当前行整体的 y 范围
    row_y1 = sorted_items[0]['bbox'][1]
    row_y2 = sorted_items[0]['bbox'][3]
    
    for item in sorted_items[1:]:
        item_box = item['bbox']
        # 计算当前元素与当前行整体包围盒的垂直重叠度
        overlap = calculate_vertical_overlap_ratio([0, row_y1, 0, row_y2], item_box)
        
        if overlap >= overlap_threshold:
            # 判定为同一行，加入当前行
            current_row.append(item)
            # 动态外扩当前行的垂直边界
            row_y1 = min(row_y1, item_box[1])
            row_y2 = max(row_y2, item_box[3])
        else:
            # 开启新的一行，先将上一行按 x_min 排序保存
            current_row.sort(key=lambda x: x['bbox'][0])
            rows.append(current_row)
            current_row = [item]
            row_y1 = item_box[1]
            row_y2 = item_box[3]
            
    if current_row:
        current_row.sort(key=lambda x: x['bbox'][0])
        rows.append(current_row)
        
    return rows

def align_columns_and_build_grid(rows, col_tolerance=30):
    """
    步骤3: 全局列锚点检测与跨行对齐，解决空单元格串行问题
    """
    if not rows:
        return []
    
    # 1. 收集全表中所有文本框的水平中心点 x_center
    all_x_centers = []
    for row in rows:
        for item in row:
            x_min, _, x_max, _ = item['bbox']
            all_x_centers.append((x_min + x_max) / 2.0)
            
    all_x_centers.sort()
    
    # 2. 对 X 中心点进行一维近邻聚类，推导全局列锚点 (Column Anchors)
    col_anchors = []
    for x in all_x_centers:
        matched = False
        for i, anchor in enumerate(col_anchors):
            if abs(anchor - x) <= col_tolerance:
                # 累积平均更新锚点位置
                col_anchors[i] = (anchor + x) / 2.0
                matched = True
                break
        if not matched:
            col_anchors.append(x)
            
    col_anchors.sort()
    num_cols = len(col_anchors)
    
    # 3. 将各行单元格对齐到最近的列槽位中
    grid = []
    for row in rows:
        grid_row = [""] * num_cols
        for item in row:
            x_center = (item['bbox'][0] + item['bbox'][2]) / 2.0
            # 找到最近的列索引
            best_col_idx = min(range(num_cols), key=lambda idx: abs(col_anchors[idx] - x_center))
            
            # 若同一槽位已存在内容（如单元格内换行），进行换行拼接
            if grid_row[best_col_idx]:
                grid_row[best_col_idx] += "\n" + item['text']
            else:
                grid_row[best_col_idx] = item['text']
                
        grid.append(grid_row)
        
    return grid

# --- 验证示例与自测执行 ---
if __name__ == "__main__":
    # 模拟包含微小抖动(±4px)与缺失空项的OCR识别结果
    sample_ocr_output = [
        # 表头 (Header): Y 在 100 附近微幅波动
        {"text": "工号", "bbox": [50, 102, 110, 128]},
        {"text": "姓名", "bbox": [150, 98, 210, 126]},
        {"text": "部门", "bbox": [250, 100, 320, 127]},
        {"text": "绩效得分", "bbox": [360, 104, 430, 129]},
        
        # 第1行: 张伟 (完整数据)
        {"text": "1001", "bbox": [52, 148, 108, 172]},
        {"text": "张伟", "bbox": [151, 151, 208, 175]},
        {"text": "技术部", "bbox": [249, 149, 318, 174]},
        {"text": "95.5", "bbox": [362, 147, 410, 173]},
        
        # 第2行: 李强 (注意: 缺失部门字段，存在空单元格)
        {"text": "1002", "bbox": [50, 192, 109, 218]},
        {"text": "李强", "bbox": [152, 189, 211, 216]},
        # 部门为空，直接到绩效得分
        {"text": "88.0", "bbox": [361, 194, 412, 219]},
    ]

    # 1. 行聚类
    clustered_rows = cluster_rows(sample_ocr_output, overlap_threshold=0.5)
    
    # 2. 列对齐并构建规整网格
    table_grid = align_columns_and_build_grid(clustered_rows, col_tolerance=35)
    
    # 3. 转换为 DataFrame 验证
    df = pd.DataFrame(table_grid[1:], columns=table_grid[0])
    print("--- 重构后的规整表格 ---")
    print(df.to_string(index=False))
    
    # 4. 导出为 Excel 文件
    # df.to_excel("reconstructed_table.xlsx", index=False)

测试输出效果对照

运行上述脚本后，终端打印的结构化表格如下所示：

可以看到，即使原始坐标存在上下浮动，算法依然准确将4个字段聚入表头；而对于第二行缺失“部门”的情况，李强的“88.0”分被精准对齐至第4列“绩效得分”，完全避免了传统粗暴拼接导致的“88.0被填入部门列”的左坍塌错误。

五、真实场景中的常见陷阱与优化建议

在实际生产业务中，除了基本的坐标抖动外，还会遇到以下三类典型干扰：

1. 单元格内多行长文本拆行

当单元格内容较长时（如“备注说明：货物已于昨日清关出库”），OCR引擎往往会将其识别为上下两个独立的文本框。若直接按行聚类，会导致这一行的高度被异常拉大，甚至被误判为两行表格。

应对策略：在完成初步行聚类前，增加一步同列垂直合并预处理：若两个文本框在水平方向的重叠度超过 80%，且垂直间距小于文字平均行高（通常为 0.3 * height），判定其同属一个单元格，将其拼接为带 \n 的单文本框，并合并包围盒。

2. 图像全局旋转与梯形形变

重叠度算法的前提是表格行基本处于水平方位。如果图片存在明显倾斜（超过 2 度），整行左端与右端的垂直落差可能超过一个单元格的高度，此时单纯依赖垂直投影会失效。

应对策略：在输入重构算法前，必须使用 OpenCV 进行水平倾角校正（Deskew）：利用霍夫直线变换（HoughLinesP）提取表格横线的主偏转角 θ，或者调用文本方向检测模型对全图执行仿射旋转校正。

3. 合并单元格与跨列居中

对于包含合并单元格（如跨多列的综合大标题、汇总行）的复杂表格，单元格的宽度明显大于标准列宽中位数。

应对策略：计算当前单元格宽度与标准列宽的比例 width / median_col_width。若跨越多个全局列锚点区间，应记录其起始列与跨越列数（Colspan），在写入 Excel 时通过 openpyxl.worksheet.merge_cells() 完成物理合并，其余覆盖列填入占位符，保持底层数据拓扑严整。