使用Python读取Excel中的合并单元格 [英] Read merged cells in Excel with Python

问题描述

我正在尝试使用xlrd读取Excel的合并单元格。

我的Excel：（注意第一列合并在三行之间）

  ABC 
 + --- + --- + ---- + 
 1 | 2 | 0 | 30 | 
 + + --- + ---- + 
 2 | | 1 | 20 | 
 + + --- + ---- + 
 3 | | 5 | 52 | 
 + --- + --- + ---- + 
  

我想在本例中读取第一列的第三行等于2，但它返回''。你有什么想法如何达到合并单元格的价值？

我的代码：

  all_data = [[]] 
 excel = xlrd.open_workbook（excel_dir + excel_file）
 sheet_0 = excel.sheet_by_index（0）＃打开第一个选项卡
 
 for row_index in range（sheet_0.nrows）：
 row =
 for col_index in range（sheet_0.ncols）：
 value = sheet_0.cell（rowx = row_index，colx = col_index）.value 
 row + ={0}.format（value）
 split_row = row.split（）
 all_data.append（split_row）
  

我所得到的：

  2'，'0'，'30'
'1'，'20'
'5'，'52'
  

我想得到什么：

 '2'，'0' '30'
'2'，'1'，'20'
'2'，'5'，'52'
  

解决方案

我刚刚尝试过，似乎适用于您的示例数据：

  all_data = [] 
 excel = xlrd.open_workbook（excel_dir + excel_file）
 sheet_0 = excel.sheet_by_index（0）＃打开第一个选项卡
 
 prev_row = [在范围（sheet_0.ncols）中没有）] 
 for row_index in range sheet_0.nrows）：
 row = [] 
 for col_index in range（sheet_0.ncols）：
 value = sheet_0.cell（rowx = row_index，colx = col_index）.value 
如果len（value）== 0：
 value = prev_row [col_index] 
 row.append（value）
 prev_row = row 
 all_data.append（row）
  

返回

  [['2'，'0'，'30']，['2'，'1'，'20']，['2'，'5'，'52' ] 
  

它跟踪上一行的值，并使用它们，如果当前的对应值行为空。

注意上述代码不检查给定单元格是否实际上是合并的单元格集合的一部分，因此在单元格实际上应为空的情况下，可能会重复以前的值。不过，这可能是一些帮助。

其他信息：

我随后发现一个文档页面，其中介绍了一个 merged_cells 属性，可以用来确定合并单元格的各种范围中包含的单元格。该文档说，它是新版本0.6.1，但是当我尝试使用xlrd-0.9.3安装由 pip 我得到错误

NotImplementedError：formatting_info = True尚未实现

我不是特别倾向于开始追逐不同版本的xlrd来测试 merged_cells 功能，但是如果上述代码不足，您可能也有兴趣这样做对于您的需求，您遇到与 formatting_info = True 相同的错误。

I am trying to read merged cells of Excel with Python using xlrd.

My Excel: (note that the first column is merged across the three rows)

    A   B   C
  +---+---+----+
1 | 2 | 0 | 30 |
  +   +---+----+
2 |   | 1 | 20 |
  +   +---+----+
3 |   | 5 | 52 |
  +---+---+----+

I would like to read the third line of the first column as equal to 2 in this example, but it returns ''. Do you have any idea how to get to the value of the merged cell?

My code:

all_data = [[]]
excel = xlrd.open_workbook(excel_dir+ excel_file)
sheet_0 = excel.sheet_by_index(0) # Open the first tab

for row_index in range(sheet_0.nrows):
    row= ""
    for col_index in range(sheet_0.ncols):
        value = sheet_0.cell(rowx=row_index,colx=col_index).value             
        row += "{0} ".format(value)
        split_row = row.split()   
    all_data.append(split_row)

What I get:

'2', '0', '30'
'1', '20'
'5', '52'

What I would like to get:

'2', '0', '30'
'2', '1', '20'
'2', '5', '52'

解决方案

I just tried this and it seems to work for your sample data:

all_data = []
excel = xlrd.open_workbook(excel_dir+ excel_file)
sheet_0 = excel.sheet_by_index(0) # Open the first tab

prev_row = [None for i in range(sheet_0.ncols)]
for row_index in range(sheet_0.nrows):
    row= []
    for col_index in range(sheet_0.ncols):
        value = sheet_0.cell(rowx=row_index,colx=col_index).value
        if len(value) == 0:
            value = prev_row[col_index]
        row.append(value)
    prev_row = row
    all_data.append(row)

returning

[['2', '0', '30'], ['2', '1', '20'], ['2', '5', '52']]

It keeps track of the values from the previous row and uses them if the corresponding value from the current row is empty.

Note that the above code does not check if a given cell is actually part of a merged set of cells, so it could possibly duplicate previous values in cases where the cell should really be empty. Still, it might be of some help.

Additional information:

I subsequently found a documentation page that talks about a merged_cells attribute that one can use to determine the cells that are included in various ranges of merged cells. The documentation says that it is "New in version 0.6.1", but when i tried to use it with xlrd-0.9.3 as installed by pip I got the error

NotImplementedError: formatting_info=True not yet implemented

I'm not particularly inclined to start chasing down different versions of xlrd to test the merged_cells feature, but perhaps you might be interested in doing so if the above code is insufficient for your needs and you encounter the same error that I did with formatting_info=True.

这篇关于使用Python读取Excel中的合并单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！