Count rows in excel sheet in Python with xlwings

Question

I have a script in Python that uses xlwings to open up an Excel file, and read and process the values of a certain column row by row. Here is the for statement:

for row in range(2, rownum):

I would like to repeat this function over every row in the sheet that actually contains something. It starts at 2 and ends at 'rownum'. My question is how to automatically count the number of rows and pass that value to 'rownum'. I'm sure xlwings has a way to do this, but I can't figure it out- perhaps the Autofit tool?

Thanks for the help!

Answer 1

Unless I've missed something while reading their API documentation it doesn't seem possible. You might need to use other libraries, for example pandas:

import pandas as pd

df = pd.read_excel(excel_file_path, sheetname="Sheet1")
print len(df)

If you don't want to use another library just for that, you can do it the hard and ugly way:

last_row = 0
while True:
    if cell_value is not None:  # replace cell_value with however 
                                # xlwings accesses a cell's value
       last_row += 1
    else:
        break

print last_row

Answer 2

It's all the API Documentation

If you're only looking for the number of rows, you can obtain the total number of row in your array/table by using the current_region property of your range and then getting the address of the last cell of this range: (It works only if your range is contiguous - no empty rows/columns inside of it)

rownum = Range('A1').current_region.last_cell.row

Alternatively, you can use table instead of current_region, the range will just be a bit different.

Once you have that, you can just loop through the rows:

for i in range(1, rownum + 1): # The indexing starts at 1
    Range((i, 1)) = ...    # Will select cell 'Ai'

But as mentioned in other answers, this multiplies the calls between app, which will be considerably slower. Better import the range, modify it and export it back to Excel.

Answer 3

With xlwings, you would read in the Range first, then iterate over it:

rng = Range((startrow, startcol), (rownum, colnum)).value
for row in rng:
    ...

Then at the end, write the result back:

Range((startrow, startcol)).value = result_rng

This way you minimize the cross-application calls which are slow.

You might also want to use Range.table.

Answer 4

I had to make a counter because I am automating a bunch of things that taken from excel and filled onto different websites. This is just the "prototype" that I came up with just to do it make sure I could do it.

wb = xw.Book(r'C:\Users\dd\Desktop\Testbook.xlsm') 
Dudsht = wb.sheets['Dud']

lastcell = Dudsht.range(1,1).end('down').row #this just does ctrl+shift+down
print(lastcell) #just so you know how many rows you have. Mine was 30.

x = 2
for i in range(x, lastcell+1):               #range of 2 to 30
        Dudsht.cells(i,2).value = 'y'        #enters 'y' triggering formulas
        if Dudsht.cells(i,1).value == 'ERROR':  
            Dudsht.cells(i,1).api.EntireRow.Interior.ColorIndex = 2
            continue          #if there is an error it will hightlight and skip an item
        time.sleep(.5)            #this was just so I could see visually
        Dudsht.cells(i,2).value = 'x'  
        print('Item' + str(i) + ' Complete')  #Item1 Complete
        time.sleep(.5)
        Dudsht.cells(i,1).api.EntireRow.Interior.ColorIndex = 3  #highlights completed item

Answer 5

If there is no blank row, you can just use this:

len(Range('A1').vertical)

Answer 6

You don't need to know how many rows in the sheet.

import xlwings as xw

wb = xw.Book('20180301.xlsm')
sh = wb.sheets['RowData']

rownum = 2
while (sh.range('A'+str(rownum)).value != None):
    value = sh.range('A'+str(rownum)).value
    print(str(value))
    rownum += 1

This will print out all data in column A.

Answer 7

Clean solution from https://gist.github.com/Elijas/2430813d3ad71aebcc0c83dd1f130e33?permalink_comment_id=2088976#gistcomment-2088976:

used_range_rows = (active_sheet.api.UsedRange.Row, active_sheet.api.UsedRange.Row + active_sheet.api.UsedRange.Rows.Count)

used_range_cols = (active_sheet.api.UsedRange.Column, active_sheet.api.UsedRange.Column + active_sheet.api.UsedRange.Columns.Count)

used_range = xw.Range(*zip(used_range_rows, used_range_cols))

Answer 8

For counting rows in a column with empty cells in between:

import xlwings as xw
wb = xw.Book(loc)
sheet = wb.sheets['sheetname']

counter = 0
rownum = 1
while (rownum >= 1):
  if sheet.range('A'+str(rownum)).value !=None:
    counter += 1
  elif sheet.range('A'+str(rownum)).value == None and sheet.range('A'+str(rownum+1)).value != None:
    counter += 1
  elif sheet.range('A'+str(rownum)).value == None and sheet.range('A'+str(rownum+1)).value == None:
    counter += 1
    break
  rownum += 1