excel 使用openpyxl合并垂直单元格

我正在尝试创建一个python代码来使用openpyxl标准化excel文件。代码几乎已经准备好了，但是当我尝试做两件事时，我遇到了很多问题：
1.在第7行的A和B列中。每个具有相同内容/信息（非空）的连续垂直单元格需要合并为一个大单元格。例如：它可以是5个连续的相同内容的垂直单元格，我需要有一个大合并单元格。
1.在列C中，来自行#7的D & E。我还希望将空单元格与它们上面的第一个非空单元格合并。
这是我到目前为止的代码。

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

# Load the Excel file
workbook = load_workbook('test.xlsx')

# Get the list of existing sheet names
sheet_names = workbook.sheetnames

# Iterate through the sheets in reverse order to avoid index issues
for sheet_name in reversed(sheet_names):
    sheet = workbook[sheet_name]
    
    # Check if the sheet name is not "AVL Report"
    if sheet_name != "AVL Report":
        # Remove the sheet
        workbook.remove(sheet)
    else:
        worksheet = workbook["AVL Report"]
        worksheet.title = "BOM"

# Get the modified sheet
modified_sheet = workbook['BOM']

# Define the columns to delete
columns_to_delete = ['C', 'D', 'G', 'H', 'J', 'M', 'N', 'O', 'P', 'Q', 'R', 'S']

# Iterate through the columns in reverse order to avoid index issues
for column in reversed(columns_to_delete):
    # Get the column index
    column_index = column_index_from_string(column)
    
    # Delete the column
    modified_sheet.delete_cols(column_index)

# Save the modified workbook
workbook.save('test_modified.xlsx')
print('Done')

我已经合并了单元格范围的基础上的价值，通过处理数据在一个工作表，并创建一个字典，其中包含必要的合并;单元格值和范围，然后应用合并字典中的合并。
下面的示例代码是对它的修改，以适应您问题中的标准1和2。
如第一句所述，有两个部分;

第1节创建合并字典merge_dict。本节还包括两个部分，用于为这两个标准正确创建字典。两个标准之间的确定是通过none_list，其中它包含Criteria 2列['C', 'D', 'E']。

合并字典是字典的字典，字典值作为列表。列字母作为主键，单元格值是每列中的子键。每个子键的值是包含该单元格值的单元格行号。
示例密钥A：

{A: {
     'Merge1': [7, 8, 9, 10, 11], 
     'Merge10': [12], 
     'Merge2': [13, 17, 18, 20, 21, 22, 23, 24], 
     'Merge4': [14], 
     'Merge5': [15, 16], 
     'Merge6': [19]}
}

从示例;列为A，具有该值的单元格显示为子键
即'Merge1'在行7、8、9、10、11中。
对于C、D和E列;

{E: {
     'Merge40': [7, 8], 
     'Merge41': [9, 10, 11, 12, 13], 
     'Merge42': [14, 15, 16, 17, 18, 19, 20], 
     'Merge43': [21, 22, 23, 24]}
}

布局是相同的，但是在这种情况下，值是子键下面等于None的单元格，直到下一个具有值的单元格。

第2部分提取行范围并与列字母组合以创建跨范围的合并。

它忽略只有一个值的子键，因为至少需要两个单元格进行合并。
如果子键在列中存在不止一次，则这些范围将被拆分为连续的范围。
例如，在列A中，子键'Merge2'有一个类似[13, 17, 18, 20, 21, 22, 23, 24]的列表，在13 & 17和18 & 20之间的范围序列中有两个断点，表明有三组单元格要合并。本节将这些组分开，再次丢弃单个单元格。因此，结果是A17-A18和A20-A24的合并，其中A13作为单个小区被丢弃。

import more_itertools as mit  # pip install more_itertools
from openpyxl import load_workbook
from openpyxl.styles import NamedStyle, Font, Alignment

def merge_cells(cl, fr, lr):  # cl = column letter, fr = first row, lr = last row
    ### Merge cells gives column letter and first and last row of the range
    cell_range = f'{cl}{fr}:{cl}{lr}'
    worksheet.merge_cells(cell_range)
    worksheet[f'{cl}{fr}'].style = merge_style

