Read merged rows and columns in excel, using python

Navigation
4

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

Using the script below, I can get the values, except for the 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)
    
asked by anonymous 21.08.2014 / 21:15

1 answer

2

The operation of merged_cells is as follows: each entry in this The 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 spreadsheet data, only the upper left cell has data and formatting, the rest appear as if they are blank.

One way to map all cells to their actual value would then be the following: 

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)]
    
21.08.2014 / 23:55
What steps can we take to protect ticket issuance of malware that changes the digitable line? DropdownList with unique values MVC