# Excel Visual Component Guide

**Last Updated:** 2025-01-27

## Overview

The Excel Visual Component (`v2/components/excel-visual.php`) is a modular, template-type-aware system that automatically extracts and visualizes data from any template JSON definition in the Excel template generator system. It supports multiple data structures (table, key-value, list) and applies intelligent formatting based on template category.

## Architecture

```
Template Page
    ↓
template-page-config.php (load_excel_visual_data)
    ↓
excel-visual-extractor.php (extract_visual_data_from_template)
    ↓
excel-visual-strategies.php (get_visual_strategy_for_template)
    ↓
excel-visual.php (renders visual)
```

## Key Components

### 1. Visual Data Extractor (`v2/config/excel-visual-extractor.php`)

**Functions:**

- `extract_visual_data_from_template()` - Main extraction function
- `find_best_sheet_for_visual()` - Smart sheet selection
- `detect_data_structure_type()` - Detects table/key-value/list structures
- `extract_table_structure()` - Extracts table data
- `extract_key_value_structure()` - Extracts key-value pairs
- `extract_list_structure()` - Extracts single-column lists
- `sample_rows_intelligently()` - Smart row sampling for large datasets

**Sheet Selection Priority:**

1. `data_entry` - Highest priority
2. `overview`
3. `calculation`
4. `report`
5. `instructions` - Lowest priority

### 2. Visual Strategies (`v2/config/excel-visual-strategies.php`)

**Template Categories Supported:**

- `shift_planning` - Table layout with time formatting
- `time_tracking` - Table layout with time/numeric formatting
- `payroll` - Table layout with currency formatting
- `employee_management` - Key-value or table layouts
- `compliance` - List or table layouts
- `other` - Generic fallback

**Visual Strategy Functions:**

- `get_visual_strategy_for_template()` - Returns strategy for template
- `apply_cell_formatting()` - Applies formatting rules to cells
- Strategy-specific functions (e.g., `get_table_strategy_shift_planning()`)

### 3. Visual Component (`v2/components/excel-visual.php`)

**Features:**

- Template-type-aware rendering
- Dynamic column/row generation
- Configurable visual strategies
- Fade effect (configurable start percentage)
- Responsive design
- Accessibility support

## Usage

### Basic Usage

```php
<?php
require_once __DIR__ . '/../config/template-page-config.php';

$templateId = 'dienstplan-excel-vorlage';
$visualData = load_excel_visual_data($templateId);

// Include the visual component
include __DIR__ . '/../components/excel-visual.php';
?>
```

### Custom Visual Data

```php
<?php
$visualData = [
    'sheet_name' => 'My Sheet',
    'headers' => ['A' => 'Column 1', 'B' => 'Column 2'],
    'rows' => [
        ['Value 1', 'Value 2'],
        ['Value 3', 'Value 4']
    ],
    'template_type' => 'shift_planning',
    'data_structure' => 'table',
    'visual_strategy' => get_visual_strategy_for_template('shift_planning', 'table')
];

include __DIR__ . '/../components/excel-visual.php';
?>
```

## Data Structures

### Table Structure

Standard Excel-like grid with headers and rows:

```php
[
    'data_structure' => 'table',
    'headers' => ['A' => 'Name', 'B' => 'Value'],
    'rows' => [
        ['Item 1', '100'],
        ['Item 2', '200']
    ]
]
```

### Key-Value Structure

Label-value pairs (common in forms):

```php
[
    'data_structure' => 'key_value',
    'headers' => ['Label' => 'Feld', 'Value' => 'Wert'],
    'rows' => [
        ['Name:', 'John Doe'],
        ['Email:', 'john@example.com']
    ]
]
```

### List Structure

Single-column data (common in checklists):

```php
[
    'data_structure' => 'list',
    'headers' => ['A' => 'Eintrag'],
    'rows' => [
        ['Task 1'],
        ['Task 2'],
        ['Task 3']
    ]
]
```

## Visual Strategies

### Shift Planning Strategy

- **Layout:** Table
- **Formatting:**
  - First column: Bold, left-aligned (employee names)
  - Time values: Center-aligned, darker color
- **Max Rows:** 8
- **Fade Start:** 60%

### Time Tracking Strategy

- **Layout:** Table
- **Formatting:**
  - First column: Bold, left-aligned
  - Time values: Center-aligned
  - Numeric values: Right-aligned
- **Max Rows:** 8
- **Fade Start:** 60%

