def join(key_range_a, key_range_b):
"""Joins the table associated with key range B to the table for key range A.
Each key in range A must have zero or one matching keys in range B (i.e. rows will not be added to table A)"""
b_headers, b_key_map = _join_map(key_range_b)
assert not b_headers is None, "Headerless tables not supported yet"
# Number of columns being added to table A
num_joined_cols = len(b_headers)
if num_joined_cols == 0:
raise ValueError(
"key_range_b indicates the source table; there must be at least one value column in addition to the key column"
)
new_rows = [b_headers]
for a_key in key_range_a:
v = b_key_map.get(a_key, ("", ) * num_joined_cols)
assert len(v) == num_joined_cols
new_rows.append(v)
ws_a = Worksheet(key_range_a._full_xlRange.Worksheet)
tb_a = ws_a._find_table_containing_range(key_range_a)
# We may have appended a single column
joined_cols = tb_a.append_empty_columns(num_joined_cols)
# If num_joined_cols is 1, may behave as a vector or scalar. However,
# new_rows is constructed for a 2D range
joined_cols.as_matrix.set(new_rows)
def normalize(self):
"""Return a normalized version of this range. The returned range is reduced to
encompass only in-use areas of the worksheet, and (if applicable) the data area of its table"""
from xl.sheet import Worksheet # $$$ remove this cyclical reference ; take WS on construction
normalized = _trim_xlRange(self._full_xlRange)
# If we're in a table, snap to the data range (excludes headers).
s = Worksheet(self._full_xlRange.Worksheet)
t = s._find_table_containing_range(self)
if (t != None):
normalized = self._full_xlRange.Application.Intersect(normalized, t.rData._full_xlRange)
return self._with_xlRange(normalized)
def normalize(self):
"""Return a normalized version of this range. The returned range is reduced to
encompass only in-use areas of the worksheet, and (if applicable) the data area of its table"""
from xl.sheet import Worksheet # $$$ remove this cyclical reference ; take WS on construction
normalized = _trim_xlRange(self._full_xlRange)
# If we're in a table, snap to the data range (excludes headers).
s = Worksheet(self._full_xlRange.Worksheet)
t = s._find_table_containing_range(self)
if (t != None):
normalized = self._full_xlRange.Application.Intersect(normalized, t.rData._full_xlRange)
return self._with_xlRange(normalized)
def _join_map(r):
ws = Worksheet(r._full_xlRange.Worksheet)
tb = ws._find_table_containing_range(r)
key_col_idx = r.column - tb.rData.column
headers = None
if tb.rHeader:
assert not tb.rHeader.shape is Scalar
headers = list(tb.rHeader.get())
del headers[key_col_idx]
m = {}
for r in tb.data_rows:
assert not r[key_col_idx] in m, "Duplicate key during join"
m[r[key_col_idx]] = r[:key_col_idx] + r[key_col_idx + 1:]
return (headers, m)
def _join_map(r):
ws = Worksheet(r._full_xlRange.Worksheet)
tb = ws._find_table_containing_range(r)
key_col_idx = r.column - tb.rData.column
headers = None
if tb.rHeader:
assert not tb.rHeader.shape is Scalar
headers = list(tb.rHeader.get())
del headers[key_col_idx]
m = {}
for r in tb.data_rows:
assert not r[key_col_idx] in m, "Duplicate key during join"
m[r[key_col_idx]] = r[:key_col_idx] + r[key_col_idx + 1:]
return (headers, m)
def join(key_range_a, key_range_b):
"""Joins the table associated with key range B to the table for key range A.
Each key in range A must have zero or one matching keys in range B (i.e. rows will not be added to table A)"""
b_headers, b_key_map = _join_map(key_range_b)
assert not b_headers is None, "Headerless tables not supported yet"
# Number of columns being added to table A
num_joined_cols = len(b_headers)
if num_joined_cols == 0:
raise ValueError("key_range_b indicates the source table; there must be at least one value column in addition to the key column")
new_rows = [ b_headers ]
for a_key in key_range_a:
v = b_key_map.get(a_key, ("",) * num_joined_cols)
assert len(v) == num_joined_cols
new_rows.append(v)
ws_a = Worksheet(key_range_a._full_xlRange.Worksheet)
tb_a = ws_a._find_table_containing_range(key_range_a)
# We may have appended a single column
joined_cols = tb_a.append_empty_columns(num_joined_cols)
# If num_joined_cols is 1, may behave as a vector or scalar. However,
# new_rows is constructed for a 2D range
joined_cols.as_matrix.set( new_rows )
def containing_table(self):
"""If this range is partially or fully contained in a Table, returns the table
Otherwise, returns None"""
from xl.sheet import Worksheet # $$$ remove this cyclical reference ; take WS on construction
ws = Worksheet(self._full_xlRange.Worksheet)
return ws._find_table_containing_range(self)
def containing_table(self):
"""If this range is partially or fully contained in a Table, returns the table
Otherwise, returns None"""
from xl.sheet import Worksheet # $$$ remove this cyclical reference ; take WS on construction
ws = Worksheet(self._full_xlRange.Worksheet)
return ws._find_table_containing_range(self)