in scripts/update_excel_openpyxl.py [0:0]
def update_excel_file(input_excel_file, output_excel_file, checklist_data):
# Load workbook
try:
wb = load_workbook(filename = input_excel_file)
if args.verbose:
print("DEBUG: workbook", input_excel_file, "opened successfully")
except Exception as e:
print("ERROR: Error when opening Excel file", input_excel_file, "-", str(e))
sys.exit(1)
# Get worksheet
try:
ws = wb[worksheet_checklist_name]
if args.verbose:
print("DEBUG: worksheet", worksheet_checklist_name, "selected successfully")
except Exception as e:
print("ERROR: Error when selecting worksheet", worksheet_checklist_name, "-", str(e))
sys.exit(1)
# Set checklist name
try:
if 'metadata' in checklist_data:
if 'name' in checklist_data['metadata']:
ws[col_checklist_name + row_checklist_name] = checklist_data["metadata"]["name"]
else:
ws[col_checklist_name + row_checklist_name] = "Azure Review Checklist"
else:
ws[col_checklist_name + row_checklist_name] = "Azure Review Checklist"
if args.verbose:
print("DEBUG: starting filling the Excel spreadsheet with the values of checklist '{0}'".format(checklist_data["metadata"]["name"]))
except Exception as e:
print("ERROR: Error when filling in worksheet", worksheet_checklist_name, "-", str(e))
sys.exit(1)
# Get default status from the JSON, default to "Not verified"
try:
status_list = checklist_data.get("status")
default_status = status_list[0].get("name")
if args.verbose:
print ("DEBUG: default status retrieved from checklist: '{0}'".format(default_status))
except:
default_status = "Not verified"
if args.verbose:
print ("DEBUG: Using default status 'Not verified'")
pass
# For each checklist item, add a row to spreadsheet
row_counter = row1
for item in checklist_data.get("items"):
# Read variables from JSON
guid = item.get("guid")
category = item.get("category")
subcategory = item.get("subcategory")
text = item.get("text")
description = item.get("description")
severity = item.get("severity")
link = item.get("link")
training = item.get("training")
status = default_status
graph_query_success = item.get("graph_success")
graph_query_failure = item.get("graph_failure")
# Update Excel
ws[col_area + str(row_counter)].value = category
ws[col_subarea + str(row_counter)].value = subcategory
ws[col_check + str(row_counter)].value = text
ws[col_desc + str(row_counter)].value = description
ws[col_sev + str(row_counter)].value = severity
ws[col_status + str(row_counter)].value = status
ws[col_link + str(row_counter)].value = link
# if link != None:
# link_elements = link.split('#')
# link_address = link_elements[0]
# if len(link_elements) > 1:
# link_subaddress = link_elements[1]
# else:
# link_subaddress = ""
# ws.api.Hyperlinks.Add (Anchor=ws[col_link + str(row_counter)].api, Address=link_address, SubAddress=link_subaddress, ScreenTip="", TextToDisplay=info_link_text)
ws[col_training + str(row_counter)].value = training
# if training != None:
# training_elements = training.split('#')
# training_address = training_elements[0]
# if len(training_elements) > 1:
# training_subaddress = training_elements[1]
# else:
# training_subaddress = ""
# ws.api.Hyperlinks.Add (Anchor=ws[col_training + str(row_counter)].api, Address=training_address, SubAddress=training_subaddress, ScreenTip="", TextToDisplay=training_link_text)
# GUID and ARG queries
ws[col_arg_success + str(row_counter)].value = graph_query_success
ws[col_arg_failure + str(row_counter)].value = graph_query_failure
ws[col_guid + str(row_counter)].value = guid
# Next row
row_counter += 1
# Display summary
if args.verbose:
number_of_checks = row_counter - row1
print("DEBUG:", str(number_of_checks), "checks added to Excel spreadsheet")
# Get worksheet
try:
wsv = wb[worksheet_values_name]
if args.verbose:
print("DEBUG: worksheet", worksheet_values_name, "selected successfully")
except Exception as e:
print("ERROR: Error when selecting worksheet", worksheet_values_name, "-", str(e))
sys.exit(1)
# Update categories
row_counter = values_row1
for item in checklist_data.get("categories"):
area = item.get("name")
wsv[col_values_area + str(row_counter)].value = area
row_counter += 1
# Display summary
if args.verbose:
print("DEBUG:", str(row_counter - values_row1), "categories added to Excel spreadsheet")
# Update status
if 'status' in checklist_data:
row_counter = values_row1
for item in checklist_data.get("status"):
status = item.get("name")
description = item.get("description")
wsv[col_values_status + str(row_counter)].value = status
wsv[col_values_description + str(row_counter)].value = description
row_counter += 1
else:
print('ERROR: no "status" information in the checklist.')
# Display summary
if args.verbose:
print("DEBUG:", str(row_counter - values_row1), "statuses added to Excel spreadsheet")
# Update severities
if 'severities' in checklist_data:
row_counter = values_row1
for item in checklist_data.get("severities"):
severity = item.get("name")
wsv[col_values_severity + str(row_counter)].value = severity
row_counter += 1
# Display summary
if args.verbose:
print("DEBUG:", str(row_counter - values_row1), "severities added to Excel spreadsheet")
# Data validation
# dv = DataValidation(type="list", formula1='=Values!$B$2:$B$6', allow_blank=True, showDropDown=True)
dv = DataValidation(type="list", formula1='=Values!$B$2:$B$6', allow_blank=True)
rangevar = col_status + str(row1) +':' + col_status + str(row1 + number_of_checks)
if args.verbose:
print("DEBUG: adding data validation to range", rangevar)
dv.add(rangevar)
ws.add_data_validation(dv)
# Close book
if args.verbose:
print("DEBUG: saving workbook", output_excel_file)
try:
wb.save(output_excel_file)
except Exception as e:
print("ERROR: Error when saving Excel file to", output_excel_file, "-", str(e))
sys.exit(1)