This test automation framework leverages Python's Unittest, data-driven testing (DDT), Excel, Jinja2, and HTML technologies to automatically generate polished visual test reports.

Core Workflow

1. Encapsulate Excel data reading logic, allowing all test data to be configured directly in Excel without hardcoding in test scripts
2. Use Unittest's discover() method to locate eligible test cases, then execute them and generate fresh test reports via a custom HTML reporting module
3. Extend Unittest's TestResult class to add custom data collection logic, then use Jinja2 template engine to render structured HTML reports
4. Pass the generated report path and execution metrics to notification modules to automatically send results via email, DingTalk, or WeChat Work

1. Data-Driven Testing Decorators

Custom decorators similar to standard DDT libraries are implemented to generate test function names compatible with Unittest framework requirements. Example code:

from typing import List, Dict

def data_parametrize(test_data: List[Dict]):
    """
    Attach test data set to the target test function
    :param test_data: Collection of test case parameters
    :return: Decorated test function
    """
    def decorator(test_func):
        setattr(test_func, "PARAMS", test_data)
        return test_func
    return decorator


def load_yaml_test_data(yaml_file: str):
    """
    Load and attach test data from a YAML configuration file
    :param yaml_file: Path to the YAML test data file
    :return: Decorated test function
    """
    def decorator(test_func):
        try:
            with open(yaml_file, "r", encoding="utf-8") as f:
                loaded_data = __import__("yaml").safe_load(f)
        except (UnicodeDecodeError, FileNotFoundError):
            with open(yaml_file, "r", encoding="gbk") as f:
                loaded_data = __import__("yaml").safe_load(f)
        setattr(test_func, "PARAMS", loaded_data)
        return test_func
    return decorator

2. Unittest Framework Integration

After applying the custom decorators, inheritance, encapsulation, and reflection patterns, the final test execution script is only ~10 lines of core code:

import unittest
from your_project.config import Config
from your_project.utils import DoExcel, Action

# Configuration and test data setup
test_excel_path = Config.TEST_CASE_PATH
excel_handler = DoExcel(test_excel_path)

test_cases, db_config, init_data, base_host = excel_handler.get_excel_init_and_cases()


class TestApiAutomationSuite(unittest.TestCase):
    maxDiff = None
    test_operator = Action(init_data, db_config)

    @classmethod
    def setUpClass(cls) -> None:
        cls.test_operator.load_modules_from_folder(Config.EXTENSIONS_DIR)

    @data_parametrize(test_cases)
    def test_run_api_case(self, test_item):
        sheet_name, case_id, run_flag, wait_time, case_name, case_desc, req_method, expect_result = self.test_operator.base_info(test_item)
        
        # Skip test case if run flag is disabled
        if self.test_operator.should_skip(run_flag):
            self.skipTest(f"Test case {case_name} marked as skipped")
        
        # Extract test data and dependencies
        extract_regex, extract_keys, dep_list, json_path_dict, request_data = self.test_operator.extractor_info(test_item)
        self.test_operator.pause_execution(wait_time)
        
        # Execute pre-test SQL queries
        self.test_operator.execute_sql(test_item)
        
        # Skip if only SQL execution is configured
        if self.test_operator.is_sql_only_mode(req_method):
            self.skipTest(f"Test case {case_name} configured for SQL only execution")

        # Send API request and validate response
        self.test_operator.send_api_request(base_host, req_method, request_data)
        self.test_operator.analyze_response(sheet_name, case_id, case_name, case_desc, extract_regex, extract_keys, dep_list, json_path_dict)
        self.test_operator.validate_results(excel_handler, sheet_name, case_id, case_name, case_desc, expect_result)

    @classmethod
    def tearDownClass(cls) -> None:
        excel_handler.close_excel()

3. Excel Test Case Management

All test case data is managed directly in Excel, enabling easy review and filtering for stakeholders. A sample Excel data reading implementation:

import openpyxl
from your_project.constants import FieldNames

class ExcelTestDataHandler:
    def get_test_case_rows(self):
        sheet_names = eval(self.get_excel_init_config().get(FieldNames.SHEETS))
        for sheet_name in sheet_names:
            current_sheet = self.wb[sheet_name]
            max_row = current_sheet.max_row
            max_col = current_sheet.max_column
            
            # Extract header row
            header_row = []
            for col_idx in range(1, max_col +1):
                header_row.append(current_sheet.cell(1, col_idx).value)
            
            # Extract each test case row
            for row_idx in range(2, max_row +1):
                case_data = {}
                for col_idx in range(1, max_col +1):
                    case_data[header_row[col_idx -1]] = current_sheet.cell(row_idx, col_idx).value
                case_data[FieldNames.SHEET_NAME] = sheet_name
                yield case_data

4. Structured Test Logging

Detailed logging is implemented to capture test execution details, including timestamps, test case IDs, request/response data, and validation results, simplifying debugging and result verification.

5. Polished HTML Test Report Generation

The core reporting logic overrides the default UnittestReport implementation to generate visually appealing HTML reports instead of plain text output. Each test case displays complete details including input data, expected results, actual results, and execution duration for full visibility into the test workflow.

Report Style Options

1. Style 1: Clean, minimal layout focused on test case details
2. Style 2: Compact dashboard view with summary statistics and detailed breakdowns

Additionally, test case results are automatically synced back to the source Excel file for permanent record-keeping.

6. Team Notification Integration

After test execution completes, the generated report and execution metrics can be passed to dedicated notification modules to send updates via:

• SMTP email with report attachment
• DingTalk group bot messages
• WeChat Work bot notifications

This ensures all team members receive real-time updates on test progress and results.