def group_cell_ranges(cell_list):
    ### Use more itertools to group consecutive lists of rows.
    ret_list = []
    for grp in mit.consecutive_groups(cell_list):
        grp_list = list(grp)
        if len(grp_list) > 1:  # Only add groups with more than one cell
            ret_list.append([grp_list[0], grp_list[-1]])

    return ret_list

### Named style to center the value in the merged cells
merge_style = NamedStyle(name="merge_style")
merge_style.font = Font(bold=True)
merge_style.alignment = Alignment(horizontal='center', vertical='center')

### Load workbook and worksheet
workbook = load_workbook('test.xlsx')
worksheet = workbook["AVL Report"]

workbook.add_named_style(merge_style)  # Add named style to the workbook

merge_dict = {}  # Dictionary for merged cells
last_value_dict = {}

none_list = ['C', 'D', 'E']  # Columns to merge none values per Criteria 2

### Section1 create 'merge_dict', contains the merge lists from the sheet ###
### Iterate from row 7 all columns to max row
for row in worksheet.iter_rows(min_row=7):
    for cell in row:
        column_letter = cell.column_letter
        c_value = cell.value
        c_row = cell.row

        if column_letter not in merge_dict:
            merge_dict[column_letter] = {}

        ### Columns under Criteria 1 and 2 need slightly different processing
        ### The none_list separates how the columns are processed
        if column_letter in none_list:  # Cells
            if c_value is not None:
                last_value_dict[column_letter] = c_value
            else:
                if column_letter in last_value_dict:
                    c_value = last_value_dict[column_letter]

            if c_value is not None:
                if c_value not in merge_dict[column_letter]:
                    merge_dict[column_letter][c_value] = [c_row]
                else:
                    merge_dict[column_letter][c_value] += [c_row]
        else:
            if c_value is None:  # Don't merge empty cells, remove if this is required
                continue

            if c_value not in merge_dict[column_letter]:
                merge_dict[column_letter][c_value] = [c_row]
            else:
                merge_dict[column_letter][c_value] += [c_row]

### Section2, Add merges to created in merged_dict to the sheet ###
for merge_column, interim_dict in merge_dict.items():
    print(f"Merging cells in column {merge_column}!")
    for m_cell_value, like_cell_val_list in interim_dict.items():
        m_size = len(like_cell_val_list)
        if m_size < 2:
            ### Ignore single cells
            print(f"The cell range consists of a single cell for cell value '{m_cell_value}', "
                  f"{merge_column}{like_cell_val_list[0]}")
            continue

        first_row, last_row = like_cell_val_list[0], like_cell_val_list[-1]
        diff = last_row - first_row + 1
        ### Range for the cell value is contiguous
        if diff == m_size:
            print(f"The cell range is contiguous for cell value: '{m_cell_value}', {merge_column}{like_cell_val_list}")
            merge_cells(merge_column, first_row, last_row)
        else:
            ### Range for the cell value is broken
            ### Attempt to group contiguous ranges and drop single cells
            print(f"The cell range is not contiguous for cell value '{m_cell_value}', "
                  f"{merge_column}{like_cell_val_list}")
            print(f"Try to group '{m_cell_value}' ranges.")
            grouped_list = group_cell_ranges(like_cell_val_list)
            if len(grouped_list) >= 1:
                print(f"Groups for: '{m_cell_value}', {merge_column}{grouped_list}")
            else:
                print(f"No groups for: '{m_cell_value}' in column {merge_column}")

            for group in grouped_list:
                print(f"Grouped cells for cell value: '{m_cell_value}', {merge_column}{group}")
                first_row, last_row = group[0], group[-1]
                merge_cells(merge_column, first_row, last_row)

    print('')

workbook.save('test_out.xlsx')

代码被设置为忽略A和B中的空单元格，因此这些单元格保持未合并。
例如B10-B12和B22-B24
代码还设置为忽略空单元格，这些空单元格在C、D和E中没有值高于它们的单元格，因此这些单元格也保持未合并。
例如D 7-D8
表之前和之后的示例;
列大小调整和单元格边框用于突出显示合并的单元格，而不是代码的一部分。

excel 使用openpyxl合并垂直单元格

1条答案

相关问题

热门标签

最新问答