blocks|key|2501546|text|您可以在工作表上使用merged_cells.ranges+(merged_cell_ranges在2.5.0-b1+(2017-10-19)版本中已弃用，已更改为merged_cells.ranges)+(似乎找不到每行)，如下所示：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|2501547|from+openpyxl+import+load_workbook
wb+=+load_workbook(filename='a+file+name')
sheet_ranges+=+wb['Sheet1']

print(sheet_ranges.merged_cells.ranges)|code-block|syntax|javascript|2501548|entityMap|0|LINK|mutability|MUTABLE|url|http://openpyxl.readthedocs.io/en/stable/changes.html#b1-2017-10-19|1|http://openpyxl.readthedocs.io/en/stable/api/openpyxl.worksheet.worksheet.html#openpyxl.worksheet.worksheet.Worksheet.merged_cell_ranges^0|A|J|V|I|1E|L|0|2A|J|1|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@$9|V|A|W|B|C]|$9|X|A|Y|B|C]]|D|@$9|Z|A|10|1|11]|$9|12|A|13|1|14]]|E|$]]|$1|F|3|G|5|H|7|15|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|16|8|@]|D|@]|E|$]]]|L|$M|$5|N|O|P|E|$Q|R]]|S|$5|N|O|P|E|$Q|T]]]]

You can use <code>merged_cells.ranges</code> (<code>merged_cell_ranges</code> has been deprecated in version <a href="http://openpyxl.readthedocs.io/en/stable/changes.html#b1-2017-10-19" rel="noreferrer">2.5.0-b1 (2017-10-19)</a>, changed to <a href="http://openpyxl.readthedocs.io/en/stable/api/openpyxl.worksheet.worksheet.html#openpyxl.worksheet.worksheet.Worksheet.merged_cell_ranges" rel="noreferrer">merged_cells.ranges</a>) on the sheet (can't seem to find per row) like this:

<pre><code>from openpyxl import load_workbook
wb = load_workbook(filename='a file name')
sheet_ranges = wb['Sheet1']

print(sheet_ranges.merged_cells.ranges)
</code></pre>

blocks|key|6029236|text|要测试单个单元格是否合并，可以检查类(名称)：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|6029237|cell+=+sheet.cell(row=15,+column=14)
if+type(cell).__name__+==+'MergedCell':
++print("Oh+no,+the+cell+is+merged!")
else:
++print("This+cell+is+not+merged.")|code-block|syntax|javascript|6029238|要“取消合并”所有单元格，可以使用函数unmerge_cells|offset|length|style|CODE|6029239|for+items+in+sorted(sheet.merged_cell_ranges):
++print(items)
++sheet.unmerge_cells(str(items))|6029240|entityMap^0|0|0|J|D|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|S|8|@$I|T|J|U|K|L]]|9|@]|A|$]]|$1|M|3|N|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

To test if a single cell is merged or not you can check the class (name):

<pre><code>cell = sheet.cell(row=15, column=14)
if type(cell).__name__ == 'MergedCell':
 print("Oh no, the cell is merged!")
else:
 print("This cell is not merged.")
</code></pre>

To "unmerge" all cells you can use the function <code>unmerge_cells</code>

<pre><code>for items in sorted(sheet.merged_cell_ranges):
 print(items)
 sheet.unmerge_cells(str(items))
</code></pre>

blocks|key|6029260|text|为了测试是否合并了单个单元格，我像@A.Liu建议的那样循环遍历sheet.merged_cells.ranges。不幸的是，检查像@0x4a6f4672这样的单元格类型不再起作用。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|6029261|下面是一个函数，它向您展示了如何做到这一点。|6029262|def+testMerge(row,+column):
++++cell+=+sheet.cell(row,+column)
++++for+mergedCell+in+sheet.merged_cells.ranges:
++++++++if+(cell.coordinate+in+mergedCell):
++++++++++++return+True
++++return+False|code-block|syntax|javascript|6029263|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|M|8|@]|9|@]|A|$G|H]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

To test if a single cell is merged, I loop through sheet.merged_cells.ranges like @A. Lau suggests.
Unfortunately, checking the cell type like @0x4a6f4672 shows does not work any more.

Here is a function that shows you how to do this.

<pre><code>def testMerge(row, column):
 cell = sheet.cell(row, column)
 for mergedCell in sheet.merged_cells.ranges:
 if (cell.coordinate in mergedCell):
 return True
 return False
</code></pre>

