def _insert_row_with_style(self, worksheet, row): # вставляем строку worksheet.insert_rows(row, 1) # копируем стили for i, cell in enumerate( worksheet.iter_cols(min_col=1, min_row=row + 1, max_row=row + 1), 1): copy_cell = worksheet.cell(row, i) if cell[0].data_type == 'f': trans = Translator( cell[0].value, str(cell[0].column_letter) + str(cell[0].row - 1)) copy_cell.value = str( trans.translate_formula( str(copy_cell.column_letter) + str(copy_cell.row))) cell[0].value = str( trans.translate_formula( str(cell[0].column_letter) + str(cell[0].row))) copy_cell.font = copy(cell[0].font) copy_cell.border = copy(cell[0].border) copy_cell.fill = copy(cell[0].fill) copy_cell.number_format = copy(cell[0].number_format) copy_cell.protection = copy(cell[0].protection) copy_cell.alignment = copy(cell[0].alignment)
def conditional_format(self, address): """ Return the conditional formats applicable for this cell """ address = AddressCell(address) all_formats = self.workbook[address.sheet].conditional_formatting formats = (cf for cf in all_formats if address.coordinate in cf) rules = [] for cf in formats: origin = AddressRange(cf.cells.ranges[0].coord).start row_offset = address.row - origin.row col_offset = address.col_idx - origin.col_idx for rule in cf.rules: if rule.formula: trans = Translator('={}'.format(rule.formula[0]), origin.coordinate) formula = trans.translate_formula(row_delta=row_offset, col_delta=col_offset) rules.append( self.CfRule( formula=formula, priority=rule.priority, dxf_id=rule.dxfId, dxf=rule.dxf, stop_if_true=rule.stopIfTrue, )) return sorted(rules, key=lambda x: x.priority)
def set_formula(self, addr, formula): if addr in self.cellmap: cell = self.cellmap[addr] else: raise Exception('Cell %s not in cellmap' % addr) seeds = [cell] if cell.is_range: for index, c in enumerate(cell.range.cells): # for each cell of the range, translate the formula if index == 0: c.formula = formula translator = Translator(unicode('=' + formula), c.address().split('!')[1]) # the Translator needs a reference without sheet else: translated = translator.translate_formula(c.address().split('!')[1]) # the Translator needs a reference without sheet c.formula = translated[1:] # to get rid of the '=' seeds.append(c) else: cell.formula = formula cellmap, G = graph_from_seeds(seeds, self) self.cellmap = cellmap self.G = G should_eval = self.cellmap[addr].should_eval self.cellmap[addr].should_eval = 'always' self.evaluate(addr) self.cellmap[addr].should_eval = should_eval print("Graph construction updated, %s nodes, %s edges, %s cellmap entries" % (len(G.nodes()),len(G.edges()),len(cellmap)))
def _move_cell(self, row, column, row_offset, col_offset, translate=False): """ Move a cell from one place to another. Delete at old index Rebase coordinate """ cell = self._get_cell(row, column) new_row = cell.row + row_offset new_col = cell.column + col_offset self._cells[new_row, new_col] = cell del self._cells[(cell.row, cell.column)] cell.row = new_row cell.column = new_col if translate and cell.data_type == "f": t = Translator(cell.value, cell.coordinate) cell.value = t.translate_formula(row_delta=row_offset, col_delta=col_offset)
encoding='ISO-8859-1') merged_df = pd.concat([sales_df, new_sales_df], sort=False) # update sales_records worksheet print('----apply formula for appended data----') sales_ws = wb['sales_records'] rows = dataframe_to_rows(merged_df, index=False) for r_idx, row in enumerate(rows, 1): for c_idx, value in enumerate(row, 1): if isinstance(value, float) and math.isnan(value): source_cell_idx = str(get_column_letter(c_idx)) + str(r_idx - 1) target_cell_idx = str(get_column_letter(c_idx)) + str(r_idx) trans = Translator(sales_ws.cell(row=r_idx - 1, column=c_idx).value, origin=source_cell_idx) value = trans.translate_formula(target_cell_idx) sales_ws.cell(row=r_idx, column=c_idx, value=value) # merge excel chart data print('----merge chart data----') chart_df = convert_ws_df(wb['chart_sample'], True) new_chart_df = pd.read_csv('files/Chart_Sales_Records.csv', encoding='ISO-8859-1') merged_df = pd.concat([chart_df, new_chart_df], sort=False) # update chart worksheet print('----refresh worksheet chart----') chart_ws = wb['chart_sample'] rows = dataframe_to_rows(merged_df, index=False) for r_idx, row in enumerate(rows, 1): for c_idx, value in enumerate(row, 1):