Length of specific column within worksheet

[Alina]

Hey there,

I am intersted in the length of one specific column of my excel worksheet. Since I do have several lengths (for A row= 54; for D= 51), I'd like to know the exact length of D, which is dynamic dependent on the individual case.

My guess was using: len(sheet['D']) which is then resulting in the maximum of the entire worksheet (e.g. here 54). Is there any function/way to receive the correct max. length for the specific column? I have found following solution on stackoverflow LINK , just curious if you would recommend something else. 

Thanks in advance. BR Alina

[Charlie Clark]

On 24 Mar 2021, at 10:06, Alina wrote:

Hey there,
I am intersted in the length of one specific column of my excel worksheet.
Since I do have several lengths (for A row= 54; for D= 51), I'd like to
know the exact length of D, which is dynamic dependent on the individual
case.
My guess was using: len(sheet['D']) which is then resulting in the maximum
of the entire worksheet (e.g. here 54). Is there any function/way to
receive the correct max. length for the specific column? I have found
following solution on stackoverflow LINK

<https://stackoverflow.com/questions/52813973/openpyxl-need-the-max-number-of-rows-in-a-column-that-has-data-in-excel/52817637#52817637>

, just curious if you would recommend something else.
Thanks in advance. BR Alina

I think the first thing to remember here is that the size of a spreadsheet is somewhat difficult to define. It's probably best, therefore, to think of a spreadsheet as being 1,000,000 rows and 16,384 columns by default.

For many reasons, openpyxl defaults to consistently sized worksheets based on the longest row or column it has, though these can include empty but formatted cells.

With that in mind you can create your own calculator:

d = ws['D']
for c in reversed(d): # start at the bottom
	if c.value is None:
		break

print(f'Highest row with a value is {c.row}')

You can expand on this to use ws.iter_cols(…), assuming you're not in read-only mode, which can make parametrising easier. You'll just need to remember to materialise the generator to be able to reverse it.

Charlie

--
Charlie Clark
Managing Director
Clark Consulting & Research
German Office
Waldlehne 23
Düsseldorf
D- 40489
Tel: +49-203-3925-0390
Mobile: +49-178-782-6226