Python Forum
How can I iterate through all cells in a column (with merge cells) with openpyxl?
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
How can I iterate through all cells in a column (with merge cells) with openpyxl?
#1
I have to write data in an excel sheet that contains merged cells, but I don't know how to do it because I can't identify the merged cells. For example, if I read all the cells in the first column of the sheet shown below, I read all of them, even if some of them belong to some merged cells:

>>> wb = openpyxl.load_workbook('test.xlsx')
>>> ws = wb['TEST']
>>>
>>> for cell in ws["A"]:
...     print(cell.value)
...
None
None
None
None
>>>
[Image: 8a2ac11369689167]

Here I can read four cells, but all of them are merged! I need to identify the main one not all of them. How can I iterate through all the "right" cells in a column? I have to identify the merged cells and write on them!
Reply
#2
I made an excel sheet with three merged cells to match your image. Try this:
>>> from openpyxl.cell import MergedCell
>>> for cell in ws["A"]:
...   print(cell)
...   print(cell.value)
...   print(isinstance(cell, MergedCell))
...   print('--')
...
<Cell 'Sheet1'.A1>
None
False
--
<MergedCell 'Sheet1'.A2>
None
True
--
<MergedCell 'Sheet1'.A3>
None
True
--
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  getting unexpected indent errors trying to move cells up jensengt 4 823 Jun-28-2023, 12:05 PM
Last Post: deanhystad
  Converting cells in excel to JSON format desmondtay 4 1,687 May-23-2022, 10:31 AM
Last Post: Larz60+
  Openpyxl-change value of cells in column based on value that currently occupies cells phillipaj1391 5 9,576 Mar-30-2022, 11:05 PM
Last Post: Pedroski55
  Find last filled column in openpyxl Irv1n 2 12,557 Jan-16-2022, 11:05 AM
Last Post: Pedroski55
  gspread - applying ValueRenderOption to a range of cells dwassner 0 1,672 Jan-12-2022, 03:05 PM
Last Post: dwassner
  Python “Formula” Package: How do I parse Excel formula with a range of cells? JaneTan 1 2,641 Jul-12-2021, 11:09 AM
Last Post: jefsummers
  OpenPyxl: How to iterate through each Column (in 1 row) to find a value? chatguy 2 17,846 Apr-06-2021, 04:52 AM
Last Post: carlhyde
  Can you help me to merge the cells with OpenPyXL? TurboC 1 2,168 Feb-01-2021, 12:54 AM
Last Post: Larz60+
Question Python + Google Sheet | Best way to update specific cells in a single Update()? Vokofe 1 2,628 Dec-16-2020, 05:26 AM
Last Post: Vokofe
  Openpyxl tkinter search a value in Excel column Heathcliff_1 0 3,214 Dec-02-2020, 04:35 PM
Last Post: Heathcliff_1

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020