Reading merged cells in Excel using Python

Question

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

My Excel: (note that the first column is concatenated in three rows)

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

I would like to read the third row of the first column as equal to 2 in this example, but it returns ''. You do not know how to get to the value of the merged cell?

My code is:

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 receive:

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

Answer 1

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)

return

[['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.

, , , , . , .

:

, merged_cells, , . , " 0.6.1", xlrd-0.9.3, pip,

NotImplementedError: formatting_info =

xlrd, merged_cells, , , , , , formatting_info=True.

Answer 2

fillna, pandas https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.fillna.html

excel = pd.read_excel(dir+filename,header=1)
excel[ColName]=excel[ColName].fillna(method='ffill')

Answer 3

openpyxl.worksheet.merged_cell_ranges

This function allows you to get an array of type ['A1:M1', 'B22:B27']that tells you that the cells will be merged.

openpyxl.worksheet.merged_cells

This function indicates whether the cell has been merged or not.