from openpyxl import Workbook from openpyxl.utils import column_index_from_string, get_column_letter # 创建一个Workbook对象 workbook = Workbook() sheet = workbook.active # 定义数据 data = [ ['Name', 'Info', 'Info', 'Score', 'Score'], ['Name', 'Age', 'City', 'Math', 'English'], ['John', 30, 'New York', 90, 85], ['Jane', 25, 'London', 80, 90], ['John', 35, 'Paris', 95, 75], ['Jane', 28, 'Berlin', 85, 80], ] # 写入数据 for row in data: sheet.append(row) def get_next_column_name(current_column_name): # 将列名转换为列索引 current_column_index = column_index_from_string(current_column_name) # 获取下一列的列索引 next_column_index = current_column_index + 1 # 将下一列的列索引转换为列名 next_column_name = get_column_letter(next_column_index) return next_column_name def get_merged_cells(sheet, start_row=None, start_column=None, end_row=None, end_column=None): min_row = start_row or sheet.min_row # 最小行 max_row = end_row or sheet.max_row # 最大行 min_col = start_column or sheet.min_column # 最小列 max_col = end_column or sheet.max_column # 最大列 merged_data = [] merged_row_dict = dict() merged_col_dict = dict() for row_num in range(min_row, max_row + 1): for col_num in range(min_col, max_col + 1): current_cell = sheet.cell(row_num, col_num) if col_num == 1: if row_num == 1: merged_col_dict.update({current_cell.coordinate: 1}) merged_row_dict.update({current_cell.coordinate: 1}) else: pre_row_cell = sheet.cell(row_num - 1, col_num) if pre_row_cell.value == current_cell.value: if pre_row_cell.coordinate not in merged_row_dict: merged_row_dict[pre_row_cell.coordinate] = 1 merged_row_dict[pre_row_cell.coordinate] += 1 else: left_cell = sheet.cell(row_num, col_num - 1) if left_cell.value == current_cell.value: if left_cell.coordinate not in merged_col_dict: merged_col_dict[left_cell.coordinate] = 1 merged_col_dict[left_cell.coordinate] += 1 if row_num != 1: pre_row_cell = sheet.cell(row_num - 1, col_num) if pre_row_cell.value == current_cell.value: if pre_row_cell.coordinate not in merged_row_dict: merged_row_dict[pre_row_cell.coordinate] = 1 merged_row_dict[pre_row_cell.coordinate] += 1 for _, num in merged_col_dict.items(): if num > 1: row_num = int(re.sub(r'\D', "", _)) col = re.sub(r'[^a-zA-Z]', "", _) next_column_name = get_next_column_name(col) merged_data.append(f"{_}:{next_column_name}{row_num}") for _, num in merged_row_dict.items(): if num > 1: row_num = int(re.sub(r'\D', "", _)) col = re.sub(r'[^a-zA-Z]', "", _) merged_data.append(f"{_}:{col}{row_num + num - 1}") return merged_data merged_data = get_merged_cells(sheet) print(merged_data) for _ in merged_data: sheet.merge_cells(_) workbook.save('output.xlsx')