How To Get Value Present In A Merged Cell?

I want to get value of a merged cell that has range from D3 to H3 using openpyxl library. As per my understanding most libraries read data from 1st cell itself. Thus the merged con

Solution 1:

As soon as the only answer is incorrect (there is no more cells_from_range function in openpyxl) I suggest alternative way. I tried and it worked for my case:

Input is sheet and Cell. But if you need, it can be easily modified to accept string cell representation like 'A3'.

import openpyxl


def getValueWithMergeLookup(sheet, cell):
    idx = cell.coordinate
    for range_ in sheet.merged_cell_ranges:
        merged_cells = list(openpyxl.utils.rows_from_range(range_))
        for row in merged_cells:
            if idx in row:
                # If thisis a merged cell,
                # return  the first cell of the merge range
                return sheet.cell(merged_cells[0][0]).value

    return sheet.cell(idx).value

Solution 2:

I wrote this based on the latest source code from Openpyxl:

defgetMergedCellVal(sheet, cell):
    rng = [s for s in sheet.merged_cells.ranges if cell.coordinate in s]
    return sheet.cell(rng[0].min_row, rng[0].min_col).value iflen(rng)!=0else cell.value

Solution 3:

Here's an approximation of the function that I use for this:

from openpyxl.cell import get_column_letter
from openpyxl.worksheet import cells_from_range

defgetValueWithMergeLookup(sheet, col, row):
    idx = '{0}{1}'.format(get_column_letter(col), row)
    for range_ in sheet.merged_cell_ranges:
        cells = list(cells_from_range(range_))[0]
        if idx in cells:
            # If this is a merged cell, you can look up the value # in the first cell of the merge rangereturn sheet.cell(cells[0]).value

    return sheet.cell(row=row, column=col).value

The only really dicey bit there is where I extract the list of cells within the range to search against. That returns a generator, so I cast it to a list (because in doesn't work on generators, apparently), which yields a tuple containing a single list element, which I extract using the 0-index.

For my purposes, this is fast enough -- I use it by iterating the cells I want to test. If you wanted to make this more performant, it might be worthwhile to invert the loop, iterating the merge ranges as your outer loop, so you only have to do that conversion once.

Solution 4:

from openpyxl import cell as xlcell, worksheet
def within_range(bounds: tuple, cell: xlcell) -> bool:
    column_start, row_start, column_end, row_end = bounds
    row= cell.row
    if row>= row_start androw<= row_end:
        column= cell.column
        if column>= column_start andcolumn<= column_end:
            returnTruereturnFalse

def get_value_merged(sheet: worksheet, cell: xlcell) ->any:
    for merged in sheet.merged_cells:
        if within_range(merged.bounds, cell):
            return sheet.cell(merged.min_row, merged.min_col).valuereturn cell.value

Should do it for current openpyxl version (2.6.3)

Solution 5:

One line to get value of a merged cell:

[r for r in sheet.merged_cells.ranges if cell.coordinate in r][0].start_cell.value