def sheet_to_inventory(group_by_col, hostname_col, sheet): if isinstance(group_by_col, six.string_types): group_by_col = (column_index_from_string( coordinate_from_string(group_by_col + "1")[0]) - 1) if isinstance(hostname_col, six.string_types): hostname_col = (column_index_from_string( coordinate_from_string(hostname_col + "1")[0]) - 1) groups = {"_meta": {"hostvars": {}}} rows = list(sheet.rows) for row in rows[1:]: host = row[hostname_col].value if host is None: continue group = row[group_by_col].value if group is None: group = default_group if group not in groups.keys(): groups[group] = {"hosts": [], "vars": {}} groups[group]["hosts"].append(host) groups["_meta"]["hostvars"][row[hostname_col].value] = {} for xlsx_head in rows[:1]: for idx, var_name in enumerate(xlsx_head): if var_name.value is None: var_name.value = "xlsx_" + var_name.coordinate if row[idx].value is not None: groups["_meta"]["hostvars"][row[0].value][ var_name.value.lower().replace(" ", "_")] = row[idx].value return groups
def _find_cell_coor_by_val(self, value: Any, start: str = "", end: str = "") -> str: """Search engine for the cell values""" assert self.worksheet if start is None or start == "": start = "A1" if end is None or end == "": end = utils.get_column_letter(self.worksheet.max_column) + str( self.worksheet.max_row) start_cell = cell_utils.coordinate_from_string(start) end_cell = cell_utils.coordinate_from_string(end) start_column = utils.column_index_from_string(start_cell[0]) start_row = start_cell[1] end_column = utils.column_index_from_string(end_cell[0]) end_row = end_cell[1] for row in range(start_row, end_row + 1): for column in range(start_column, end_column + 1): val = self.worksheet[utils.get_column_letter(column) + str(row)].value if isinstance(val, str): val = val.replace("\n", " ") val = val.replace(" ", " ") if value == val: return utils.get_column_letter(column) + str(row) return None
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 sheet_to_inventory(group_by_col, hostname_col, sheet): if isinstance(group_by_col, six.string_types): group_by_col = column_index_from_string( coordinate_from_string(group_by_col + '1')[0]) - 1 if isinstance(hostname_col, six.string_types): hostname_col = column_index_from_string( coordinate_from_string(hostname_col + '1')[0]) - 1 groups = {'_meta': {'hostvars': {}}} rows = list(sheet.rows) for row in rows[1:]: host = row[hostname_col].value if host is None: continue group = row[group_by_col].value if group is None: group = default_group if group not in groups.keys(): groups[group] = {'hosts': [], 'vars': {}} groups[group]['hosts'].append(row[hostname_col].value) groups['_meta']['hostvars'][row[hostname_col].value] = {} for excel_head in rows[:1]: for idx, var_name in enumerate(excel_head): if var_name.value is None: var_name.value = "excel_" + var_name.coordinate if row[idx].value is not None: groups['_meta']['hostvars'][row[0].value][ var_name.value.lower().replace(' ', '_')] = row[idx].value return groups
def calculate_value_cell(self, cell, sheet_h): formula = cell.value performance_value = 0 if not self.is_formula(formula): return formula if formula.find("SUM") >= 0: sum_index = formula.split(':') sum_min_index = coordinate_from_string( re.sub(r'=SUM\(', '', sum_index[0])) sum_max_index = coordinate_from_string( re.sub(r'\)', '', sum_index[1])) # print(sum_min_index) # print(sum_max_index) sum_min_col = column_index_from_string(sum_min_index[0]) sum_min_row = int(sum_min_index[1]) sum_max_col = column_index_from_string(sum_max_index[0]) sum_max_row = int(sum_max_index[1]) if sum_min_col != sum_max_col: print('not sum the same col, not support at this time!') return # row_count = sum_min_row # print(sum_min_col, sum_min_row, sum_max_col, sum_max_row) # print(row_count) for row in sheet_h.iter_rows(min_col=sum_min_col, max_col=sum_max_col, min_row=sum_min_row, max_row=sum_max_row): for cell in row: value = cell.value if value: try: value = self.round_v2(value) performance_value = performance_value + value # print(value) except Exception as e: str(e) return performance_value elif formula.find("+") >= 0: formula = re.sub('=', '', formula) cell_list = formula.split('+') for cell_index in cell_list: value = sheet_h[cell_index].value if value: # print(value) try: float(value) # print(value) value = self.round_v2(value) # print(value) performance_value = performance_value + value except Exception as e: str(e) return performance_value else: print('not SUM formula')
def _get_enums(self, bitfield: Any, excel_row: int, excel_row_cnt: int) -> None: """Tried to find and fill up all register bitfields enumerations.""" assert self.worksheet if excel_row_cnt <= 1: # There is no enums return enum_name_cr = cell_utils.coordinate_from_string( self.header_cells["Enum Name"]) desc_cr = cell_utils.coordinate_from_string( self.header_cells["Description"]) value_cr = cell_utils.coordinate_from_string( self.header_cells["Value"]) excel_row += 1 excel_row_cnt -= 1 for row in range(excel_row, excel_row + excel_row_cnt): cell = enum_name_cr[0] + str(row) if isinstance(self.worksheet[cell].value, str): enum_name = self.worksheet[cell].value enum_descr = self.worksheet[desc_cr[0] + str(row)].value or "N/A" enum_value: str = self.worksheet[value_cr[0] + str(row)].value enum_descr = enum_descr.replace("\n", " ") if enum_value is None: click.echo( f"Warning: The Enum {enum_name} is missing and it will be skipped." ) else: bitfield.add_enum( RegsEnum(enum_name, enum_value, enum_descr, bitfield.width))
def generate_short_links(generation): """Сгенерировать короткие ссылки на движке yourls :param generation: Одна генерация :type generation: LinkGeneration """ wb = xl.load_workbook(generation.links_file) sheet_name, links_column = wb.defined_names['link'].attr_text.split('!') ws = wb[sheet_name] links_column_number = column_index_from_string( coordinate_from_string(links_column)[0]) if generation.self_id: ids_column = column_index_from_string( coordinate_from_string( wb.defined_names['id'].attr_text.split('!')[1])[0]) row_number = 2 links = {} while ws.cell(row=row_number, column=links_column_number).value: links[row_number] = {} links[row_number]['link'] = ws.cell(row=row_number, column=links_column_number).value if generation.self_id: links[row_number]['id'] = ws.cell(row=row_number, column=ids_column).value row_number += 1 if generation.self_id: if check_id_validity(links): if check_id_existence(links, generation.engine): generation.state = ST_ERR generation.processed_comments = ID_ALREADY_USED generation.save() return else: generation.state = ST_ERR generation.processed_comments = ID_NOT_FIT_YOURLS generation.save() return elif not generation.self_id: assign_id_to_links(links, generation.engine) ws.insert_cols(links_column_number + 1) ws.cell(row=1, column=links_column_number + 1).value = 'Короткая ссылка' for link in links: ws.cell(row=link, column=(links_column_number + 1)).value = \ r'{eng_name}/{id}'.format(eng_name=generation.engine, id=links[link]['id']) wb.save(generation.links_file.path) if push_links_to_yourls(links, generation.engine, generation.title): generation.state = ST_FIN else: generation.state = ST_ERR generation.processed_comments = YOURLS_DB_ERROR generation.save()
def merge_with_right(ws, cell_range, number__merge_cells): start_cell, end_cell = cell_range.split(':') start_coord = coordinate_from_string(start_cell) start_row = start_coord[1] start_col = column_index_from_string(start_coord[0]) end_coord = coordinate_from_string(end_cell) end_row = end_coord[1] end_col = column_index_from_string(end_coord[0]) for row in range(start_row, end_row + 1): for col in range(start_col, end_col + 1, number__merge_cells): init_col = get_column_letter(col) next_col = get_column_letter(col + 1) ws.merge_cells('{0}{2}:{1}{2}'.format(init_col, next_col, row))
def getRangeOfRowCols(xlRange): rangeTuples = [] start,end = xlRange.split(":") startCoord = coordinate_from_string(start) startCol, startRow = column_index_from_string(startCoord[0]), startCoord[1] endCoord = coordinate_from_string(end) endCol, endRow = column_index_from_string(endCoord[0]), endCoord[1] rangeTuples.append((startRow, endRow)) rangeTuples.append((startCol, endCol)) return rangeTuples
def get_comment_value_by_assignment_id(marked_grade_cell: Cell, marked_comment_cells: Cell, row_number, ws: worksheet): """ Helper method to find the corresponding comment value for a given grade_cell. Column index marked by assignment ID in upload_marker row. Row indexing by given row_number. The list of cells (marked_comment_cells) is cleaned before and do not contain the CommentMarkerPrefix anymore, but assignment IDs. :param marked_grade_cell: Grade cell marked by assignment ID in the upload_marker row. :param marked_comment_cells: List of all comment cells we want to find the assignment ID of marked_grade_cell in. :param row_number: Number of current row. :param ws: Worksheet to read from (data_only). :return: Empty string if cell value is None or if cell is not found. """ if Config.get_behavior_comments_switch(): for coment_cell in marked_comment_cells: # find matching assignment id if str(coment_cell.value) == str(marked_grade_cell.value): # extract coordinates (column index) column_index_of_comment_cell = column_index_from_string( coordinate_from_string(coment_cell.coordinate)[0]) # read value of cell comment_value = ws.cell( row=row_number, column=column_index_of_comment_cell).value if not comment_value: # normalize - we want string only as return type comment_value = '' return comment_value if Config.get_behavior_log(): print('Comment for grade cell ' + str(marked_grade_cell.coordinate) + ' not found!') return ''
def getRowCol(cellAddress): try: column, row = coordinate_from_string(cellAddress) return column_index_from_string(column), row except: mb.showinfo("Alert", "Please enter a valid cell address (Letter(s) followed by Number(s): 'A67'") print("Please enter a valid cell address (Letter(s) followed by Number(s): 'A67'")
def write_xlsx( template_xlsx: str, sheet_xy_csv: List[Any], output_xlsx: str, delimiter=",", ): """Xlsxファイルへの書き込みを行う Args: template_xlsx (str): 入力Xlsxファイル名 sheet_xy_csv (List[Any]): シートNo + セル位置 + 入力ファイル output_xlsx (str): 出力Xlsxファイル名 """ wb = load_workbook(filename=template_xlsx) for sheet_no, address, input_file in sheet_xy_csv: with open(input_file) as f: reader = csv.reader(f, delimiter=delimiter) input_csv = [row for row in reader] ws = wb.worksheets[sheet_no - 1] start_col_letter, start_row = coordinate_from_string(address) start_col = column_index_from_string(start_col_letter) write_list_2d(ws, input_csv, start_row, start_col) wb.save(output_xlsx)
def find_headers(self, aSheet_name="") -> bool: """ aSheet_name: str -> sheet name """ xColumns = [] if aSheet_name: self.set_sheet(aSheet_name) else: aSheet_name = self.sheet.title # Find the first cell with values in each column. for oCol in self.sheet.iter_cols(max_row=50, max_col=50): for oCell in oCol: if not oCell.value: continue aHeader = oCell.value aIndex = coordinate_from_string(oCell.coordinate)[0] nStart = coordinate_from_string(oCell.coordinate)[1] oColumn = Column(aSheet_name, aHeader, aIndex, nStart, 0) xColumns.append(oColumn) break # If no columns found return false if not xColumns: return False # Add new columns self.columns.extend(xColumns) # Find the last cell with value in each column. for oColumn in self.columns: nSheet_end = self.sheet.max_row while True: oCell = f'{oColumn.index}{nSheet_end}' if self.sheet[oCell].value or nSheet_end == oColumn.start: oColumn.end = nSheet_end logging.debug(f"Found Column: {oColumn}") break nSheet_end -= 1 return True
def test_command(): for i , row in blk_info_df.iterrows(): print (i,row["Block"], row["Pivot"]) xy = coordinate_from_string(row["Pivot"]) cur_col = column_index_from_string(xy[0]) cur_row = xy[1] print(xy, cur_col,cur_row) print('Value ',inside_sheet.cell(column=cur_col,row=cur_row).value) return
def style_range(ws, cell_range, style=None): """ :param ws: Excel worksheet instance :param cell_range: An excel range to style (e.g. A1:F20) :param style: An openpyxl Style object """ start_cell, end_cell = cell_range.split(':') start_coord = coordinate_from_string(start_cell) start_row = start_coord[1] start_col = column_index_from_string(start_coord[0]) end_coord = coordinate_from_string(end_cell) end_row = end_coord[1] end_col = column_index_from_string(end_coord[0]) for row in range(start_row, end_row + 1): for col in range(start_col, end_col + 1): ws.cell(column=col, row=row).style = style
def get_vals_from_selector(sel: str = 'a3,a4,a5') -> List[str]: vals = [] # case insensitive sel = sel.upper() # range values if ':' in sel: cells = sel.split(':') # A3 to ('A', 3) letter1, num1 = coordinate_from_string(cells[0]) letter2, num2 = coordinate_from_string(cells[1]) if len(cells) != 2: print("Bad syntax in range selector") exit() if letter1 != letter2: print("Only ranges with the same letter are supported") exit() # we do not want an infinite loop if syntax is wrong max_count = 10000 tmp = '' c = num1 while c <= num2 and c < max_count: tmp += letter1 + str(c) + ',' c += 1 if tmp.endswith(','): tmp = tmp[:-1] sel = tmp # comma-separated values if ',' in sel: cells = sel.split(',') for el in cells: # A3 to ('A', 3) letter, num = coordinate_from_string(el) cell_val = df[letter].iloc[num - 1] # xlsx can be number instead of string vals.append(str(cell_val)) return vals
def _update_style(ws, cell_range): start_cell, end_cell = str(cell_range).split(':') start_coord = coordinate_from_string(start_cell) end_coord = coordinate_from_string(end_cell) start_row = start_coord[1] end_row = end_coord[1] start_col = column_index_from_string(start_coord[0]) end_col = column_index_from_string(end_coord[0]) border_style = ws.cell(row=start_row, column=start_col).border.left.style color = ws.cell(row=start_row, column=start_col).border.left.color cellborder = _get_border(border_style, color) for row in range(start_row, end_row + 1): for col in range(start_col, end_col + 1): ws.cell(row=row, column=col).border = cellborder
def row_and_column(cells_address: str) -> (int, int): """ Converts the string address of the cell (e.g. A1) into the row's number (starting from 1) and column's number (starting from 1). """ column_as_letter, row_number = coordinate_from_string(cells_address) column_number = column_index_from_string(column_as_letter) return row_number, column_number
def _get_registers(self) -> None: """Function finds all registers in XLS sheet and store them.""" regname_cr = cell_utils.coordinate_from_string(self.header_cells["Register Name"]) sr_access_cr = cell_utils.coordinate_from_string(self.header_cells["Access rw = has shadow register"]) desc_cr = cell_utils.coordinate_from_string(self.header_cells["Description"]) offset_cr = cell_utils.coordinate_from_string(self.header_cells["Shadow Register Offset/bit offset"]) width_cr = cell_utils.coordinate_from_string(self.header_cells["Register Width / Field width"]) s = 1 + regname_cr[1] skip = 0 for r in range(s, self.ws.max_row + 1): cell = regname_cr[0] + str(r) if skip > 0: skip -= 1 elif isinstance(self.ws[cell].value, str): # We have a register, just Mask out the Fuse register only access = self.ws[sr_access_cr[0] + str(r)].value \ if isinstance(self.ws[sr_access_cr[0] + str(r)].value, str) else "" if any(x in access for x in ["rw", "ro", "wo"]): # Now we have just Shadow registers only # Some registers are defined multiply to store bigger data # those could be detected by merged description field reg_name = self.ws[cell].value # Now, normalize the name reg_name, reg_reverse = self._filterout_bitrange(reg_name) cells = self._get_merged_by_first_cell(desc_cr[0] + str(r)) if cells is not None: # set the skip for next search cells = cells.split(':') skip = cell_utils.coordinate_from_string(cells[1])[1] - \ cell_utils.coordinate_from_string(cells[0])[1] reg_offset = int(self.ws[offset_cr[0] + str(r)].value, 16) reg_width = int(self.ws[width_cr[0] + str(r)].value) * (skip + 1) reg_descr = self.ws[desc_cr[0] + str(r)].value reg_name = reg_name.strip() register = RegsRegister(reg_name, reg_offset, reg_width, reg_descr, reg_reverse, access) self.registers.add_register(register) cells = self._get_merged_by_first_cell(regname_cr[0] + str(r)) if cells is not None: # find the number of rows of the register description cells = cells.split(':') reg_lines = cell_utils.coordinate_from_string(cells[1])[1] - cell_utils.coordinate_from_string(cells[0])[1] self._get_bitfields(register, r, reg_lines + 1)
def calc_and_limit_sheet_dim(sheet, max_col, max_row): dim = sheet.calculate_dimension() dim = dim.split(':') xy = coordinate_from_string(dim[1]) count_col = column_index_from_string(xy[0]) count_row = xy[1] if max_col and (count_col > max_col): count_col = max_col if max_row and (count_row > max_row): count_row = max_row return (count_col, count_row)
def process_table(xlsx_path, export_datas): pythoncom.CoInitialize() excel = win32.Dispatch('Excel.Application') wb = excel.Workbooks.Open(xlsx_path) wb.Worksheets(1).Delete() for export_data in export_datas: ws = wb.Worksheets(export_data['sheet_name']) cols = ws.UsedRange.Columns.Count rows = ws.UsedRange.Rows.Count ws.Columns.WrapText = True for i in range(1, cols + 1): col_text_exist = False for r in range(1, rows + 1): if ws.Cells(r, i).Value is not None: col_text_exist = True if col_text_exist == True: ws.Columns(i).ColumnWidth = 250 for i in range(1, rows + 1): row_text_exist = False for c in range(1, cols + 1): if ws.Cells(r, i).Value is not None: row_text_exist = True if row_text_exist == True: ws.Rows(i).RowHeight = 250 ws.Rows.AutoFit() ws.Rows.VerticalAlignment = 2 ws.Rows.HorizontalAlignment = 3 ws.Columns.AutoFit() for merge_data in export_data['merge_datas']: merge_data = eval(merge_data) start_col, start_row = coordinate_from_string( merge_data['address']) start_col = column_index_from_string(start_col) end_col, end_row = start_col + (merge_data['size'][0] - 1), start_row + ( merge_data['size'][1] - 1) end_addr = get_column_letter(end_col) + str(end_row) ws.Range(merge_data['address'] + ":" + end_addr).Merge() table_end_addr = get_column_letter(cols) + str(rows) ws.Columns.AutoFit() ws.Rows.AutoFit() ws.Range("A1:" + table_end_addr).BorderAround(ColorIndex=1, Weight=4, LineStyle=1) wb.Save() wb.Close() pythoncom.CoUninitialize()
def buildTargetRange(srcCoords, tr): rangeTuples = [] startCoord = coordinate_from_string(tr) startCol, startRow = column_index_from_string(startCoord[0]), startCoord[1] endCol, endRow = startCol + srcCoords[1][1] - srcCoords[1][0], startRow + srcCoords[0][1] - srcCoords[0][0] rangeTuples.append((startRow, endRow)) rangeTuples.append((startCol, endCol)) return rangeTuples
def parsing_column_indexs(self, ws): ws_column_indexs = {} # Parse mandatory property for index, row in enumerate(ws.rows): if index > self.maximum_column_index_row: break for cell in row: if (cell.value is not None) and (cell.value in self.DEFAULT_COLUMN_INDEXS): ws_column_indexs[cell.value] = column_index_from_string( coordinate_from_string(cell.coordinate)[0]) print('Mandatory : ' + str(cell.value) + ' : ' + str(cell.coordinate) + ' : ' + str( column_index_from_string( coordinate_from_string(cell.coordinate)[0]))) self.start_row = index + 1 if len(ws_column_indexs) > 0: break # Parse optional property for index, row in enumerate(ws.rows): if index > self.maximum_column_index_row: break if index != self.start_row - 1: continue for cell in row: if (cell.value is not None) and (cell.value not in self.DEFAULT_COLUMN_INDEXS): field_name = str(cell.value).lower().strip().replace( " ", "_") ws_column_indexs[field_name] = column_index_from_string( coordinate_from_string(cell.coordinate)[0]) print('Optional : ' + field_name + ' : ' + str(cell.coordinate) + ' : ' + str( column_index_from_string( coordinate_from_string(cell.coordinate)[0]))) break print('Done parsing column indexes') return ws_column_indexs
def sheet_to_inventory(group_by_col, hostname_col, ip_col, sheet): if isinstance(group_by_col, six.string_types): group_by_col = (column_index_from_string( coordinate_from_string(group_by_col + "1")[0]) - 1) if isinstance(hostname_col, six.string_types): hostname_col = (column_index_from_string( coordinate_from_string(hostname_col + "1")[0]) - 1) if isinstance(ip_col, six.string_types): ip_col = ( column_index_from_string(coordinate_from_string(ip_col + "1")[0]) - 1) groups = {"_meta": {"hostvars": {}}} rows = list(sheet.rows) for row in rows[1:]: host = row[hostname_col].value if host is None or len(host) == 0: continue if ip_col: ip = {"ansible_host": row[ip_col].value} else: ip = None group = row[group_by_col].value if group is None: group = default_group if group not in groups.keys(): #2020 change "hosts" from list to dict groups[group] = {"hosts": {}, "vars": {}} groups[group]["hosts"][host] = ip # groups["_meta"]["hostvars"][host] = {} for xlsx_head in rows[:1]: for idx, var_name in enumerate(xlsx_head): if var_name.value is None: var_name.value = "xlsx_" + var_name.coordinate if row[idx].value is not None: groups["_meta"]["hostvars"][host][ var_name.value.lower().replace(" ", "_")] = row[idx].value return groups
def cell_00(ws, cell): """Если нет данных, то проставляем нули в первой и последних строках таблицы""" # cell - верхняя-левая ячейка col1, row1 = coordinate_from_string(cell) col1 = column_index_from_string(col1) for row in [row1, ws.max_row]: for col in range(col1, ws.max_column + 1): cell_value = ws.cell(row, col).value if cell_value == None: ws.cell(row, col).value = '0.00'
def cellFormat(ws, cell, cols: int = None): """Форматируем ячейки (выравниваем по правому краю)""" # 'cols' - кол-во колонок, данные в которых будут отформатированы # (если 'cols' не задан, то форматируются данные во всех колонках, начиная с 'cell') col1, row1 = coordinate_from_string(cell) col1 = column_index_from_string(col1) colEnd = col1 + cols if cols else ws.max_column + 1 rowEnd = ws.max_row + 1 for row in range(row1, rowEnd): for col in range(col1, colEnd): ws.cell(row, col).alignment = Alignment(horizontal='right')
def _get_bitfields(self, reg: Any, excel_row: int, excel_row_cnt: int) -> None: """Tried to find and fill up all register bitfields.""" assert self.worksheet if excel_row_cnt <= 1: # There is no bitfields return bitfieldname_cr = cell_utils.coordinate_from_string( self.header_cells["Field Name"]) desc_cr = cell_utils.coordinate_from_string( self.header_cells["Description"]) offset_cr = cell_utils.coordinate_from_string( self.header_cells["Shadow Register Offset/bit offset"]) width_cr = cell_utils.coordinate_from_string( self.header_cells["Register Width / Field width"]) rv_cr = cell_utils.coordinate_from_string(self.header_cells["Value"]) excel_row += 1 excel_row_cnt -= 1 for row in range(excel_row, excel_row + excel_row_cnt): cell = bitfieldname_cr[0] + str(row) if isinstance(self.worksheet[cell].value, str): bitfield_name = self.worksheet[cell].value bitfield_offset = int(self.worksheet[offset_cr[0] + str(row)].value) bitfield_width = int(self.worksheet[width_cr[0] + str(row)].value) bitfield_descr = self.worksheet[desc_cr[0] + str(row)].value or "N/A" bitfield_rv = self.worksheet[rv_cr[0] + str(row)].value or "N/A" bitfield_descr = bitfield_descr.replace("\n", " ") bitfield = RegsBitField( reg, bitfield_name, bitfield_offset, bitfield_width, bitfield_descr, reset_val=bitfield_rv, ) reg.add_bitfield(bitfield) cells = self._get_merged_by_first_cell(bitfieldname_cr[0] + str(row)) if cells is not None: # find the number of rows of the register description cells = cells.split(":") reg_lines = ( cell_utils.coordinate_from_string(cells[1])[1] - cell_utils.coordinate_from_string(cells[0])[1]) self._get_enums(bitfield, row, reg_lines + 1)
def _find_cell_coor_by_val(self, value:Any, start:str = "", end:str = "") -> str: """Search engine for the cell values""" if start == "" or start == None: start = "A1" if end == "" or end == None: end = utils.get_column_letter(self.ws.max_column) + str(self.ws.max_row) s = cell_utils.coordinate_from_string(start) e = cell_utils.coordinate_from_string(end) sc = utils.column_index_from_string(s[0]) sr = s[1] ec = utils.column_index_from_string(e[0]) er = e[1] for r in range(sr, er+1): for c in range(sc, ec+1): val = self.ws[utils.get_column_letter(c) + str(r)].value if isinstance(val, str): val = val.replace("\n", " ") val = val.replace(" ", " ") if value == val: return utils.get_column_letter(c) + str(r) return None
def getTableHeaders(filepath): wb = load_workbook(filename=filepath, read_only=True) sheet = wb.active col_headings = [] index = 0 dim = sheet.calculate_dimension() dim = dim.split(':') xy = coordinate_from_string(dim[1]) max_col = column_index_from_string(xy[0]) for row in sheet.iter_rows(min_row=1, min_col=1, max_row=1, max_col=max_col): for cell in row: index += 1 if cell.value != None: col_headings.append((index, cell.value)) return col_headings
def _get_bitfields(self, reg: Any, excel_row: int, excel_row_cnt: int) -> None: """Tried to find and fill up all register bitfields.""" if excel_row_cnt <= 1: # There is no bitfields return bitfieldname_cr = cell_utils.coordinate_from_string(self.header_cells["Field Name"]) desc_cr = cell_utils.coordinate_from_string(self.header_cells["Description"]) offset_cr = cell_utils.coordinate_from_string(self.header_cells["Shadow Register Offset/bit offset"]) width_cr = cell_utils.coordinate_from_string(self.header_cells["Register Width / Field width"]) rv_cr = cell_utils.coordinate_from_string(self.header_cells["Value"]) excel_row += 1 excel_row_cnt -= 1 for r in range(excel_row, excel_row + excel_row_cnt): cell = bitfieldname_cr[0] + str(r) if isinstance(self.ws[cell].value, str): bitfield_name = self.ws[cell].value bitfield_offset = int(self.ws[offset_cr[0] + str(r)].value) bitfield_width = int(self.ws[width_cr[0] + str(r)].value) bitfield_descr = self.ws[desc_cr[0] + str(r)].value bitfield_rv = self.ws[rv_cr[0] + str(r)].value bitfield_rv = bitfield_rv if bitfield_rv is not None else "N/A" bitf = RegsBitField(reg, bitfield_name, bitfield_offset, bitfield_width, bitfield_descr, reset_val=bitfield_rv) reg.add_bitfield(bitf) cells = self._get_merged_by_first_cell(bitfieldname_cr[0] + str(r)) if cells is not None: # find the number of rows of the register description cells = cells.split(':') reg_lines = cell_utils.coordinate_from_string(cells[1])[1] - \ cell_utils.coordinate_from_string(cells[0])[1] self._get_enums(bitf, r, reg_lines + 1)