### Payroll Strategy

- **Layout:** Table
- **Formatting:**
  - First column: Bold, left-aligned
  - Currency values: Right-aligned, green color
  - Numeric values: Right-aligned
- **Max Rows:** 8
- **Fade Start:** 60%

### Employee Management Strategy

- **Layout:** Key-value or table
- **Formatting:**
  - Label column: Bold, left-aligned
  - Value column: Left-aligned
- **Max Rows:** 10
- **Fade Start:** 70%

### Compliance Strategy

- **Layout:** List or table
- **Formatting:**
  - First column: Bold (if table)
  - Status values: Center-aligned, green color
- **Max Rows:** 10-12
- **Fade Start:** 70%

## Customization

### Template-Specific Overrides

Add overrides in `apply_template_visual_overrides()` function:

```php
$overrides = [
    'my-template-id' => [
        'max_rows' => 10,
        'fade_start' => 65,
        'highlight_first_column' => true
    ]
];
```

### Custom Formatting Rules

Create custom formatters in visual strategies:

```php
'cell_formatters' => [
    'custom_pattern' => [
        'alignment' => 'center',
        'detect_pattern' => '/^CUSTOM$/',
        'color' => '#059669',
        'font_weight' => 'bold'
    ]
]
```

## Performance

**Extraction Performance:**

- Average: ~0.3ms per template
- Min: ~0.2ms
- Max: ~0.5ms
- Total for 28 templates: ~7-11ms

**Optimization:**

- Intelligent row sampling for large datasets
- Efficient data structure detection
- Caching-friendly design

## Testing

### Run Extraction Tests

```bash
php v2/systems/excel-template-generator/scripts/test-visual-extraction.php
```

### Test Results Location

`v2/systems/excel-template-generator/reports/visual-extraction-test-results.json`

## Troubleshooting

### No Data Extracted

**Symptoms:** Empty visual or default fallback

**Solutions:**

1. Check template definition file exists
2. Verify sheet has `data_entry` type
3. Check cells have valid addresses
4. Review error logs for extraction errors

### Incorrect Data Structure Detection

**Symptoms:** Wrong layout (e.g., table shown as list)

**Solutions:**

1. Verify sheet structure matches expected pattern
2. Check first row for header indicators
3. Review `detect_data_structure_type()` logic
4. Add manual override if needed

### Formatting Not Applied

**Symptoms:** Cells don't match expected formatting

**Solutions:**

1. Verify visual strategy is loaded
2. Check cell formatter patterns match data
3. Review `apply_cell_formatting()` function
4. Check for CSS conflicts

### Performance Issues

**Symptoms:** Slow page load

**Solutions:**

1. Check extraction time in test results
2. Review row sampling settings
3. Optimize template definition (reduce cells)
4. Consider caching visual data

## Best Practices

1. **Always provide fallback data** - Use `get_default_visual_data()` if extraction fails
2. **Test with multiple templates** - Verify extraction works across categories
3. **Use appropriate strategies** - Match strategy to template category
4. **Optimize row counts** - Use sampling for large datasets
5. **Handle errors gracefully** - Log errors but show fallback visual
6. **Document customizations** - Note any template-specific overrides

## Examples

### Example 1: Dienstplan Template

```php
$templateId = 'dienstplan-excel-vorlage';
$visualData = load_excel_visual_data($templateId);
// Automatically extracts table structure with time formatting
```

### Example 2: Employee Onboarding Template

```php
$templateId = 'employee-onboarding';
$visualData = load_excel_visual_data($templateId);
// Automatically extracts key-value structure
```

### Example 3: Compliance Checklist Template

```php
$templateId = 'compliance-audit';
$visualData = load_excel_visual_data($templateId);
// Automatically extracts list structure
```

## Related Documentation

- [Template Page Guide](./TEMPLATE_PAGE_GUIDE.md) - Creating template pages
- [Template Definition Guide](systems/excel-template-generator/docs/TEMPLATE_DEFINITION_GUIDE.md) - Template JSON structure
- [Visualization Guide](systems/excel-template-generator/docs/guides/VISUALIZATION_GUIDE.md) - Chart visualization

## Support

For issues or questions:

1. Check error logs: `error_log()` output
2. Review test results: `visual-extraction-test-results.json`
3. Check template analysis: `visual-patterns-analysis.json`
4. Contact: Hady Elhady (`hady@ordio.com`)