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