python - 如何使用python拆分excel工作簿中的合并单元格

Question

有什么方法可以使用 python 拆分/取消合并 excel 工作簿中的单元格？我想要的解释如下-

结果应该是一个包含以下条目的新 excel 文件 -

我使用 xlrd 为所有合并列复制相同字符串的解决方案如下 -

[注意:我正在使用的 xlrd 中尚未实现“formatted_info = True”标志，因此我无法直接获取合并单元格的列表。我不应该在设置中升级 xlrd。]

def xlsx_to_dict():
    workbook = xlrd.open_workbook(xlsfile)
    worksheet_names = workbook.sheet_names()
    for worksheet_name in worksheet_names:
        worksheet = workbook.sheet_by_name(worksheet_name)
        num_rows = worksheet.nrows - 1
        num_cells = worksheet.ncols - 1
        curr_row = -1
        header_row = worksheet.row(0)
        columns = []
        for cell in range(len(header_row)):
            value = worksheet.cell_value(0, cell)
            columns.append(value)

        cities = []

        for row in range(1,num_rows):
            value = worksheet.cell_value(row,0)
            type = worksheet.cell_type(row,0)
            if  not value == "":
                cities.append(value)

        names = []
        for row in range(1,num_rows):
            value = worksheet.cell_value(row,1)
            type = worksheet.cell_type(row,1)
            if  not value == "":
                names.append(value)

            current_city = cities[0]
            result_dict = {}
            for curr_row in range(1,num_rows):
                row = worksheet.row(curr_row)
                curr_cell = -1
                curr_name = names[0]
                while curr_cell < num_cells:
                    curr_cell += 1
                    cell_value = worksheet.cell_value(curr_row, curr_cell)
                    if cell_value in cities and curr_cell == 0:
                        current_city = cell_value
                        if not result_dict.has_key(current_city):
                            result_dict[current_city] = {}
                        continue
                    if cell_value == "" and curr_cell == 0:
                        continue
                    if cell_value in names and curr_cell == 1:
                        curr_name = cell_value
                        if not result_dict[current_city].has_key(curr_name):
                            result_dict[current_city][curr_name] = {}
                        continue
                    if cell_value == "" and curr_cell == 1:
                        continue
                    try:
                        result_dict[current_city][curr_name]['Phone'].append(cell_Value)
                    except:
                        result_dict[current_city][curr_name]['Phone'] = [cell_value]

上述函数将返回如下 python 字典 -

{ 'New York' : { 'Tom' : [92929292, 33929] }, ........}

然后我将遍历该目录并编写新的 excel。

但是，我想要一些通用的方法来拆分合并的单元格。

Answer 1

此函数获取“真实”单元格值，即如果坐标位于合并单元格内的任意位置，则合并单元格的值。

def unmergedValue(rowx,colx,thesheet):
    for crange in thesheet.merged_cells:
        rlo, rhi, clo, chi = crange
        if rowx in xrange(rlo, rhi):
            if colx in xrange(clo, chi):
                return thesheet.cell_value(rlo,clo)
    #if you reached this point, it's not in any merged cells
    return thesheet.cell_value(rowx,colx)

松散地基于 http://www.lexicon.net/sjmachin/xlrd.html#xlrd.Sheet.merged_cells-attribute

效率很低，但对于小型电子表格应该是可以接受的。