erika_excel.py

from openpyxl import load_workbook

try:
    # Load the workbook
    workbook = load_workbook('areport.xlsx')
except FileNotFoundError as e:
    print(f"Error: The workbook '{e.filename}' was not found. Ensure it's the same directory as this script")
    exit(1)  # Exit the script

# Select the worksheet by its name
worksheet = workbook.active

header_row = ["User Name (Original Name)", "Email", "Job Title", "Select team", "Country/Region Name"]


# Delete rows 5-17
for i in range(17, 4, -1):
    worksheet.delete_rows(i)

# Delete all columns except B, E, F, M, N
for i in reversed(range(1, worksheet.max_column + 1)):
    if i not in [2, 5, 6, 13, 14]:  # Columns B, E, F, M, N
        worksheet.delete_cols(i)

# Iterate over the rows in reverse order
for i in range(worksheet.max_row, 0, -1):
    # Check if the cells in columns C and D are empty
    if not worksheet.cell(row=i, column=3).value and not worksheet.cell(row=i, column=4).value:
        # Delete the row
        worksheet.delete_rows(i)

# Find the row that contains the header
for i, row in enumerate(worksheet.iter_rows(values_only=True), start=1):
    if list(row) == header_row:
        start_row = i + 1
        break

teams = set()
for row in worksheet.iter_rows(min_row=start_row, min_col=4, max_col=4):
    # Assuming team names are in column D and data starts from row 2
    if row[0].value:  # If cell is not empty
        # Split the cell's value at commas
        team_names = row[0].value.split(',')
        # Add each team name to the set
        for team_name in team_names:
            teams.add(team_name.strip())  # strip() is used to remove leading and trailing whitespace

# For each unique team name, create a new worksheet and add a header row
for team in teams:
    team_sheet = workbook.create_sheet(title=team)
    team_sheet.append(["Name", "Title", "Region", "Manager"])

# Iterate over the rows again, and for each row, create a list and append it to the worksheet of its team
for row in worksheet.iter_rows(min_row=start_row, max_row=worksheet.max_row):
    if not any(cell.value for cell in row):
        break

    # Check if the cell's value is not None or empty before trying to split it
    if row[3].value and row[3].value.strip():
        team_names = row[3].value.split(',')  # Assuming team names are in column D
        for team_name in team_names:
            team_name = team_name.strip()  # Remove leading and trailing whitespace
            if team_name in teams:  # If the team name is in the set of team names
                # Create a list with the person's name, their title, and their region
                person_info = [row[0].value, row[2].value, row[4].value]  # Assuming person's name is in column A, title in column C, and region in column E
                # Append the list to the corresponding team's worksheet
                team_sheet = workbook[team_name]
                team_sheet.append(person_info)

# After adding all team members, add a row with "Total" and the count of team members
for team in teams:
    team_sheet = workbook[team]
    team_count = len(team_sheet['A']) - 1  # Subtract 1 for the header row
    team_sheet.append(["Total", team_count])

# Save the workbook
workbook.save('output.xlsx')