excel 获取某个合并像元的坐标

v09wglhw  于 2023-02-10  发布在  其他
关注(0)|答案(3)|浏览(158)

我试图在excel -x1c 0d1x中获取合并单元格的起始列和结束列
我想知道开始和结束列数的单元格'人类可读'。这个代码给我所有合并的单元格表-

from openpyxl import load_workbook
wb = load_workbook('Book2.xlsx')
sheet_ranges = wb['Sheet1']

print(sheet_ranges.merged_cells.ranges)

但我怎么才能找到只为“人类可读”的呢?

kwvwclae

kwvwclae1#

这对我很有效

from openpyxl import load_workbook

def main():
    wb = load_workbook("/Book2.xlsx")
    sheet = wb['Sheet1']
    cell = content_checker(sheet)
    merged_span_check(sheet, cell)

def content_checker(sheet):
    for row in sheet:
        for cell in row:
            if cell.value == 'Human Readable':
                return cell

def merged_span_check(sheet, cell):
    for merged_cell in sheet.merged_cells.ranges:
        if cell.coordinate in merged_cell:
            print(merged_cell)

if __name__ == '__main__':
    main()
qyuhtwio

qyuhtwio2#

应该能够使用列表解析来查找和打印所有示例,包括检查大小写不敏感。

from openpyxl import load_workbook
wb = load_workbook('Book2.xlsx')
sheet_ranges = wb['Sheet1']

search = 'human readable'
newlist = [c for c in sheet_ranges.merged_cells.ranges
           if search in c.start_cell.value.lower()]
print(newlist)
gev0vcfq

gev0vcfq3#

我首先收集merged_cells中的所有单元格:

def collect_merge_dict(sheet):
    merge_dict = {}
    merge_ranges = sheet.merged_cells.ranges
    for index, merged_range in enumerate(merge_ranges):
        for col in range(merged_range.min_col, merged_range.max_col + 1):
            for row in range(merged_range.min_row, merged_range.max_row + 1):
                coord = (row, col)
                merge_dict[coord] = index

    return merge_dict

然后我们可以使用d6stringer的content_checker:

def content_checker(sheet):
    for row in sheet:
        for cell in row:
            if cell.value == 'Human Readable':
                return cell

再看看这个单元格:

def main():
    wb = load_workbook("/Book2.xlsx")
    sheet = wb['Sheet1']
    merge_dict = collect_merge_dict(sheet)
    cell = content_checker(sheet)

    index = merge_dict.get((cell.row, cell.column), -1)
    if index > -1:
        cell_range = list(sheet.merged_cells.ranges)[index]
        print(cell_range)

相关问题