CODEWITHSUNDEEP
pythonexcelopenpyxl
Angel
Angel

Reputation: 47

Merging Specific Cells in an Excel Sheet with Python

I've been trying to merge cells that meet specific criteria with the cell next to it via a loop, but I'm not quite sure how to go about it. For example, starting at row 7, if the cell has the word "Sample" in it, I want it to merge with the cell in the column next to it and I want to keep doing that until I get to the end of that row. I'm currently using openpyxl for this.

Here is what I've tried (it does not work):

wb = load_workbook('Test.xlsx')
ws = wb.active
worksheet = wb['Example']
q_cells = []
for row_cells in worksheet.iter_rows(min_row = 7):
    for cell in row_cells:
        if cell.value == 'Sample':
            q_cells.append(cell.coordinate)

for item in q_cells:
    worksheet.merge_cells(item:item+1)

wb.save('merging.xlsx')

I'm not quite sure how best to proceed with this code. Any help would be appreciated!

Upvotes: 3

Views: 20890

Answers (1)

SuperScienceGrl
SuperScienceGrl

Reputation: 641

merge_cells takes a string (eg: "A2:A8") or a set of values. From the docs:

>>> ws.merge_cells('A2:D2')
>>> ws.unmerge_cells('A2:D2')
>>>
>>> # or equivalently
>>> ws.merge_cells(start_row=2, start_column=1, end_row=4, end_column=4)
>>> ws.unmerge_cells(start_row=2, start_column=1, end_row=4, end_column=4)

Source: https://openpyxl.readthedocs.io/en/stable/usage.html

It sounds like you will want to find your first cell and your last cell, and merge as such (here I'm using f-strings):

ws.merge_cells(f'{first_cell.coordinate}:{last_cell.coordinate}')

Merged cells in openpyxl change from type 'Cell' to type 'MultiCellRange', which is specified as a particular range of cell coordinates. Openpyxl will let you overlap merge ranges without throwing an error, but Excel won't let you open the resulting file without a warning (and probably removing the later merges). If you want to merge, you have to specify the whole range.

Upvotes: 7

Related Questions