Parsing excel sheet in python with no empty spaces if column extends across multiple rows

Viewed 27

I am trying to parse an excel sheet in which some columns spread across multiple rows. Here is the sheet - enter image description here

I am trying to get all the inputs and those start from column E. In the sheet 'human readable' is just 1 column in row 4. When I print row 4 I get the output - 'human readable', '', '', '', 'from OVERRIDE_EN_TT', 'test00[3]', 'test00[2:0]', 'test08[2:0]', 'test09[2:0]', 'test10[2:0]', 'test11[2:0]. Those 2 empty spaces after 'human readable' are causing error for me. The code I have is -

import xlrd
wbook = xlrd.open_workbook(Book2.xlsx, 'r')
my_sheet = wbook.sheet_by_name("Sheet1")
for ii in range(0, my_sheet.nrows):
  if "table_start" in str(my_sheet.cell(ii, 0)):
    starting_row = ii+1 
    hr_row = ii+3                       
    len_row = len(my_sheet.row(hr_row))
    for aa in range(1, len_row):
        if ((my_sheet.cell(starting_row, aa).value == 'INPUT') and not \
            (my_sheet.cell(starting_row+1, aa).value == 'human readable'):
            starting_column = aa                                    
            break      

How can I get starting column as column E? Or if there is any other way to do it, how can I do it? I want a list of items from column E to end . I want starting column in order to run a loop like this -

for i in range(1, len_row):
  for j in range(starting_column, ending column):
    my_list.append(my_sheet.cell(i, j).value)

I want my_list like -

['', 'from OVERRIDE_EN_TT', 'test00[3]', 'test00[2:0]', 'test08[2:0]', 'test09[2:0]', 'test10[2:0]', 'test11[2:0]]

I am having trouble to get the starting column because of the merged cell.

0 Answers
Related