Python: open existing Excel file and count rows in sheet

julesmummdry picture julesmummdry · Apr 15, 2014 · Viewed 8.1k times · Source

I have an existing Excel file. I want to load that one and get the count of rows in this sheet, to later write in the next row of this sheet and save it again. I get following error messages:

AttributeError: 'Worksheet' object has no attribute 'nrows'

But clearly this method exists, coz everyone is using it to get the count. The Code I wrote looks like this:

def write_xls_result(test_case):
    testCase = re.sub("/", "_", test_case)
    automation_report = os.path.expanduser("~/Library/pathtofile/UITests.xctest/Contents/Resources/Automation_Result.xls")
    if os.path.isfile(automation_report):

        w = copy(open_workbook(automation_report))
        copy_sheet = w.get_sheet(0)
        col_width = 256 * 30

        try:
            for i in itertools.count():
                copy_sheet.col(i).width = col_width
        except ValueError:
            pass

        for row in range(copy_sheet.nrows):
             print '{} {}'.format("Row COUNT",copy_sheet.nrows)

        row_index = 10
        copy_sheet.write(row_index,0, testCase)
        w.save('Automation_Result.xls')
        row_index += 1
        print '{} {}'.format("RRRROOOOWWWWW",row_index)

    else:

So I tried a different approach as well:

def write_xls_result(test_case):
    testCase = re.sub("/", "_", test_case)
    automation_report = os.path.expanduser("~/Library/pathtofile/UITests.xctest/Contents/Resources/Automation_Result.xls")
    if os.path.isfile(automation_report):
        workbook = xlrd.open_workbook(automation_report)
        result_sheet = workbook.get_sheet(0)
        rowcount = result_sheet.nrows
        print '{} {}'.format("Row COUNT",rowcount)

        col_width = 256 * 30

        try:
            for i in itertools.count():
                result_sheet.col(i).width = col_width
        except ValueError:
            pass

        row_index = 10
        result_sheet.write(row_index,0, testCase)
        workbook.save('Automation_Result.xls')
        row_index += 1
        print '{} {}'.format("RRRROOOOWWWWW",row_index)

    else:

And I get this Error:

raise XLRDError("Can't load sheets after releasing resources.")
xlrd.biffh.XLRDError: Can't load sheets after releasing resources.

I am still new to python, maybe I am just doing something wrong. Some help or hints would be nice. thanks

Answer

beroe picture beroe · Apr 15, 2014

Your top code is either run differently, or is missing the xlrd portion of xlrd.open_workbook...

You can get the result_sheet without that error by using:

result_sheet = workbook.sheet_by_index(0)

(I get an error trying .get_sheet)

What library are you using? Just xlrd? I don't see a .width property of a column (at least in my example case, it is type list), and not sure what you are doing with that part of the code anyway.

Do you always want to write the number of rows found into row 10? That number never gets indexed in a functional way, and the last line before the else is always going to print 11.