def update_excel_file()

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)