如何在合并的单元格中获得价值? [英] How to get value present in a merged cell?

问题描述

我想使用openpyxl库获取从D3到H3范围的合并单元格的值. 根据我的理解，大多数库都从第一个单元格本身读取数据.因此，合并后的内容会出现在其中，但是当我读取它时，它的值是none.

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.

以下是我的代码:

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;

输出为:

Suite Location is None

从D3到H3的单元格中的值为:

The value in cell for D3 to H3 is :

c:\users\xyz\desktop\abc\c++\events\comevents

我什至尝试打印工作表中的所有值，但除整数值外，所有值都返回None.

I even tried printing all the values in the sheet but except for integer values all values returned None.

以下是更改的代码:

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;

输出为:

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

Excel文件具有以下内容:

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

推荐答案

一旦唯一的答案不正确(openpyxl中不再有cells_from_range函数)，我建议您采用其他方法.我尝试了一下，并且对我的情况有用:

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:

输入是工作表和单元格.但是，如果需要，可以轻松地对其进行修改以接受字符串单元格表示形式，例如"A3".

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

这篇关于如何在合并的单元格中获得价值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！