Python Merge Excel Cells with Same Value in One Column – Python Tutorial

In python, we can use openpyxl package to merge cells with same value in one column.

Preliminary

You should install openpyxl using pip.

pip install openpyxl

Data in Excel file

The data should be example as below.

The first row shoud be header or column names.

In this exampe, the column name is [Class, Name, Age, City]

Second, the same value should be continue.

How to merge cells?

Here is the example code.

import openpyxl

from openpyxl.styles import Alignment
from openpyxl.utils import  get_column_letter, column_index_from_string

def merge_cells(result_file, sheet_name, col_name, out_file):
    wb = openpyxl.load_workbook(result_file)
    ws = wb[sheet_name]
    col_num = column_index_from_string(col_name)
    print("col_num=",col_num)
    # check col num
    if col_num < 1:
        raise ValueError("col_num must be a positive integer.")

    # get data
    col_list = [cell[0].value for cell in ws.iter_rows(min_row=2, min_col=col_num, max_col=col_num)]
    if not col_list:
        return # data is empty

    # merge cells
    merge_ranges = []
    start_index = 0
    end_index = 0
    current_value = col_list[0]
    print(col_list)
    for i in range(1, len(col_list)):
        if col_list[i] != current_value:
            if end_index >= start_index:
                merge_ranges.append((start_index, end_index))
            current_value = col_list[i]
            start_index = i
        else:
            end_index = i

    if end_index >= start_index:
        merge_ranges.append((start_index, end_index))

    col_letter = get_column_letter(col_num)
    print("col_letter=", col_letter)

    print(merge_ranges)
    for start, end in merge_ranges:
        merge_range = f"{col_letter}{start+2}:{col_letter}{end+2}"
        ws.merge_cells(merge_range)

        for row in ws[merge_range]:
            for cell in row:
                cell.alignment = Alignment(horizontal="center", vertical="center")

    wb.save(out_file)

excel_file = "excel_test.xlsx"
out_file = "excel_test-2.xlsx"

merge_cells(excel_file, "sheet1", "A")

We should notice:

The A column is “Class“.

This code will merge cells with the same value in A column.

You can not use like this:

merge_cells(excel_file, "sheet1", "Class")

Run this code, we will see: