data-comparison/DYNAMIC_SHEET_FIX.md

Dynamic Sheet Handling Fix

Problem

The application was failing with "Sheet 'US URGENT' not found in data" error when working with Excel files that don't contain a sheet named 'US URGENT'. The code needed to be made dynamic to work with any sheet names.

Root Cause

The issue was in the web interface JavaScript where:

1. On page load, analyzeData() was called immediately
2. The sheet filter dropdown was empty at that time
3. An empty string was being sent as sheet_filter to the backend
4. The backend wasn't properly handling empty strings

Fixes Applied

1. Frontend JavaScript Improvements (web_gui.py)

Fixed analyzeData() function:

// Before: const sheetFilter = document.getElementById('sheetFilter').value;
// After: 
const sheetFilterElement = document.getElementById('sheetFilter');
const sheetFilter = sheetFilterElement.value || null; // Use null if empty

Improved updateSheetFilter() function:

• Added handling for empty sheet lists
• Added loading state for sheet dropdown
• Better initialization logic

Enhanced page load handling:

window.onload = function() {
    // Initialize sheet filter with loading state
    updateSheetFilter([], null);
    analyzeData();
};

2. Backend Improvements (web_gui.py)

Enhanced request handling:

# Handle empty string as None
if sheet_filter == '' or sheet_filter == 'undefined':
    sheet_filter = None

Added debugging information:

# Debug: Print available sheets
available_sheets = list(comparator_instance.data.keys())
print(f"Available sheets: {available_sheets}")
print(f"Requested sheet_filter: {repr(sheet_filter)}")

3. Core Logic Improvements (data_comparator.py)

Enhanced error handling:

# Validate that the requested sheet exists
if sheet_filter not in sheet_names:
    raise ValueError(f"Sheet '{sheet_filter}' not found in data. Available sheets: {sheet_names}")

How It Works Now

1. Page Load:

   • Sheet dropdown shows "Loading sheets..."
   • analyzeData() is called with sheet_filter = null

2. Backend Processing:

   • Loads Excel file and gets available sheet names
   • If sheet_filter is null or empty, defaults to first sheet
   • Validates sheet exists before processing

3. Response:

   • Returns results with sheet_names array and current_sheet_filter
   • Frontend populates dropdown with actual sheet names
   • User can then select different sheets

4. Sheet Selection:

   • User selects different sheet from dropdown
   • filterBySheet() calls analyzeData() with selected sheet
   • Backend processes the specific sheet

Benefits

✅ Dynamic: Works with any Excel file regardless of sheet names ✅ Robust: Handles empty/invalid sheet names gracefully
✅ User-friendly: Shows available sheets in dropdown ✅ Error-resistant: Provides clear error messages ✅ Backward compatible: Still works with existing functionality

Test Coverage

Created test_dynamic_sheets.py to verify:

• Default sheet selection (no filter)
• Each available sheet works
• Invalid sheet names are handled
• Empty string sheet names default properly

Usage Examples

# Works with any Excel file
comparator = KSTCoordiComparator("any-file.xlsx")
comparator.load_data()

# Auto-selects first sheet
summary = comparator.get_comparison_summary()

# Or specify any existing sheet
summary = comparator.get_comparison_summary("Sheet1")
summary = comparator.get_comparison_summary("Data")
summary = comparator.get_comparison_summary("US URGENT")  # Only if it exists

The application now dynamically adapts to any Excel file structure!