# excel-template-generator-advanced Full Instructions

## Testing

After creating/updating a template:

1. Generate Excel file using template generator
2. Open in Excel Online/Desktop
3. Verify formulas calculate correctly
4. Verify styling matches Ordio branding
5. Verify example rows display properly
6. Test data validation rules
7. Test conditional formatting

## Error Handling

- Always validate template definitions before processing
- Provide user-friendly error messages in German
- Log errors with context for debugging
- Validate cell addresses (format: A1, B2, etc.)
- Validate cell ranges (format: A1:B2)
- Include context in error messages (sheet name, cell address)
- Use actionable error messages that guide users to fix issues
- Validate metadata structure (name, category, version required)
- Validate sheets array is not empty

## Performance Optimization

- Use style caching to avoid recreating styles
- Apply styles to ranges, not individual cells when possible
- Use batch operations for large datasets
- Enable disk caching for large files
- Use streaming for files >5MB
- Limit style cache size to 500 entries if >1000 (keep most recent)
- Trigger garbage collection for templates with >5000 cells
- Track performance metrics (generation time, memory usage, cells processed)

## Keyword-Driven Template Development

### Keyword Analysis

All template development should be informed by keyword analysis:

- **Keyword Analysis Report:** `v2/systems/excel-template-generator/docs/KEYWORD_ANALYSIS_REPORT.md` - Complete analysis with 294 HR keywords, 770K+ searches
- **Prioritization Matrix:** `v2/systems/excel-template-generator/docs/TEMPLATE_PRIORITIZATION_MATRIX.md` - Scoring methodology and priority tiers
- **Opportunity Roadmap:** `v2/systems/excel-template-generator/docs/TEMPLATE_OPPORTUNITY_ROADMAP.md` - 3-phase development roadmap
- **Implementation Guidelines:** `v2/systems/excel-template-generator/docs/TEMPLATE_IMPLEMENTATION_GUIDELINES.md` - SEO, content, compliance guidelines
- **Next Steps:** See implementation guidelines above for immediate action items

### Priority Templates

**Tier 1 (Quick Wins - 0% competition):**

- Wochenplan Vorlage (10,100 searches, 0% competition) ⭐⭐⭐
- Zeiterfassung Excel variants (4,500 searches, 0% competition) ⭐⭐
- Kündigung Arbeitsvertrag Vorlage (7,900 searches, 50% competition) ⭐

**Tier 2 (Strategic - Medium competition):**

- Termination category templates (199,250 total volume, 32.9% competition)
- Vacation Leave templates (16,950 total volume, 34.5% competition)
- Compliance expansion (21,450 total volume, 38.4% competition)

**Tier 3 (Long-term - High competition):**

- Recruitment templates (450,400 total volume, 37.4% competition)
- Training templates (13,100 total volume, 33.5% competition)

### SEO Requirements for Keyword-Driven Templates

When creating templates based on keyword analysis:

- **Primary keyword placement:** H1, first paragraph, URL, filename
- **Long-tail keywords:** Include 3-5 variations naturally in content
- **Meta tags:** Optimized title (50-60 chars) and description (150-160 chars)
- **Content tone:** Use "du" form, natural Ordio mentions (once per section)
- **Internal linking:** Link to related templates and category pages
- **Schema markup:** Product schema for template downloads, FAQ schema

### Template Inventory Management

**Template Inventory System:**

- **Inventory Document:** `v2/systems/excel-template-generator/docs/TEMPLATE_INVENTORY.md` - Complete inventory of all 28 current templates
- **Inventory Database:** `v2/systems/excel-template-generator/data/template-inventory.json` - Structured JSON database
- **Master Database:** `v2/systems/excel-template-generator/data/template-database.json` - Unified database with templates, backlog, and keywords
- **Backlog:** `v2/systems/excel-template-generator/docs/TEMPLATE_BACKLOG.md` - Prioritized backlog of future templates

**Before creating new templates:**

1. **Check Template Inventory:** Verify template doesn't already exist
2. **Check Backlog:** Review prioritized backlog for planned templates
3. **Check Keyword Mapping:** Ensure keywords aren't already assigned
4. **Validate No Cannibalization:** Verify new template won't compete with existing ones

**Backlog Prioritization:**

- **Tier 1 (Score ≥70):** Immediate opportunities, high impact
- **Tier 2 (Score 50-69):** Strategic opportunities, medium-high impact
- **Tier 3 (Score 30-49):** Long-term opportunities, medium impact
- **Tier 4 (Score <30):** Low priority, consider only if resources allow

**Business Context Filtering (CRITICAL):**

All templates must be relevant to Ordio's target audience: **Gastronomie, Einzelhandel, Pflege, Handwerk** (KMU to Enterprise).

**Filtering Requirements:**

1. **Personal Termination Exclusion:** Must NOT be personal termination (checked FIRST)

   - Excludes: wohnung, fitnessstudio, versicherung, handyvertrag, sportverein kündigung
   - Exception: Business termination allowed (arbeitsvertrag, arbeitnehmer)

