def offset(self, r=0, c=0): """ return new range object offset from the original by r rows and c columns :param r: number of rows to offset by :param c: number of columns to offset by :return: new range object """ coords = _a2cr(self.address) if len(coords) == 2: newaddr = _cr2a(coords[0] + c, coords[1] + r) else: newaddr = _cr2a(coords[0] + c, coords[1] + r, coords[2] + c, coords[3] + r) return Rng(address=newaddr, sheet=self.sheet)
def resize(self, r=0, c=0, abs=True): """ new range object with address with same top left coordinate but different size (see abs param) :param r: :param c: :param abs: if true, then r and c determine the new size, otherwise they are added to current size :return: new range object """ coords = _a2cr(self.address) if len(coords) == 2: coords = coords + coords if abs: newaddr = _cr2a(coords[0], coords[1], coords[0] + max(0, c - 1), coords[1] + max(0, r - 1)) else: newaddr = _cr2a(coords[0], coords[1], max(coords[0], coords[2] + c), max(coords[1], coords[3] + r)) return Rng(address=newaddr, sheet=self.sheet)
def from_pandas(self, pdobj, header=True, index=True, index_label=None, outline_string=None): """ write a pandas object to excel via clipboard :param pdobj: any DataFrame or Series object see DataFrame.to_clipboard? for info on params below :param header: if False, strip header :param index: if False, strip index :param index_label: index header :param outline_string: a string used to identify outline main levels (eg " All") :return: """ temp=_df_to_ll(pdobj,header=header,index=index) trange = self.resize(len(temp), len(temp[0])) coords=_a2cr(trange.address) for j,c in enumerate(range(coords[0],coords[2]+1)): for i,r in enumerate(range(coords[1], coords[3] + 1)): addr=_cr2a(c,r) self.sheet.cell_data[addr] = temp[i][j] self.address = trange.address if outline_string is not None: boundaries = _df2outline(pdobj, outline_string) self.outline(boundaries) return trange
def iloc(self, r=0, c=0): """ return a cell in the range based on coordinates starting from left top cell :param r: row index :param c: columns index :return: """ coords = _a2cr(self.address) newaddr = _cr2a(coords[0] + c, coords[1] + r) return Rng(address=newaddr, sheet=self.sheet)
def _get_contiguous(address, cells): #get current top left and bottom right boundaries=_a2cr(address,f4=True) #look for any cells contiguous to current rectangle for c in cells: cc=_a2cr(c,f4=True) #coords of current cell if _is_contiguous(boundaries,cc): # expand top left bottom right boundaries=_expand_range(boundaries,cc) return _cr2a(*boundaries)
def subrng(self, t, l, nr=1, nc=1): """ given a range returns a subrange defined by relative coordinates :param t: row offset from current top row :param l: column offset from current top column :param nr: number of rows in subrange :param nc: number of columns in subrange :return: range object """ coords = _a2cr(self.address) newaddr = _cr2a(coords[0] + l, coords[1] + t, coords[0] + l + nc-1, coords[1] + t + nr-1) return Rng(address=newaddr, sheet=self.sheet)
def col_dict(self): """ given a range with a header row, return a dictionary of range objects, each representing a column of the current range :return: dict, where keys are header strings, while values are column range objects """ out = {} hdr = self.row(1).value()[0] c1, r1, c2, r2 = _a2cr(self.address) for n, c in zip(hdr, range(c1, c2 + 1)): na = _cr2a(c, r1 + 1, c, r2) out[n] = Rng(address=na, sheet=self.sheet) return out
def column(self, idx): """ range with given col of current range :param idx: indexing is 1-based, negative indices start from last col :return: new range object """ coords = _a2cr(self.address) if len(coords) == 2: return _copy(self) else: newcoords = _copy(coords) if idx < 0: newcoords[0] = newcoords[2] + idx + 1 else: newcoords[0] += idx - 1 newcoords[2] = newcoords[0] newaddr = _cr2a(*newcoords) return Rng(address=newaddr, sheet=self.sheet)