Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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 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
like image 958
manisha Avatar asked May 09 '14 10:05

manisha


People also ask

How do you return a value from a merged cell?

Right-click the merged cell B1:D1, select "paste special -> formulas" You should see the merged cell being 0. Type Col1 in the merged cell. You should now see all B2, C2, D2 to be Col1, i.e. now you can reference the merged cell as you expect it to be.

Do formulas work on merged cells?

In this scenario, the paste may apply the formula to each underlying cell of the merged cell, if the source range is not also a merged cell of the same size. Excel then calculates the result by using all the formulas in the underlying cells of the merged cell.

Can you use Vlookup with merged cells?

The Vlookup function will work in merged cells as usual in Google Sheets, but the output won't be what you are seeking. What's the solution to it? For example, if an item has three prices based on its grade, we may enter such data as below. Item: Mango (A1:A3 merged and entered it).


1 Answers

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
like image 196
The Godfather Avatar answered Sep 27 '22 15:09

The Godfather