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