Spreadsheet Data Management

Interacting with test data stored in Excel requires a dedicated handler for reading rows, writing results, and determining the dataset size. The openpyxl library facilitates these operations.

from openpyxl import load_workbook

class ExcelHandler:
    def __init__(self, file_path):
        self.file_path = file_path

    def fetch_case_data(self, sheet_name, row_index):
        workbook = load_workbook(self.file_path)
        sheet = workbook[sheet_name]
        # Extract values from the first 6 columns for the specified row
        case_data = [sheet.cell(row=row_index + 1, column=c).value for c in range(1, 7)]
        return case_data

    def persist_result(self, sheet_name, target_row, target_col, content):
        workbook = load_workbook(self.file_path)
        sheet = workbook[sheet_name]
        sheet.cell(row=target_row, column=target_col).value = content
        workbook.save(self.file_path)

    def get_total_rows(self, sheet_name):
        workbook = load_workbook(self.file_path)
        sheet = workbook[sheet_name]
        return sheet.max_row

API Test Execution Engine

Using the Excel handler, the automation script iterates through specified worksheets, dispatches HTTP requests based on the method defined in the data source, and records the response and test status back into the spreadsheet.

import requests
from excel_handler import ExcelHandler # Assuming the above code is saved as excel_handler.py

SPREADSHEET_FILE = 'test_scenarios.xlsx'
TARGET_SHEETS = ['authentication', 'top_up', 'cash_out']
session_cookies = None

def dispatch_request(http_method, endpoint, payload):
    global session_cookies
    if http_method.lower() == 'get':
        response = requests.get(endpoint, params=payload, cookies=session_cookies)
    else:
        response = requests.post(endpoint, =payload, cookies=session_cookies)
    
    if response.cookies:
        session_cookies = response.cookies
    return response

def run_api_automation():
    xl_handler = ExcelHandler(SPREADSHEET_FILE)
    
    for sheet in TARGET_SHEETS:
        max_rows = xl_handler.get_total_rows(sheet)
        for current_row in range(1, max_rows):
            case_data = xl_handler.fetch_case_data(sheet, current_row)
            # case_data layout: [case_id, description, url, params, method, expected_code]
            case_id, _, url, params_str, method, expected_code = case_data
            
            # Convert string representation of dictionary to actual dict
            payload = eval(params_str) if params_str else {}
            response = dispatch_request(method, url, payload)
            response_ = response.()
            
            xl_handler.persist_result(sheet, current_row + 1, 7, str(response_))
            
            if str(response_.get('code')) == str(expected_code):
                xl_handler.persist_result(sheet, current_row + 1, 8, 'Pass')
            else:
                xl_handler.persist_result(sheet, current_row + 1, 8, 'Fail')

Web UI Automation with CSV and HTMLTestRunner

For UI testing, leveraging unittest alongside CSV data sources provides a robust framework. The HTMLTestRunner package generates comprehensive test reports. When handling CSV files containing non-ASCII characters, utilizing utf-8-sig encoding ensures proper display in spreadsheet applications.

import csv
import unittest
import time
import os
from selenium import webdriver
from selenium.webdriver.common.by import By
import HTMLTestRunner

class LoginValidationTest(unittest.TestCase):
    def setUp(self):
        self.driver = webdriver.Chrome()

    def test_login_process(self):
        driver = self.driver
        csv_file_path = 'test_credentials.csv'
        
        with open(csv_file_path, 'r', encoding='utf-8-sig') as file:
            reader = csv.reader(file)
            for record in reader:
                username, password, test_desc, screenshot_id, error_elem, expected_msg = record
                
                driver.get('https://passport.example.com/user/signin')
                driver.find_element(By.ID, 'input1').clear()
                driver.find_element(By.ID, 'input1').send_keys(username)
                driver.find_element(By.ID, 'input2').clear()
                driver.find_element(By.ID, 'input2').send_keys(password)
                driver.find_element(By.ID, 'signin').click()
                
                try:
                    actual_msg = driver.find_element(By.ID, error_elem).text
                    self.assertEqual(actual_msg, expected_msg, f'Mismatch: Expected {expected_msg}, Got {actual_msg}')
                except Exception as e:
                    driver.save_screenshot(f'{screenshot_id}.png')
                    self.fail(f'Element retrieval failed or assertion error: {str(e)}')

    def tearDown(self):
        self.driver.refresh()
        self.driver.quit()

if __name__ == '__main__':
    suite = unittest.TestSuite()
    suite.addTest(LoginValidationTest('test_login_process'))
    
    timestamp = time.strftime('%Y%m%d%H%M%S')
    report_directory = f'reports/{timestamp}'
    os.makedirs(report_directory, exist_ok=True)
    
    report_path = os.path.join(report_directory, 'test_report.html')
    with open(report_path, 'wb') as report_file:
        runner = HTMLTestRunner.HTMLTestRunner(
            stream=report_file,
            title='Login Module Test Report',
            description='Detailed results for login validation scenarios'
        )
        runner.run(suite)