in scripts/create_master_checklist.py [0:0]
def update_excel_file(input_excel_file, output_excel_file, checklist_data):
# Constants
worksheet_checklist_name = 'Checklist'
row1 = 8 # First row after which the Excel spreadsheet will be updated
col_checklist_name = "A"
row_checklist_name = "4"
guid_column_index = "M"
comment_column_index = "I"
sample_cell_index = 'A4'
# WAF checklists do not have category and subcategory
if args.waf:
col_checklist="A"
col_waf_pillar = "B"
col_services = "C"
col_check = "D"
col_desc = "E"
col_sev = "F"
col_status = "G"
col_comment = "H"
col_link = "I"
col_training = "J"
col_arg = "K"
col_guid = "L"
else:
col_checklist="A"
col_area = "B"
col_subarea = "C"
col_waf_pillar = "D"
col_services = "E"
col_check = "F"
col_desc = "G"
col_sev = "H"
col_status = "I"
col_comment = "J"
col_link = "K"
col_training = "L"
col_arg = "M"
col_guid = "N"
info_link_text = 'More info'
training_link_text = 'Training'
worksheet_values_name = 'Values'
values_row1 = 2
col_values_severity = "A"
col_values_status = "B"
col_values_area = "C"
col_values_description = "H"
last_column = col_guid
# 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:
ws[col_checklist_name + row_checklist_name] = checklist_data["metadata"]["name"]
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 selecting 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
checklist_name = format4excel(item.get("checklist"))
guid = format4excel(item.get("guid"))
if not args.waf:
category = format4excel(item.get("category"))
subcategory = format4excel(item.get("subcategory"))
waf_pillar = format4excel(item.get("waf"))
text = format4excel(item.get("text"))
description = format4excel(item.get("description"))
severity = format4excel(item.get("severity"))
link = format4excel(item.get("link"))
training = format4excel(item.get("training"))
status = default_status
graph_query = format4excel(item.get("graph"))
if args.waf:
services = format4excel(item.get("service"))
else:
# Transform services array in a comma-separated string
services = ""
if "services" in item:
for service in item["services"]:
if len(services) > 0:
services += ", "
services += service
# Update Excel
ws[col_checklist + str(row_counter)].value = checklist_name
if not args.waf:
ws[col_area + str(row_counter)].value = category
ws[col_subarea + str(row_counter)].value = subcategory
ws[col_waf_pillar + str(row_counter)].value = waf_pillar
ws[col_services + str(row_counter)].value = services
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
if not args.no_links:
if link:
ws[col_link + str(row_counter)].value = info_link_text
ws[col_link + str(row_counter)].hyperlink = link
ws[col_link + str(row_counter)].style = "Hyperlink"
# ws[col_link + str(row_counter)].value = '=HYPERLINK("{}", "{}")'.format(link, info_link_text)
if training:
ws[col_training + str(row_counter)].value = training_link_text
ws[col_training + str(row_counter)].value = training
ws[col_training + str(row_counter)].style = "Hyperlink"
# ws[col_training + str(row_counter)].value = '=HYPERLINK("{}", "{}")'.format(training, training_link_text)
ws[col_arg + str(row_counter)].value = graph_query
ws[col_guid + str(row_counter)].value = guid
# Next row
row_counter += 1
# Create table
# Corrupts file!!!!
# table_ref = "A" + str(row1 - 1) + ":" + last_column + str(row_counter - 1)
# if args.verbose:
# print("DEBUG: creating table for range {0}...".format(table_ref))
# table = Table(displayName="Checklist", ref=table_ref)
# ws.add_table(table)
# Get number of checks
number_of_checks = row_counter - row1
# Display summary
if args.verbose:
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 status
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
# Display summary
if args.verbose:
print("DEBUG:", str(row_counter - values_row1), "statuses added to Excel spreadsheet")
# Update severities
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
# UserWarning: Data Validation extension is not supported and will be removed!!!!
# dv = DataValidation(type="list", formula1='=Values!$B$2:$B$6', allow_blank=True, showDropDown=True)
if not args.no_data_validation:
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)