How to get value present in a merged cell?
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 this is 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:
def getMergedCellVal(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 if len(rng)!=0 else 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
def getValueWithMergeLookup(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 range
return 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 and row <= row_end:
column = cell.column
if column >= column_start and column <= column_end:
return True
return False
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).value
return cell.value
Should do it for current openpyxl version (2.6.3)
manisha
Updated on July 29, 2022Comments
-
manisha almost 2 years
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 content is present in it but I get a none value when I read it.
Following is my code:
wb = load_workbook(work_directory_path+'/outputs/report_vap.xlsx') ws = wb.get_sheet_by_name("Summary") suite_path = ws.cell('D3').value if not isinstance(suite_path, unicode): value=unicode(suite_path) value=value.encode('utf8') print "Suite Location is "+value;
The output is :
Suite Location is None
The value in cell for D3 to H3 is :
c:\users\xyz\desktop\abc\c++\events\comevents
I even tried printing all the values in the sheet but except for integer values all values returned None.
Following is the changed code:
wb = load_workbook(work_directory_path+'/outputs/report_vap.xlsx') ws = wb.get_sheet_by_name("Summary") for row_index in range (ws.get_highest_row()): for col_index in range (ws.get_highest_column()): print ws.cell(row=row_index, column=col_index).value suite_path = ws.cell('A11').value print suite_path if not isinstance(suite_path, unicode): value=unicode(suite_path) value=value.encode('utf8') print "Suite Location is "+value;
The output is:
None None None None None None None None None None None None None None None None None None None None None None None None None None None None None None None None None None None None None None None None None None None None None None None None None None None None None 1 1 None None None None None None 9 1106 None None None None None None 10 1107 None None None None None None None None None None Suite Location is None Suite Location is None 12
The Excel file has following content:
Project/module ID Project/module Build Analysis Language Compiler Source File Source File 1_1 HTMLEdit.vcxproj Success C++ Microsoft Visual Studio 2010 ( version 10.0 ) 1 1 1_2 HTMLEdit.vcxproj Success C++ Microsoft Visual Studio 2010 ( version 10.0 ) 9 1106 Total 10 1107