# Excel Template Generator Quick Reference **Last Updated:** 2025-11-20 ## Common Commands ### Template Generation ```bash # Generate from JSON definition php v2/systems/excel-template-generator/scripts/template-cli.php generate template.json output.xlsx # Or use PHP directly php -r "require 'v2/systems/excel-template-generator/helpers/template-generator.php'; \$g = new OrdioTemplateGenerator(file_get_contents('template.json')); \$g->generate()->save('output.xlsx');" ``` ### Template Validation ```bash # Validate template definition php v2/systems/excel-template-generator/scripts/template-validator.php template.json --verbose # Validate template + generated Excel php v2/systems/excel-template-generator/scripts/template-validator.php template.json output.xlsx --json # Branding validation (NEW) php v2/systems/excel-template-generator/scripts/validate-branding.php template.json # Formula quality check (NEW) php v2/systems/excel-template-generator/scripts/formula-quality-checker.php template.json --verbose # Visualization quality check (NEW) php v2/systems/excel-template-generator/scripts/visualization-quality-checker.php template.json --verbose # Quality gates (all validations) (NEW) php v2/systems/excel-template-generator/scripts/template-quality-gates.php template.json --strict # Python validation (Excel file) python3 v2/scripts/validate-template.py output.xlsx --verbose ``` ### Quality Scoring ```bash # Score template quality php v2/systems/excel-template-generator/scripts/template-quality-scorer.php template.json --benchmark --csv # Output formats: --json, --csv, --both ``` ### Quality Testing ```bash # Run comprehensive tests php v2/scripts/test-template-quality.php --template=template.json --verbose # Test all templates php v2/scripts/test-template-quality.php --all # Integration test (NEW) php v2/systems/excel-template-generator/scripts/test-integration.php --verbose ``` ### Automated Workflow (NEW) ```bash # Complete automated workflow (recommended) php v2/systems/excel-template-generator/scripts/generate-template-workflow.php --template=template.json # With options php v2/systems/excel-template-generator/scripts/generate-template-workflow.php --template=template.json --skip-research --auto-approve ``` ### Python Enhancement ```bash # Enhance with charts/pivot tables python3 v2/scripts/enhance-template.py output.xlsx enhancements.json enhanced.xlsx --progress --verbose ``` ### Composer Scripts ```bash # Setup environment composer template:setup # Generate template composer template:generate template.json # Validate template composer template:validate template.json # Test template composer template:test template.json ``` ### Template Preprocessing (NEW) ```bash # List available components php -r "require 'v2/systems/excel-template-generator/helpers/template-component-loader.php'; \$l = new OrdioTemplateComponentLoader(); print_r(\$l->listComponents());" # Validate parameters php -r "require 'v2/systems/excel-template-generator/helpers/template-parameterizer.php'; \$p = new OrdioTemplateParameterizer(); \$t = json_decode(file_get_contents('template.json'), true); \$v = \$p->validateParameters(\$t, ['team_size' => 10]); print_r(\$v);" # Optimize formulas php -r "require 'v2/systems/excel-template-generator/helpers/formula-optimizer.php'; \$o = new OrdioFormulaOptimizer(); \$t = json_decode(file_get_contents('template.json'), true); \$r = \$o->optimize(\$t); print_r(\$r['optimizations']);" ``` ## Template Structure ### Basic Template Structure ```json { "metadata": { "name": "Template Name", "category": "shift_planning|time_tracking|payroll|employee_management|compliance|other", "version": "1.0.0", "description": "Template description", "author": "Ordio GmbH", "created_date": "YYYY-MM-DD", "use_cases": ["Use case 1"], "tags": ["tag1", "tag2"] }, "sheets": [ { "name": "Sheet Name", "type": "instructions|overview|data_entry|calculation|report|reference", "order": 0, "cells": [...], "rows": [...], "columns": [...] } ], "branding": { "use_default": true }, "extends": "base-template-id", // Optional: inherit from base template "components": [ // Optional: use reusable components { "id": "compliance-arbzg-check", "parameters": { "hours_range": "B2:B20", "result_cell": "C2" } } ], "parameters": [ // Optional: template parameters { "id": "team_size", "type": "number", "name": "Team Size", "min": 1, "max": 50, "default": 10 } ] } ``` ### Sheet Types - `instructions` - User guide/instructions - `overview` - Dashboard/summary view - `data_entry` - User input areas - `calculation` - Automated calculations - `report` - Analysis and reports - `reference` - Reference data ### Standard Sheet Order 1. Anleitung (Instructions) 2. Übersicht (Overview) 3. Daten (Data Entry) 4. Berechnungen (Calculations) 5. Berichte (Reports) 6. Referenz (Reference) - optional ## Formula Library ### HR Formulas **Location:** `v2/systems/excel-template-generator/data/template-formulas/hr-formulas.json` **Common Formulas:** - `working_hours` - Calculate working hours - `break_validation` - Validate break requirements - `arbzg_compliance` - ArbZG compliance check - `minimum_wage_check` - Minimum wage validation - `overtime_calculation` - Overtime calculation **Usage:** ```json { "cells": [ { "address": "C2", "value": { "formula_id": "working_hours", "parameters": { "START": "A2", "END": "B2" } }, "data_type": "formula" } ] } ``` ### Advanced Formulas **Location:** `v2/systems/excel-template-generator/data/template-formulas/advanced-formulas.json` **Common Formulas:** - `array_lookup` - Array-based lookup - `dynamic_sum` - Dynamic sum with conditions - `date_calculations` - Date calculations - `statistical_functions` - Statistical functions ### Formula Best Practices 1. **Always use error handling:** ```json { "formula": "=WENNFEHLER(SUMME(A:A);0)" } ``` 2. **Use named ranges:** ```json { "named_ranges": [ { "name": "Mindestlohn", "range": "A1" } ] } ``` 3. **Use German function names:** - `SUMME` instead of `SUM` - `WENN` instead of `IF` - `SVERWEIS` instead of `VLOOKUP` - `WENNFEHLER` instead of `IFERROR` ## Branding Reference ### Colors **Location:** `v2/systems/excel-template-generator/config/template-branding.php` **Primary Colors:** - Primary: `#4D8EF3` (Ordio Blue) - Primary Hover: `#3B82F6` - Primary Light: `#EFF6FF` - Text Primary: `#374151` - Text Secondary: `#6B7280` - Success: `#10B981` - Warning: `#F59E0B` - Error: `#EF4444` - Border: `#D3D8DF` - Background: `#FFFFFF` ### Fonts - Primary: `Inter` - Heading: `Gilroy-Bold` - Body: `Inter` (11pt) ### Cell Style Presets **Available Presets:** - `header` - Blue background, white text, bold, centered - `header_light` - Light gray background, dark text, bold - `data_cell` - White background, dark text, left-aligned - `formula_cell` - Subtle gray background, right-aligned - `total_row` - Light gray background, bold, top border - `title` - Large, bold, dark text - `subtitle` - Medium, bold, dark text - `highlight` - Blue background, white text - `success_cell` - Green background, white text - `warning_cell` - Orange background, dark text - `error_cell` - Red background, white text - `input_cell` - White background, blue border - `locked_cell` - Gray background, locked **Usage:** ```json { "cells": [ { "address": "A1", "value": "Header", "style": { "preset": "header" } } ] } ``` ### Number Formats **German Formats:** - Currency: `#,##0.00 €` - Percentage: `0.00%` - Date: `TT.MM.JJJJ` - Time: `HH:MM` - Datetime: `TT.MM.JJJJ HH:MM` **Usage:** ```json { "cells": [ { "address": "A1", "value": 1234.56, "format": "currency" } ] } ``` ## Data Validation ### Dropdown List ```json { "data_validation": [ { "range": "A2:A10", "type": "list", "list_values": ["Ja", "Nein", "Teilzeit", "Vollzeit"], "error_message": "Bitte wählen Sie einen Wert aus der Liste.", "input_message": "Wählen Sie einen Wert aus der Liste." } ] } ``` ### Date Range ```json { "data_validation": [ { "range": "B2:B10", "type": "date", "operator": "between", "formula1": "DATUM(2025;1;1)", "formula2": "DATUM(2025;12;31)", "error_message": "Bitte geben Sie ein gültiges Datum im Jahr 2025 ein." } ] } ``` ### Number Range ```json { "data_validation": [ { "range": "C2:C10", "type": "whole", "operator": "between", "formula1": "1", "formula2": "12", "error_message": "Bitte geben Sie eine Zahl zwischen 1 und 12 ein." } ] } ``` ## Conditional Formatting ### Cell Value ```json { "conditional_formatting": [ { "range": "C2:C10", "type": "cellValue", "operator": "greaterThan", "formula": "10", "style": { "fill": { "color": "#EF4444" }, "font": { "color": "#FFFFFF", "bold": true } } } ] } ``` ### Formula-Based ```json { "conditional_formatting": [ { "range": "D2:D10", "type": "formula", "formula": "D2<12,82", "style": { "fill": { "color": "#F59E0B" }, "font": { "color": "#FFFFFF" } } } ] } ``` ## File Locations ### Template Definitions - **Definitions:** `v2/systems/excel-template-generator/data/template-definitions/` - **Examples:** `v2/systems/excel-template-generator/data/template-definitions/examples/` - **Schema:** `v2/systems/excel-template-generator/data/template-definitions/schema.json` ### Formula Libraries - **HR Formulas:** `v2/systems/excel-template-generator/data/template-formulas/hr-formulas.json` - **Advanced Formulas:** `v2/systems/excel-template-generator/data/template-formulas/advanced-formulas.json` - **Industry Formulas:** `v2/systems/excel-template-generator/data/template-formulas/industry-formulas.json` - **Lookup Formulas:** `v2/systems/excel-template-generator/data/template-formulas/lookup-formulas.json` - **Public Data:** `v2/systems/excel-template-generator/data/template-formulas/public-data.json` ### Configuration - **Branding:** `v2/systems/excel-template-generator/config/template-branding.php` - **Registry:** `v2/systems/excel-template-generator/data/template-registry.json` ### Scripts - **Generator:** `v2/systems/excel-template-generator/helpers/template-generator.php` - **CLI:** `v2/systems/excel-template-generator/scripts/template-cli.php` - **Validator:** `v2/systems/excel-template-generator/scripts/template-validator.php` - **Quality Scorer:** `v2/systems/excel-template-generator/scripts/template-quality-scorer.php` - **Python Enhancement:** `v2/scripts/enhance-template.py` - **Python Validation:** `v2/scripts/validate-template.py` ### Documentation - **Workflow:** `docs/systems/excel-generator/AI_AGENT_WORKFLOW.md` - **Best Practices:** `docs/systems/excel-generator/BEST_PRACTICES_GUIDE.md` - **Python Setup:** `docs/systems/excel-generator/PYTHON_SETUP.md` - **Quick Reference:** `docs/systems/excel-generator/QUICK_REFERENCE.md` (this file) ## Troubleshooting ### Template Generation Fails **Problem:** Template generation fails with error. **Solutions:** 1. Check JSON syntax: `php -r "json_decode(file_get_contents('template.json'));"` 2. Validate schema: `php v2/systems/excel-template-generator/scripts/template-validator.php template.json` 3. Check file paths and references 4. Verify all required fields present ### Formulas Not Working **Problem:** Formulas don't calculate correctly. **Solutions:** 1. Check formula syntax (must start with `=`) 2. Verify cell references exist 3. Check for German vs English function names 4. Test formula in Excel manually 5. Use `WENNFEHLER` for error handling ### Branding Not Applied **Problem:** Styles don't match Ordio branding. **Solutions:** 1. Check `branding.use_default` is `true` 2. Verify style presets are used 3. Check branding config file exists 4. Validate colors match Ordio palette ### Python Enhancement Fails **Problem:** Python enhancement script fails. **Solutions:** 1. Check Python installed: `python3 --version` 2. Install dependencies: `pip install -r requirements.txt` 3. Check virtual environment activated 4. Verify input files exist 5. Check file permissions ### Validation Errors **Problem:** Template validation shows errors. **Solutions:** 1. Review error messages 2. Check schema compliance 3. Fix missing required fields 4. Validate German locale 5. Check branding compliance ### Quality Score Low **Problem:** Quality score below 80%. **Solutions:** 1. Review suggestions from scorer 2. Add missing features 3. Improve documentation 4. Add error handling 5. Enhance user experience ## Common Patterns ### Named Range Definition ```json { "named_ranges": [ { "name": "Mindestlohn", "range": "A1", "scope": "Sheet1" } ] } ``` ### Formula with Named Range ```json { "formula": "=WENNFEHLER(WENN(B2