This code returns a list of widgets from spreadsheet ranges that are enabled for processing. The code works but it is poorly implemented, especially in terms of proper use of zip and layout.
The code needs to run on various ranges but was initially hard-coded for development. In
openpyxl row/column processing is more efficient than coordinate ranges and coordinate range functionality is progressively being depreciated. (The coordinate code is only used for development and will be deleted.)
It's my first list comprehension and getting into zip so I'd also appreciate advice on how to lay out the long single lines of code.
from __future__ import print_function from openpyxl import Workbook from openpyxl.utils import get_column_letter import openpyxl def do_widgets(ws, col0, col1): """ Process each widget. """ NO_WDGTS = 5 ENABLE_START_ROW = 6 ENABLE_END_ROW = ENABLE_START_ROW + NO_WDGTS - 1 WDGT_START_ROW = 1 WDGT_END_ROW = WDGT_START_ROW + NO_WDGTS - 1 # Coordinate code - delete whole block before implementation col_letter0 = get_column_letter(col0) col_letter1 = get_column_letter(col1) wdgos_range = [(col_letter0 + str(ENABLE_START_ROW) + ":" + col_letter0 + str(ENABLE_END_ROW))] wdgos_range.append(col_letter1 + str(WDGT_START_ROW) + ":" + col_letter1 + str(WDGT_END_ROW)) # Delete # wdgos = [cell.value for cell in zip(ws['B6:B10'], ws['C1:C5']) # if cell.value] wdgos = [cell.value for cell in zip(ws[wdgos_range], ws[wdgos_range]) if cell.value] # Row/column code wdgts = [cell for cell in zip([cell.value for col in ws.iter_cols(min_row=ENABLE_START_ROW, min_col=col0, max_row=ENABLE_END_ROW, max_col=col0) for cell in col], [cell.value for col in ws.iter_cols(min_row=WDGT_START_ROW, min_col=col1, max_row=WDGT_END_ROW, max_col=col1) for cell in col]) if cell] # Use list of enabled widgets print("Enable range , Widget range ") print(wdgos_range) for wdgo in wdgos: print(wdgo) print() print(wdgts, '\n') wb = Workbook() ws = wb.active rows = [ ['Number', 'Batch 1', 'Batch 2'], [2, 40, 30], [3, 40, 25], [4, 50, 30], [5, 30, 10], [0, 0, 0], [0, 1, 0], [1, 1, 0], [0, 0, 1], [0, 1, 0], ] for row in rows: ws.append(row) # Worksheet, enable column, widget column do_widgets(ws, 2, 3)
Check results ============= E501:25:80:line too long (85 > 79 characters) E127:26:46:continuation line over-indented for visual indent E128:30:22:continuation line under-indented for visual indent E128:34:22:continuation line under-indented for visual indent E128:36:22:continuation line under-indented for visual indent
Enable range , Widget range  ['B6:B10', 'C1:C5'] 30 25 10 [30, 25, 10]