def range_boundaries(address, cell=None, sheet=None): try: # if this is normal reference then just use the openpyxl converter boundaries = openpyxl_range_boundaries(address) if None not in boundaries or ':' in address: return boundaries, sheet except ValueError: pass # test for R1C1 style address boundaries = r1c1_boundaries(address, cell=cell, sheet=sheet) if boundaries: return boundaries # Try to see if the is a structured table reference boundaries = structured_reference_boundaries(address, cell=cell) if boundaries: return boundaries # Try to see if this is a defined name name_addr = cell and cell.excel and cell.excel.defined_names.get(address) if name_addr: return openpyxl_range_boundaries(name_addr[0]), name_addr[1] if len(address.split(':')) > 2: raise NotImplementedError("Multiple Colon Ranges: {}".format(address)) raise ValueError( "{0} is not a valid coordinate or range".format(address))
def range_boundaries(address, cell=None, sheet=None): try: # if this is normal reference then just use the openpyxl converter boundaries = openpyxl_range_boundaries(address) if None not in boundaries or ':' in address: return boundaries, sheet except ValueError: pass # test for R1C1 style address boundaries = r1c1_boundaries(address, cell=cell, sheet=sheet) if boundaries: return boundaries # Try to see if the is a structured table reference boundaries = structured_reference_boundaries(address, cell=cell) if boundaries: return boundaries # Try to see if this is a defined name name_addr = cell and cell.excel and cell.excel.defined_names.get(address) if name_addr: return openpyxl_range_boundaries(name_addr[0]), name_addr[1] if len(address.split(':')) > 2: raise NotImplementedError("Multiple Colon Ranges: {}".format(address)) raise ValueError("{0} is not a valid coordinate or range".format(address))
def range_boundaries(address, cell=None, sheet=None): try: # if this is normal reference then just use the openpyxl converter boundaries = openpyxl_range_boundaries(address) if None not in boundaries or ':' in address: return boundaries, sheet except ValueError: pass # test for R1C1 style address boundaries = r1c1_boundaries(address, cell=cell, sheet=sheet) if boundaries: return boundaries # Try to see if the is a structured table reference boundaries = structured_reference_boundaries(address, cell=cell) if boundaries: return boundaries # Try to see if this is a defined name name_addr = cell and cell.excel and cell.excel.defined_names.get(address) if name_addr: if len(name_addr) == 1: return openpyxl_range_boundaries(name_addr[0][0]), name_addr[0][1] else: return AddressMultiAreaRange( tuple( AddressRange(range_alias, sheet=worksheet) for range_alias, worksheet in name_addr)), None addrs = address.split(':') if len(addrs) > 2: # Multi colon range resolves to rectangle containing all nodes try: nodes = tuple( AddressRange.create(addr, cell=cell, sheet=sheet) for addr in addrs) min_col_idx = min(n.col_idx for n in nodes) max_col_idx = max((n.col_idx + n.size.width - 1) for n in nodes) min_row = min(n.row for n in nodes) max_row = max((n.row + n.size.height - 1) for n in nodes) sheets = {n.sheet for n in nodes if n.sheet} if not sheet: sheet = next(iter(sheets), None) assert not sheets or sheets == {sheet} return (min_col_idx, min_row, max_col_idx, max_row), sheet except ValueError: pass raise ValueError("{0} is not a valid coordinate or range".format(address))
def structured_reference_boundaries(address, cell=None): # Excel reference: https://support.office.com/en-us/article/ # Using-structured-references-with-Excel-tables- # F5ED2452-2337-4F71-BED3-C8AE6D2B276E match = TABLE_REF_RE.match(address) if not match: return None if cell is None: raise PyCelException( "Must pass cell for Structured Reference {}".format(address)) name = match.group('table_name') table, sheet = cell.excel.table(name) if table is None: raise PyCelException( "Table {} not found for Structured Reference: {}".format( name, address)) boundaries = openpyxl_range_boundaries(table.ref) assert None not in boundaries selector = match.group('table_selector') if not selector: # all columns and the data rows rows, start_col, end_col = None, None, None else: selector_match = TABLE_SELECTOR_RE.match(selector) if selector_match is None: raise PyCelException( "Unknown Structured Reference Selector: {}".format(selector)) row_or_column = selector_match.group('row_or_column') this_row_column = selector_match.group('this_row_column') if row_or_column: rows = start_col = None end_col = row_or_column elif this_row_column: rows = '#This Row' start_col = None end_col = this_row_column else: rows = selector_match.group('rows') start_col = selector_match.group('start_col') end_col = selector_match.group('end_col') if not rows: rows = None else: assert '[' in rows rows = [r.split(']')[0] for r in rows.split('[')[1:]] if len(rows) != 1: # not currently supporting multiple row selects raise PyCelException( "Unknown Structured Reference Rows: {}".format( address)) rows = rows[0] if end_col.startswith('#'): # end_col collects the single field case assert rows is None and start_col is None rows = end_col end_col = None elif end_col.startswith('@'): rows = '#This Row' end_col = end_col[1:] if len(end_col) == 0: end_col = start_col if rows is None: # skip the headers and footers min_row = boundaries[1] + (table.headerRowCount if table.headerRowCount else 0) max_row = boundaries[3] - (table.totalsRowCount if table.totalsRowCount else 0) else: if rows == '#All': min_row, max_row = boundaries[1], boundaries[3] elif rows == '#Data': min_row = boundaries[1] + (table.headerRowCount if table.headerRowCount else 0) max_row = boundaries[3] - (table.totalsRowCount if table.totalsRowCount else 0) elif rows == '#Headers': min_row = boundaries[1] max_row = boundaries[1] + (table.headerRowCount if table.headerRowCount else 0) - 1 elif rows == '#Totals': min_row = boundaries[3] - (table.totalsRowCount if table.totalsRowCount else 0) + 1 max_row = boundaries[3] elif rows == '#This Row': # ::TODO:: If not in a data row, return #VALUE! How to do this? min_row = max_row = cell.address.row else: raise PyCelException( "Unknown Structured Reference Rows: {}".format(rows)) if end_col is None: # all columns min_col_idx, max_col_idx = boundaries[0], boundaries[2] else: # a specific column column_idx = next( (idx for idx, c in enumerate(table.tableColumns) if c.name == end_col), None) if column_idx is None: raise PyCelException( "Column {} not found for Structured Reference: {}".format( end_col, address)) max_col_idx = boundaries[0] + column_idx if start_col is None: min_col_idx = max_col_idx else: column_idx = next((idx for idx, c in enumerate(table.tableColumns) if c.name == start_col), None) if column_idx is None: raise PyCelException( "Column {} not found for Structured Reference: {}".format( start_col, address)) min_col_idx = boundaries[0] + column_idx if min_row > max_row or min_col_idx > max_col_idx: raise PyCelException("Columns out of order : {}".format(address)) return (min_col_idx, min_row, max_col_idx, max_row), sheet
def structured_reference_boundaries(address, cell=None): # Excel reference: https://support.office.com/en-us/article/ # Using-structured-references-with-Excel-tables- # F5ED2452-2337-4F71-BED3-C8AE6D2B276E match = TABLE_REF_RE.match(address) if not match: return None if cell is None: raise PyCelException( "Must pass cell for Structured Reference {}".format(address)) name = match.group('table_name') table, sheet = cell.excel.table(name) if table is None: raise PyCelException( "Table {} not found for Structured Reference: {}".format( name, address)) boundaries = openpyxl_range_boundaries(table.ref) assert None not in boundaries selector = match.group('table_selector') if not selector: # all columns and the data rows rows, start_col, end_col = None, None, None else: selector_match = TABLE_SELECTOR_RE.match(selector) if selector_match is None: raise PyCelException( "Unknown Structured Reference Selector: {}".format(selector)) row_or_column = selector_match.group('row_or_column') this_row_column = selector_match.group('this_row_column') if row_or_column: rows = start_col = None end_col = row_or_column elif this_row_column: rows = '#This Row' start_col = None end_col = this_row_column else: rows = selector_match.group('rows') start_col = selector_match.group('start_col') end_col = selector_match.group('end_col') if not rows: rows = None else: assert '[' in rows rows = [r.split(']')[0] for r in rows.split('[')[1:]] if len(rows) != 1: # not currently supporting multiple row selects raise PyCelException( "Unknown Structured Reference Rows: {}".format( address)) rows = rows[0] if end_col.startswith('#'): # end_col collects the single field case assert rows is None and start_col is None rows = end_col end_col = None elif end_col.startswith('@'): rows = '#This Row' end_col = end_col[1:] if len(end_col) == 0: end_col = start_col if rows is None: # skip the headers and footers min_row = boundaries[1] + ( table.headerRowCount if table.headerRowCount else 0) max_row = boundaries[3] - ( table.totalsRowCount if table.totalsRowCount else 0) else: if rows == '#All': min_row, max_row = boundaries[1], boundaries[3] elif rows == '#Data': min_row = boundaries[1] + ( table.headerRowCount if table.headerRowCount else 0) max_row = boundaries[3] - ( table.totalsRowCount if table.totalsRowCount else 0) elif rows == '#Headers': min_row = boundaries[1] max_row = boundaries[1] + ( table.headerRowCount if table.headerRowCount else 0) - 1 elif rows == '#Totals': min_row = boundaries[3] - ( table.totalsRowCount if table.totalsRowCount else 0) + 1 max_row = boundaries[3] elif rows == '#This Row': # ::TODO:: If not in a data row, return #VALUE! How to do this? min_row = max_row = cell.address.row else: raise PyCelException( "Unknown Structured Reference Rows: {}".format(rows)) if end_col is None: # all columns min_col_idx, max_col_idx = boundaries[0], boundaries[2] else: # a specific column column_idx = next((idx for idx, c in enumerate(table.tableColumns) if c.name == end_col), None) if column_idx is None: raise PyCelException( "Column {} not found for Structured Reference: {}".format( end_col, address)) max_col_idx = boundaries[0] + column_idx if start_col is None: min_col_idx = max_col_idx else: column_idx = next((idx for idx, c in enumerate(table.tableColumns) if c.name == start_col), None) if column_idx is None: raise PyCelException( "Column {} not found for Structured Reference: {}".format( start_col, address)) min_col_idx = boundaries[0] + column_idx if min_row > max_row or min_col_idx > max_col_idx: raise PyCelException("Columns out of order : {}".format(address)) return (min_col_idx, min_row, max_col_idx, max_row), sheet