Beispiel #1
0
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)
Beispiel #2
0
 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)
Beispiel #3
0
 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)
Beispiel #4
0
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)
Beispiel #5
0
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)
Beispiel #6
0
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 )
Beispiel #7
0
 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)
Beispiel #8
0
 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)