Read rows and columns merged in excel, using python

Question

I’m having trouble reading the information from a spreadsheet that has rows and columns merged. I tried to use merged_cell to get the value, but I don’t understand how it works. Can anyone help me?

Using the script below, I can get the values, except for for merged cells. How can I get all cells, including merged cells?

from xlrd import open_workbook,cellname

workbook = open_workbook('file.xls')
sheet = workbook.sheet_by_index(0)

for row_index in range(sheet.nrows):       
for col_index in range(sheet.ncols):    

print cellname(row_index,col_index),'-',    
print sheet.cell_value(row_index,col_index)

Answer 1

The functioning of merged_cells is as follows: each entry in that list contains 4 values - the first row (top), the last row, the first column (left) and the last column. These lines and columns form a rectangle. In the worksheet data, only the upper left cell has data and formatting, the others appear as if they were blank.

A way to map all cells to their actual value would then be as follows:

from collections import defaultdict
mescladas = defaultdict(lambda: '')

for crange in sheet.merged_cells:    # Para cada intervalo (conjunto de células mescladas)
    rlo, rhi, clo, chi = crange      # Descobre o "retângulo" que elas formam
    for rowx in range(rlo, rhi):     # Para cada linha
        for colx in range(clo, chi): # e cada coluna
            mescladas[(rowx,colx)] = sheet.cell_value(rlo, clo) # Copie o valor da célula
                                                                # superior esquerda

for row_index in range(sheet.nrows):
   for col_index in range(sheet.ncols):
       print cellname(row_index,col_index),'-',
       print sheet.cell_value(row_index,col_index) or mescladas[(row_index,col_index)]