python-3.xexceldataframexlwings

Use the row containing the sought value in an excel file as the dataframe column's titles with xlwings


How can I use the row containing a string as the dataframe column's titles, e.g:

EXCEL DATA:

FIELDS VALUES
F1 V1
F2 V2
F3 V3
F4 V4

With F2 as sought value, my Dataframe should be as below:

F2 V2
F3 V3
F4 V4

How can I achieve this please, I have tried many scripts with xlwings?

sheet_name='Sheet1'
df = pd.read_excel(file, sheet_name=sheet_name.strip())                    
 
search_value='F2'             
row_index=df[df.iloc[:,0]==search_value].index[0]

app = xw.App()
wb = app.books.open(file)     
# Specify the starting row containing the value 
starting_row = row_index  

data_range = wb.sheets['Sheet1'].range(f'A{starting_row}').options(pd.DataFrame, header=True, index=False, expand='table').value
 
wb.close()
 
app.quit()
 
print('############### DATAFRAME #################')
print(data_range)

Solution

  • I have used starting_row = row_index +3 and it's ok.

    sheet_name='Sheet1'
    df = pd.read_excel(file, sheet_name=sheet_name.strip())                    
     
    search_value='F2'             
    row_index=df[df.iloc[:,0]==search_value].index[0]
    
    app = xw.App()
    wb = app.books.open(file)     
    # Specify the starting row containing the value 
    starting_row = row_index  + 3 
    
    data_range = wb.sheets['Sheet1'].range(f'A{starting_row}').options(pd.DataFrame, header=True, index=False, expand='table').value
     
    wb.close()
     
    app.quit()
     
    print('############### DATAFRAME #################')
    print(data_range)