Python: open existing Excel file and count rows in sheet

Question

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 1

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.

Answer 2

The second approach is correct except that you should replace:

w.save('Automation_Result.xls')

with:

workbook.save('Automation_Result.xls')

Since the workbook variable is the reference to the xlrd Workbook you've opened in the code block.