Reading large excel tables (guessing size)

I'm using pywin32 to read and write to excel. Now the only method I know is accessing Range.Value however, I usually don't know t开发者_StackOverflow社区he size of the full excel table in advance. So at the moment I read line by line until I find a completely empty line. This can be quite slow.

Of course I will try tricks like reading blocks of data - then I'd have to find an optimal block size.

Do you know another method (maybe some internal excel function) or other approaches which are faster?

You can use xlrd to open a workbook and read the size of particular worksheet. It's quite fast.

Hints: book = xlrd.open_workbook("myfile.xls") then you get Sheet object by sheet = book.sheet_by_index(sheetx) or sheet = book.sheet_by_name(sheet_name) and you have sheet.nrows property with number of rows in given sheet.

Here is the API documentation.

In VBA, we often use the End statement like this:

Worksheets("sheet1").Cells(Rows.Count, "A").End(xlUp).Row

That may help you find the last used cell of a column (yet, I don't know how to extend this method to pywin)

How about getting the whole range with the Worksheet.UsedRange property ?