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)
