如何在Python中使用Openpyxl查找单个列/行上的有效元素数? [英] How to find the active number of elements on a single column/row using Openpyxl in Python?
本文介绍了如何在Python中使用Openpyxl查找单个列/行上的有效元素数?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在使用openpyxl.
I am using openpyxl.
- 我有下面的基本电子表格.
- 我正在尝试使用len()和filter来获取特定列中的有效元素数量,但仍然没有得到我想要的东西.
示例代码:
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'])
输出:
_code : Value of rock from spreadsheet is 30
_code : Values are (<Cell u'Sheet'.A1>, <Cell u'Sheet'.A2>, <Cell u'Sheet'.A3>, <Cell u'Sheet'.A4>, <Cell u'Sheet'.A5>, <Cell u'Sheet'.A6>, <Cell u'Sheet'.A7>, <Cell u'Sheet'.A8>, <Cell u'Sheet'.A9>, <Cell u'Sheet'.A10>, <Cell u'Sheet'.A11>, <Cell u'Sheet'.A12>, <Cell u'Sheet'.A13>, <Cell u'Sheet'.A14>, <Cell u'Sheet'.A15>, <Cell u'Sheet'.A16>, <Cell u'Sheet'.A17>, <Cell u'Sheet'.A18>, <Cell u'Sheet'.A19>, <Cell u'Sheet'.A20>, <Cell u'Sheet'.A21>, <Cell u'Sheet'.A22>, <Cell u'Sheet'.A23>, <Cell u'Sheet'.A24>, <Cell u'Sheet'.A25>, <Cell u'Sheet'.A26>, <Cell u'Sheet'.A27>, <Cell u'Sheet'.A28>, <Cell u'Sheet'.A29>, <Cell u'Sheet'.A30>)
_code : Values are (<Cell u'Sheet'.A1>, <Cell u'Sheet'.A2>, <Cell u'Sheet'.A3>, <Cell u'Sheet'.A4>, <Cell u'Sheet'.A5>, <Cell u'Sheet'.A6>, <Cell u'Sheet'.A7>, <Cell u'Sheet'.A8>, <Cell u'Sheet'.A9>, <Cell u'Sheet'.A10>, <Cell u'Sheet'.A11>, <Cell u'Sheet'.A12>, <Cell u'Sheet'.A13>, <Cell u'Sheet'.A14>, <Cell u'Sheet'.A15>, <Cell u'Sheet'.A16>, <Cell u'Sheet'.A17>, <Cell u'Sheet'.A18>, <Cell u'Sheet'.A19>, <Cell u'Sheet'.A20>, <Cell u'Sheet'.A21>, <Cell u'Sheet'.A22>, <Cell u'Sheet'.A23>, <Cell u'Sheet'.A24>, <Cell u'Sheet'.A25>, <Cell u'Sheet'.A26>, <Cell u'Sheet'.A27>, <Cell u'Sheet'.A28>, <Cell u'Sheet'.A29>, <Cell u'Sheet'.A30>)
_code : Values of b 30
len()和filter都没有提供期望值,即7,而是始终输出最大值30.同样,即使我这样做,len(sheet ['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
工作表:
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解决方案:
使用.filter(...
方法扩展openpyxl class Worksheet
.
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)
用法:
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
这篇关于如何在Python中使用Openpyxl查找单个列/行上的有效元素数?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文