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)