2. **Ambiguous Keyword Resolution:** Must pass `resolve_ambiguous_keyword()` check

   - Testament: Always excluded
   - Mietvertrag: Excluded unless business context
   - Vollmacht: Requires business context
   - Handout: Allowed only if training context
   - Flyer: Always excluded
   - Visitenkarte: Allowed only if business context

3. **Business Context:** Must pass `has_business_context()` check

   - Contains workplace/business terms (arbeitsplatz, unternehmen, firma, geschäft, betrieb)
   - Contains HR/workforce terms (mitarbeiter, personal, hr, team, schicht, dienstplan, mitarbeitergespräch, personalbogen, anwesenheitsliste, abwesenheitsnotiz)
   - Contains compliance/business terms (compliance, arbeitsrecht, lohnabrechnung, gefährdungsbeurteilung, arbeitsbescheinigung, arbeitszeugnis)
   - Contains industry terms (gastronomie, restaurant, einzelhandel, pflege, handwerk, pflegetagebuch, pflegeplanung, speisekarte, getränkekarte)
   - Contains core Ordio features (schichtplan, dienstplan, zeiterfassung, urlaub, arbeitszeit, urlaubsantrag, urlaubsplanung, arbeitszeiterfassung, stundennachweis, wochenplan)

4. **Minimum Alignment:** Ordio alignment ≥0.5 OR audience match "medium"/"high"

5. **Minimum Audience:** Audience match "medium" OR Ordio alignment ≥0.7

**Personal Termination Keywords (EXCLUDE - Checked FIRST):**

- `kündigung wohnung`, `wohnungskündigung`, `wohnung kündigen`
- `kündigung mietvertrag`, `mietvertrag kündigen`, `mietvertragskündigung`
- `kündigung fitnessstudio`, `fitnessstudio kündigen`, `fitnessstudio kündigung`
- `versicherung kündigen`, `kündigung versicherung`, `versicherungskündigung`
- `handyvertrag kündigen`, `kündigung handyvertrag`
- `sportverein kündigen`, `kündigung sportverein`, `verein kündigen`, `kündigung verein`
- **Exception:** Business termination allowed (`arbeitsvertrag kündigen`, `kündigung arbeitnehmer`)

**Ambiguous Keywords (Context-Aware Resolution):**

- `testament` → Always excluded (personal)
- `mietvertrag` → Excluded unless business context (geschäft, gewerbe, betrieb)
- `vollmacht` → Requires business context (arbeitsplatz, unternehmen, behörde)
- `handout` → Allowed only if training/onboarding context
- `flyer` → Always excluded (consumer marketing)
- `visitenkarte` → Allowed only if business context

**Consumer Keywords (EXCLUDE):**

- Art/craft: pixel, art, tattoo, bügelperlen, kürbis, mandala, basteln, ausmalen, pokemon, kniffel, tippspiel
- Personal/home: wunschzettel, geburtstag, einladung, trauerkarte, stammbaum, save the date, hochzeit
- Children/school: steckbrief, schüler, kinder, schule (unless workplace training context)
- Hobbies: 3d-drucker, 3d druck, 3d vorlagen, edding

**Prioritization Weights:**

- Volume: 20%
- Competition: 15%
- Opportunity: 15%
- **Ordio Alignment: 35%** (most important)
- **Audience Match: 15%**

**See:** `v2/systems/excel-template-generator/docs/FILTERING_GUIDELINES.md` for complete filtering criteria and examples.

**Cannibalization Avoidance:**

- Similarity threshold: >80% similarity = potential cannibalization
- Group similar keywords into single templates
- Use template variants rather than separate templates
- Check validation report for flagged issues

**Keyword Mapping Best Practices:**

- One primary keyword per template (highest volume, best match)
- 3-5 target keywords per template
- Avoid duplicate keyword assignments
- Focus on spreadsheet templates aligned with Ordio products

### Keyword Tracking and Registry

**ALWAYS check keyword registry before creating template pages:**

1. **Check Keyword Registry:** `data/template-keywords.json`

   - Each template has assigned keywords (top/target/relevant/LSI)
   - Use top keywords for H1, URL, filename
   - Use target keywords for H2/H3, meta description
   - Use relevant/LSI keywords for content body

2. **Keyword Classification:**

   - **Top keywords:** Primary keywords (highest volume, exact match) - use in H1, URL, first paragraph
   - **Target keywords:** Secondary keywords (high volume, close match) - use in H2/H3, meta description
   - **Relevant keywords:** Related keywords (moderate volume) - use in content body
   - **LSI keywords:** Semantically related (lower volume) - use for content depth

