Exit while loop when it reaches the end of the excel file in python

Question

Hi I am new to python and was trying some codes to make my job easier. So here is my code

 counter = 0
 while True:

    row_values = worksheet.row_slice(counter+1,start_colx=0, end_colx=4)

    row['Dealer'] = int(row_values[0].value)
    row['Name'] = str(row_values[1].value)
    row['City'] = str(row_values[2].value)

    counter += 1

    if not row['Dealer']:
        break

I am trying to break the while loop when row['Dealer'] is null, that is when the end of excel file is reached. But it doesn't seem to work somehow. It keeps on giving IndexError: list index out of range. Some help would be appreciated. Thanks

Hi I edited my code to the following below:

   counter = 0
   while True:
    row_values = worksheet.row_slice(counter+1,start_colx=0, end_colx=30)
    row['gg'] = worksheet.cell_value(1+counter,1).strip()
    if not row['gg']:
        break


    row['Dealer'] = int(row_values[0].value)
    row['Name'] = str(row_values[1].value)
    row['City'] = str(row_values[2].value) 
    counter += 1

But the error is still the same! Help would be highly appreciated.

Answer 1

Question: break the while loop when row['Dealer'] is null, that is when the end of excel file is reached.

You can get the end of Rows using worksheet.nrows, for instance:

import xlrd 
book = xlrd.open_workbook("myfile.xls") 
sh = book.sheet_by_index(0) 
print("Sheetname:{0} Rows:{1} Columns:{2}".
    format(sh.name, sh.nrows, sh.ncols)) 

# Iterate all Rows
row_value = {}
for rx in range(sh.nrows): 
    print(sh.row(rx))
    row_value['Dealer'] = sh.cell_value(rowx=rx, colx=0)
    row_value['Name'] = sh.cell_value(rowx=rx, colx=1)
    row_value['City'] = sh.cell_value(rowx=rx, colx=2)

    # Break before end of Rows at first Empty Cell
    if row_value['Dealer'] == '':
        break

Answer 2

The pandas library is a great choice for working with Excel.

Install pandas with pip.

pip install pandas

Install xlrd (needed for working with Excel files).

pip install xlrd

From there you can read the excel spreadsheet with the following.

import pandas as pd
df = pd.read_excel('your_excel.xlsx')

df is now a DataFrame object. You can analyze and transform the data using the available pandas tools. Full documentation for read_excel().