# Excel Template Generator System

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

## Overview

The Excel Template Generator is a comprehensive system for creating high-quality, Ordio-branded Excel templates for HR and workforce management use cases. The system enables AI agents (Cursor) to generate templates with consistent branding, intelligent feature selection, and automated quality assurance.

## Quick Start

### For AI Agents (Cursor)

**Recommended: Use Automated Workflow**

```bash
php v2/systems/excel-template-generator/scripts/generate-template-workflow.php --template=template.json
```

This automatically:

1. Resolves template inheritance
2. Applies reusable components
3. Validates parameters
4. Generates Excel file
5. Validates branding compliance
6. Checks formula quality
7. Checks visualization quality
8. Scores template quality
9. Updates registry

**Manual Workflow:**
See `AI_AGENT_WORKFLOW.md` for complete step-by-step guide.

### For Developers

**Generate Template:**

```bash
php v2/systems/excel-template-generator/scripts/template-cli.php generate template.json output.xlsx
```

**Quality Checks:**

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

# Individual checks
php v2/systems/excel-template-generator/scripts/validate-branding.php template.json
php v2/systems/excel-template-generator/scripts/formula-quality-checker.php template.json
php v2/systems/excel-template-generator/scripts/visualization-quality-checker.php template.json
```

**Integration Test:**

```bash
php v2/systems/excel-template-generator/scripts/test-integration.php --verbose
```

## Documentation

### Core Documentation

- **[README.md](README.md)** - This file, system overview and quick start
- **[AI_AGENT_WORKFLOW.md](AI_AGENT_WORKFLOW.md)** - Complete workflow guide for AI agents
- **[BEST_PRACTICES_RESEARCH.md](../../../v2/systems/excel-template-generator/docs/BEST_PRACTICES_RESEARCH.md)** - Comprehensive best practices research
- **[LIBRARY_RESEARCH.md](../../../v2/systems/excel-template-generator/docs/LIBRARY_RESEARCH.md)** - Library and technology research

### Additional Resources

- **[BRANDING_GUIDE.md](../../../v2/systems/excel-template-generator/docs/BRANDING_GUIDE.md)** - Complete branding guidelines
- **[FILE_STRUCTURE.md](../../../v2/systems/excel-template-generator/FILE_STRUCTURE.md)** - System file structure
- **[GOOGLE_SHEETS_INTEGRATION.md](../../google-sheets-integration/GOOGLE_SHEETS_INTEGRATION.md)** - Google Sheets integration guide

## Google Sheets Integration

Templates are automatically available in both Excel and Google Sheets formats:

**Generate Google Sheets Template:**
```bash
php v2/scripts/templates/generate-google-sheets-template.php --template=dienstplan-excel-vorlage
```

**Sync Google Sheets Templates:**
```bash
php v2/scripts/templates/sync-google-sheets-templates.php --all
```

**Check Sync Status:**
```bash
php v2/scripts/templates/sync-google-sheets-templates.php --check
```

See [Google Sheets Integration Guide](../../google-sheets-integration/GOOGLE_SHEETS_INTEGRATION.md) for complete documentation.

## Key Features

### ✅ Branding Consistency

- Automatic branding validation
- 20+ style presets
- Chart color palettes
- Complete branding guide

### ✅ Intelligent Formulas

- Use case-based recommendations
- Formula quality checks
- Performance optimization
- German locale enforcement

### ✅ Quality Automation

- 150-point quality scoring
- 5 quality gates
- Automated improvement suggestions
- Competitive benchmarking

### ✅ Dual Format Support

- **Excel templates:** Generated via PhpSpreadsheet, served as .xlsx downloads
- **Google Sheets templates:** Generated via Google Sheets API, served as copy URLs
- **Synchronized:** Both formats use same JSON definitions, automatically synced
- **Platform adaptation:** Templates adapted to each platform's strengths

### ✅ Customizability

- Template parameters
- Reusable components
- Template inheritance
- Dynamic customization

### ✅ Smart Visualizations

- Intelligent chart selection
- Optimal chart placement
- Quality validation
- Palette recommendations

### ✅ Performance

- Style caching
- Batch operations
- Formula optimization
- Memory management

## Documentation

### Core Guides

- **[AI_AGENT_WORKFLOW.md](AI_AGENT_WORKFLOW.md)** - Complete workflow guide for AI agents
- **[BRANDING_GUIDE.md](../../../v2/systems/excel-template-generator/docs/BRANDING_GUIDE.md)** - Branding standards and guidelines
- **[BEST_PRACTICES_RESEARCH.md](../../../v2/systems/excel-template-generator/docs/BEST_PRACTICES_RESEARCH.md)** - Comprehensive best practices research
- **[LIBRARY_RESEARCH.md](../../../v2/systems/excel-template-generator/docs/LIBRARY_RESEARCH.md)** - Library and technology research

### Additional Resources

- **[PERFORMANCE_OPTIMIZATION.md](../../../v2/systems/excel-template-generator/docs/PERFORMANCE_OPTIMIZATION.md)** - Performance optimization guide
- **[QUICK_REFERENCE.md](../../../v2/systems/excel-template-generator/docs/QUICK_REFERENCE.md)** - Command reference
**Note:** Historical implementation summaries have been deleted and are available in Git history. Use `git log --all --full-history -- docs/archive/completed-projects/excel-generator-implementation-summary.md` to access.
- **[NEXT_STEPS.md](../../../v2/systems/excel-template-generator/docs/NEXT_STEPS.md)** - Future enhancements and next steps
- **[FILE_STRUCTURE.md](../../../v2/systems/excel-template-generator/FILE_STRUCTURE.md)** - System file structure

## System Architecture

### Core Components

- **Template Generator**: `v2/systems/excel-template-generator/helpers/template-generator.php`
- **Branding Config**: `v2/systems/excel-template-generator/config/template-branding.php`
- **Formula Library**: `v2/systems/excel-template-generator/data/template-formulas/hr-formulas.json`
- **Template Registry**: `v2/systems/excel-template-generator/data/template-registry.json`

### Quality Systems

- **Branding Validator**: `v2/systems/excel-template-generator/helpers/template-branding-validator.php`
- **Formula Recommender**: `v2/systems/excel-template-generator/helpers/formula-recommender.php`
- **Quality Scorer**: `v2/systems/excel-template-generator/scripts/template-quality-scorer.php`
- **Quality Gates**: `v2/systems/excel-template-generator/scripts/template-quality-gates.php`

### Customization Systems

- **Parameterizer**: `v2/systems/excel-template-generator/helpers/template-parameterizer.php`
- **Component Loader**: `v2/systems/excel-template-generator/helpers/template-component-loader.php`
- **Inheritance**: `v2/systems/excel-template-generator/helpers/template-inheritance.php`

### Visualization Systems

- **Chart Selector**: `v2/systems/excel-template-generator/helpers/chart-selector.php`
- **Chart Placer**: `v2/systems/excel-template-generator/helpers/chart-placer.php`
- **Visualization Checker**: `v2/systems/excel-template-generator/scripts/visualization-quality-checker.php`

### Performance Systems

- **Formula Optimizer**: `v2/systems/excel-template-generator/helpers/formula-optimizer.php`
- **Performance Tracking**: Built into template generator

## Template Structure

Templates are defined using JSON following the schema in `v2/systems/excel-template-generator/data/template-definitions/schema.json`.

**Basic Structure:**

```json
{
  "metadata": {
    "name": "Template Name",
    "category": "shift_planning",
    "version": "1.0.0"
  },
  "branding": {
    "use_default": true
  },
  "extends": "base-template-id",  // Optional
  "components": [...],  // Optional
  "parameters": [...],  // Optional
  "sheets": [...]
}
```

## Quality Standards

**Quality Gates (All Must Pass):**

1. Schema validation
2. Branding compliance (score >= 80)
3. Formula quality (score >= 80)
4. Visualization quality (score >= 80)
5. Minimum quality score (>= 80%)

**Quality Scoring (150 points):**

- Structure & Organization: 20 points
- Formulas & Calculations: 20 points
- Data Validation: 15 points
- Styling & Branding: 15 points
- Compliance: 15 points
- Documentation: 10 points
- User Experience: 5 points
- Advanced Features: 10 points
- Performance: 5 points
- Accessibility: 5 points
- **Branding Compliance: 10 points** (NEW)
- **Formula Quality: 10 points** (NEW)
- **Visualization: 10 points** (NEW)
- **Customizability: 10 points** (NEW)
- **Modularity: 10 points** (NEW)

## Examples

Example templates are available in:

- `v2/systems/excel-template-generator/data/template-definitions/examples/`

## Support

For questions or issues:

1. Check documentation in `docs/systems/excel-generator/`
2. Review example templates
3. Run integration tests: `php v2/systems/excel-template-generator/scripts/test-integration.php`
4. Check quality gates: `php v2/systems/excel-template-generator/scripts/template-quality-gates.php template.json`

## Status

✅ **Production Ready**

All systems implemented, tested, and documented. Ready for use by AI agents to generate high-quality Excel templates.