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 copy_xl(src, col1, col2, col3):
"""
Copy files by list in excel document.
Parameters:
src (Pathlib object): The pathlib object of source xlsx file.
col1 (str): Column containing source path + filename string.
col2 (str): Column containing destination path + filename string.
col3 (str): Column containing log data were copy was successful or not.
"""
wb = load_workbook(src, read_only=False) # workbook
ws = wb["Sheet1"] # workbook sheet activate
src_column = column_index_from_string(col1)
dst_column = column_index_from_string(col2)
log_column = column_index_from_string(col3)
for row in range(1, ws.max_row + 1):
try:
if ws.cell(row, src_column).value is not None:
if not Path(ws.cell(row, dst_column).value).parent.exists():
Path(ws.cell(row, dst_column).value).parent.mkdir(parents=True, exist_ok=True)
sh.move(ws.cell(row, src_column).value, ws.cell(row, dst_column).value)
ws.cell(row, log_column, "successful copy")
else:
pass
except Exception as error:
log.exception(f"error copy {get_column_letter(src_column)}{row} => {get_column_letter(dst_column)}{row} {error}", exc_info=False)
ws.cell(row, log_column, f"error copy {get_column_letter(src_column)}{row} => {get_column_letter(dst_column)}{row}")
wb.save(src)
wb.close()
def _p_tittle(sheet, processes: list):
"""
性能报告tittle设置
:param sheet:
:param processes:
:return:
"""
cur_letter = 'C'
for process in processes:
if cur_letter == 'C':
sheet[cur_letter + '1'] = process
sheet[cur_letter + '2'] = '内存'
next_letter = cell_utils.get_column_letter(
cell_utils.column_index_from_string(cur_letter) + 1)
sheet[next_letter + '2'] = 'CPU'
sheet.merge_cells(cur_letter + '1:' + next_letter + '1')
cur_letter = cell_utils.get_column_letter(
cell_utils.column_index_from_string(next_letter) + 1)
else:
sheet[cur_letter + '1'] = process
sheet[cur_letter + '2'] = 'PID'
mid_letter = cell_utils.get_column_letter(
cell_utils.column_index_from_string(cur_letter) + 1)
sheet[mid_letter + '2'] = '内存'
end_letter = cell_utils.get_column_letter(
cell_utils.column_index_from_string(mid_letter) + 1)
sheet[end_letter + '2'] = 'CPU'
sheet.merge_cells(cur_letter + '1:' + end_letter + '1')
cur_letter = cell_utils.get_column_letter(
cell_utils.column_index_from_string(end_letter) + 1)
def writeNoData(sh, tableList):
def writeND(br, er, ec):
# writes "No Data" above Header/Data Segment & "End of Column"
for row in range(br, er):
for col in range(1, ec):
if sh.cell(row, col).value in [None, '', 0]:
sh.cell(row, col).value = "No Data"
# ADDED ALL FONT COLORS TO GREEN FOR NOW
sh.cell(row, col).font = Font(color="FFFFFF", size=12)
# determines what is endCol
if col + 1 == endCol:
sh.cell(row, col + 1).value = "End Column"
sh.cell(row, col + 1).font = Font(color="FFFFFF", size=12)
index = 0
for sheetRange in tableList:
endCol = column_index_from_string(sheetRange['endData'][0]) + 1
endRow = sheetRange['beginHeader'][1]
if index == 0:
beginRow = 1
else:
beginRow = tableList[index - 1]['endData'][1] + 1
writeND(beginRow, endRow, endCol)
index += 1
beginRow = tableList[len(tableList) - 1]['endData'][1] + 1
endRow = sh.max_row + 1
endCol = column_index_from_string(tableList[len(tableList) - 1]['endData'][0]) + 1
writeND(beginRow, endRow, endCol)
def get_properties(wb,excel_index,parameter):
headings_start_col = excel_index['Section or material properties col']
values_start_col = excel_index['Section or material values col']
start_row = excel_index['Properties start row']
if parameter == 'Material':
ws = wb['Materials']
elif parameter == 'Section':
ws = wb['Section Properties']
else:
print('Input should be either "Material" or"Section"')
#parameter = 'unknown'
#if ws['A1'].value == 'Section #':
# parameter = 'Section'
#else:
# parameter = 'Material'
parameter_type = {};
current_property_col = headings_start_col;
current_value_col = values_start_col;
i = 1
while ws[current_property_col+str(1)].value is not None:
current_row = start_row
parameter_type[parameter+' '+str(i)]={}
while ws[current_property_col + str(current_row)].value is not None:
properties = {}
properties_heading = ws[current_property_col + str(current_row)].value
properties_value = ws[current_value_col + str(current_row)].value
#enter the new entry into the index
parameter_type[parameter+' '+str(i)][properties_heading] = properties_value
current_row = current_row + 1
i += 1
current_property_col = get_column_letter(column_index_from_string(current_property_col)+3)
current_value_col = get_column_letter(column_index_from_string(current_value_col)+3)
return parameter_type
def get_panels(wb, excel_index):
cur_panel_col = 'A'
ws = wb['Panels']
panels = []
panel_num = 1
while ws[cur_panel_col + '1'].value is not None:
xCol = get_column_letter(column_index_from_string(cur_panel_col) + 1)
yCol = get_column_letter(column_index_from_string(cur_panel_col) + 2)
zCol = get_column_letter(column_index_from_string(cur_panel_col) + 3)
x1 = ws[xCol + '4'].value
x2 = ws[xCol + '5'].value
x3 = ws[xCol + '6'].value
x4 = ws[xCol + '7'].value
y1 = ws[yCol + '4'].value
y2 = ws[yCol + '5'].value
y3 = ws[yCol + '6'].value
y4 = ws[yCol + '7'].value
z1 = ws[zCol + '4'].value
z2 = ws[zCol + '5'].value
z3 = ws[zCol + '6'].value
z4 = ws[zCol + '7'].value
panels.append(
Panel(num=panel_num,
point1=[x1, y1, z1],
point2=[x2, y2, z2],
point3=[x3, y3, z3],
point4=[x4, y4, z4]))
panel_num += 1
cur_panel_col = get_column_letter(
column_index_from_string(cur_panel_col) + 5)
print('Read ' + str(len(panels)) + ' panels')
return panels
def get_node_info(wb,excel_index,node_num_col,parameter):
if parameter == 'Floor Bracing':
ws = wb['Floor Bracing']
elif parameter == 'Bracing':
ws = wb['Bracing']
elif parameter == 'Floor Plans':
ws = wb['Floor Plans']
horiz_col = get_column_letter(column_index_from_string(node_num_col)+1)
vert_col = get_column_letter(column_index_from_string(node_num_col)+2)
mass_col = get_column_letter(column_index_from_string(node_num_col)+3)
start_row = excel_index['Properties start row']
nodes = []
mass_nodes = []
current_row = start_row
while ws[node_num_col + str(current_row)].value is not None:
horiz = ws[horiz_col + str(current_row)].value
vert = ws[vert_col + str(current_row)].value
if parameter == 'Floor Plans':
mass_at_node = ws[mass_col + str(current_row)].value
if mass_at_node ==1:
mass_nodes.append([horiz,vert])
#enter the new entry into the list
nodes.append([horiz, vert])
current_row = current_row + 1
return nodes, mass_nodes
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 removeColumns(worksheet):
to_idx = find_row_with_key(worksheet, "To")
from_idx = find_row_with_key(worksheet, "From")
x = find_blank(worksheet)
if (x == None):
endCol = worksheet.max_column + 1
else:
endCol = column_index_from_string(x)
delArray = []
for col in worksheet.iter_cols(min_row=to_idx,
max_row=to_idx,
min_col=3,
max_col=endCol):
for cell in col:
to_cell = str(cell.column_letter) + str(to_idx)
from_cell = str(cell.column_letter) + str(from_idx)
if worksheet[to_cell].value != None and worksheet[
from_cell].value != None:
delArray.append(column_index_from_string(cell.column_letter))
for i in reversed(
delArray
): #Must delete in reversed array as it changes excel sheet during manipulation
worksheet.delete_cols(i, 1)
def handle_uploaded_file(form=None, f=None, model_name=None):
wb = load_workbook(f)
print(wb.sheetnames)
# ws = wb.active
# ws=wb.get_sheet_by_name("data")
sheet_name = form.cleaned_data.get('sheet_name')
data_type = form.cleaned_data.get('data_type')
min_row = form.cleaned_data.get('from_row')
max_row = form.cleaned_data.get('to_row')
min_col = form.cleaned_data.get('from_col')
max_col = form.cleaned_data.get('to_col')
header_row = form.cleaned_data.get('header_row')
model = apps.get_model(app_label='demandware', model_name=data_type)
if sheet_name == None or sheet_name == '':
sheet_name = 'data'
ws = wb[sheet_name]
if max_row == 0:
max_row = ws.max_row
if max_col == '':
max_col = ws.max_column
min_col_num = pycell.column_index_from_string(min_col)
max_col_num = pycell.column_index_from_string(max_col)
dataset = ws.iter_rows(min_row=min_row,
min_col=min_col_num,
max_col=max_col_num,
max_row=max_row)
header = ws.iter_rows(min_row=header_row,
min_col=min_col_num,
max_col=max_col_num,
max_row=header_row)
header = [cell.value for cell in list(header)[0]]
_count = 0
insertDataSet = []
extInsertDataSet = []
fields = get_model_fields(model)
print(fields)
for row in dataset:
values = {}
extValues = {}
for key, _cell in zip(header, row):
if key in fields:
values[key] = _cell.value if _cell.value else ''
else:
extValues[key] = _cell.value if _cell.value else ''
_count = _count + 1
insertDataSet.append(values)
extInsertDataSet.append(extValues)
message = detect_service(model_name=data_type,
data=insertDataSet,
extData=extInsertDataSet,
header=header)
result = dict(message=message, count=_count)
return result
def set_number_format( _worksheet, _table_begin_cell, _table_end_cell, _number_formats, _has_header = False ):
begin_column = _worksheet[_table_begin_cell].column
begin_column_index = column_index_from_string(begin_column)
begin_row = _worksheet[_table_begin_cell].row
end_column = _worksheet[_table_end_cell].column
end_column_index = column_index_from_string(end_column)
end_row = _worksheet[_table_end_cell].row
# print("begin: " + begin_column + ", index: " + str(begin_column_index) + ", row: " + str(begin_row))
# print("end: " + end_column + ", index: " + str(end_column_index) + ", row: " + str(end_row))
styling_header = _has_header
styling_first_table_row = True
styling_index = 0
styling_max_column = end_column_index - begin_column_index
for table_row in range(begin_row, end_row+1):
if styling_header:
styling_header = False
continue
for table_col in range(begin_column_index, end_column_index+1):
cell = _worksheet.cell(column=table_col, row=table_row)
cell.number_format = _number_formats[get_column_letter(table_col)]
def fill_html_table_from_excel_sheet(table,
sheet,
start_row=1,
end_row=None,
start_column='A',
end_column=None,
ignoreEmptyRow=True,
datetime_format_mapping=None):
if not end_column:
end_column = detect_sheet_end_column(sheet)
start_column_index = column_index_from_string(start_column)
end_column_index = column_index_from_string(end_column)
hidden_cells = hidden_coordinate_set_of_merged_cells(
sheet.merged_cells.ranges)
merged_mapping = merge_cell_mapping_by_top_left(sheet.merged_cells.ranges)
for ri, row in enumerate(sheet):
ri += 1
if ri < start_row:
continue
if end_row is not None and ri > end_row:
continue
if ignoreEmptyRow and all_cells_empty(row):
continue
html_row = table.append_row()
for cell in row[start_column_index - 1:end_column_index]:
value = str(cell.value or '').replace('\n', '<br />')
if cell.is_date and cell.value:
fmt = (datetime_format_mapping or {}).get(cell.number_format)
if not fmt:
fmt = default_datetime_format_mapping.get(
cell.number_format)
if fmt:
value = cell.value.strftime(fmt)
html_cell = html_row.append_cell(value)
html_cell.set_escape(False)
merged = merged_mapping.get(cell.coordinate)
if merged:
html_cell.set_colspan(merged.max_col + 1 - merged.min_col)
html_cell.set_rowspan(merged.max_row + 1 - merged.min_row)
html_cell_style = {
'text-align': cell.alignment.horizontal,
'vertical-align': cell.alignment.vertical,
}
fill_color = cell.fill.start_color.index
if fill_color and fill_color.startswith('FF'):
html_cell_style['background-color'] = '#{:s}'.format(
fill_color[2:])
html_cell.set_style(html_cell_style)
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 getPerimeter(sheetRange):
beginCol = column_index_from_string(sheetRange['beginHeader'][0])
beginRow = sheetRange['beginHeader'][1]
endCol = column_index_from_string(sheetRange['endData'][0])
endRow = sheetRange['endData'][1]
return {'beginCol': beginCol, 'endCol': endCol, 'beginRow': beginRow, 'endRow': endRow}
def delete_columns(self, col_range: str) -> None:
col_range = col_range.split(":")
start = column_index_from_string(col_range[0])
try:
end = column_index_from_string(col_range[1])
except IndexError:
end = start + 1
self.sheet.delete_cols(start, end - start)
def distances(df, file_count, files2, fields1, fields2, fields3):
df = df.fillna(0)
for i in range(file_count):
sl = files2.astype(str).loc[i, "Store"] + "_" + files2.astype(str).loc[
i, "Location"]
my = files2.astype(str).loc[i, "Month Year"]
#get month year string
out_fields3 = [sl + " " + s + " " + my for s in fields3]
out_fields2 = [sl + " " + s + " " + my for s in fields2]
out_fields1 = [sl + " " + s + " " + my for s in fields1]
#create labels 1 for output columns
if i == 0:
df[out_fields3[0]] = 0
df[out_fields3[1]] = 0
df[out_fields3[2]] = 0
x_ref = df[out_fields2[1]]
y_ref0 = df[out_fields2[2]]
p_ref = df[out_fields1[0]]
#y_ref=[0 for x in range (y_ref0)];
y_ref = [0] * y_ref0.shape[0]
for j in range(y_ref0.shape[0]):
if y_ref0[j] != 0:
y_ref[j] = column_index_from_string(y_ref0[j])
else:
y_ref[j] = 0
else:
x = df[out_fields2[1]]
y0 = df[out_fields2[2]]
p = df[out_fields1[0]]
y = [0] * y0.shape[0]
for j in range(y0.shape[0]):
if y0[j] != 0:
y[j] = column_index_from_string(y0[j])
else:
y[j] = 0
#df[out_fields3[0]]=abs(numpy.subtract(x,x_ref))+abs(numpy.subtract(y,y_ref));
#df[out_fields3[1]]=(numpy.square(numpy.subtract(x,x_ref))+numpy.square(numpy.subtract(y,y_ref)))**0.5;
for j in range(y0.shape[0]):
if x[j] != 0 and y[j] != 0 and x_ref[j] != 0 and y_ref[j] != 0:
df.loc[j, out_fields3[0]] = abs(x[j] -
x_ref[j]) + abs(y[j] -
y_ref[j])
df.loc[j, out_fields3[1]] = ((x[j] - x_ref[j])**2 +
(y[j] - y_ref[j])**2)**0.5
df.loc[j, out_fields3[2]] = p[j] - p_ref[j]
else:
df.loc[j, out_fields3[0]] = 0
df.loc[j, out_fields3[1]] = 0
df.loc[j, out_fields3[2]] = 0
x_ref = x
y_ref = y
p_ref = p
return df
def format_horizontal(ws, loc_range: str, num_format: str):
# loc_range: format "A10:D10" multiple capital letters followd by multiple numbers
loc = re.match(r'^([A-Z]+)([0-9]+):([A-Z]+)([0-9]+)$', loc_range)
row_init = int(loc.group(2))
col_start_num = column_index_from_string(loc.group(1))
col_end_num = column_index_from_string(loc.group(3))
for col in range(col_start_num, col_end_num + 1):
loc = get_column_letter(col) + str(row_init)
ws[loc].number_format = num_format
def get_floor_plans(wb,excel_index):
headings_col = excel_index['Floor plan start col']
section_col = excel_index['Floor plan section col']
start_node_col = excel_index['Floor plan start node col']
end_node_col = excel_index['Floor plan end node col']
start_row = excel_index['Properties start row']
ws = wb['Floor Plans']
all_plans = []
current_headings_col = headings_col
current_section_col = section_col
current_start_node_col = start_node_col
current_end_node_col = end_node_col
i = 1
while ws[current_headings_col + str(4)].value is not None:
[nodes, mass_nodes] = get_node_info(wb, excel_index, current_headings_col, 'Floor Plans')
current_row = start_row
cur_members = []
max_node_x = 0
max_node_y = 0
min_node_x = 0
min_node_y = 0
while ws[current_start_node_col + str(current_row)].value is not None:
section = ws[current_section_col + str(current_row)].value
start_node_num = ws[current_start_node_col + str(current_row)].value
end_node_num = ws[current_end_node_col + str(current_row)].value
start_node = nodes[start_node_num - 1]
end_node = nodes[end_node_num - 1]
cur_members.append(Member(start_node, end_node, section))
#find scaling factor in x and y, find area
cur_nodes = []
cur_nodes.append(start_node)
cur_nodes.append(end_node)
for node in cur_nodes:
if max_node_x < start_node[0]:
max_node_x = node[0]
if max_node_y < node[1]:
max_node_y = node[1]
if min_node_x > node[0]:
min_node_x = node[0]
if min_node_y > node[1]:
min_node_y = node[1]
current_row = current_row + 1
scaling_x = max_node_x - min_node_x
scaling_y = max_node_y - min_node_y
#area = scaling_x*scaling_y
all_plans.append(FloorPlan(number=i, members=cur_members, mass_nodes=mass_nodes, scaling_x = scaling_x, scaling_y = scaling_y))
i += 1
current_headings_col = get_column_letter(column_index_from_string(current_headings_col) + 9)
current_section_col = get_column_letter(column_index_from_string(current_section_col) + 9)
current_start_node_col = get_column_letter(column_index_from_string(current_start_node_col) + 9)
current_end_node_col = get_column_letter(column_index_from_string(current_end_node_col) + 9)
return all_plans
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 get_bracing(wb, excel_index, parameter):
headings_col = excel_index['Bracing start col']
section_col = excel_index['Bracing section col']
start_node_col = excel_index['Bracing start node col']
end_node_col = excel_index['Bracing end node col']
start_row = excel_index['Properties start row']
if parameter == 'Floor Bracing':
ws = wb['Floor Bracing']
elif parameter == 'Bracing':
ws = wb['Bracing']
elif parameter == 'Space Bracing':
ws = wb['Space Bracing']
else:
print(
'Input should be either "Floor Bracing", "Space Bracing" or "Bracing"'
)
all_bracing = []
current_headings_col = headings_col
current_section_col = section_col
current_start_node_col = start_node_col
current_end_node_col = end_node_col
i = 1
while ws[current_headings_col + str(4)].value is not None:
nodes, mass_nodes = get_node_info(wb, excel_index,
current_headings_col, parameter)
current_row = start_row
j = 1
cur_members = []
while ws[current_start_node_col + str(current_row)].value is not None:
section = ws[current_section_col + str(current_row)].value
start_node_num = ws[current_start_node_col +
str(current_row)].value
end_node_num = ws[current_end_node_col + str(current_row)].value
start_node = nodes[start_node_num - 1]
end_node = nodes[end_node_num - 1]
cur_members.append(Member(start_node, end_node, section))
current_row = current_row + 1
j += 1
all_bracing.append(
BracingScheme(number=i, members=cur_members,
mass_nodes=mass_nodes))
i += 1
current_headings_col = get_column_letter(
column_index_from_string(current_headings_col) + 9)
current_section_col = get_column_letter(
column_index_from_string(current_section_col) + 9)
current_start_node_col = get_column_letter(
column_index_from_string(current_start_node_col) + 9)
current_end_node_col = get_column_letter(
column_index_from_string(current_end_node_col) + 9)
print('Read ' + str(len(all_bracing)) + ' bracing schemes')
return all_bracing
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 write_blunk_address_for_excel(sheet, start_column, index, address, name):
start_column_idx = column_index_from_string(start_column)
sheet.cell(row=index,
column=start_column_idx,
value=util.int2ip4(int(address)))
sheet.cell(row=index, column=start_column_idx + 1, value=name)
def set_fill_color(sheet, start_column, index, row):
start_column_idx = column_index_from_string(start_column)
for column in range(len(row)):
cell = sheet.cell(row=index, column=start_column_idx + column)
cell.fill = TITLE_FILL_COLOR
cell.font = BOLD_FONT
def _get_column_as_letter(self, sheet, column_to_convert, startcol=0):
if not isinstance(column_to_convert,
(int, str_type, unicode_type, Container)):
raise TypeError(
"column must be an index, column letter or column name")
column_as_letter = None
if column_to_convert in self.data_df.columns: # column name
column_index = self.data_df.columns.get_loc(
column_to_convert
) + startcol + 1 # worksheet columns index start from 1
column_as_letter = cell.get_column_letter(column_index)
# column index
elif isinstance(column_to_convert, int) and column_to_convert >= 1:
column_as_letter = cell.get_column_letter(startcol +
column_to_convert)
# assuming we got column letter
elif isinstance(column_to_convert,
(str_type, unicode_type)) < get_column_letter(
sheet.max_column):
column_as_letter = column_to_convert
if column_as_letter is None or cell.column_index_from_string(
column_as_letter) > sheet.max_column:
raise IndexError("column: %s is out of columns range." %
column_to_convert)
return column_as_letter
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_last_data_row(worksheet, start_row, column_range_start):
column_index = cell.column_index_from_string(column_range_start)
for current_row in range(start_row, worksheet.max_row):
val = worksheet.cell(row=current_row, column=column_index).value
if worksheet.cell(row=current_row, column=column_index).value == None:
return current_row - 1
return worksheet.max_row
def write_to_config(btn):
config = ConfigParser()
config.read('config.ini')
for i in range(len(ENTRY_LIST)):
#loop through entry fields
val = ENTRY_LIST[i].get()
try:
#Convert alphanumeric column to int
val = str(cell.column_index_from_string(val))
except ValueError:
val = '-1'
new_tuple = (LISTINGS[i][0], '-1')
LISTINGS[i] = new_tuple
#Update LISTINGS
new_tuple = (LISTINGS[i][0], val)
LISTINGS[i] = new_tuple
try:
config.set('main', LISTINGS[i][0], val)
except (NoSectionError, DuplicateSectionError,
MissingSectionHeaderError):
init_config()
config.read('config.ini')
config.set('main', LISTINGS[i][0], val)
except NoOptionError:
pass
# save to config file
with open('config.ini', 'w') as configfile:
config.write(configfile)
btn.configure(state=NORMAL)
def insertScreenReader(sh, tableList):
blank = aodaConfig.screen_message
table_size = len(tableList)
if table_size == 1:
dataColumn = column_index_from_string(tableList[0]['beginData'][0])
headerRow = tableList[0]['beginData'][1] - 1
dataCell = "".join((get_column_letter(dataColumn), str(headerRow)))
text = blank + "The table starts on " + dataCell + ". Column Titles are in Row " + str(headerRow) + "."
else:
statement = ''
for x in tableList:
dataColumn = (x['beginData'][0])
headerRow = x['beginData'][1] - 1
dataCell = "".join((dataColumn, str(headerRow)))
headers = ', ' + dataCell + ', Column Title in Row ' + str(headerRow)
statement = statement + headers
text = blank + 'There are ' + str(table_size) + ' tables in this sheet. The tables start at' + statement + '.'
cell = findScreenReader(sh, tableList[0]['beginHeader'][1])
if cell:
cell.value = text
else:
sh.cell(1, 1).value = text
def write_row_for_excel(sheet, start_column, index, row):
start_column_idx = column_index_from_string(start_column)
for column in range(len(row)):
cell_value = row[column]
cell = sheet.cell(row=index, column=start_column_idx + column, value=cell_value)
if (cell_value is not None) and (1 < len(cell_value.split('\n'))):
cell.alignment = Alignment(wrapText=True)
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 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 colorize_column( _worksheet, _col_color, _col_reference, _color_level = 0.8 ):
"""
_col_color: The Column Letter of the Column that should be colorized
_col_reference: The Column Letter of the Column that is used as reference
_color_level: (float) This "Level" is used for the orange color (Default: 0.8)"""
pFill_orange = PatternFill(patternType=fills.FILL_SOLID, start_color='FCD5B4')
pFill_red = PatternFill(patternType=fills.FILL_SOLID, start_color='E6B9B8')
if _color_level is None:
level = 0.8
try:
level = float(_color_level)
except (ValueError, TypeError) as e:
print("Unable to set color_level -> defaults to 0.8")
level = float(0.8)
col1_index = column_index_from_string(_col_color)
col_ref_index = column_index_from_string(_col_reference)
table_rows = _worksheet.max_row
for row_num in range(2, table_rows+1):
col1_value = _worksheet[str(_col_color)+str(row_num)].value
col_ref_value = _worksheet[str(_col_reference)+str(row_num)].value
try:
c1 = float(col1_value)
c2 = float(col_ref_value)
except (ValueError, TypeError) as e:
continue
if c1 > (c2*level):
if c1 > c2:
_worksheet[str(_col_color)+str(row_num)].fill = pFill_red
else:
_worksheet[str(_col_color)+str(row_num)].fill = pFill_orange