3. **SEO Optimization Checklist:**

   - [ ] Top keyword in H1 tag
   - [ ] Top keyword in URL/filename
   - [ ] Top keyword in first paragraph (first 100 words)
   - [ ] Top keyword in meta title
   - [ ] 2-3 target keywords in meta description
   - [ ] Target keywords in H2/H3 headings
   - [ ] Relevant keywords in content body (2-3% density)
   - [ ] LSI keywords for content depth
   - [ ] Internal links to related templates

4. **Keyword Data Reference:**
   - Check `keyword_data` in registry for volume, difficulty, intent, CPC
   - Prioritize keywords with lower difficulty (easier to rank)
   - Consider intent: Transactional/Commercial > Informational

**See:** [Keyword Tracking Guide](../../v2/systems/excel-template-generator/docs/guides/KEYWORD_TRACKING_GUIDE.md) for complete SEO optimization workflow.

## Batch Generation

### Generating All Templates

Use the batch generation script to generate all templates:

```bash
php v2/systems/excel-template-generator/scripts/regenerate-all-templates-cli.php
```

**IMPORTANT:** The batch regeneration script automatically:

- ✅ Removes all existing `.xlsx` files from the output folder before regenerating
- ✅ Ensures clean output without old/test files
- ✅ Regenerates all templates fresh

**Options:**

- `--keep-existing` - Skip cleaning output folder (keep existing files)

**Best Practice:** Use CLI generation method (`regenerate-all-templates-cli.php`) for batch generation:

- Direct file output
- Better error handling
- Consistent quality
- No HTTP overhead
- Automatic cleanup of old files

### Output Structure

Generated templates are saved to:

```
v2/systems/excel-template-generator/output/generated-templates/
```

### Quality Thresholds

- **Minimum Quality Score:** 90/100
- **Branding Compliance:** 100% (all presets used correctly)
- **Style Consistency:** 80%+ preset usage
- **Formula Syntax:** All English functions, "=" prefix

### Validation

Always validate generated templates:

```bash
php v2/systems/excel-template-generator/scripts/validate-all-generated.php --min-score=90
```

### Maintenance Scripts

- `generate-all-templates.php` - Batch generation
- `validate-all-generated.php` - Quality validation
- `regenerate-template.php` - Regenerate single template
- `regenerate-category.php` - Regenerate category
- `template-cli.php` - Single template generation CLI

See `v2/systems/excel-template-generator/docs/guides/BATCH_GENERATION_GUIDE.md` for complete workflow.

## Design Standards

### Color Coding

