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 size(self):
"""
return size of range
:return: columns, rows
"""
temp=_a2cr(self.address)
return temp[2]-temp[0]+1,temp[3]-temp[1]+1
def outline(self, boundaries):
"""
TODO
group rows as defined by boundaries object
:param boundaries: dictionary, where keys are group "main level" and values is a list of two
identifying subrows referring to main level
:return:
"""
import six
top=2**20
lvl=0
for k,[f, l] in six.iteritems(boundaries):
if l<top:
top=l # this only works if boundaries are correctly sorted
lvl+=1
r=self.offset(r=k).resize(r=l-k).entire_row()
coords = _a2cr(r.address)
for row in range(coords[1], coords[3] + 1): # if I don't handle each row separately levels get overwritten!
addr='%i:%i'%(row,row)
if addr not in self.sheet.cell_options.keys(): self.sheet.cell_options[addr] = {}
if 'level' in self.sheet.cell_options[addr].keys():
lvl=max(lvl,self.sheet.cell_options[addr]['level'])
self.sheet.cell_options[addr]['level']=lvl
if lvl>2:
self.sheet.cell_options[addr]['collapsed']=True
elif lvl==2:
self.sheet.cell_options[addr]['hidden'] = True
r=self.offset(r=k-1).row(1)
r.font_format(bold=True)
def freeze_panes(self):
"""
freezes panes at upper left cell of range
:return:
"""
c,r=_a2cr(self.address)
self.parent.ws.freeze_panes(r-1,c-1)
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 row_height(self, h):
"""
change height of the row(s) of range
:param h: height
:return:
"""
c1,r1,c2,r2=_a2cr(self.address)
self.parent.ws.set_column(r1-1,height=h)
def column_width(self, w):
"""
change width of the column(s) of range
:param w: width
:return:
"""
c1,r1,c2,r2=_a2cr(self.address)
self.parent.ws.set_column(c1-1,c2-1,width=w)
def entire_col(self):
"""
get entire row(s) of current range
:return: new object
"""
c = _a2cr(self.address)
if len(c) == 2: c += c
cc = '%s:%s' % (_n2x(c[0]), _n2x(c[2]))
return Rng(address=cc, sheet=self.sheet)
def entire_row(self):
"""
get entire row(s) of current range
:return: new object
"""
c = _a2cr(self.address)
if len(c) == 2: c += c
cc = '%s:%s' % (c[1], c[3])
return Rng(address=cc, sheet=self.sheet)
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 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 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 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)
def close(self):
"""
close a workbook without saving it
:return:
"""
#create workbook
self.wb=XLW.Workbook(self.path,{'nan_inf_to_errors': True,'default_date_format': 'yyyy-mm-dd',})
#create sheets
for sheet in self.sheets:
sheet.ws=self.wb.add_worksheet(sheet.name)
sheet.ws.outline_settings(outline_below=False,outline_right=False)
# write all data to all sheets
for sheet in self.sheets:
cells=list(set(sheet.cell_data.keys()).
union(set(sheet.cell_formats.keys())).
union(set(sheet.cell_options.keys()))
)
rows=list(filter(lambda x: _isrow(x),cells))
for row in rows:
if row in sheet.cell_formats.keys():
format = sheet.cell_formats[row]
format = self.wb.add_format(format)
else:
format=None
if row in sheet.cell_options.keys():
options= sheet.cell_options[row]
else:
options={}
coords=_a2cr(row)
for r in range(coords[1],coords[3]+1):
sheet.ws.set_row(r,None,cell_format=format,options=options)
cols=list(filter(lambda x: _iscol(x),cells))
for col in cols:
if col in sheet.cell_formats.keys():
format = sheet.cell_formats[col]
format = self.wb.add_format(format)
else:
format=None
if col in sheet.cell_options.keys():
options= sheet.cell_options[row]
else:
options={}
coords=_a2cr(row)
for c in range(coords[0],coords[2]+1):
sheet.ws.set_col(c,None,cell_format=format,options=options)
cells=list(set(cells)-set(rows)-set(cols))
for cell in cells:
if cell in sheet.cell_formats.keys():
format=sheet.cell_formats[cell]
format = self.wb.add_format(format)
else:
format=None
if cell in sheet.cell_data.keys():
value=sheet.cell_data[cell]
if len(str(value))>0:
if str(value)[0]=='=':
sheet.ws.write_formula(cell, value, format)
elif str(value)[0]== '{':
sheet.ws.write_array_formula(cell, value, format)
else:
coords=_a2cr(cell)
if len(coords)==2:
coords=[coords[0],coords[1],coords[0],coords[1]]
for c in range(coords[0],coords[2]+1):
for r in range(coords[1],coords[3]+1):
sheet.ws.write(r - 1, c - 1, value, format)
else:
coords=_a2cr(cell)
if len(coords)==2:
coords=[coords[0],coords[1],coords[0],coords[1]]
for c in range(coords[0],coords[2]+1):
for r in range(coords[1],coords[3]+1):
sheet.ws.write(r - 1, c - 1, value, format)
else:
sheet.ws.write_blank(cell, None, format)
for addr, figpath in sheet.images.items():
c, r = _a2cr(addr)
sheet.ws.insert_image(r, c, figpath)
self.parent.workbooks.remove(self)
self.wb.close()