blocks|key|6029275|text|这些都很有帮助(谢谢)，但当我在几个电子表格中使用这些方法时，它并没有将我期望的所有单元格都解开。我不得不对合并进行循环和restest测试，最终将它们全部完成。在我的例子中，它花了4次才让所有的东西都像预期的那样解开：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|6029276|++++mergedRanges+=+sheet_ranges.merged_cells.ranges
++++###+How+many+times+do+we+run+unmerge?
++++i=0
++++###+keep+testing+and+removing+ranges+until+they+are+all+actually+gone
++++while+mergedRanges:
++++++++for+entry+in+mergedRanges:
++++++++++++i%2B=1
++++++++++++print("++unMerging:+"+%2B+str(i)+%2B+":+"+%2Bstr(entry))
++++++++++++ws.unmerge_cells(str(entry))|code-block|syntax|javascript|6029277|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

These all helped (thanks), but when I used the approaches with a couple of spreadsheets, it wasn't unmerging all the cells I expected. I had to loop and restest for merges to finally get them all to complete. In my case, it took 4 passes to get everything to unmerge as expected:

<pre><code> mergedRanges = sheet_ranges.merged_cells.ranges
 ### How many times do we run unmerge?
 i=0
 ### keep testing and removing ranges until they are all actually gone
 while mergedRanges:
 for entry in mergedRanges:
 i+=1
 print(" unMerging: " + str(i) + ": " +str(entry))
 ws.unmerge_cells(str(entry))
</code></pre>

blocks|key|2501621|text|这个问题询问了如何检测合并的单元格并读取它们，但到目前为止，所提供的问题只涉及检测和取消合并。下面是一个函数，它返回单元格的逻辑值，即用户在合并后的单元格中看到的值：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2501622|import+sys
from+openpyxl+import+load_workbook
from+openpyxl.cell.cell+import+MergedCell


def+cell_value(sheet,+coord):
++cell+=+sheet[coord]
++if+not+isinstance(cell,+MergedCell):
++++return+cell.value

++#+"Oh+no,+the+cell+is+merged!"
++for+range+in+sheet.merged_cells.ranges:
++++if+coord+in+range:
++++++return+range.start_cell.value

++raise+AssertionError('Merged+cell+is+not+in+any+merge+range!')


workbook+=+load_workbook(sys.argv[1])
print(cell_value(workbook.active,+sys.argv[2]))|code-block|syntax|javascript|2501623|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

The question asks about detecting merged cells and reading them, but so far the provided questions only deal with detecting and unmerging. Here is a function which returns the logical value of the cell, the value that the user would see as contained on a merged cell:
<pre class="lang-py prettyprint-override"><code>import sys
from openpyxl import load_workbook
from openpyxl.cell.cell import MergedCell


def cell_value(sheet, coord):
 cell = sheet[coord]
 if not isinstance(cell, MergedCell):
 return cell.value

 # &quot;Oh no, the cell is merged!&quot;
 for range in sheet.merged_cells.ranges:
 if coord in range:
 return range.start_cell.value

 raise AssertionError('Merged cell is not in any merge range!')


workbook = load_workbook(sys.argv[1])
print(cell_value(workbook.active, sys.argv[2]))
</code></pre>

I'm trying to read data from an Excel sheet that contains merged cells.
When reading merged cells with openpyxl the first merged cell contain the value and the rest of the cells are empty.
I would like to know about each cell if it is merged and how many cells are merged but I couldn't find any function that does so.
The sheet have empty others cells, so I can't use that.

How to detect merged cells in an Excel sheet?

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云智能顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

聚焦“写作效率、视觉美观与运行性能”三方面进行全面升级，为您提供更高效、稳定的创作环境

社区富文本&Markdown编辑器全新改版上线，欢迎大家体验!

诚挚邀请您参与本次调研，分享您的真实使用感受与建议。您的反馈至关重要，感谢您的支持与参与！

社区新版编辑器体验调研

我正在尝试从包含合并单元格的Excel工作表中读取数据。当使用openpyxl读取合并的单元格时，第一个合并的单元格包含该值，其余的单元格为空。我想知道每个单元格是否合并，以及合并了多少个单元格，但我找不到任何这样做的函数。工作表中有空的其他单元格，所以我不能使用它。

问如何在Excel表格中检测合并的单元格？
EN

回答 5

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在Excel表格中检测合并的单元格？EN

回答 5

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在Excel表格中检测合并的单元格？
EN