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