- **Input Cells (Empty):** Light gray background (#F9FAFB), `input_cell_empty` preset
- **Input Cells (Filled):** White background, `input_cell` preset
- **Formula Cells:** Subtle gray background (#F9FAFB), right-aligned, `formula_cell` preset
- **Headers:** Blue background (#4D8EF3), white text, no borders, `header` preset
- **Status Indicators:** Green/yellow/red for OK/warning/error (`compliance_ok`, `compliance_warning`, `compliance_error`)

### Table Formatting

- **Headers:** First row of data entry sheets, `header` preset, no borders
- **Borders:** Thin borders (#D3D8DF) on data cells, applied automatically
- **Column Widths:** 12-20pt based on content type
- **Row Heights:** 15pt standard, 18pt for headers

### Typography

- **Font Mapping:** Inter → Calibri, Gilroy-Bold → Calibri Bold (handled automatically)
- **Font Sizes:** 9-24pt range, 11pt default
- **Alignment:** Left for text, right for numbers/formulas

### Default Style Application

The generator automatically applies default styles:

- **Instruction sheets:** `note_cell` preset to unstyled cells
- **Data entry sheets:** `data_cell` preset to unstyled cells, `header` preset to first row
- **Formula cells:** `formula_cell` preset automatically

### Automatic Table Formatting

Data entry sheets automatically receive:

- Table borders (thin, #D3D8DF)
- Header row formatting
- Proper column widths
- Input cell styling

**See:** `v2/systems/excel-template-generator/docs/guides/TEMPLATE_DESIGN_STANDARDS.md` for complete design guidelines.

### Missing Categories Identified

1. **Recruitment** - 450,400 volume, 0 templates (highest priority long-term)
2. **Termination** - 199,250 volume, 0 templates (high priority)
3. **Vacation Leave** - 16,950 volume, 0 templates (medium priority)
4. **Training** - 13,100 volume, 0 templates (low priority)
5. **Other HR** - 1,750 volume, 0 templates (low priority)

## Chart Generation

**Charts are now fully supported via PhpSpreadsheet Chart API.**

### Chart Definition Format

Charts are defined in JSON template definitions within sheet definitions:

```json
{
  "sheets": [
    {
      "name": "Analytics",
      "charts": [
        {
          "type": "bar",
          "title": "Chart Title",
          "data_range": "Sheet1!A1:B10",
          "category_labels": "Sheet1!A1:A10",
          "position": {
            "cell": "D3",
            "width": 400,
            "height": 300
          },
          "color_palette": "professional"
        }
      ]
    }
  ]
}
```

### Supported Chart Types

- `bar` - Horizontal bar chart
- `column` - Vertical bar chart
- `line` - Line chart
- `pie` - Pie chart
- `area` - Area chart
- `scatter` - Scatter plot

### Chart Parameters

- **type**: Chart type (required)
- **title**: Chart title displayed above chart (required)
- **data_range**: Data range for chart values, supports cross-sheet references like "Sheet1!A1:B10" (required)
- **category_labels**: Optional category labels for X-axis (supports cross-sheet references)
- **position**: Chart position and size (required)
  - **cell**: Top-left cell address (e.g., "D3")
  - **width**: Chart width in pixels (default: 400)
  - **height**: Chart height in pixels (default: 250)
- **color_palette**: Color palette name from branding config (default: "professional")
  - Available palettes: `default`, `professional`, `status`, `categorical`, `gradient_blue`, `monochrome`

### Cross-Sheet References

Charts support cross-sheet data ranges:

```json
{
  "data_range": "Berechnungen!A4:C9",
  "category_labels": "Berechnungen!A4:A9"
}
```

### Color Palettes

Color palettes are defined in `config/template-branding.php`:

- **default**: Multi-color palette with Ordio brand colors
- **professional**: Blue gradient palette (recommended for most charts)
- **status**: Status-based colors (success, warning, error, neutral)
- **categorical**: Distinct colors for multiple categories
- **gradient_blue**: Blue gradient variations
- **monochrome**: Grayscale palette

**Note:** PhpSpreadsheet Chart API has limited support for programmatic color setting. Colors are typically applied via Excel's chart formatting interface after generation. The system logs palette selection for reference.

### Chart Best Practices

- **Use charts for insights, not decoration** - Charts should provide value (minimum usefulness score: 7/10)
- **Bar charts** for comparisons (e.g., costs per employee, hours by department)
- **Line charts** for time series (e.g., hours worked over time)
- **Pie charts** for proportions (e.g., cost distribution, status breakdown)
- **Position charts** to avoid overlapping with data tables
- **Use descriptive titles** that clearly explain what the chart shows
- **Ensure data ranges** contain actual data (not empty cells) for best results

### Chart Validation

Validate charts using the analysis script:

```bash
php v2/systems/excel-template-generator/scripts/analyze-charts.php <template-id>
```

**See:** [Chart Generation Implementation](../../v2/systems/excel-template-generator/docs/CHART_GENERATION_IMPLEMENTATION.md) for complete documentation.

## Reference Files

### Core System Files

- Template Generator: `v2/systems/excel-template-generator/helpers/template-generator.php`
- Function Translations: `v2/systems/excel-template-generator/data/function-translations.json`
- Branding Config: `v2/systems/excel-template-generator/config/template-branding.php`
- Example Templates: `v2/systems/excel-template-generator/data/template-definitions/examples/`

### Documentation Structure

**Primary Guides:**

- Template Definition Guide: `v2/systems/excel-template-generator/docs/TEMPLATE_DEFINITION_GUIDE.md`
- Template Implementation Guidelines: `v2/systems/excel-template-generator/docs/TEMPLATE_IMPLEMENTATION_GUIDELINES.md`
- Documentation Index: `v2/systems/excel-template-generator/docs/README.md`

**Guides (`docs/guides/`):**

- Visualization Guide: `v2/systems/excel-template-generator/docs/guides/VISUALIZATION_GUIDE.md`
- Style Guide: `v2/systems/excel-template-generator/docs/guides/STYLE_GUIDE.md`
- Audit Guide: `v2/systems/excel-template-generator/docs/guides/AUDIT_GUIDE.md`
- Keyword Analysis Guide: `v2/systems/excel-template-generator/docs/guides/KEYWORD_ANALYSIS_GUIDE.md`
- Keyword Tracking Guide: `v2/systems/excel-template-generator/docs/guides/KEYWORD_TRACKING_GUIDE.md`

**Reference Materials (`docs/reference/`):**

- Formula Reference: `v2/systems/excel-template-generator/docs/reference/FORMULA_REFERENCE.md`
- Style Presets: `v2/systems/excel-template-generator/docs/reference/STYLE_PRESETS.md`
- Validation Patterns: `v2/systems/excel-template-generator/docs/reference/VALIDATION_PATTERNS.md`
- Best Practices: `v2/systems/excel-template-generator/docs/reference/BEST_PRACTICES.md`

**Research Documents (`docs/research/`):**

- **Comprehensive Research:** `v2/systems/excel-template-generator/docs/research/PHPSPREADSHEET_COMPREHENSIVE_RESEARCH.md` - Complete PhpSpreadsheet feature inventory and analysis
- **Research Summary:** `v2/systems/excel-template-generator/docs/research/RESEARCH_SUMMARY.md` - Executive summary of all research findings
- **Feature Testing:** `v2/systems/excel-template-generator/docs/research/FEATURE_TESTING_RESULTS.md` - Test results for advanced features
- **Implementation Examples:** `v2/systems/excel-template-generator/docs/research/IMPLEMENTATION_EXAMPLES.md` - Code examples for new features
- **UI/UX Best Practices:** `v2/systems/excel-template-generator/docs/research/EXCEL_UI_UX_BEST_PRACTICES.md` - Excel template UI/UX best practices
- **Competitive Analysis:** `v2/systems/excel-template-generator/docs/research/COMPETITIVE_ANALYSIS.md` - Competitive template analysis
- Performance Research: `v2/systems/excel-template-generator/docs/research/PERFORMANCE_OPTIMIZATION_RESEARCH.md`
- Library Research: `v2/systems/excel-template-generator/docs/research/LIBRARY_RESEARCH.md`
- Best Practices Research: `v2/systems/excel-template-generator/docs/research/BEST_PRACTICES_RESEARCH.md`

**Other Documentation:**

- Error Handling Guide: `v2/systems/excel-template-generator/docs/ERROR_HANDLING_IMPROVEMENTS.md`
- Template Opportunity Roadmap: `v2/systems/excel-template-generator/docs/TEMPLATE_OPPORTUNITY_ROADMAP.md`
- Template Prioritization Matrix: `v2/systems/excel-template-generator/docs/TEMPLATE_PRIORITIZATION_MATRIX.md`

## Related Documentation

See [docs/ai/RULE_TO_DOC_MAPPING.md](../../docs/ai/RULE_TO_DOC_MAPPING.md) for complete mapping.

**Key Documentation:**

- [docs/systems/excel-generator/README.md](../../docs/systems/excel-generator/README.md) - `docs/systems/excel-generator/README.md` - Excel generator index
- [docs/systems/excel-generator/](../../docs/systems/excel-generator/) - `docs/systems/excel-generator/` - All Excel generator documentation
# excel-template-generator-core Full Instructions

## Template Generator System Overview

The Ordio Template Generator creates Excel templates from JSON definitions with automatic formula translation, styling, and example data support.

## Critical Rules

### Formula System

**ALWAYS use English function names:**

- ✅ `SUM`, `IF`, `IFERROR`, `VLOOKUP`, `MIN`, `MAX`
- ❌ `SUMME`, `WENN`, `WENNFEHLER`, `SVERWEIS`, `MINIMUM`, `MAXIMUM`

**ALWAYS include "=" prefix in formulas:**

- ✅ `"formula": "=SUM(A1:A10)"`
- ❌ `"formula": "SUM(A1:A10)"` (system adds it, but include explicitly)

**Formula detection:**

- Formulas are detected by `data_type: "formula"` OR value starting with "="
- System automatically translates German→English function names
- System automatically adds "=" prefix if missing

### Input Cell Styling

**Empty input cells:**

- Use `input_cell_empty` preset (light gray #F9FAFB background)
- Applied via `input_ranges` array in sheet definition
- Only applied to truly empty cells (null or empty string, not 0)

**Filled input cells:**

- Use `input_cell` preset (white background)
- Applied explicitly in example rows and data cells

**Never use black/default formatting for input cells** - always use Ordio-branded styles.

**Valid Presets Only:**

- ✅ `header` - For table headers in row 1
- ✅ `input_cell` - For filled example cells
- ✅ `input_cell_empty` - For empty input cells
- ✅ `title` - For form-style sheet titles in row 1
- ✅ `subtitle` - For section headings
- ✅ `note_cell` - For instructional notes
- ✅ `info_cell` - For informational messages
- ❌ `data_cell` - INVALID (use `input_cell` instead)
- ❌ `formula_cell` - INVALID (formulas don't need special preset)
- ❌ `header_light` - INVALID (use `header` instead)

### Example Data Rows

**Always include 5-6 example rows for comprehensive templates (2-3 for simple templates):**

- Helps users understand template usage
- Shows formulas in action
- Demonstrates expected data format
- Makes template look "full" and professional
- Include varied scenarios:
  - Full-time vs part-time employees
  - Different departments/roles
  - Different statuses (Aktiv, Urlaub, Krank)
  - Different shift types (if applicable)
- Ensure examples demonstrate all dropdown options

**Example row structure:**

```json
{
  "example_rows": [
    {
      "row_number": 2,
      "cells": [
        {
          "address": "A2",
          "value": "Example Value",
          "style": { "preset": "input_cell" },
          "data_type": "text"
        },
        {
          "address": "B2",
          "value": 40,
          "style": { "preset": "input_cell" },
          "data_type": "number"
        }
      ]
    }
  ]
}
```

**CRITICAL - Data Types:**

- Use `"data_type": "number"` for numeric values (even if in dropdown with numeric options)
- Use `"data_type": "text"` for text values
- Use `"data_type": "date"` for dates with format specification
- Match data_type to actual value type - don't use "text" for numbers

**Data Type Validation:**

- ✅ `data_type: "date"` → Value must be date string (YYYY-MM-DD) or empty
- ✅ `data_type: "number"` → Value must be numeric or empty
- ✅ `data_type: "text"` → Value must be text (NOT date strings like "2025-08-05")
- ❌ Never use date strings in text fields (use `data_type: "date"` instead)
- ❌ Never use text values in number fields

### Data Validation: Dropdowns vs Number Validation

**Use dropdowns for categorical fields:**

- Status (Aktiv, Inaktiv, Urlaub, Krank)
- Vertragsart (Vollzeit, Teilzeit, Minijob)
- Abteilung (Service, Küche, Management, Reinigung, Verwaltung, Sonstiges)
- Schichttyp (Frühschicht, Spätschicht, Nachtschicht, Ganztag, Frei)
- Standort (if predefined locations)
- Any field with limited, predefined options

**Use number validation for numeric fields:**

- Stundenlohn (wages) - Use decimal validation (≥ 12.82)
- Wochenstunden (weekly hours) - Use decimal validation (0-48) for flexibility
- Arbeitsstunden (daily hours) - Use decimal validation (0-10)
- Any numeric field where users need precise values (e.g., 22.5 hours, €18.75)

**CRITICAL:** Don't force numeric fields into dropdowns just to increase audit scores. Prioritize user flexibility and optimal data types. Use dropdowns for categorical data, number validation for numeric data.

**Example dropdown validation:**

```json
{
  "range": "C2:C20",
  "type": "list",
  "list_values": ["Aktiv", "Inaktiv", "Urlaub", "Krank"]
}
```

## Template Definition Schema

### Required Fields

- `metadata.name` - Template name
- `metadata.category` - Template category
- `metadata.version` - Version number
- `sheets[]` - Array of sheet definitions

### Sheet Definition

Each sheet can include:

- `name` - Sheet name
- `type` - Sheet type (instructions, data_entry, report)
- `order` - Display order
- `cells[]` - Individual cell definitions
- `rows[]` - Row definitions with cells
- `example_rows[]` - Example data rows
- `input_ranges[]` - Ranges for empty cell styling
- `data_validation[]` - Data validation rules
- `conditional_formatting[]` - Conditional formatting rules
- `columns[]` - Column width definitions

### Cell Definition

```json
{
  "address": "A1",
  "value": "Cell Value or Formula",
  "style": { "preset": "style_name" },
  "data_type": "text|number|formula",
  "format": "#,##0.00"
}
```

### Formula Reference

```json
{
  "address": "B2",
  "value": {
    "formula_id": "formula_name",
    "parameters": {
      "param_name": "param_value"
    }
  },
  "data_type": "formula"
}
```

## Branding Compliance

### Colors

- ✅ Use Ordio blue (#4D8EF3), green (#10B981), red (#EF4444)
- ❌ Never use purple (#8B5CF6) in templates
- Use neutral grays for backgrounds (#F9FAFB, #F5F5F5)

### Headers

**CRITICAL - Header Row Placement:**

- **Data Entry Sheets (`data_entry`):**

  - ❌ NO title headers (duplicates sheet name)
  - ✅ Table headers MUST be in row 1
  - ✅ Use `preset: "header"` for column headers
  - ✅ Freeze panes at A2 to keep headers visible

- **Report Sheets (`report`):**

  - ❌ NO title headers (unless providing context)
  - ✅ Table headers in row 1 for data tables
  - ✅ Freeze panes at A2 if table has many rows

- **Calculation Sheets (`calculation`):**

  - ❌ NO title headers
  - ✅ Headers in row 1 if displaying tabular data
  - ✅ Freeze panes at A2 if table has many rows

- **Instruction Sheets (`instructions`):**
  - ✅ Title headers acceptable (e.g., "Template Name - Anleitung")
  - ✅ Use `preset: "title"` for main heading
  - ❌ Do NOT freeze panes (no scrolling needed)

**Freeze Panes Configuration:**

Add `freeze_pane` property to sheet definitions:

```json
{
  "freeze_pane": {
    "cell": "A2",
    "description": "Freeze header row"
  }
}
```

**Header Styling:**

- Use `header` preset (blue background, white text)
- **No borders** on headers (cleaner look)
- Center-aligned text
- Bold font for emphasis
- Ensure headers fit without truncation

**Example - Correct Header Structure:**

```json
{
  "name": "Mitarbeiter",
  "type": "data_entry",
  "freeze_pane": {"cell": "A2"},
  "cells": [
    {"address": "A1", "value": "Name", "style": {"preset": "header"}},
    {"address": "B1", "value": "Stundenlohn", "style": {"preset": "header"}}
  ],
  "input_ranges": [
    {"range": "A2:A18", "style_preset": "input_cell_empty"}
  ],
  "example_rows": [
    {"row_number": 2, "cells": [...]}
  ]
}
```

**Common Mistakes:**

- ❌ Title header in row 1, table headers in row 3
- ❌ Headers not in row 1 (prevents filtering/sorting)
- ❌ Missing freeze panes for data entry sheets
- ❌ Merged cells in header row (breaks data structure)

### Column Widths

- Auto-width for name columns
- Specific widths (12-15) for number columns
- Ensure headers fit without truncation

## Template Quality Checklist

Before creating/updating templates:

### Headers

- [ ] Table-style sheets: Headers in row 1 (A1, B1, C1, etc.)
- [ ] Form-style sheets: Title in row 1 (A1) with `preset: "title"`
- [ ] No empty row 1
- [ ] No headers in example rows
- [ ] Freeze panes configured (A2) for data_entry/report/calculation sheets

### Dummy Data

- [ ] Data types match values (date strings only with `data_type: "date"`)
- [ ] Realistic, context-appropriate examples
- [ ] Varied scenarios (full-time/part-time, different departments, etc.)
- [ ] German names and realistic company names for German templates
- [ ] 5-6 example rows for comprehensive templates, 2-3 for simple templates

### Formulas

- [ ] All formulas use English function names (SUM, IF, not SUMME, WENN)
- [ ] All formulas have "=" prefix
- [ ] Valid cell references (no A0, no columns beyond ZZ)
- [ ] No circular references

### Formatting

- [ ] Only valid presets used (header, input_cell, input_cell_empty, title, subtitle, note_cell, info_cell)
- [ ] No invalid presets (data_cell, formula_cell, header_light)
- [ ] Consistent styling across template
- [ ] Headers use `header` preset
- [ ] Input cells use `input_cell` or `input_cell_empty`

### Structure

- [ ] Excel Tables configured correctly (if applicable)
- [ ] Freeze panes configured (if applicable)
- [ ] Hyperlinks work correctly (if applicable)
- [ ] Data validation rules applied
- [ ] Conditional formatting for compliance
- [ ] No purple colors
- [ ] Headers have no borders
- [ ] Column widths appropriate

**See:** `v2/systems/excel-template-generator/docs/TEMPLATE_QUALITY_CHECKLIST.md` for complete checklist.

## Common Patterns

### Weekly Schedule Template

```json
{
  "sheets": [
    {
      "name": "Schedule",
      "type": "data_entry",
      "cells": [
        {
          "address": "A1",
          "value": "Employee",
          "style": { "preset": "header" }
        },
        { "address": "B1", "value": "Monday", "style": { "preset": "header" } }
      ],
      "example_rows": [
        {
          "row_number": 2,
          "cells": [
            {
              "address": "A2",
              "value": "John Doe",
              "style": { "preset": "input_cell" }
            },
            {
              "address": "B2",
              "value": "8",
              "style": { "preset": "input_cell" }
            }
          ]
        }
      ],
      "input_ranges": [
        { "range": "A2:A20", "style_preset": "input_cell_empty" },
        { "range": "B2:H20", "style_preset": "input_cell_empty" }
      ]
    }
  ]
}
```

### Compliance Check Sheet

```json
{
  "cells": [
    {
      "address": "B4",
      "value": "=IF(MIN(Sheet1!B2:B20)>=12.82,\"OK\",\"Prüfen\")",
      "style": { "preset": "compliance_ok" },
      "data_type": "formula"
    }
  ],
  "conditional_formatting": [
    {
      "range": "B4:B10",
      "type": "cellValue",
      "operator": "equal",
      "formula": "\"OK\"",
      "style": {
        "fill": { "color": "#ECFDF5" },
        "font": { "color": "#10B981", "bold": true }
      }
    }
  ]
}
```
- docs/ai/rules-archive/excel-template-generator-advanced-full.md

# Excel Template Generator System - Advanced Patterns

**Note:** This file is part of a split from `excel-template-generator.mdc`. See also:

- `excel-template-generator-core.mdc` - System overview, critical rules, template definition schema, branding compliance, quality checklist, common patterns


## Heavy Instructions Moved

**CRITICAL:** The detailed instructions, edge cases, and massive data for this rule have been moved to optimize AI context.
You MUST read the full documentation before proceeding:
`docs/ai/rules-archive/excel-template-generator-advanced-full.md`
- docs/ai/rules-archive/excel-template-generator-core-full.md

# Excel Template Generator System - Core Patterns

**Note:** This file is part of a split from `excel-template-generator.mdc`. See also:

- `excel-template-generator-advanced.mdc` - Testing, error handling, performance optimization, keyword-driven development, batch generation, design standards, chart generation


## Heavy Instructions Moved

**CRITICAL:** The detailed instructions, edge cases, and massive data for this rule have been moved to optimize AI context.
You MUST read the full documentation before proceeding:
`docs/ai/rules-archive/excel-template-generator-core-full.md`

## Overview

**This rule has been split into focused files for better maintainability:**

- **`excel-template-generator-core.mdc`** - System overview, critical rules, template definition schema, branding compliance, template quality checklist, common patterns
- **`excel-template-generator-advanced.mdc`** - Testing, error handling, performance optimization, keyword-driven development, batch generation, design standards, chart generation

The Ordio Template Generator creates Excel templates from JSON definitions with automatic formula translation, styling, and example data support.

**See the split files above for detailed documentation.**

# Excel Template Integrity Rules

**Last Updated:** 2026-03-17

## Overview

When editing template definitions, the template generator, or regeneration scripts, follow these rules to avoid Excel "problem with some content" / "repair or remove unreadable content" errors.

## Validation Steps

1. **After template definition changes:** Run `php v2/systems/excel-template-generator/scripts/regenerate-all-templates-cli.php` (includes XLSX validation)
2. **Manual validation:** `python3 v2/scripts/dev-helpers/validate-generated-xlsx.py`
3. **Inspect structure (debugging):** `python3 v2/scripts/dev-helpers/inspect-xlsx-structure.py path/to/file.xlsx`
4. **Excel MCP inspection (optional, when debugging):** Use Excel MCP to inspect formulas, verify data validation rules, check conditional formatting, and read cell values. Use when Python/PHP validation scripts find issues or for detailed inspection. Example: "Use Excel MCP to inspect v2/systems/excel-template-generator/output/generated-templates/dienstplan-excel-vorlage.xlsx"
5. **Before deploy:** Open key templates (dienstplan-excel-vorlage.xlsx, schichtplan-excel-vorlage.xlsx) in Excel and LibreOffice – no repair prompt

**When to use Excel MCP vs Python/PHP scripts:**
- **Python/PHP scripts:** Primary validation mechanism - use for automated validation, CI/CD, batch checks
- **Excel MCP:** Inspection/debugging tool - use when scripts find issues, for formula verification, structure checks, or detailed inspection
- **Manual Excel/LibreOffice:** Final verification before deploy - visual inspection for repair prompts

## PhpSpreadsheet Version

- **Current:** 5.5.0 (fixes chart displayBlanksAs corruption, includes performance improvements)
- **Minimum:** 5.5.0 (required for PhpSpreadsheet 5.x API compatibility)
- **composer.json:** Use `^5.5` to receive patches
- **Cache API:** PhpSpreadsheet 5.5 requires PSR-16 cache object (not array). Cache removed from `saveToStream()` - PhpSpreadsheet handles memory internally.

## Common Corruption Causes

| Cause | Prevention |
|-------|------------|
| Chart `dispBlanksAs val="0"` | PhpSpreadsheet 5.5.0+ (includes fix from 1.29+); use `DataSeries::EMPTY_AS_GAP` in Chart constructor |
| Output buffer / BOM | `ob_start()` at top of download script; clear buffers before `readfile()` |
| Deprecation warnings | Suppress E_DEPRECATED during generation in template-cli.php |
| Invalid formulas | Use valid cell references; formula1/formula2 as numeric strings |
| Broken excel_table | Contiguous ranges; unique table names |
| Table XML corruption | **PhpSpreadsheet 5.5+** includes fix from PR #4556 (eliminates xml:space from table nodes). excel_table is supported; verify with regeneration and manual Excel/LibreOffice test before deploy |

## Chart Generation

In `template-generator.php`, Chart constructor must use:
```php
\PhpOffice\PhpSpreadsheet\Chart\DataSeries::EMPTY_AS_GAP  // displayBlanksAs - NOT 0
```

## Download API

`v2/api/download-template.php` must:
- Call `ob_start()` after requires to capture any accidental output
- Call `while (ob_get_level()) { ob_end_clean(); }` before headers and `readfile()`

## Excel MCP Integration

**Excel MCP is for inspection/debugging, not template generation.** Template generation uses PhpSpreadsheet (production code path).

**Use Excel MCP when:**
- Python/PHP validation scripts find issues
- Diagnosing "problem with some content" corruption
- Verifying formulas are correctly applied
- Checking conditional formatting and data validation rules
- Comparing template structure against expected definition

**Excel MCP tools:**
- `read_data_from_excel` - Read cell values, formulas, data validation rules
- `write_data_to_excel` - Write data (for testing/debugging only)
- `apply_formula` - Apply formulas (for testing/debugging only)

**See:** `docs/systems/excel-generator/EXCEL_MCP_INTEGRATION.md` for comprehensive guide

## References

- [EXCEL_TEMPLATE_BEST_PRACTICES.md](docs/systems/excel-generator/EXCEL_TEMPLATE_BEST_PRACTICES.md)
- [EXCEL_MCP_INTEGRATION.md](docs/systems/excel-generator/EXCEL_MCP_INTEGRATION.md)
- [REGENERATION_WORKFLOW.md](v2/systems/excel-template-generator/docs/REGENERATION_WORKFLOW.md)
- [PhpSpreadsheet Issue #4411](https://github.com/PHPOffice/PhpSpreadsheet/issues/4411)