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 write_to_exel(our_dict, sheet): try: book = load_workbook(file_exel) except FileNotFoundError: book = Workbook() book.remove(book['Sheet']) finally: ws = book.create_sheet(sheet) i, j = 2, 2 formula = "=SUM((C2-B2)/B2)" ws['C3'] = formula for key in sorted(our_dict.keys()): ws.cell(row=1, column=j).value = key for key1, value1 in sorted(our_dict[key].items()): if ws.cell(row=i, column=1).value != key1: ws.cell(row=i, column=1).value = key1 ws.cell(row=i, column=j).value = value1 ws.cell(row=(i + 1), column=j).value = Translator( formula, origin="C3").translate_formula( ws.cell(row=(i + 1), column=j).coordinate) i += 2 else: ws.cell(row=i, column=j).value = value1 ws.cell(row=(i + 1), column=j).value = Translator( formula, origin="C3").translate_formula( ws.cell(row=(i + 1), column=j).coordinate) i += 2 i = 2 j += 1 book.save(filename=file_exel)
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 _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 busca_vecino(): global último_renglón # Busca el último pago realizado por el vecino indicado, actualizando las fórmulas en caso # de ser necesario renglón_buscado = None nuevo_renglón = len(benef_pagos) + 1 reinicializa_renglon() for x in iterador: if x.value == cmbVecinos.get(): renglón_buscado = x.row for idx, col_name in enumerate(columns_pagos): from_cell = f"{get_column_letter(idx+1)}{renglón_buscado}" to_cell = f"{get_column_letter(idx+1)}{nuevo_renglón}" # if cell content is a formula, asume it as relative addressing, else, copy it directly if isinstance( ws_vigilancia[from_cell].value, str) and ws_vigilancia[from_cell].value[0] == '=': valor_celda = Translator(ws_vigilancia[from_cell].value, origin=from_cell) \ .translate_formula(to_cell) else: valor_celda = ws_vigilancia[from_cell].value if valor_celda is None: valor_celda = '' # print(f" {col_name+':':20} {valor_celda}") if col_name == 'Beneficiario': var_Beneficiario.insert(0, valor_celda) elif col_name == 'Dirección': var_Direccion.insert(0, valor_celda) elif col_name == 'E-mail o celular': var_Email.insert(0, valor_celda) elif col_name == 'Fecha': var_Fecha.insert(0, valor_celda.strftime("%d-%m-%Y")) elif col_name == 'Monto': var_Monto.insert( 0, valor_celda if es_fórmula(valor_celda) else edita_número(valor_celda)) elif col_name == 'Monto $': var_Monto_USD.insert( 0, valor_celda if es_fórmula(valor_celda) else edita_número(valor_celda)) elif col_name == 'Concepto': var_Concepto.insert(0, valor_celda) elif col_name == 'Categoría': var_Categoria.insert(0, valor_celda) elif col_name == 'Generar': var_Generar.set(bool(valor_celda)) elif col_name == 'Enviado': var_Enviado.set(bool(valor_celda)) elif col_name == '$': var_chk_USD.set(bool(valor_celda)) último_renglón = renglón_buscado break if DEBUG: print(f" -> último renglón = {último_renglón}")
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)
def apply_grand_total(grandtotalRows, worksheet, endRow, startCol, endCol, referenceRow, grandTotalTitle="Total"): row = worksheet[endRow] row[0].value = grandTotalTitle row[0].font = bold_font for j in range(startCol, endCol): target = row[j] source = referenceRow[j] if source.data_type == 'f': target.value = Translator(source.value, source.coordinate).translate_formula( target.coordinate) else: sumFormula = '' for sumRow in grandtotalRows: sumFormula = sumRow[ j].coordinate if sumFormula == '' else sumFormula + '+' + sumRow[ j].coordinate target.value = "=SUM(" + sumFormula + ")" copy_style(source, target) apply_style([row], startCol=startCol, endCol=endCol)
def parse_formula(self, element): """ possible formulae types: shared, array, datatable """ formula = element.find(FORMULA_TAG) formula_type = formula.get('t') coordinate = element.get('r') value = "=" if formula.text is not None: value += formula.text if formula_type == "array": value = ArrayFormula(ref=formula.get('ref'), text=value) elif formula_type == "shared": idx = formula.get('si') if idx in self.shared_formulae: trans = self.shared_formulae[idx] value = trans.translate_formula(coordinate) elif value != "=": self.shared_formulae[idx] = Translator(value, coordinate) elif formula_type == "dataTable": value = DataTableFormula(**formula.attrib) return value
def TestInput(data): fdp = atheris.FuzzedDataProvider(data) #Initial tokenizer for random string and process it Tokenizer(fdp.ConsumeString(200)) #Translate random string formulae Translator(fdp.ConsumeString(200), origin="A1").translate_formula("B2")
def fill_formula(self, col, n: int = 1, name: str = 'ActLog'): """Copy cell formula down in col""" ws = self.wb[name] # NOTE ws max row kinda messy, checks max row with table/formats, NOT values start_row = self.max_row for i in range(n): c = ws.cell(start_row + i, col) ws.cell(start_row + i + 1, col).value = Translator(c.value, origin=c.coordinate) \ .translate_formula(inc_rng(c.coordinate))
def adding_rows(column, origin=None, formula=None, value=None): if formula: for x in range(3, 27): planilha.cell(row=x, column=column, value=Translator(formula, origin=origin).translate_formula( row_delta=x - 3, col_delta=0)) if value: for x in range(3, 27): planilha.cell(row=x, column=column, value=value)
def test_shared_formula(self, WorkSheetParser): parser = WorkSheetParser src = """ <c r="A9" t="str" xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"> <f t="shared" si="0"/> <v>9</v> </c> """ element = fromstring(src) parser.shared_formulae['0'] = Translator("=A4*B4", "A1") formula = parser.parse_formula(element) assert formula == "=A12*B12"
def pasteRangeCols(startCol, startRow, endCol, endRow, sheetReceiving, sourceSheet, sourceStartRowOffset=0, sourceStartColOffset=0, enableRowGrouping=False, enableColGrouping=False, extend_data=False, style_col=-1): countRow = 0 + sourceStartRowOffset total_rows = endRow - startRow total_cols = endCol - startCol groupRowList = get_group_ranges(range(sourceStartRowOffset, total_rows + sourceStartRowOffset), sourceSheet.row_dimensions, offset = startRow - sourceStartRowOffset, cl_type='row') \ if enableRowGrouping else [] groupColList = get_group_ranges(range(sourceStartColOffset+1, total_cols+ sourceStartColOffset+2), sourceSheet.column_dimensions, offset = startCol - sourceStartColOffset-1, cl_type='col') \ if enableColGrouping else [] for i in range(startRow, endRow + 1, 1): countCol = 1 + sourceStartColOffset for j in range(startCol, endCol + 1, 1): source = sourceSheet.cell(row=sourceStartRowOffset, column=j) \ if extend_data else sourceSheet.cell(row=countRow+1, column=countCol) style_cell = sourceSheet.cell( row=countRow + 1, column=style_col) if style_col > 0 else source value = source.value target = sheetReceiving.cell(row=i, column=j) if source.data_type == 'f': target.value = Translator(value, source.coordinate).translate_formula( target.coordinate) else: target.value = value countCol += 1 copy_style(style_cell, target) countRow += 1 if enableRowGrouping: apply_groups(sheetReceiving.row_dimensions, groupRowList, cl_type='row') if enableColGrouping: apply_groups(sheetReceiving.column_dimensions, groupColList, cl_type='col')
def append(self, element, insertMode=False, **kwargs): line = self.getCurrentLine() cell = self.currentSheet.cell(row=line, column=self.col) if insertMode: #copy previous line's cell value above = self.currentSheet.cell(row=line - 1, column=self.col) cell._style = above._style cell.number_format = above.number_format if element == '' and type( above.value) is str and above.value.startswith( '='): # need to translate formula cell.value = Translator( above.value, origin=above.coordinate).translate_formula(cell.coordinate) if element != '' and not cell.protection.locked: cell.value = element self.col += 1
def calc_cell(sheet): line_cnt = 0 for row in sheet.rows: if line_cnt > 100: return line_cnt = line_cnt + 1 need_lines = 0 for cell in row: #模板计算 if cell.value is not None and cell.data_type == 's' and cell.value.find( '{{') > -1: result = exec_template(env, cell.value, real_dict) row = cell.row start_col = cell.column result_lines = result.split('\n') for one_line in result_lines: #展开模板计算结果 col = start_col for one in one_line.split(): p_cell = sheet.cell(row=row, column=col) if len(one) < 14 and is_number( one): #14位工号,已经到万亿了,现在还不可能有这么大的数 p_cell.value = float(one) p_cell.data_type = 'n' else: p_cell.value = one col = col + 1 row = row + 1 need_lines = row - cell.row continue #复制公式 elif cell.value is not None and cell.data_type == 'f' and cell.value.startswith( '='): row = cell.row + 1 for one in range(1, need_lines): p_cell = sheet.cell(row=row, column=cell.column).coordinate sheet[p_cell] = Translator( cell.value, origin=cell.coordinate).translate_formula(p_cell) row = row + 1 need_lines = row - cell.row continue need_lines = need_lines
def pasteRange(startCol, startRow, endCol, endRow, sheetReceiving, copiedData, rowOffset=0, colOffset=0): firstList = [] lastList = [] #typeList = [] countRow = 1 if startRow == endRow: endRow = startRow + 1 for i in list(range(startRow, endRow, 1)): countCol = 1 for j in range(startCol, endCol, 1): source = copiedData.cell(row=countRow + rowOffset, column=countCol) target = sheetReceiving.cell(row=i, column=j) #if type(target).__name__ == 'MergedCell': # print('hello') if source.data_type == 'f': target.value = Translator(source.value, source.coordinate).translate_formula( target.coordinate) else: target.value = source.value #if source.has_style: # target._style = copy(source._style) copy_style(source, target) if i == startRow: firstList.append(target.coordinate) #typeList.append(source.data_type) if i == endRow - 1: lastList.append(target.coordinate) countCol += 1 countRow += 1 #return map(lambda x, y: str(x) + ":" + y, firstList, lastList) return [str(i) + ":" + str(j) for i, j in zip(firstList, lastList)] #, typeList
def parse_formula(self, element): """ possible formulae types: shared, array, datatable """ formula = element.find(FORMULA_TAG) data_type = 'f' formula_type = formula.get('t') coordinate = element.get('r') value = "=" if formula.text is not None: value += formula.text if formula_type == "array": self.array_formulae[coordinate] = dict(formula.attrib) elif formula_type == "shared": idx = formula.get('si') if idx in self.shared_formulae: trans = self.shared_formulae[idx] value = trans.translate_formula(coordinate) elif value != "=": self.shared_formulae[idx] = Translator(value, coordinate) return value
from openpyxl import load_workbook from openpyxl.formula.translate import Translator wb = load_workbook('./books/21/21.turnover_worker.xlsx') ws = wb.active ws['D3'] = Translator(ws['C3'].value, origin='C3').translate_formula('D3') wb.save('./books/21/21.turnover_worker_changed.xlsx')
def write_to_excel(self, mv, DataFrame, path='', file_name='document.xlsx', list_columns=None, sheet=''): if sheet == '': list_sheet = dict(map(lambda ws: (ws.title, ws), mv.worksheets)) else: list_sheet = [sheet] for sheet in list_sheet: get_descript_sheet = mv[sheet] tmp_startrow = 0 for mark, (startcol, startrow) in self.create_marks(get_descript_sheet, list_columns): self.log.message_debug("Finde mark: {}".format(mark)) if startrow != tmp_startrow and len(DataFrame.index) > 1: tmp_startrow = startrow get_descript_sheet.insert_rows(startrow + 1, len(DataFrame)) for index, row in enumerate(DataFrame.loc[:, [mark]].iterrows()): if type( get_descript_sheet.cell( row=startrow + index, column=startcol)).__name__ != 'MergedCell': cell = get_descript_sheet.cell(row=startrow, column=startcol) new_cell = get_descript_sheet.cell( row=startrow, column=startcol).offset(row=index, column=0) from openpyxl.formula.translate import Translator from openpyxl.formula import Tokenizer tok = Tokenizer(str(row[1][mark])) if tok.formula.startswith('='): new_cell.value = Translator( row[1][mark], origin=cell.coordinate).translate_formula( row_delta=startrow - 2) else: new_cell.value = row[1][mark] if cell.has_style: new_cell.font = copy(cell.font) new_cell.border = copy(cell.border) new_cell.fill = copy(cell.fill) new_cell.number_format = copy(cell.number_format) new_cell.protection = copy(cell.protection) new_cell.alignment = copy(cell.alignment) for key, col, style in self.sp.pars_style_from_dataframe( DataFrame.iloc[index]['Format']): if col == mark: if key == 'style': get_descript_sheet.cell( row=startrow + index, column=startcol).style = style if key == 'merge_cell': get_descript_sheet.merge_cells( start_row=startrow + index, start_column=startcol, end_row=startrow + index + style[1], end_column=startcol + style[0]) if key == 'hide': get_descript_sheet.row_dimensions[ startrow + index].hidden = True new_cell.value = style mv.save(os.path.join(path, file_name)) # mv.close() self.log.message_debug("Write file: {}".format(path))
def createMergedSheet(worksheet, regex_filter, workbook, startCol, startRow, initialRowOffset, postRowShrinkage, groupRows=False, subtotalRows=False, totalColOffset=0, totalColOffsetUpperBound=-1, subtotalFunctionNum=9, grandTotal=False, grandTotalTitle=None): print("Processing...") itemList = list( filter(lambda i: regex_filter.match(i), workbook.sheetnames)) firstSheet = None subtotalOffset = 1 if subtotalRows else 0 listOfSubTotals = [] for sn in itemList: startRow += subtotalOffset sheet1 = workbook[sn] # Add Sheet name firstSheet = sheet1 if sheet1 == None else sheet1 endCol = sheet1.max_column endRow = startRow + sheet1.max_row - initialRowOffset - postRowShrinkage print('sc:{} sr: {} ec: {} er: {} sn: {} subtotal {} grandTotal {}'. format(startCol, startRow, endCol, endRow, sn, subtotalRows, grandTotal)) subtotalCoordinates = pasteRange( startCol, startRow, endCol, endRow, worksheet, sheet1, initialRowOffset) # Change the 4 number values if subtotalRows: subTotalTitle = sn.replace("-MTD", "").replace("-YTD", "").replace("-QTD", "") worksheet.cell(row=startRow - 1, column=2).value = subTotalTitle listRowSubTotal = [] for j in range(totalColOffset, endCol, 1): target = worksheet.cell(row=startRow - 1, column=j) source = worksheet.cell(row=startRow, column=j) if source.data_type == 'f': target.value = Translator( source.value, source.coordinate).translate_formula(target.coordinate) else: target.value = "=SUBTOTAL(" + str( subtotalFunctionNum) + "," + subtotalCoordinates[ j - 1] + ")" copy_style(source, target) listRowSubTotal.append(target.coordinate) if grandTotal: listOfSubTotals.append(listRowSubTotal) rows = [worksheet[startRow - 1]] apply_style(rows, border=None) if len(itemList) == 1: if grandTotalTitle: worksheet.cell(row=startRow - 1, column=1).value = grandTotalTitle if groupRows: #for idx in range(startRow, endRow): worksheet.row_dimensions.group(start=startRow, end=endRow - 1, hidden=True) startRow = endRow #break if grandTotal: startCell = worksheet.cell(row=endRow, column=1) startCell.value = "Total" if grandTotalTitle is None else grandTotalTitle startCell.font = bold_font grandTotalList = [] if subtotalRows: temp = [] grandTotalList = ['' for i in listOfSubTotals[0]] for item in listOfSubTotals: temp = [ str(i) + "+" + str(j) if len(i) > 0 else j for i, j in zip(grandTotalList, item) ] grandTotalList = temp else: for j in range(startCol, endCol): grandTotalList.append( worksheet.cell(startRow, j).coordinate + ":" + worksheet.cell(endRow, j).coordinate) if totalColOffsetUpperBound > 0: endCol = totalColOffsetUpperBound for j in range(totalColOffset, endCol, 1): target = worksheet.cell(row=endRow, column=j) source = worksheet.cell(row=endRow - 2, column=j) if source.data_type == 'f': target.value = Translator(source.value, source.coordinate).translate_formula( target.coordinate) else: target.value = "=SUM(" + grandTotalList[j - totalColOffset] + ")" copy_style(source, target) rows = [worksheet[endRow]] apply_style(rows, startCol=totalColOffset - 1, endCol=endCol) # You can save the template as another file to create a new file here too.s print("Range transferred!") return firstSheet, endRow
#Select sheet of interest c_sheet = wb[sheetnames[1]] #Select column of interest c_sheet["B"] #Select first sheet first_sheet = wb[sheetnames[0]] #TO do: Figure out how to use dynamic cell references rather than hard coded as below. for row in range(1,c_sheet.max_row +1) cellref = "B1" # Translate formula with autoincrements c_sheet.cell(row,3).value = Translator(c_sheet.cell(row,2).value, origin = "B1").translate_formula("C1") #This will do the same as above but with dynamic cell refernce. c_sheet.cell(row,3).value = Translator(c_sheet.cell(row,2).value, origin = cellref).translate_formula("C1") # Copy formula as is. c_sheet.cell(2,3).value = c_sheet.cell(2,2).value
def tile(self, sheetname, rows, columns, row_spacing=1, col_spacing=1): """ Repeat the contents of an Excel worksheet, tiling it into a grid :param sheetname: name of Excel worksheet :param rows: number of times to repeat down the rows :param columns: number of times to repeat across the columns :param row_spacing: number of rows to insert between each repeated template :param col_spacing: number of columns to insert between each repeated template :return: """ if sheetname in self.tiled_sheets: raise Exception("Can only expand the sheet '{sheet}' once".format( sheet=sheetname)) self.tiled_sheets.add(sheetname) ws = self.wb[sheetname] rng = ws[ws.dimensions] row_offset = ws.max_row + row_spacing col_offset = ws.max_column + col_spacing self.updated_dimensions[sheetname] = (row_offset, col_offset) self.grid_dimensions[sheetname] = (rows, columns) merged_cells = [(r.min_row, r.min_col, r.max_row, r.max_col) for r in ws.merged_cells.ranges] for j in range(columns): for i in range(rows): if i == 0 and j == 0: continue for row in rng: for source_cell in row: target_cell = ws.cell( source_cell.row + row_offset * i, source_cell.column + col_offset * j) if not isinstance(source_cell.value, int): target_cell._value = Translator(source_cell._value, origin=source_cell.coordinate). \ translate_formula(target_cell.coordinate) else: target_cell.value = source_cell.value target_cell.data_type = source_cell.data_type if source_cell.has_style: target_cell._style = copy(source_cell._style) if source_cell.hyperlink: target_cell._hyperlink = copy( source_cell.hyperlink) if source_cell.comment: target_cell.comment = copy(source_cell.comment) for min_row, min_col, max_row, max_col in merged_cells: ws.merge_cells(start_row=min_row + row_offset * i, start_column=min_col + col_offset * j, end_row=max_row + row_offset * i, end_column=max_col + col_offset * j) for i in range(1, rows): for ii in range(len(rng)): ws.row_dimensions[ii + 1 + row_offset * i].height = ws.row_dimensions[ii + 1].height for j in range(1, columns): for jj in range(len(rng[0])): ws.column_dimensions[get_column_letter(jj+col_offset*j+1)].width = \ ws.column_dimensions[get_column_letter(jj + 1)].width
def actualiza_hoja_de_cálculo(renglón_a_copiar: int, nuevo_renglón: int, este_renglón: bool): global pagos_por_guardar, var_Categoria, lista_categorías if not valida_campos_Ok(): return # Hoja VIGILANCIA if renglón_a_copiar != nuevo_renglón: for col in range(ws_vigilancia.max_column): from_cell = f"{get_column_letter(col+1)}{renglón_a_copiar}" to_cell = f"{get_column_letter(col+1)}{nuevo_renglón}" # if cell content is a formula, asume it as relative addressing, else, copy it directly if es_fórmula(ws_vigilancia[from_cell].value): ws_vigilancia[to_cell] = Translator(ws_vigilancia[from_cell].value, origin=from_cell) \ .translate_formula(to_cell) else: ws_vigilancia[to_cell] = ws_vigilancia[from_cell].value if ws_vigilancia[from_cell].has_style: ws_vigilancia[to_cell]._style = ws_vigilancia[from_cell]._style ws_vigilancia[ f"{column('Generar')}{nuevo_renglón}"] = 'ü' if var_Generar.get( ) else None ws_vigilancia[ f"{column('Enviado')}{nuevo_renglón}"] = 'ü' if var_Enviado.get( ) else None ws_vigilancia[f"{column('Fecha')}{nuevo_renglón}"] = datetime.strptime( var_Fecha.get(), "%d-%m-%Y") ws_vigilancia[f"{column('Monto')}{nuevo_renglón}"] = convierte_en_float( var_Monto.get()) ws_vigilancia[ f"{column('Beneficiario')}{nuevo_renglón}"] = var_Beneficiario.get() ws_vigilancia[ f"{column('E-mail o celular')}{nuevo_renglón}"] = var_Email.get() ws_vigilancia[f"{column('Dirección')}{nuevo_renglón}"] = var_Direccion.get( ) ws_vigilancia[f"{column('Concepto')}{nuevo_renglón}"] = var_Concepto.get() ws_vigilancia[f"{column('Categoría')}{nuevo_renglón}"] = var_Categoria.get( ) ws_vigilancia[f"{column('Monto $')}{nuevo_renglón}"] = convierte_en_float( var_Monto_USD.get()) ws_vigilancia[f"{column('$')}{nuevo_renglón}"] = 'ü' if var_chk_USD.get( ) else None # Hoja RESUMEN VIGILANCIA if var_Categoria.get() == GyG_constantes.CATEGORIA_VIGILANCIA: row_resumen = benef_resumen.index(var_Beneficiario.get()) + 1 lista_celdas = [ f"{get_column_letter(columns_resumen.index(mes) + 1)}{row_resumen}" for mes in lista_meses ] for celda, contenido in zip(lista_celdas, lst_entries): valor = contenido.get() if es_fórmula( contenido.get()) else convierte_en_float(contenido.get()) # print(f"{celda}: {valor}, ") if valor: ws_resumen[celda] = valor cell_Enviado = ws_vigilancia[f"{column('Enviado')}{renglón_a_copiar}"] if cell_Enviado.has_style: ws_vigilancia[ f"{column('Generar')}{nuevo_renglón}"]._style = cell_Enviado._style ws_vigilancia[ f"{column('$')}{nuevo_renglón}"]._style = cell_Enviado._style # Actualiza tabla de categorías categoria = var_Categoria.get() if categoria not in lista_categorías: lista_categorías.insert(0, categoria) var_Categoria["values"] = lista_categorías # Guarda pagos_por_guardar = True btnGuarda.configure(text='GUARDA', state=tk.NORMAL) último_renglón = nuevo_renglón # messagebox.showinfo(message=f"El pago de {var_Beneficiario.get()} fue actualizado" if este_renglón \ # else f"Se agregó un nuevo pago para {var_Beneficiario.get()}") ventana_información(f"El pago {género('de', var_Beneficiario.get())} fue actualizado" if este_renglón \ else f"Se agregó un nuevo pago para {género('la', var_Beneficiario.get())}")
#example 10.8 from openpyxl.formula.translate import Translator #copy formula sheet1['D2'] = 'DIFF' sheet1['D3'] = '=C$9-C3' sheet1['D4'] = Translator("=C$9-C3", origin="D3").translate_formula("D4") for row in range(4, 8): coor = sheet1.cell(column=4, row=row).coordinate #copy formula to range sheet1.cell(column=4, row=row).value=Translator("=C$9-C3", origin="D3"). \ translate_formula(coor) #example 10.9 sheet1['A10'] = '=A2' for col in range(1, 4): coor = sheet1.cell(column=col, row=3).coordinate coor1 = sheet1.cell(column=col, row=10).coordinate print(coor, coor1) sheet1.cell(column=col, row=10).value=Translator("=A2", origin="A10"). \ translate_formula(coor1)
sales_df = convert_ws_df(wb['sales_records'], True) new_sales_df = pd.read_csv('files/1000_Sales_Records.csv', 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)
def parse_cell(self, element): value = element.find(self.VALUE_TAG) if value is not None: value = value.text formula = element.find(self.FORMULA_TAG) data_type = element.get('t', 'n') coordinate = element.get('r') self._col_count += 1 style_id = element.get('s') # assign formula to cell value unless only the data is desired if formula is not None and not self.data_only: data_type = 'f' if formula.text: value = "=" + formula.text else: value = "=" formula_type = formula.get('t') if formula_type: if formula_type != "shared": self.ws.formula_attributes[coordinate] = dict( formula.attrib) else: si = formula.get( 'si') # Shared group index for shared formulas # The spec (18.3.1.40) defines shared formulae in # terms of the following: # # `master`: "The first formula in a group of shared # formulas" # `ref`: "Range of cells which the formula applies # to." It's a required attribute on the master # cell, forbidden otherwise. # `shared cell`: "A cell is shared only when si is # used and t is `shared`." # # Whether to use the cell's given formula or the # master's depends on whether the cell is shared, # whether it's in the ref, and whether it defines its # own formula, as follows: # # Shared? Has formula? | In ref Not in ref # ========= ==============|======== =============== # Yes Yes | master impl. defined # No Yes | own own # Yes No | master impl. defined # No No | ?? N/A # # The ?? is because the spec is silent on this issue, # though my inference is that the cell does not # receive a formula at all. # # For this implementation, we are using the master # formula in the two "impl. defined" cases and no # formula in the "??" case. This choice of # implementation allows us to disregard the `ref` # parameter altogether, and does not require # computing expressions like `C5 in A1:D6`. # Presumably, Excel does not generate spreadsheets # with such contradictions. if si in self.shared_formula_masters: trans = self.shared_formula_masters[si] value = trans.translate_formula(coordinate) else: self.shared_formula_masters[si] = Translator( value, coordinate) style_array = None if style_id is not None: style_id = int(style_id) style_array = self.styles[style_id] if coordinate: row, column = coordinate_to_tuple(coordinate) else: row, column = self._row_count, self._col_count cell = Cell(self.ws, row=row, col_idx=column, style_array=style_array) self.ws._cells[(row, column)] = cell if value is not None: if data_type == 'n': value = _cast_number(value) if is_date_format(cell.number_format): data_type = 'd' value = from_excel(value, self.epoch) elif data_type == 'b': value = bool(int(value)) elif data_type == 's': value = self.shared_strings[int(value)] elif data_type == 'str': data_type = 's' elif data_type == 'd': value = from_ISO8601(value) else: if data_type == 'inlineStr': child = element.find(self.INLINE_STRING) if child is not None: data_type = 's' richtext = Text.from_tree(child) value = richtext.content if self.guess_types or value is None: cell.value = value else: cell._value = value cell.data_type = data_type
def translate_formula(self, sheetname, workbook, source, target): # worksheet = workbook[sheetname] # Add Sheet name formula = sheetname[source].value sheetname[target] = Translator(formula, origin=source).translate_formula(target)
from openpyxl import load_workbook from openpyxl.formula.translate import Translator wb = load_workbook('./books/21/21.rough_profit.xlsx') ws = wb.active origin_cell_no = 'D6' ws[origin_cell_no] = '=C6-B6' # this becomes the original formula to be copied for row_no in range(7, ws.max_row + 1): cell_no = f'D{row_no}' ws[cell_no] = Translator(ws[origin_cell_no].value, origin=origin_cell_no).translate_formula(cell_no) wb.save('./books/21/21.rough_profit_changed.xlsx')
for cell in ws1['A:A']: ws0.cell(row=cell.row, column=1, value=cell.value) for cell in ws1['B:B']: ws0.cell(row=cell.row, column=2, value=cell.value) ws0['C1'].value = ws1['C1'].value ws0['D1'].value = ws1['D1'].value ws0['E1'].value = ws1['E1'].value ws0['F1'].value = ws1['F1'].value if rgn_num == 'Y' or rgn_num == 'YES': ws0['C2'] = "=SUM('SNR Totals'!C2,'SER Totals'!C2,'SCR Totals'!C2,'NWR Totals'!C2,'NER Totals'!C2,'IC Totals'!C2,'CNR Totals'!C2,'CCC Totals'!C2)" ws0['D2'] = Translator("=SUM('SNR Totals'!C2,'SER Totals'!C2,'SCR Totals'!C2,'NWR Totals'!C2,'NER Totals'!C2,'IC Totals'!C2,'CNR Totals'!C2,'CCC Totals'!C2)", 'C2').translate_formula('D2') ws0['E2'] = Translator("=SUM('SNR Totals'!C2,'SER Totals'!C2,'SCR Totals'!C2,'NWR Totals'!C2,'NER Totals'!C2,'IC Totals'!C2,'CNR Totals'!C2,'CCC Totals'!C2)", 'C2').translate_formula('E2') ws0['F2'] = "=C2/E2" ws0['F2'].style = 'Percent' columns = ws0.max_column rows = ws0.max_row #iterate formula down in column C c=3 for row in ws0.iter_rows(min_col=3, min_row=3, max_col=3, max_row=rows): for cell in row: ws0['C'+str(c)] = Translator("=SUM('SNR Totals'!C2,'SER Totals'!C2,'SCR Totals'!C2,'NWR Totals'!C2,'NER Totals'!C2,'IC Totals'!C2,'CNR Totals'!C2,'CCC Totals'!C2)", 'C2').translate_formula('C'+str(c)) c+=1 #iterate formula down in column D d=3
def read_cells(archive, ignore_sheets=[], ignore_hidden=False, include_only_sheets=None): global debug cells = {} sheets = [] functions = set() cts = dict(read_content_types(archive)) strings_path = cts.get( SHARED_STRINGS ) # source: https://bitbucket.org/openpyxl/openpyxl/src/93604327bce7aac5e8270674579af76d390e09c0/openpyxl/reader/excel.py?at=default&fileviewer=file-view-default if strings_path is not None: if strings_path.startswith("/"): strings_path = strings_path[1:] shared_strings = read_string_table(archive.read(strings_path)) else: shared_strings = [] ignore_sheets = frozenset(ignore_sheets) if include_only_sheets is not None: include_only_sheets = frozenset(include_only_sheets) for sheet in detect_worksheets(archive): sheet_name = sheet['title'] function_map = {} if sheet_name in ignore_sheets or (include_only_sheets is not None and sheet_name not in include_only_sheets): continue sheets.append(sheet_name) root = fromstring( archive.read(sheet['path']) ) # it is necessary to use cElementTree from xml module, otherwise root.findall doesn't work as it should hidden_cols = False nb_hidden = 0 if ignore_hidden: hidden_col_min = None hidden_col_max = None for col in root.findall('.//{%s}cols/*' % SHEET_MAIN_NS): if 'hidden' in col.attrib and col.attrib['hidden'] == '1': hidden_cols = True hidden_col_min = int(col.attrib['min']) hidden_col_max = int(col.attrib['max']) for c in root.findall('.//{%s}c/*/..' % SHEET_MAIN_NS): cell_data_type = c.get('t', 'n') # if no type assigned, assign 'number' cell_address = c.attrib['r'] skip = False if hidden_cols: found = re.search(CELL_REF_RE, cell_address) col = col2num(found.group(1)) if col >= hidden_col_min and col <= hidden_col_max: nb_hidden += 1 skip = True if not skip: cell = { 'a': '%s!%s' % (sheet_name, cell_address), 'f': None, 'v': None } if debug: logging.debug('Cell', cell['a']) for child in c: child_data_type = child.get( 't', 'n') # if no type assigned, assign 'number' if child.tag == '{%s}f' % SHEET_MAIN_NS: if 'ref' in child.attrib: # the first cell of a shared formula has a 'ref' attribute if debug: logging.debug( '*** Found definition of shared formula ***', child.text, child.attrib['ref']) if "si" in child.attrib: function_map[child.attrib['si']] = ( child.attrib['ref'], Translator(str('=' + child.text), cell_address) ) # translator of openpyxl needs a unicode argument that starts with '=' # else: # print "Encountered cell with ref but not si: ", sheet_name, child.attrib['ref'] if child_data_type == 'shared': if debug: logging.debug( '*** Found child %s of shared formula %s ***' % (cell_address, child.attrib['si'])) ref = function_map[child.attrib['si']][0] formula = function_map[child.attrib['si']][1] translated = formula.translate_formula( cell_address) cell['f'] = translated[ 1:] # we need to get rid of the '=' else: cell['f'] = child.text elif child.tag == '{%s}v' % SHEET_MAIN_NS: if cell_data_type == 's' or cell_data_type == 'str': # value is a string try: # if it fails, it means that cell content is a string calculated from a formula cell['v'] = shared_strings[int(child.text)] except: cell['v'] = child.text elif cell_data_type == 'b': cell['v'] = bool(int(child.text)) elif cell_data_type == 'n': cell['v'] = _cast_number(child.text) elif child.text is None: continue if cell['f'] is not None: pattern = re.compile(r"([A-Z][A-Z0-9]*)\(") found = re.findall(pattern, cell['f']) map(lambda x: functions.add(x), found) if cell['f'] is not None or cell['v'] is not None: should_eval = 'always' if cell[ 'f'] is not None and 'OFFSET' in cell['f'] else 'normal' # cleaned_formula = cell['f'] cleaned_formula = cell['f'].replace( ", ", ",") if cell['f'] is not None else None if "!" in cell_address: cells[cell_address] = Cell(cell_address, sheet_name, value=cell['v'], formula=cleaned_formula, should_eval=should_eval) else: cells[sheet_name + "!" + cell_address] = Cell( cell_address, sheet_name, value=cell['v'], formula=cleaned_formula, should_eval=should_eval) return cells, sheets