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()