"""

import openpyxl
import os
import re

file_path = r"C:\Users\AK001027556\Desktop\New folder\Training\python_excel_script\Honda\vectorFramework\SCU_TestCases_1.xlsx"

file_path = r"C:\Users\AK001027556\Desktop\New folder\Training\python_excel_script\Honda\vectorFramework\unmerged_SCU_TestCases_1.xlsx"
OUTPUT_FILE_PATH = r"C:\Users\AK001027556\Desktop\New folder\Training\python_excel_script\Honda\vectorFramework\output_data_new_format.xlsx"

def create_and_write_excel( model_variable, expected_value):
try:
if os.path.exists(OUTPUT_FILE_PATH):
workbook = openpyxl.load_workbook(OUTPUT_FILE_PATH)
sheet = workbook.active
next_row = sheet.max_row + 1
else:
workbook = openpyxl.Workbook()
sheet = workbook.active
sheet.title = "Extracted Data"
headers = {
'Serial No': 'A',
'Testcase ID': 'B',
'Description': 'C', # Placeholder for Description
'Model Variable': 'D',
'Expected Value': 'E'
}

        # Write headers starting from row 3
        header_row_index = 3
        for header, col_letter in headers.items():
            sheet[f'{col_letter}{header_row_index}'] = header

        next_row = header_row_index + 1  # Start data from row 4

    # Write the data to the next row
    sheet[f'A{next_row}'] = next_row - 3  # Serial No (adjusting for header rows)
    sheet[f'B{next_row}'] = ""
    sheet[f'C{next_row}'] = ""  # Description - leave empty or add a default
    sheet[f'D{next_row}'] = model_variable
    sheet[f'E{next_row}'] = expected_value

    workbook.save(OUTPUT_FILE_PATH)
    print(f"Successfully wrote data to {OUTPUT_FILE_PATH}")

except Exception as e:
    print(f"An error occurred while writing to the Excel file: {e}")

def get_last_used_column(sheet):
if sheet.max_row == 0 or sheet.max_column == 0:
return 0 # Return 0 for an empty sheet
else:
return sheet.max_row

def split_variable_value(data_string):
if '=' in data_string:
parts = data_string.split('=')
variable_name = parts[0].strip()
value = parts[1].strip()
# print("variable_name= ", variable_name, " Value = ", value)
create_and_write_excel(variable_name,value)

def write_text_to_excel_cell(row_num, col_id, value):
try:
# Check if the file exists before attempting to load
if not os.path.exists(OUTPUT_FILE_PATH):
print(f"Error: File not found at {OUTPUT_FILE_PATH}. Cannot write to a specific cell in a non-existent file.")
print("Please use the create_and_write_excel function first to create the file.")
return

    workbook = openpyxl.load_workbook(OUTPUT_FILE_PATH)
    sheet = workbook.active

    if isinstance(col_id, (int, str)):
        sheet.cell(row=row_num, column=col_id).value = str(value)
    else:
        print(f"Error: Invalid column identifier type. Expected int or str, but got {type(col_id)}")
        return
    workbook.save(OUTPUT_FILE_PATH)
    print(f"Successfully wrote '{str(value)}' to cell {sheet.cell(row=row_num, column=col_id).coordinate} in {OUTPUT_FILE_PATH}")

except FileNotFoundError:
    print(f"Error: File not found at {OUTPUT_FILE_PATH}")
except Exception as e:
    print(f"An error occurred while writing to the specific cell: {e}")

def extract_model_variable(str_value):
if str_value is None:
return

str_value = str(str_value)

extracted_values = re.findall(r'.*?~?=.*', str_value)
cleaned_values = []
for value in extracted_values:
    cleaned_value = re.sub(r'^\s*\d+\.\s*', '', value).strip()
    cleaned_values.append(cleaned_value)

for value in cleaned_values:
    split_variable_value(value)

try:
workbook = openpyxl.load_workbook(file_path)
sheet = workbook["SCU_TESTCASES"]

print(f"Reading data from sheet: {sheet.title}")

for row in sheet.iter_rows(min_row=2, min_col=1):
    for cell in row:
        colName = ""
        if cell.value is not None:
            if cell.column == 2:
                print("Test case No : ", cell.value)
                lastCol = get_last_used_column(sheet)+1
                print("Col number after Test case No : ",lastCol)
                write_text_to_excel_cell(lastCol,cell.column,cell.value)
                print("cell_row : ",cell.row, "    cell_col : ",cell.column,"    cell_value : ","")

            elif cell.column == 7:
                print("precondition")
                colName = "precondition"
                lastCol = get_last_used_column(sheet)+1
                print("Col number after PreCondition : ", lastCol)
                write_text_to_excel_cell(lastCol, 3, cell.value)
                # extract_model_variable(cell.value)
                print("cell_row : ", cell.row, "    cell_col : ", cell.column, "    cell_value : ", "")


            elif cell.column == 8:
                print("Test steps")
                colName = "Test steps"
                lastCol = get_last_used_column(sheet) + 1
                print("Col number after PreCondition : ", lastCol)
                write_text_to_excel_cell(cell.row, 3, colName)
                # extract_model_variable(cell.value)
                print("cell_row : ", cell.row, "    cell_col : ", cell.column, "    cell_value : ", "")


            elif cell.column == 9:
                print("Expected Result")
                colName = "Expected Result"
                lastCol = get_last_used_column(sheet) + 1
                print("Col number after PreCondition : ", lastCol)
                write_text_to_excel_cell(cell.row,3 , colName)
                # extract_model_variable(cell.value)
                print("cell_row : ", cell.row, "    cell_col : ", cell.column, "    cell_value : ", "")


            elif cell.column == 11:
                print("Post Condition")
                colName = "Post Condition"
                lastCol = get_last_used_column(sheet) + 1
                print("Col number after PreCondition : ", lastCol)
                write_text_to_excel_cell(cell.row, 3, colName)
                # extract_model_variable(cell.value)
                print("cell_row : ", cell.row, "    cell_col : ", cell.column, "    cell_value : ", "")

    print()

except FileNotFoundError:
print(f"Error: File not found at {file_path}")
except Exception as e:
print(f"An error occurred: {e}")

"""