python返回元素所在行_如何在Python中使用Openpyxl查找单个列/行上的有效元素数？...

I am using openpyxl.

I have the below basic spreadsheet.

I am trying to just get the active number of elements in a particular column using len() and filter but still not getting what I wanted.

Sample Code:

load_xls_file = open("./sample.xlsx", "r")

wb = load_workbook(load_xls_file)

sheet = wb.get_sheet_by_name("Sheet")

rock = len(sheet['A'])

print '_code : Value of rock from spreadsheet is',rock

print '_code : Values are', filter(None,sheet['A'])

print '_code : Values are', sheet['A']

print '_code : Values of b', len(sheet['B'])

Output:

_code : Value of rock from spreadsheet is 30

_code : Values are (, , , , , , , , , , , , , , , , , , , , , , , , , , , , , )

_code : Values are (, , , , , , , , , , , , , , , , , , , , , , , , , , , , , )

_code : Values of b 30

Neither len(), nor filter isn't providing the expected value i.e. 7 rather it prints the max value of 30 all the time. Also even when I do len(sheet['B']) is still provide the same value of 30.

Am i making any simple mistake ? Kindly provide your comments.

解决方案Question: get the active number of elements in a particular column

Worksheet:

Title Title Title

1 3 4

None None None

1 3 4

min_col = 1 # 'A'

val_counter = 0

# Iterate all Rows, starting at 'min_row='

# Iterate only ONE Column, therefore 'min_col=' and 'max_col=' have the same value

# Returns a Tuple of Column Values ((value A2,), (value A3), ...)

for cells in ws.iter_rows(min_row=2,

min_col=min_col, max_col=min_col,

values_only=True):

value = cells[0]

# Condition, which to count

if value is not None:

val_counter += 1

print('Values are {}'.format(val_counter))

# >>> Values are 2

OOP Solution:

Extending openpyxl class Worksheet with a .filter(... methode.

import openpyxl

class Worksheet:

def __init__(self, pyxl):

for attr in ['filter', 'filter_list']:

setattr(pyxl.worksheet.worksheet.Worksheet,

attr,

getattr(Worksheet, attr)

)

def filter(self, f, range=None, values_only=True):

cells = self.iter_rows(min_row=range[0],

min_col=range[1],

max_row=range[2],

max_col=range[3],

values_only=values_only

)

for row in cells:

yield from (c for c in row if f(c))

def filter_list(self, f, range=None, values_only=True):

return [v for v in self.filter(f, range, values_only)]

# Extend openpyxl Worksheet

Worksheet(openpyxl)

Usage:

wb = openpyxl.Workbook()

ws = wb.active

# Add some test data

ws.append(['Title', 'Title', 'Title'])

for r in range(3):

if r == 1:

ws.append([None, None, None])

else:

ws.append([1, 3, 4])

# Filter Values, where Cell.value is not None

# range(min_row, min_col, max_row, max_col)

# Return a List of Values

cells = ws.filter_list(lambda v: v is not None,

range=(2, 1, ws.max_row, 1)

print('Values are {}'.format(len(cells)))

# >>> Values are 2