Get Excel row number via MS Excel

Question

I have the code below, it use for looking a match cell value and return the row of match cell value, such as looking value is 'LogiGear' at cell D57 (row 58), it will return the row 58. I wanna improve this code, just looking for D column only. I don't know why it return the error 'invalid class string'.

Any advice?

'''
get excell row number via MS
'''

def GetExcellRowNumberViaMS(strFile, strSheet, text):
    try:
        bFound = False
        #Create an instance of Excel.Application        
        xlApp = win32com.client.Dispatch("Excel.Application")
                    lnRowNumber=0;
                    i = 1
                    lastrow = xlSheet.UsedRange.Rows.Count
                    lastcol = xlSheet.UsedRange.Columns.Count
                    while(i<65000 and bFound ==False):
                        if(i == lastrow+1):
                            break
                        j = 1
                        while(j<256):
                            if(j == lastcol+1):
                                break
                            value = str(xlSheet.Cells(i,j).Text)
                            if(value.strip() == str(text).strip()):
                                lnRowNumber = i
                                bFound = True
                                break
                            j = j + 1
                        i = i + 1     
                    return lnRowNumber
    except Exception:
        return 0

The code you've pasted is quite long. It's not easy to see which part is causing the problem. Could you try editing the code to remove the parts which aren't relevant to your problem? That would make it easier for us to work with. Some instructions for that are here: stackoverflow.com/help/mcve . — Li-aung Yip
– Li-aung Yip, Commented Nov 20, 2015 at 10:43
As an aside, your problem might be more easily solved using the xlrd library for Python. — Li-aung Yip
– Li-aung Yip, Commented Nov 20, 2015 at 10:45
I have edited my code. I am using win32com. Please help with win32com — Bob
– Bob, Commented Nov 20, 2015 at 10:47
The edited version of your code won't run. Can you edit it so that it will run when copy-pasted into a .py file? — Li-aung Yip
– Li-aung Yip, Commented Nov 20, 2015 at 10:48

Li-aung Yip · Accepted Answer · 2015-11-20 13:41:41Z

When asking a question, it's helpful to include a small example of the expected input and output. (You should do this for future questions.)

Here's an example I have made up in Excel. We want to find the row that is a Vegetable.

The code you originally posted searches all rows and columns. When simplified and written in a "pythonic" style, it looks like:

import win32com.client

excel = win32com.client.Dispatch("Excel.Application")
worksheet = excel.ActiveSheet # The currently open worksheet, in the currently open file.

# This version searches all rows and columns.
for row_num in range(worksheet.UsedRange.Rows.Count):
    for col_num in range(worksheet.UsedRange.Columns.Count):
        # Note Python's range() counts from 0 and Excel counts from 1
        value = worksheet.Cells(row_num + 1, col_num + 1).Value
        print ("Value at row %i, column %i is `%s`" % (row_num, col_num, value))
        if value == "Vegetable":
            print "Hooray! Row %i is a vegetable!" % row_num

The output looks like:

...
Value at row 4, column 0 is `5.0`
Value at row 4, column 1 is `Eggplant`
Value at row 4, column 2 is `Vegetable`
Hooray! Row 4 is a vegetable!
Value at row 5, column 0 is `6.0`
...

The above code loops over all column numbers. You can tell it to only look at one column, by fixing col_num to be a certain number.

for row_num in range(worksheet.UsedRange.Rows.Count):
    col_num = 2 # Fixed to look at column 2 only
    # Note Python's range() counts from 0 and Excel counts from 1
    value = worksheet.Cells(row_num + 1, col_num + 1).Value
    print ("Value at row %i, column %i is `%s`" % (row_num, col_num, value))
    if value == "Vegetable":
        print "Hooray! Row %i is a vegetable!" % row_num

The output now looks like:

Value at row 0, column 2 is `Fruit`
Value at row 1, column 2 is `Fruit`
Value at row 2, column 2 is `Fruit`
Value at row 3, column 2 is `Fruit`
Value at row 4, column 2 is `Vegetable`
Hooray! Row 4 is a vegetable!
Value at row 5, column 2 is `Fruit`

Notice that only one line of code was changed.

Finally,

except Exception:
    return 0

Catching all exceptions is horrible practice. It prevents you from seeing errors. If you can't see an error, you can't debug it.

Collectives™ on Stack Overflow

Get Excel row number via MS Excel

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related