def corrector_scha_13_(id_fond, ws, df_avancor):
""" Копируем количество паев с нужной точностью """
# форма:
# 0420502 Справка о стоимости _13
# Точность указания кол-ва паев
fix = pai_search(id_fond)
# или так:
# wb_pif_info = load_pif_info(file_name=pif_info,
# path_2file=dir_shablon)
# sheet_name = id_fond
# ws_pif_info = wb_pif_info[sheet_name]
# col_fIx = 'B'
# row_fix = 2
# cell = col_fIx + str(row_fix)
# fix = ws_pif_info[cell].value
today_row, today_col = coordinate_to_tuple('I144')
previous_row, previous_col = coordinate_to_tuple('K144')
today = toFixed(df_avancor.loc[today_row, today_col], digits=fix)
previous = toFixed(df_avancor.loc[previous_row, previous_col], digits=fix)
ws.cell(10, 3).value = today
ws.cell(10, 4).value = previous
# Форматируем ячейки
for row in range(9, ws.max_row + 1):
for col in range(3, ws.max_column + 1):
ws.cell(row, col).alignment = Alignment(horizontal='right')
def TestInput(data):
fdp = atheris.FuzzedDataProvider(data)
try:
cell.absolute_coordinate(fdp.ConsumeString(20))
cell.cols_from_range(fdp.ConsumeString(20))
cell.column_index_from_string(fdp.ConsumeString(20))
cell.coordinate_from_string(fdp.ConsumeString(20))
cell.coordinate_to_tuple(fdp.ConsumeString(20))
cell.get_column_interval(fdp.ConsumeInt(10),fdp.ConsumeInt(10))
cell.get_column_letter(fdp.ConsumeInt(10))
cell.quote_sheetname(fdp.ConsumeString(20))
cell.range_boundaries(fdp.ConsumeString(20))
cell.range_to_tuple(fdp.ConsumeString(20))
cell.rows_from_range(fdp.ConsumeString(20))
except ValueError as e:
error_list = [
"is not a valid coordinate range",
"Invalid column index",
"is not a valid column name",
"is not a valid coordinate or range",
"Value must be of the form sheetname!A1:E4"
]
expected_error = False
for error in error_list:
if error in str(e):
expected_error = True
if not expected_error:
raise e
except CellCoordinatesException:
pass
def copyFromAvancore(ws, AvancoreCellBegin, AvancoreCellEnd, AvancoreTblCols):
cell_start_row, cell_start_col = coordinate_to_tuple(AvancoreCellBegin)
cell_end_row, cell_end_col = coordinate_to_tuple(AvancoreCellEnd)
# Номера колонок в таблице Аванкор (за исключением пустых)
columns_numbers = fun.find_columns_numbers(df_avancor, cell_end_col,
AvancoreTblCols, cell_start_row,
data_col=cell_start_col)
# Номера строк в таблице Аванкор
rows_numbers = [x for x in range(cell_start_row, cell_end_row + 1)]
# координаты первой ячейки в таблице xbrl
col_begin, row_begin = fun.begin_cell(ws, AvancoreTblCols)
# Копирование данных из таблицы Аванкор в таблицу XBRL
dcop.copy_data(ws, df_avancor, rows_numbers, columns_numbers,
row_begin, col_begin)
def _import_plate_legend(self, excel_file, corner_position):
""" assign an custom plate map to the current plate (Excel file)
Unique map value/description of each well has their indices grouped
together. This assigns a 'custom_map' to the current object
Args:
excel_file(str): the file path to the plate map
corner_cell(str): the coordinate for corner cell in Excel format
(e.g. 'A12')
"""
xl_filepath = Path(excel_file)
plate_xlsheet = load_workbook(str(xl_filepath.resolve()))
start_row, start_column = coordinate_to_tuple(corner_position)
# offset 1 from corner to skip the header values
start_row += 1
start_column += 1
platelegend = np.empty((self.height, self.width), dtype='object')
for r, annotation in enumerate(
plate_xlsheet.active.iter_rows(
min_col=start_column,
max_col=start_column + self.width - 1,
min_row=start_row,
max_row=start_row + self.height - 1,
values_only=True,
)):
platelegend[r, :] = annotation
self.__setattr__('custom_map', PlateLegend(platelegend, custom=True))
def return_plate_index(well_pos):
""" this function uses openpyxl's utility function for multi-well grid layout
It converts letter-based coordinate system to 1-based numeric coordinate
(e.g. C8 becomes (3,8))
"""
column, row = coordinate_to_tuple(well_pos)
return row, column
def set_current(self, sheet, position):
self.current_sheet = sheet
self.current_cell = position
self.current_row, self.current_col = coordinate_to_tuple(position)
self.current_pos_to_label = self.collector.pos_to_label[sheet]
self.current_sheet_is_vertical = self.collector.sheet_is_vertical[sheet]
if self.collector.graph is not None:
self.current_edges = defaultdict(set)
def range_values(wb, v):
row0, col0 = coordinate_to_tuple(v.cell)
ws = wb[v.sheet]
for row in ws.iter_rows(min_row=row0,
max_row=row0 + len(years(wb, v)) - 1,
min_col=col0,
max_col=col0 + width(v.freq) - 1):
for cell in row:
yield cell.value
def calc_cell(self, cell_index, ws_name):
"""
Calculate the cell formula by str index.
If the cell does not have the formula it returns the cell's value.
:type cell_index: basestring
:type ws_name: basestring
"""
row, column = coordinate_to_tuple(cell_index)
value = self._cell_to_value(row, column, ws_name)
cell = self.wb[ws_name]._get_cell(row, column)
return self._deserialize_value(value, cell.data_type)
def empty_cell(ws, cellBegin, cellEnd):
""" Проверяем является ли ячейка пустой"""
rowBegin, colBegin = coordinate_to_tuple(cellBegin)
rowEnd, colEnd = coordinate_to_tuple(cellEnd)
drow = rowBegin + (rowEnd - rowBegin) + 1
dcol = colBegin + (colEnd - colBegin) + 1
for row in range(rowBegin, rowBegin + (rowEnd - rowBegin) + 1):
for col in range(colBegin, colBegin + (colEnd - colBegin) + 1):
cellData = ws.cell(row, col).value
if not cellData or \
str(cellData).startswith('Не установлен') or \
cellData == 'None' or \
cellData != cellData or \
cellData == error_txt:
red_error(ws.cell(row, col))
log.error(
f'"{ws.title}" --> пустая ячейка "{get_column_letter(col) + str(row)}"'
)
def years(wb, v) -> [int]:
row, _ = coordinate_to_tuple(v.cell)
ws = wb[v.sheet]
result = []
while True:
value = ws.cell(row=row, column=1).value
try:
year = uncomment(value, int)
except (ValueError, TypeError):
break
result.append(year)
row += 1
return result
def makeForm(shortURL, avancoreTitle, max_number, cell_start):
cell_start_row, cell_start_col = coordinate_to_tuple(cell_start)
sheetName = fun.sheetNameFromUrl(urlSheets, shortURL) # имя вкладки
ws = wb[sheetName]
print(f'{sheetName} - {shortURL}')
# Копируем данные из файла Аванкор
if copyFromAvancore(ws, avancoreTitle, max_number, cell_start_row,
cell_start_col):
# Записываем в форму идентификатор фонда
dcop.copy_id_fond_to_tbl(ws, id_fond)
else:
# Если ничего скопировано не было, то Раздел пуст.
# Удаляем вкладку
wb.remove(ws)
def fioShort(ws, AvancoreTitle, avancor_fio_col, cell_fio):
"""Копируем короткое-ФИО подписанта"""
# Формы:
# 0420502 Справка о стоимости _56 SR_0420502_Podpisant
# 0420502 Справка о стоимости _57 SR_0420502_Podpisant_spec_dep
# количество строк в таблице Аванкор
index_max = df_avancor.shape[0]
# Номер строки с названием раздела в файле Аванкор
avancor_title_row = fun.razdel_name_row(df_avancor, AvancoreTitle,
index_max)
# ФИО подписанта
fio = df_avancor.loc[avancor_title_row, avancor_fio_col]
# записываем в форму xbrl
row_fio, col_fio = coordinate_to_tuple(cell_fio)
ws.cell(row_fio, col_fio).value = fio
def cell_action(self, tok):
"""
over all token occurrence we do transliteration action!
tok is a cell reference, in these cases we can transliterate syntax in a
python way.
Depending on flag self.current_sheet_is_vertical we choose the right
direction for the time stepper
:param tok: exel token like E5, $AA$12
:return: transliterated syntax
"""
if self.collector is not None:
if not self.current_sheet:
raise ValueError("ggg")
sheet = tok.cell.sheet
position = tok.cell.pos
if sheet:
# maybe from other sheet!
if sheet != self.current_sheet:
log.info(f"In sheet {self.current_sheet} cell {self.current_cell} token {tok} read input from external sheet. Treated as exogenous value")
val = self.collector.wb_data[sheet][position].value
return val
row, col = coordinate_to_tuple(de_dollar(position))
offset = col if self.current_sheet_is_vertical else row
if offset in self.current_pos_to_label:
label = self.current_pos_to_label[offset]
if label is None:
log.info(f'in sheet {self.current_sheet} lost edge {position} -> {self.current_cell} due to empty label')
return
else:
val = self.collector.wb_data[self.current_sheet][position].value
log.info("Found params sheet {} cell {}:{}".format(self.current_sheet,tok.cell.pos,val))
return val
delta_time = row-self.current_row if self.current_sheet_is_vertical else col-self.current_col
self.current_edges[label].add(delta_time)
if delta_time:
return("{}[T{}]".format(label,delta_time))
else:
return("{}[T]".format(label))
def is_vertical_range(rng:list)->bool:
"""
:param rng:
:return:
"""
rows_cols = [coordinate_to_tuple(de_dollar(x)) for x in rng]
rows = set((x[0] for x in rows_cols))
cols = set((x[1] for x in rows_cols))
if len(rows)==1:
if len(cols)>1:
return True
else:
raise ValueError("Cannot recognize if {} is vertical model range")
else:
# We assume it was >1
if len(cols) == 1:
return False
else:
raise ValueError("Cannot recognize if {} is horizontal model range")
def __init__(self, url:str, only_data:bool=False, relative:bool=False, add_fingerprint:bool=False,
parsed:bool=False, use_graph:bool=True, tag:str=None, description:str=None):
"""
Data injestion, we need 2 instances of the sheet:
- wb_data: with static data
- wb: with the formulas
:param url: url to an input excel file
:param only_data: read only static values
:param parsed: use parsed formaulas instead of excel version
:param add_fingerprint: add fingerprint to internal representation
:param tag: add a tag attribute to fingherprint
:param description: add a description attribute to fingherprint
:param relative: all area are treated as if they starts from Row1 Col1
:param use_graph: Initialize and collect the topology of all parsed models default:True
available only with parsed=True
description or tag enable automatically add_fingerprint=True
"""
if not isfile(url):
raise FileNotFoundError("File {} does not exists".format(url))
self.sheets = {}
self.pseudo = {}
self.url = url
self.anon_models = {}
self.parsed = parsed
if use_graph and parsed:
self.edges = tree() # temporary data structure for edges
self.graph = networkx.DiGraph(
creator='XLtoy',
version=version,
datetime=datetime.now().isoformat())
else:
self.graph = None
self.labels_as_data = True
self.models_as_data = only_data
log.debug("Labels read as {}".format( 'data' if self.labels_as_data else 'formula'))
log.debug("Models read as {}".format('data' if self.models_as_data else 'formula'))
if self.parsed:
log.info("Parser enabled")
# workbook injestion, we need 2 instances: data and formulas
# if only_data is true each point to the same instance.
with open(self.url, "rb") as f:
in_mem_file = io.BytesIO(f.read())
with timeit("load workbook"):
self.wb_data = load_workbook(in_mem_file, data_only=True, read_only=True)
if only_data:
self.wb = self.wb_data
else:
self.wb = load_workbook(in_mem_file, read_only=True)
self.relative = relative
with timeit("set ranges"):
self.sheetnames = self.wb_data.sheetnames
self.named_ranges = self.wb_data.defined_names.definedName
self.set_ranges()
with timeit("labels handler"):
self.labels = self.handle_range(
self.label_names,
self.labels_as_data)
# Here we have collected only labels, so if parsed is True
# we need a data structure for bind position of formula to his label
self.sheet_is_vertical = {}
if self.parsed:
with timeit("parsing"):
# models pre-scan, this solves the anonymous models problems.
# we don't know if they are vertical or horizontal
self.models = self.handle_range(
self.model_names,
True)
self.find_anonymous_models()
self.pos_to_label = defaultdict(lambda: {})
for sheet,rng in self.labels.items():
if sheet not in self.sheet_is_vertical:
is_vert = is_vertical_range(rng.keys())
self.sheet_is_vertical[sheet] = is_vert
else:
is_vert = self.sheet_is_vertical[sheet]
for coord, label in rng.items():
row, col = coordinate_to_tuple(coord)
self.pos_to_label[sheet][col if is_vert else row] = label
self.parser = Parser(collector=self)
self.models = self.handle_range(
self.model_names,
self.models_as_data)
else:
with timeit("models handler"):
self.models = self.handle_range(
self.model_names,
self.models_as_data)
self.find_anonymous_models()
with timeit("data handler"):
self.data = self.handle_range(
self.data_names,
use_data=True)
self.set_pseudo_excel(add_fingerprint=add_fingerprint,
tag=tag,
description=description)
self.set_graph()
def coordinates_of_cell_region(start, rows, columns):
startColumn, startRow = coordinate_to_tuple(start)
return tuple('{:s}{:d}'.format(get_column_letter(column), row)
for row in range(startRow, startRow + rows)
for column in range(startColumn, startColumn + columns))
def first_year(wb, v):
row, _ = coordinate_to_tuple(v.cell)
return wb[v.sheet].cell(row=row, column=1).value
def plot_table(ws, df, start_cell, title, index_name=u'时间'):
# plot table title
set_title(ws, start_cell, title)
# plot table header
start_row, start_column = coordinate_to_tuple(start_cell)
if (len(df.index.names) == 1):
# plot columns
i = 0
for column_name in df.columns:
column_cell = ws[tuple_to_coordinate(start_row, start_column + i)]
cell_range = '%s:%s' % (column_cell.coordinate,
column_cell.coordinate)
set_header(ws, cell_range, name=column_name, merged=False)
i = i + 1
elif (len(df.index.names) == 2):
# plot index column
index_cell = ws[tuple_to_coordinate(start_row + 1, start_column)]
cell_range = '%s:%s' % (index_cell.coordinate, index_cell.coordinate)
set_header(ws, cell_range, name=index_name, merged=False)
# plot columns
i = 1
for column_name in df.index.levels[0]:
column_cell = ws[tuple_to_coordinate(start_row + 1,
start_column + i)]
cell_range = '%s:%s' % (column_cell.coordinate,
column_cell.coordinate)
set_header(ws, cell_range, name=column_name, merged=False)
i = i + 1
else:
# plot index column
index_cell_lt = ws[tuple_to_coordinate(start_row + 1, start_column)]
index_cell_rb = ws[tuple_to_coordinate(start_row + 2, start_column)]
cell_range = '%s:%s' % (index_cell_lt.coordinate,
index_cell_rb.coordinate)
set_header(ws, cell_range, name=index_name, merged=True)
#plot top level column
second_column_size = len(df.index.levels[1])
top_column_row = start_row + 1
top_column_column = start_column + 1
second_column_row = start_row + 2
for top_column_name in df.index.levels[0]:
column_start_cell = ws[tuple_to_coordinate(top_column_row,
top_column_column)]
column_end_cell = ws[tuple_to_coordinate(
top_column_row, top_column_column + second_column_size - 1)]
top_column_cell_range = '%s:%s' % (column_start_cell.coordinate,
column_end_cell.coordinate)
set_header(ws,
top_column_cell_range,
name=top_column_name,
merged=True)
second_column_start_cell = ws[tuple_to_coordinate(
second_column_row, top_column_column)]
second_column_end_cell = ws[tuple_to_coordinate(
second_column_row, top_column_column + second_column_size - 1)]
second_column_cell_range = '%s:%s' % (
second_column_start_cell.coordinate,
second_column_end_cell.coordinate)
# plot second columns
second_column_column = top_column_column
for second_column_name in df.index.levels[1]:
second_column_cell = ws[tuple_to_coordinate(
second_column_row, second_column_column)]
second_column_cell.value = second_column_name
second_column_column = second_column_column + 1
# plot border
set_header(ws, second_column_cell_range, merged=False)
top_column_column = top_column_column + second_column_size
# plot body
if (len(df.index.names) == 1):
# set body style
value_row = start_row + 1
value_column = start_column
for i in range(0, df.shape[0]):
s = df.loc[i]
j = 0
for d in df.columns:
v = s.get(d)
value_cell = ws[tuple_to_coordinate(value_row + i,
value_column + j)]
value_cell_range = '%s:%s' % (value_cell.coordinate,
value_cell.coordinate)
set_body(ws, value_cell_range)
value_cell.value = v
if isinstance(value_cell.value, np.float64):
value_cell.number_format = numbers.FORMAT_NUMBER
j = j + 1
elif (len(df.index.names) == 2):
write_date = True
index_row = start_row + 2
index_column = start_column
value_column = start_column + 1
# set date column style
index_start_cell = ws[tuple_to_coordinate(index_row, index_column)]
index_end_cell = ws[tuple_to_coordinate(
index_row + len(df.index.levels[1]) - 1, index_column)]
index_cell_range = '%s:%s' % (index_start_cell.coordinate,
index_end_cell.coordinate)
set_body(ws, index_cell_range)
for c0 in df.index.levels[0]:
s0 = df.loc[c0]
# set body style
value_row = start_row + 2
value_start_cell = ws[tuple_to_coordinate(value_row, value_column)]
value_end_cell = ws[tuple_to_coordinate(
value_row + len(df.index.levels[1]) - 1, value_column)]
value_cell_range = '%s:%s' % (value_start_cell.coordinate,
value_end_cell.coordinate)
set_body_num(ws, value_cell_range)
for c1 in df.index.levels[1]:
v = s0.loc[c1][0] if c1 in s0.index else ''
if write_date:
index_cell = ws[tuple_to_coordinate(
index_row, index_column)]
index_cell.value = c1
index_row = index_row + 1
value_cell = ws[tuple_to_coordinate(value_row, value_column)]
value_cell.value = v
if isinstance(value_cell.value, np.float64):
if value_cell.value < 1:
value_cell.number_format = numbers.FORMAT_PERCENTAGE_00
else:
value_cell.number_format = numbers.FORMAT_NUMBER_00
try:
if int(value_cell.value) == value_cell.value:
value_cell.number_format = numbers.FORMAT_NUMBER
except:
pass
value_row = value_row + 1
write_date = False
value_column = value_column + 1
else:
write_index = True
index_row = start_row + 3
index_column = start_column
value_column = start_column + 1
# set date column style
index_start_cell = ws[tuple_to_coordinate(index_row, index_column)]
index_end_cell = ws[tuple_to_coordinate(
index_row + len(df.index.levels[2]) - 1, index_column)]
index_cell_range = '%s:%s' % (index_start_cell.coordinate,
index_end_cell.coordinate)
set_body(ws, index_cell_range)
for c0 in df.index.levels[0]:
s0 = df.loc[c0]
value_row = start_row + 3
# set body styel
value_start_cell = ws[tuple_to_coordinate(value_row, value_column)]
value_end_cell = ws[tuple_to_coordinate(
value_row + len(df.index.levels[2]) - 1,
value_column + len(df.index.levels[1]) - 1)]
value_cell_range = '%s:%s' % (value_start_cell.coordinate,
value_end_cell.coordinate)
set_body_num(ws, value_cell_range)
for c1 in df.index.levels[1]:
s1 = s0.loc[c1]
value_row = start_row + 3
for c2 in df.index.levels[2]:
if write_index:
index_cell = ws[tuple_to_coordinate(
index_row, index_column)]
index_cell.value = c2
index_row = index_row + 1
v = s1.loc[c2][0] if c2 in s1.index else ''
value_cell = ws[tuple_to_coordinate(
value_row, value_column)]
value_cell.value = v
if isinstance(value_cell.value, np.float64):
if value_cell.value < 1:
value_cell.number_format = numbers.FORMAT_PERCENTAGE_00
else:
value_cell.number_format = numbers.FORMAT_NUMBER_00
try:
if int(value_cell.value) == value_cell.value:
value_cell.number_format = numbers.FORMAT_NUMBER
except:
pass
value_row = value_row + 1
write_index = False
value_column = value_column + 1
