# Feature Examples Guide

**Last Updated:** 2025-11-20

This guide provides comprehensive examples for each feature of the Excel Template Generator system.

## Table of Contents

1. [Branding Examples](#branding-examples)
2. [Formula Examples](#formula-examples)
3. [Component Examples](#component-examples)
4. [Chart Examples](#chart-examples)
5. [Pattern Detection Examples](#pattern-detection-examples)
6. [Competitive Analysis Examples](#competitive-analysis-examples)
7. [Quality Gates Examples](#quality-gates-examples)

## Branding Examples

### Example 1: Using Style Presets

```json
{
  "cells": [
    {
      "address": "A1",
      "value": "Mitarbeiter",
      "style": { "preset": "header" },
      "data_type": "text"
    },
    {
      "address": "A2",
      "value": "Max Mustermann",
      "style": { "preset": "data_cell" },
      "data_type": "text"
    },
    {
      "address": "B10",
      "value": "=SUMME(B2:B9)",
      "style": { "preset": "formula_cell" },
      "data_type": "formula"
    },
    {
      "address": "A10",
      "value": "Gesamt",
      "style": { "preset": "total_row" },
      "data_type": "text"
    }
  ]
}
```

### Example 2: Status Indicators

```json
{
  "cells": [
    {
      "address": "C2",
      "value": "OK",
      "style": { "preset": "success_cell" },
      "data_type": "text"
    },
    {
      "address": "C3",
      "value": "Warnung",
      "style": { "preset": "warning_cell" },
      "data_type": "text"
    },
    {
      "address": "C4",
      "value": "Fehler",
      "style": { "preset": "error_cell" },
      "data_type": "text"
    }
  ]
}
```

### Example 3: Branding Validation

```bash
# Validate branding
php v2/systems/excel-template-generator/scripts/validate-branding.php template.json

# Auto-fix branding violations
php v2/systems/excel-template-generator/scripts/branding-auto-fixer.php template.json --apply
```

**Expected Output:**

- Score: 90+/100
- No errors
- Warnings for non-standard font sizes (if any)

## Formula Examples

### Example 1: Pattern-Based Formula Recommendations

```php
<?php
require_once 'v2/systems/excel-template-generator/helpers/formula-pattern-detector.php';

$detector = new OrdioFormulaPatternDetector();
$patterns = $detector->detectPatterns($template);
$recommendations = $detector->getRecommendationsForPatterns($template);

// Patterns detected:
// - time_series: Dates + values
// - categorical: Categories + counts
// - numerical: Range of numbers

// Recommendations include:
// - Time series → SUM, AVERAGE, date calculations
// - Categorical → COUNT, LOOKUP, SUMIF
// - Numerical → SUM, AVERAGE, error handling
```

### Example 2: Competitive Formula Recommendations

```php
<?php
require_once 'v2/systems/excel-template-generator/helpers/formula-recommender.php';

$recommender = new OrdioFormulaRecommender();
$competitiveRecs = $recommender->getCompetitiveRecommendations('shift_planning', true);

// Returns:
// - Formulas used by 50%+ competitors
// - Gap formulas (competitors use, we don't have)
// - Usage percentages
```

### Example 3: Formula with Error Handling

```json
{
  "formula": "=WENNFEHLER(SUMME(B2:B20);0)",
  "description": "Sum with error handling",
  "cell": "B21"
}
```

## Component Examples

### Example 1: Using Compliance Component

```json
{
  "components": [
    {
      "id": "compliance-arbzg-check",
      "parameters": {
        "hours_range": "B2:B20",
        "result_cell": "C2"
      }
    }
  ]
}
```

### Example 2: Using Calculation Component

```json
{
  "components": [
    {
      "id": "payroll-calculation-block",
      "parameters": {
        "base_salary_range": "B2:B20",
        "hours_range": "C2:C20",
        "hourly_rate_range": "D2:D20",
        "result_range": "E2:E20"
      }
    }
  ]
}
```

### Example 3: Using Chart Component

```json
{
  "components": [
    {
      "id": "chart-bar-template",
      "parameters": {
        "data_range": "A1:B10",
        "chart_title": "Mitarbeiter Übersicht",
        "position_cell": "D2"
      }
    }
  ]
}
```

## Chart Examples

### Example 1: Chart Selection Based on Pattern

```php
<?php
require_once 'v2/systems/excel-template-generator/helpers/chart-selector.php';

$selector = new OrdioChartSelector();
$recommendations = $selector->recommendCharts($template);

// Returns recommendations based on:
// - Detected data patterns
// - Use case
// - Data characteristics
```

### Example 2: Chart Definition

```json
{
  "charts": [
    {
      "type": "bar",
      "title": "Arbeitsstunden Vergleich",
      "data_range": "A1:B10",
      "position": {
        "cell": "D2",
        "width": 8,
        "height": 6
      },
      "color_palette": "professional",
      "style": {
        "show_legend": true,
        "show_data_labels": false
      }
    }
  ]
}
```

## Pattern Detection Examples

### Example 1: Time Series Detection

```json
{
  "cells": [
    { "address": "A2", "value": "01.01.2025", "data_type": "date" },
    { "address": "A3", "value": "02.01.2025", "data_type": "date" },
    { "address": "B2", "value": "8", "data_type": "number" },
    { "address": "B3", "value": "7.5", "data_type": "number" }
  ]
}
```

**Detected Pattern:** `time_series`
**Recommended Formulas:** SUM, AVERAGE, date difference calculations
**Recommended Charts:** Line chart

### Example 2: Categorical Detection

```json
{
  "cells": [
    { "address": "A2", "value": "Abteilung A", "data_type": "text" },
    { "address": "A3", "value": "Abteilung B", "data_type": "text" },
    { "address": "B2", "value": "15", "data_type": "number" },
    { "address": "B3", "value": "12", "data_type": "number" }
  ]
}
```

**Detected Pattern:** `categorical`
**Recommended Formulas:** COUNT, SUMIF, LOOKUP
**Recommended Charts:** Bar chart or pie chart (if ≤5 categories)

## Competitive Analysis Examples

### Example 1: Running Competitive Analysis

```bash
# Search for templates
php v2/systems/excel-template-generator/scripts/competitive-analysis-automation.php \
  --category=shift_planning \
  --max=10 \
  --download

# Analyze downloaded templates
php v2/systems/excel-template-generator/scripts/template-research-analyzer.php \
  shift_planning \
  --gap-analysis \
  --best-practices

# Benchmark against competitors
php v2/systems/excel-template-generator/scripts/competitive-benchmarking.php \
  --ordio-template=template.json \
  --competitor-data=competitive-analysis.json
```

### Example 2: Using Competitive Insights

```php
<?php
require_once 'v2/systems/excel-template-generator/helpers/formula-recommender.php';

$recommender = new OrdioFormulaRecommender();
$recommendations = $recommender->getRecommendationsForUseCase('shift_planning');

// Includes:
// - competitive_insights: Analysis of competitor formulas
// - competitive_formulas: Formulas used by competitors
// - competitive_summary: Summary statistics
```

## Quality Gates Examples

### Example 1: Running Quality Gates

```bash
php v2/systems/excel-template-generator/scripts/template-quality-gates.php template.json --strict
```

**Output:**

```
=== QUALITY GATES ===

✓ Schema Validation              : PASSED
✓ Branding Compliance            : PASSED (Score: 95/100)
✓ Formula Quality               : PASSED (Score: 88/100)
✓ Visualization Quality          : PASSED (Score: 90/100)
✓ Accessibility                 : PASSED (Score: 85/100)
✓ Performance                   : PASSED (Score: 92/100)
✓ Usability                     : PASSED (Score: 88/100)
✓ Minimum Score                 : PASSED (Score: 85%)

=== SUMMARY ===
Total Gates: 8
Passed: 8
Failed: 0

✓ ALL QUALITY GATES PASSED
```

### Example 2: Quality Gate Failure

If a gate fails:

```
✗ Branding Compliance            : FAILED (Score: 75/100)
  ✗ Invalid color used: #FF0000. Must use Ordio brand colors only.
  ⚠ High percentage of custom styles (35%). Consider using presets.

Fix: Run branding-auto-fixer.php --apply
```

## Complete Workflow Example

### Example: Generating a Shift Planning Template

```bash
# Step 1: Research
php v2/systems/excel-template-generator/scripts/competitive-analysis-automation.php \
  --category=shift_planning \
  --max=5

# Step 2: Generate template (using workflow)
php v2/systems/excel-template-generator/scripts/generate-template-workflow.php \
  --template=shift-planning.json \
  --verbose

# Step 3: Test comprehensively
php v2/scripts/test-template-suite.php \
  --template=shift-planning.json \
  --verbose

# Step 4: Review quality gates
php v2/systems/excel-template-generator/scripts/template-quality-gates.php \
  shift-planning.json \
  --strict
```

## Best Practices Examples

### Example 1: Complete Template with All Features

See `v2/systems/excel-template-generator/data/template-definitions/examples/comprehensive-example.json` for a complete example demonstrating:

- Template inheritance
- Component usage
- Parameter definitions
- Pattern-based formulas
- Chart recommendations
- Branding presets
- Data validation
- Conditional formatting

### Example 2: Minimal Template

```json
{
  "metadata": {
    "name": "Simple Template",
    "category": "other",
    "version": "1.0.0"
  },
  "sheets": [
    {
      "name": "Daten",
      "cells": [
        {
          "address": "A1",
          "value": "Name",
          "style": { "preset": "header" }
        }
      ]
    }
  ]
}
```

## Troubleshooting Examples

### Example 1: Branding Violations

**Problem:** Branding score below 90%

**Solution:**

```bash
php v2/systems/excel-template-generator/scripts/branding-auto-fixer.php template.json --apply
```

### Example 2: Formula Quality Issues

**Problem:** Formulas missing error handling

**Solution:**

- Use `OrdioFormulaRecommender::wrapInErrorHandling()`
- Or manually wrap: `=WENNFEHLER(original_formula;0)`

### Example 3: Missing Charts

**Problem:** Template needs charts but none defined

**Solution:**

```php
$selector = new OrdioChartSelector();
$recommendations = $selector->recommendCharts($template);
// Use recommendations to add charts
```

## Reference

- **Complete Example:** `v2/systems/excel-template-generator/data/template-definitions/examples/comprehensive-example.json`
- **Component Library:** `v2/systems/excel-template-generator/data/template-components/README.md`
- **Formula Library:** `v2/systems/excel-template-generator/data/template-formulas/hr-formulas.json`
- **Workflow Guide:** `docs/systems/excel-generator/AI_AGENT_WORKFLOW.md`