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
