import os import re import shutil import pandas as pd import openpyxl from…
- User
import os import re import shutil import pandas as pd import openpyxl from openpyxl.styles import Alignment, Font, PatternFill
This functions tries to create folder inside project directory.
def create_folder(folder_name: str) -> None: """ :rtype: Creates the folder of the respective company. """ try: os.makedirs(os.path.join(dir_path, folder_name), exist_ok=True) except FileExistsError: pass
def create_excel_sheet(excel_book: openpyxl.workbook) -> openpyxl.workbook: ws = excel_book.create_sheet(brand) ws['B1'] = f"{incident} provided access to users" ws['C1'] = incident ws['B2'] = "Roles" ws['C2'] = "GRC Request Numbers" ws.merge_cells("A1:A2") ws['A1'] = "No."
cell_style() for column in ws.columns: max_length = max(len(str(cell.value)) for cell in column) adjusted_width = (max_length + 2) * 1.2 ws.column_dimensions[column[0].column_letter].width = adjusted_width ws.freeze_panes = 'A3' ws.sheet_view.zoomScale = 180 print(f"[INFO] Created {brand} sheet in the Excel Workbook") return excel_bookdef cell_style() -> None: sheet = wb[brand]
# Define the background color color = 'FFFF00' # Yellow color # Apply color to rows 1 and 2 data for row in range(1, 3): for cell in sheet[row]: cell.fill = PatternFill(start_color=color, end_color=color, fill_type='solid') for row in sheet.iter_rows(): for cell in row: cell.alignment = Alignment(horizontal='center', vertical='center') cell.font = Font(name='Cascadia Code')This functions tries to create role-based files inside the folders which was created previously.
def role_specific_files(file: str, workbook: openpyxl.workbook) -> None: """ :rtype: Displays the Company, Report file and the number of roles within the company. """ # Creating Folder if brand in ['VANS', 'TNF', 'TBL']: create_folder(brand)
workbook = create_excel_sheet(workbook) sheet = workbook[brand] df = pd.read_csv(file) df = df[['User Login', 'Email', 'Role']] df = df.sort_values(by=['Role'], ascending=True, ignore_index=True) roles = df['Role'].unique() total_roles = roles.size # Insert data row-wise from A3 for row, role in enumerate(roles, start=3): sheet.cell(row=row, column=1).value = total_roles - row + 3 sheet.cell(row=row, column=2).value = role cell_style() template_df = pd.read_csv('Create_User_Template.csv') for role in roles: role_df = df[df['Role'] == role] temp = template_df.copy() temp['USERID'] = role_df['User Login'] temp['MANAGER'] = user_id temp['EMAIL'] = role_df['Email'] temp.to_csv(os.path.join(dir_path, brand, role + '.csv'), index=False) shutil.make_archive(brand, "zip", dir_path + brand) print(f"[INFO] Found {total_roles} {brand} Business roles.")def find_brand() -> str: """ :rtype: Finding the Brand name from the file using Regular Expression. """ return re.search("[A-Z]{3,4}", report).group()
if name == "main": # Project Directory dir_path = 'C:\Projects\Store_User_Creation\'
# Emergency Firefighter ID in PG1 system # user_id = 'FF_SEC_2' if brand == 'VANS' else 'FF_SEC_2' user_id = 'FF_SEC_3' # Incident Number incident = 'INC3189547' # Name of the Excel File to be created excel_file = 'Store_User_Creation.xlsx' # Creating blank Excel Workbook wb = openpyxl.Workbook() # Selecting the Report Files reports = [key for key in os.listdir(dir_path) if key.startswith('report')] if reports: print("-------------------------------------------------------------------") print(f"All the Reports of {incident}:") print("-------------------------------------------------------------------\n{0}\n".format("\n".join(reports))) print("Generating Log....") print("-------------------------------------------------------------------") for report in reports: # Checking Brand brand = find_brand() # Creating the Role-Based Files in their Respective Folder role_specific_files(report, wb) # Deleted default Sheet from the workbook del wb[wb.sheetnames[0]] wb.save(excel_file) print(f"[INFO] Successfully created {excel_file} and {len(reports)} zip files containing Role-Specific files.") else: print(f"[INFO] No report files present in {dir_path}") - Assistant
This script works for a specific use case of Role-based files generation for the store user creation process. It reads report files (in CSV format) containing user login, email, and role information and performs the following tasks:
- Finds the brand name in the file using Regular Expressions.
- Creates an Excel file with the brand name for the incident, with different roles.
- Creates separate role-based folders for different roles inside each brand's folder and sorts the users based on their roles.
- Writes login and email data to a Create_User_Template.csv file for each role and brand in the respective folder.
- Zips the generated folders and saves them to the specified location.
To adapt the script to your use case, you just need to adjust the path, incident number, and format of the report files, if required. Please note that this script assumes the presence of certain columns in the CSV (e.g., 'User Login', 'Email', 'Role'), so make sure your input CSV files have these columns. If there are any differences, you may need to modify the code accordingly.