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

Armalla

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.

Passable

I want to have the same width and a quite large one for every cell. so I say that the columns should be this width. so far this works. the last lines are only a test right now, in the end I want the actual row count to be the index +1, write in that row and than increase the count. but actually i dont need to increase, coz when the script runs again it doesnt know it. so just writing into row index (actual count)+1. I import xlrd, xlwt, xlutils. I will try again to run it without copy and sheet_by_index. thanks for your input! – Armalla

you are right, now I see the error, that its a list. with this snippet I just want to make every column the same bigger width. lets see if the code works if i dont do the column width magic. – Armalla

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.

Trottier

ah yes this is a typo, thanks didnt see this, but changing it, makes no difference to the error message. I still get : raise XLRDError("Can't load sheets after releasing resources.") xlrd.biffh.XLRDError: Can't load sheets after releasing resources. – Armalla

@Armalla ok, let's see then, can you post the traceback? On what line is it throwing the error? – Trottier

the get_sheet is causing it. I will ry to use sheet_by_index. – Armalla