Excel merged cells have values only in the top-left corner; how should they be extracted?

This paper uses synthetic samples to demonstrate the processing method. The code has been verified using examples in an isolated environment; this does not guarantee effectiveness for all real files or products. Before processing important files, please retain the original copies and verify them against the boundaries and acceptance criteria outlined in this document. Why does reading a merged cell always return None? When using openpyxl to iterate through Excel row data, developers often encounter a typical issue: in the Excel application, cells A2:A5 clearly display the same department name, yet when read by the code, only cell A2 contains a value, while cells A3 through A5 are all None. This is not due to file corruption; rather, it stems from openpyxl’s underlying storage mechanism for merged cells. According to the official documentation, MergedCellRange Only the actual value and border information are saved in the top-left cell; all other positions are replaced with MergedCell object. These MergedCell The object itself does not carry business data; its value is set to None by default. If values are retrieved directly by coordinates without special handling, information will inevitably be lost. Therefore, before reading, it is necessary to explicitly “inherit” the values of merged cells to all their constituent sub-cells in order to restore the integrity of the business data. synthesis example: boundary between correct expansion and error handling To clearly illustrate the issue, we construct a synthetic worksheet entirely generated in memory, thereby avoiding reliance on external files. Suppose that the range B2:B4 is merged and its value is “R&D Department,” while C2:C4 correspond to the names of three employees. The correct extraction result should be three complete records, with each row’s department field set to “R&D Department.” If the code fails to handle merged ranges, the output will become [('研发部', '张三'), (None, '李四'), (None, '王五')] , which will cause association breaks during subsequent database entry or translation. Another common input error is merging a range that spans multiple columns while the top-left cell contains a null value, such as a merge from D2:E3 with an empty string. In this case, blindly filling would propagate the null value to all four cells, potentially masking the original data’s missingness. A reasonable handling strategy is: first check whether the top-left cell’s value is None or purely blank; if so, log a warning and skip the fill operation, preserving the original structure for manual review, rather than silently overwriting potential business markers with an empty string. For real-world projects, please prepare your own de-identified data. .xlsx sample, via load_workbook('your_file.xlsx') Replace the memory-building logic in the following text; do not use the raw production environment files directly. Memory-level Python implementation and assertion verification The following example uses openpyxl to construct a worksheet with merged cells in memory, demonstrating the safe expansion logic. Note: This code is for educational demonstration purposes only and has not been validated in a production environment; it must be run in an isolated environment where dependency versions are explicitly locked. If you need to process real files, please replace the loading method and ensure that the data has been anonymized. # 依赖: openpyxl (请在自己的环境中锁定版本) from openpyxl import Workbook from openpyxl.cell.cell import MergedCell def expand_merged_cells(ws): """将合并区域的值填充到所有子单元格，返回展开后的二维列表""" # 先收集所有合并区域及其左上角值 merge_map = {} for mcr in ws.merged_cells.ranges: top_left_value = ws.cell(mcr.min_row, mcr.min_col).value merge_map[(mcr.min_row, mcr.min_col, mcr.max_row, mcr.max_col)] = top_left_value rows = [] for row in ws.iter_rows(min_row=1, max_row=ws.max_row, max_col=ws.max_column): expanded_row = [] for cell in row: val = cell.value # 关键修正：MergedCell对象的value始终为None，必须通过范围判断继承值 if isinstance(cell, MergedCell): for (r1, c1, r2, c2), merged_val in merge_map.items(): if r1 <= cell.row <= r2 and c1 <= cell.column <= c2: val = merged_val break expanded_row.append(val) rows.append(expanded_row) return rows # 合成示例：B2:B4 合并为“研发部” wb = Workbook() ws = wb.active ws['A1'], ws['B1'], ws['C1'] = 'ID', 'Dept', 'Name' ws['A2'], ws['B2'], ws['C2'] = 1, '研发部', '张三' ws['A3'], ws['C3'] = 2, '李四' ws['A4'], ws['C4'] = 3, '王五' ws.merge_cells('B2:B4') result = expand_merged_cells(ws) assert result[1][1] == '研发部' # B2 assert result[2][1] == '研发部' # B3 应继承 assert result[3][1] == '研发部' # B4 应继承 assert result[1][2] == '张三' Acceptance Criteria and Manual Review Trigger Conditions Observation of the phenomenon processing decision Whether manual review is required has a non-empty value in the top-left corner of the merged region, and all subcells are None automatically populates this value across the entire region no merged region’s top-left corner is an empty string or None records a warning, retaining the original None status is merged region spans more than 10 rows or 5 columns Marked as an exceptionally large merge, automatic processing is paused. is Multiple overlapping merged regions exist on the same line throws a structured conflict error, terminating processing of this line is expands to a row where the number of fields differs from that of other rows Treated as structural damage, the original line is output for comparison. is Note: The thresholds mentioned above (such as 10 lines, 5 columns) are merely custom parameters for this synthesis example and do not represent industry standards. In actual projects, they should be recalibrated based on business tolerance levels. The code demonstration only verifies logical correctness; the end-to-end performance must also be evaluated in conjunction with the layout complexity of real-world documents. References and Next Steps openpyxl.worksheet.merge module documentation uses this checklist for document translation first determines which elements are text, which are formulas, and which are business codes, then proceeds with table translation. Post-translation verification not only checks whether the Chinese text has been correctly rendered into the target language, but also ensures that formulas, identifiers, and citation relationships have remained unchanged. OmniDoc (product entry point) provides an entry point for translating multi-format documents, and the official website highlights its OCR, terminology database, and bilingual alignment features. For teams that wish to start processing directly from files, this is a path worth evaluating with real-world samples. Please use de-identified files that you are authorized to upload, and verify the results against the checklist provided in this document; we do not claim that OmniDoc utilizes the open-source libraries or internal algorithms referenced herein, nor do we substitute examples for product performance testing. - 1 -