doc/scripts/modules/excel_handler.py

import os
import openpyxl
from openpyxl.styles import Font, Alignment, PatternFill


def create_workbook():
    """
    Create Excel workbook and set basic formatting
    
    Returns:
        tuple: (workbook object, worksheet object)
    """
    wb = openpyxl.Workbook()
    ws = wb.active
    ws.title = "Docs Structure Comparison"
    
    # Set headers
    headers = ["File Path", "File Extension", "Top Level Directory", "Source Version Exists", "Target Version Exists", 
               "Status", "Source Version File Size(KB)", "Target Version File Size(KB)", 
               "Markdown Syntax Consistency", "Formula"]
    
    for col_num, header in enumerate(headers, 1):
        cell = ws.cell(row=1, column=col_num)
        cell.value = header
        cell.font = Font(bold=True)
        cell.alignment = Alignment(horizontal="center")
    
    return wb, ws


def get_status_fills():
    """
    Get fill colors for different statuses
    
    Returns:
        dict: Dictionary containing fill colors for different statuses
    """
    return {
        'consistent': PatternFill(start_color='FFC6EFCE', end_color='FFC6EFCE', fill_type='solid'),  # Green
        'source_only': PatternFill(start_color='FFFFC7CE', end_color='FFFFC7CE', fill_type='solid'),  # Red
        'target_only': PatternFill(start_color='FFFFFF00', end_color='FFFFFF00', fill_type='solid')   # Yellow
    }


def write_file_info(ws, row_num, file_path, file_info, source_exists, target_exists, status_fills):
    """
    Write file information to worksheet
    
    Parameters:
        ws: Worksheet object
        row_num: Row number
        file_path: File path
        file_info: File information dictionary
        source_exists: Whether source version exists
        target_exists: Whether target version exists
        status_fills: Status fill color dictionary
        
    Returns:
        str: File status
    """
    # Determine status
    if source_exists and target_exists:
        status = "Consistent"
        status_fill = status_fills['consistent']
    elif source_exists and not target_exists:
        status = "Source Only"
        status_fill = status_fills['source_only']
    elif not source_exists and target_exists:
        status = "Target Only"
        status_fill = status_fills['target_only']
    else:
        status = "Does Not Exist"
        status_fill = None
    
    # Write basic information
    ws.cell(row=row_num, column=1, value=file_path)
    ws.cell(row=row_num, column=2, value=file_info['extension'])
    ws.cell(row=row_num, column=3, value=file_info['top_dir'])
    ws.cell(row=row_num, column=4, value="Yes" if source_exists else "No")
    ws.cell(row=row_num, column=5, value="Yes" if target_exists else "No")
    
    # Set status cell
    status_cell = ws.cell(row=row_num, column=6, value=status)
    if status_fill:
        status_cell.fill = status_fill
    
    return status


def write_file_sizes(ws, row_num, source_file_info, target_file_info):
    """
    Write file size information
    
    Parameters:
        ws: Worksheet object
        row_num: Row number
        source_file_info: Source version file information
        target_file_info: Target version file information
    """
    ws.cell(row=row_num, column=7, value=source_file_info.get('size', ''))
    ws.cell(row=row_num, column=8, value=target_file_info.get('size', ''))


def write_markdown_consistency(ws, row_num, consistency_result):
    """
    Write Markdown syntax consistency results
    
    Parameters:
        ws: Worksheet object
        row_num: Row number
        consistency_result: Consistency check result
    """
    ws.cell(row=row_num, column=9, value=consistency_result)


def write_formula_text(ws, row_num, file_path, source_dir=None, target_dir=None):
    """
    Write formula column
    
    Parameters:
        ws: Worksheet object
        row_num: Row number
        file_path: File path
        source_dir: Source directory path (optional)
        target_dir: Target directory path (optional)
    """
    # Use provided directory names or default to generic names
    source_dir_name = source_dir if source_dir else "source"
    target_dir_name = target_dir if target_dir else "target"
    
    # Generate formula text based on file path
    formula_text = f"Compare {source_dir_name}\\{file_path} and {target_dir_name}\\{file_path} paragraph by paragraph with each heading as a paragraph, to ensure the target version is a complete and accurate translation of the source version."
    ws.cell(row=row_num, column=10, value=formula_text)


def adjust_column_widths(ws):
    """
    Adjust worksheet column widths
    
    Parameters:
        ws: Worksheet object
    """
    ws.column_dimensions['A'].width = 50  # File Path
    ws.column_dimensions['B'].width = 10  # File Extension
    ws.column_dimensions['C'].width = 15  # Top Level Directory
    ws.column_dimensions['D'].width = 15  # Source Version Exists
    ws.column_dimensions['E'].width = 15  # Target Version Exists
    ws.column_dimensions['F'].width = 20  # Status
    ws.column_dimensions['G'].width = 20  # Source Version File Size
    ws.column_dimensions['H'].width = 20  # Target Version File Size
    ws.column_dimensions['I'].width = 50  # Markdown Syntax Consistency
    ws.column_dimensions['J'].width = 100 # Formula


def save_workbook(wb, output_file):
    """
    Save workbook to file
    
    Parameters:
        wb: Workbook object
        output_file: Output file path
        
    Returns:
        bool: Whether save was successful
    """
    try:
        print(f"Preparing to save Excel file to: {output_file}")
        
        # Ensure output directory exists
        output_dir = os.path.dirname(output_file)
        if output_dir and not os.path.exists(output_dir):
            os.makedirs(output_dir)
            print(f"Created output directory: {output_dir}")
        
        # Save Excel file
        wb.save(output_file)
        print(f"\nCompleted! File structure comparison results have been saved to: {output_file}")
        
        # Verify if file was successfully created
        if os.path.exists(output_file):
            file_size = os.path.getsize(output_file)
            print(f"Excel file successfully created, file size: {file_size} bytes")
            return True
        else:
            print("Error: Excel file was not successfully created")
            return False
            
    except Exception as e:
        print(f"Error saving Excel file: {str(e)}")
        import traceback
        traceback.print_exc()
        return False


def print_statistics(ws, row_num):
    """
    Print statistics results
    
    Parameters:
        ws: Worksheet object
        row_num: Total number of rows
    """
    print(f"Compared {row_num - 2} files in total")
    
    # Statistics results
    consistent_count = sum(1 for i in range(2, row_num) if ws.cell(row=i, column=6).value == "Consistent")
    source_only_count = sum(1 for i in range(2, row_num) if ws.cell(row=i, column=6).value == "Source Only")
    target_only_count = sum(1 for i in range(2, row_num) if ws.cell(row=i, column=6).value == "Target Only")
    
    print("Statistics results:")
    print(f"- Files existing in both versions: {consistent_count}")
    print(f"- Files existing only in source version: {source_only_count}")
    print(f"- Files existing only in target version: {target_only_count}")