# AI Agent Workflow Guide for Excel Template Generation

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

## Overview

This guide provides a comprehensive workflow for AI agents (Cursor) to generate high-quality Excel templates using the Ordio template generator system. Follow this workflow to ensure consistency, quality, and best practices.

## Table of Contents

1. [Workflow Overview](#workflow-overview)
2. [Research Phase](#research-phase)
3. [Design Phase](#design-phase)
4. [Implementation Phase](#implementation-phase)
5. [Validation Phase](#validation-phase)
6. [Quality Assurance Phase](#quality-assurance-phase)
7. [Documentation Phase](#documentation-phase)
8. [Common Pitfalls](#common-pitfalls)
9. [Examples](#examples)

## Workflow Overview

The complete workflow consists of 7 phases:

```
Research → Design → Preprocessing → Implementation → Validation → Quality Assurance → Documentation
```

**Estimated Time:** 30-60 minutes per template (depending on complexity)

**Automated Workflow:** Use `v2/systems/excel-template-generator/scripts/generate-template-workflow.php` for automated end-to-end workflow execution.

## Quick Start: Automated Workflow

For fastest results, use the automated workflow:

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

This automatically:

1. **Web Research:** Searches template sources (Microsoft Office, Vertex42, Smartsheet)
2. **Competitive Analysis:** Analyzes competitor templates
3. **Benchmarking:** Compares Ordio template against competitors
4. Resolves template inheritance
5. Applies reusable components
6. Validates parameters
7. Generates Excel file
8. Validates branding compliance
9. Checks formula quality
10. Checks visualization quality
11. Checks accessibility, performance, usability
12. Scores template quality
13. Updates registry

## Phase 1: Research Phase

### 1.1 Competitive Analysis

**Goal:** Understand what competitors offer and identify best practices.

**Steps:**

1. **Identify Competitors:**

   - Search for similar Excel templates online
   - Check template marketplaces (Microsoft Office Templates, Vertex42, etc.)
   - Review industry-specific templates

2. **Analyze Structure:**

   - How many sheets do they use?
   - What's the sheet organization?
   - What data entry areas exist?
   - What calculations are performed?

3. **Identify Features:**

   - What formulas are used?
   - What data validation rules exist?
   - What visualizations (charts, conditional formatting)?
   - What compliance checks?

4. **Document Findings:**
   - Save analysis to `v2/systems/excel-template-generator/data/template-research/competitive-analysis/`
   - Use format: `{category}-{competitor}-analysis.json`

**Tools:**

- Use `v2/systems/excel-template-generator/scripts/competitive-analysis-automation.php` for automated web research
- Use `v2/systems/excel-template-generator/scripts/template-research-analyzer.php` for analyzing downloaded templates
- Use `v2/systems/excel-template-generator/scripts/competitive-benchmarking.php` for comparing against competitors
- Browser for manual research
- Document findings in structured format following `feature-database-schema.json`

**Decision Tree: Research Approach**

```
Start Research
│
├─ Has competitive data available?
│  ├─ Yes → Use competitive-benchmarking.php to compare
│  └─ No → Run competitive-analysis-automation.php
│     │
│     ├─ Category known?
│     │  ├─ Yes → Use --category=shift_planning
│     │  └─ No → Research category first
│     │
│     └─ Download templates?
│        ├─ Yes → Use --download flag
│        └─ No → Only find templates (--no-analyze)
│
└─ Analyze findings
   ├─ Identify unique features
   ├─ Identify best practices
   └─ Document gaps vs Ordio templates
```

### 1.2 User Needs Analysis

**Goal:** Understand what users actually need.

**Steps:**

1. **Identify Use Cases:**

   - What problems does this template solve?
   - Who is the target user?
   - What workflows does it support?

2. **Define Requirements:**

   - Required data inputs
   - Required calculations
   - Required outputs/reports
   - Compliance requirements

3. **Consider Edge Cases:**
   - What happens with invalid data?
   - How to handle missing data?
   - What are common user errors?

### 1.3 Compliance Research

**Goal:** Ensure German HR compliance.

**Steps:**

1. **ArbZG Requirements:**

   - Maximum working hours (10h/day, 48h/week)
   - Break requirements (30min after 6h, 45min after 9h)
   - Rest periods (11h between shifts)

2. **Minimum Wage:**

   - Current rate: €12.82/hour (2025)
   - Regional variations
   - Validation requirements

3. **Industry-Specific:**
   - Sector-specific regulations
   - Collective agreements
   - Regional requirements

**Resources:**

- `v2/systems/excel-template-generator/data/template-formulas/hr-formulas.json` - HR compliance formulas
- `v2/systems/excel-template-generator/data/template-formulas/public-data.json` - Public data (holidays, wage rates)

## Phase 2: Design Phase

### 2.1 Sheet Structure Design

**Goal:** Plan the template structure.

**Standard Sheet Order:**

1. **Anleitung (Instructions)** - User guide
2. **Übersicht (Overview)** - Dashboard/summary
3. **Daten (Data)** - Data entry areas
4. **Berechnungen (Calculations)** - Automated calculations
5. **Berichte (Reports)** - Analysis and summaries
6. **Referenz (Reference)** - Reference data (optional)

**Design Principles:**

- **Separation of Concerns:** Data entry separate from calculations
- **User Flow:** Logical progression from input to output
- **Clarity:** Each sheet has a clear purpose
- **Modularity:** Sheets can be used independently if needed

### 2.2 Formula Planning

**Goal:** Plan all formulas needed.

**Steps:**

1. **Identify Calculations:**

   - What needs to be calculated?
   - What are the dependencies?
   - What are the formulas?

2. **Use Formula Library:**

   - Check `v2/systems/excel-template-generator/data/template-formulas/hr-formulas.json`
   - Check `v2/systems/excel-template-generator/data/template-formulas/advanced-formulas.json`
   - Reuse existing formulas when possible

3. **Plan Error Handling:**

   - Use `WENNFEHLER` for all formulas that might error
   - Provide default values
   - Handle edge cases

4. **Use Named Ranges:**
   - Define named ranges for complex references
   - Improve formula readability
   - Make templates more maintainable

### 2.3 Data Validation Planning

**Goal:** Plan validation rules.

**Types of Validation:**

- **Dropdown Lists:** Constrained choices
- **Date Ranges:** Valid date ranges
- **Number Ranges:** Min/max values
- **Custom Formulas:** Complex validation rules

**German Requirements:**

- Error messages in German
- Input prompts in German
- Clear, helpful messages

### 2.4 Visualization Planning

**Goal:** Plan charts and conditional formatting.

**Charts:**

- What data should be visualized?
- What chart types are appropriate?
- Where should charts be placed?

**Conditional Formatting:**

- What status indicators are needed?
- What error highlighting?
- What success indicators?

## Phase 3: Preprocessing Phase (NEW)

### 3.1 Template Inheritance

**Goal:** Use base templates for consistency.

**Steps:**

1. **Create Base Template:**
   - Define common structure, styles, formulas
   - Save to `v2/systems/excel-template-generator/data/template-definitions/base/`
2. **Extend Base Template:**
   ```json
   {
     "extends": "base-shift-planning",
     "metadata": {
       "name": "Advanced Shift Planning"
     }
   }
   ```

**Benefits:**

- Consistent structure across templates
- Shared formulas and styles
- Easier maintenance

### 3.2 Reusable Components

**Goal:** Use pre-built components for common features.

**Available Components:**

- `compliance-arbzg-check` - ArbZG compliance formulas and validation
- `instructions-sheet` - Standard instructions sheet template

**Usage:**

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

**See:** `v2/systems/excel-template-generator/data/template-components/` for all available components

### 3.3 Template Parameters

**Goal:** Make templates customizable.

**Define Parameters:**

```json
{
  "parameters": [
    {
      "id": "team_size",
      "type": "number",
      "name": "Team Size",
      "min": 1,
      "max": 50,
      "default": 10
    }
  ]
}
```

**Use in Template:**

- Reference as `{team_size}` in formulas
- Parameters are validated automatically

## Phase 4: Implementation Phase

### 4.1 Branding Decision Tree

**When selecting styles:**

```
Need to style a cell?
│
├─ What type of cell?
│  ├─ Header (row 1) → Use 'header' or 'header_light' preset
│  ├─ Title → Use 'title' preset
│  ├─ Subtitle → Use 'subtitle' preset
│  ├─ Formula → Use 'formula_cell' preset
│  ├─ Data entry → Use 'data_cell' or 'input_cell' preset
│  ├─ Total row → Use 'total_row' preset
│  ├─ Status OK → Use 'success_cell' preset
│  ├─ Status Warning → Use 'warning_cell' preset
│  ├─ Status Error → Use 'error_cell' preset
│  └─ Other → Use appropriate preset or custom style
│
├─ Custom style needed?
│  ├─ Yes → Check branding validator first
│  │  └─ Use branding-auto-fixer.php if violations found
│  └─ No → Use preset (recommended)
│
└─ Color selection
   ├─ Primary brand color → #4D8EF3
   ├─ Success → #10B981
   ├─ Warning → #F59E0B
   ├─ Error → #EF4444
   └─ Text → #374151 (primary), #6B7280 (secondary)
```

### 4.2 Component Selection Decision Tree

**When to use components:**

```
Need functionality?
│
├─ Compliance check?
│  ├─ ArbZG → Use 'compliance-arbzg-check' component
│  └─ Mindestlohn → Use 'compliance-mindestlohn-check' component
│
├─ Calculation block?
│  ├─ Payroll → Use 'payroll-calculation-block' component
│  ├─ Vacation → Use 'vacation-balance-tracker' component
│  ├─ Sick leave → Use 'sick-leave-tracker' component
│  ├─ Shift costs → Use 'shift-cost-calculator' component
│  └─ Time calculations → Use 'time-calculation-block' component
│
├─ Sheet needed?
│  ├─ Instructions → Use 'instructions-sheet' component
│  ├─ Dashboard → Use 'dashboard-sheet' or 'kpi-dashboard' component
│  ├─ Employee list → Use 'employee-list-table' component
│  └─ Performance → Use 'performance-scorecard' component
│
├─ Chart needed?
│  ├─ Bar chart → Use 'chart-bar-template' component
│  ├─ Line chart → Use 'chart-line-template' component
│  └─ Pie chart → Use 'chart-pie-template' component
│
├─ Validation needed?
│  └─ Use 'data-validation-rules' component
│
├─ Formatting needed?
│  ├─ Status indicators → Use 'conditional-formatting-status' component
│  └─ Totals row → Use 'summary-totals-row' component
│
└─ Date range?
   └─ Use 'date-range-selector' component
```

### 4.3 Chart Selection Decision Tree

**When to add charts:**

```
Need visualization?
│
├─ What data pattern detected?
│  ├─ Time Series → Line chart (trends over time)
│  ├─ Categorical (≤5 categories) → Pie chart or bar chart
│  ├─ Categorical (>5 categories) → Bar chart
│  ├─ Comparison → Bar chart or column chart
│  └─ Mixed → Bar chart (most flexible)
│
├─ Use chart selector?
│  ├─ Yes → Use OrdioChartSelector.recommendCharts()
│  │  └─ Follow recommendations
│  └─ No → Use decision tree above
│
├─ Chart placement?
│  └─ Use OrdioChartPlacer to optimize placement
│
└─ Color palette?
   ├─ Professional → Use 'professional' palette
   ├─ Status → Use 'status' palette
   ├─ Categorical → Use 'categorical' palette
   └─ Default → Use 'default' palette
```

## Phase 4: Implementation Phase (Original)

### 4.1 Create Template Definition

**Goal:** Create JSON template definition.

**Steps:**

1. **Start with Metadata:**

   ```json
   {
     "metadata": {
       "name": "Template Name",
       "category": "shift_planning",
       "version": "1.0.0",
       "description": "Template description",
       "author": "Ordio GmbH",
       "created_date": "2025-11-20",
       "use_cases": ["Use case 1", "Use case 2"],
       "tags": ["tag1", "tag2"]
     }
   }
   ```

2. **Define Sheets:**

   - Follow planned structure
   - Use appropriate sheet types
   - Set sheet order

3. **Add Cells:**

   - Define all cells with addresses
   - Set values, formulas, styles
   - Add comments where helpful

4. **Add Formulas:**

   - Reference formula library
   - Use named ranges
   - Add error handling

5. **Add Validation:**

   - Define validation rules
   - Add German error messages
   - Add input prompts

6. **Add Conditional Formatting:**
   - Define formatting rules
   - Use Ordio colors
   - Apply to appropriate ranges

**Reference:**

- `v2/systems/excel-template-generator/data/template-definitions/schema.json` - Complete schema
- `v2/systems/excel-template-generator/data/template-definitions/examples/` - Example templates

### 4.2 Generate Template

**Goal:** Generate Excel file.

**Command:**

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

**Or use PHP directly:**

```php
require_once 'v2/systems/excel-template-generator/helpers/template-generator.php';
$templateJson = file_get_contents('template.json');
$generator = new OrdioTemplateGenerator($templateJson);
$spreadsheet = $generator->generate();
$generator->save('output.xlsx');
```

### 3.3 Enhance with Python (if needed)

**Goal:** Add advanced features (charts, pivot tables).

**Command:**

```bash
python3 v2/scripts/enhance-template.py output.xlsx enhancements.json enhanced.xlsx
```

**When to Use:**

- Need charts
- Need pivot tables
- Need sparklines
- Need advanced conditional formatting

## Phase 5: Validation Phase

### 5.1 Schema Validation

**Command:**

```bash
php v2/systems/excel-template-generator/scripts/template-validator.php template.json generated.xlsx
```

**Checks:**

- JSON schema compliance
- Formula syntax
- Data validation rules
- Conditional formatting
- German locale compliance

### 5.2 Branding Validation (NEW)

**Command:**

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

**Checks:**

- Color compliance (Ordio brand colors only)
- Font usage (Inter/Gilroy-Bold only)
- Style preset usage
- Chart color palettes

**Score:** 0-100 (must be >= 80 for approval)

### 5.3 Formula Quality Check (NEW)

**Command:**

```bash
php v2/systems/excel-template-generator/scripts/formula-quality-checker.php template.json
```

**Checks:**

- Error handling (WENNFEHLER)
- German locale (German function names)
- Volatile functions (avoid JETZT, HEUTE)
- Performance (INDEX/MATCH vs SVERWEIS)

**Score:** 0-100 (must be >= 80 for approval)

### 5.4 Visualization Quality Check (NEW)

**Command:**

```bash
php v2/systems/excel-template-generator/scripts/visualization-quality-checker.php template.json
```

**Checks:**

- Chart titles present
- Color palettes defined
- Chart placement optimized
- No overlaps

**Score:** 0-100 (must be >= 80 for approval)

### 5.5 Quality Gates (NEW)

**Command:**

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

**Gates:**

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

**All gates must pass for approval.**

### 4.1 Schema Validation

**Goal:** Validate template definition structure.

**Command:**

```bash
php v2/systems/excel-template-generator/scripts/template-validator.php template.json --verbose
```

**Checks:**

- Required fields present
- Valid data types
- Valid references
- Schema compliance

### 4.2 Formula Validation

**Goal:** Validate formulas.

**Checks:**

- Formula syntax correct
- References valid
- Error handling present
- German function names (recommended)

### 4.3 Excel File Validation

**Goal:** Validate generated Excel file.

**Command:**

```bash
python3 v2/scripts/validate-template.py output.xlsx --verbose
```

**Checks:**

- File integrity
- Formulas work correctly
- Charts present (if defined)
- Data validation rules applied
- Branding compliance

## Phase 6: Quality Assurance Phase

### 6.1 Quality Scoring (ENHANCED)

**Command:**

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

**New Scoring Dimensions:**

- Branding Compliance (10 points)
- Formula Quality (10 points)
- Visualization Appropriateness (10 points)
- Customizability (10 points)
- Modularity (10 points)

**Total Score:** 150 points (was 100)

**Grade Thresholds:**

- A: >= 90%
- B: >= 80%
- C: >= 70%
- D: >= 60%
- F: < 60%

### 6.2 Improvement Suggestions (ENHANCED)

**Command:**

```bash
php v2/systems/excel-template-generator/scripts/template-improvement-suggestions.php template.json
```

**New Categories:**

- Branding improvements
- Formula quality improvements
- Visualization enhancements
- Customizability enhancements
- Modularity improvements
- Documentation additions

### 6.3 Competitive Analysis Integration (NEW)

Quality scorer automatically compares against competitive analysis:

- Feature gaps
- Best practices implementation
- Quality comparison

**Location:** `v2/systems/excel-template-generator/data/template-research/competitive-analysis/`

### 5.1 Quality Scoring

**Goal:** Score template quality.

**Command:**

```bash
php v2/systems/excel-template-generator/scripts/template-quality-scorer.php template.json --benchmark --verbose
```

**Scoring Categories:**

- 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 & Optimization (5 points)
- Accessibility (5 points)

**Target:** Score ≥ 80% (Grade B or higher)

### 5.2 Quality Testing

**Goal:** Run comprehensive tests.

**Command:**

```bash
php v2/scripts/test-template-quality.php --template=template.json --verbose
```

**Tests:**

- Template generation
- Quality scoring
- Excel compatibility
- Formula testing
- Validation

### 5.3 Manual Testing

**Goal:** Test in real Excel.

**Steps:**

1. Open generated file in Excel
2. Test all data entry areas
3. Verify formulas calculate correctly
4. Test data validation rules
5. Check conditional formatting
6. Verify charts (if present)
7. Test in Google Sheets (optional)
8. Test in LibreOffice (optional)

## Phase 7: Documentation Phase

### 6.1 Template Documentation

**Goal:** Document the template.

**Required:**

- Template description
- Use cases
- Instructions (in instructions sheet)
- Formula documentation
- Compliance notes

### 6.2 Registry Update

**Goal:** Add to template registry.

**File:** `v2/systems/excel-template-generator/data/template-registry.json`

**Steps:**

1. Add template entry
2. Update category counts
3. Add metadata
4. Link to template file

### 6.3 Create Examples

**Goal:** Create example usage.

**Optional:**

- Example data
- Screenshots
- Usage tutorial
- Video walkthrough

## Decision Trees Summary

### Quick Reference: When to Use What

**Branding:**

- Always use presets when possible
- Run branding-auto-fixer.php if violations found
- Check color consistency across sheets

**Formulas:**

- Use formula-pattern-detector.php to detect patterns
- Use formula-recommender.php for recommendations
- Check competitive analysis for competitor formulas
- Always wrap in WENNFEHLER for error handling

**Charts:**

- Use chart-selector.php for recommendations
- Use chart-placer.php for optimal placement
- Always include title and legend

**Components:**

- Check component library first before building custom
- Use components for common patterns
- 19 components available - check README.md

**Quality:**

- Run all quality gates before approval
- Fix branding violations automatically
- Review competitive benchmarking
- Ensure score ≥ 80%

## Common Pitfalls

### Pitfall 1: Missing Error Handling

**Problem:** Formulas without `WENNFEHLER` can show errors.

**Solution:** Always wrap formulas in `WENNFEHLER`:

```json
{
  "formula": "=WENNFEHLER(SUMME(A:A);0)"
}
```

### Pitfall 2: Hard-Coded Values

**Problem:** Values hard-coded in formulas make templates inflexible.

**Solution:** Use named ranges or reference cells:

```json
{
  "formula": "=WENNFEHLER(A2*Mindestlohn;0)"
}
```

### Pitfall 3: Missing German Locale

**Problem:** English function names don't work in German Excel.

**Solution:** Use German function names:

- `SUMME` instead of `SUM`
- `WENN` instead of `IF`
- `SVERWEIS` instead of `VLOOKUP`

### Pitfall 4: Poor Sheet Organization

**Problem:** Unclear structure confuses users.

**Solution:** Follow standard sheet order and naming conventions.

### Pitfall 5: Missing Instructions

**Problem:** Users don't know how to use the template.

**Solution:** Always include an instructions sheet.

### Pitfall 6: Inconsistent Branding

**Problem:** Templates don't match Ordio branding.

**Solution:** Use branding presets from `template-branding.php`.

### Pitfall 7: Missing Validation

**Problem:** Users can enter invalid data.

**Solution:** Add data validation rules with German error messages.

### Pitfall 8: No Compliance Checks

**Problem:** Templates don't validate German HR compliance.

**Solution:** Add ArbZG and minimum wage validation.

## Examples

### Example 1: Simple Shift Planning Template

**Use Case:** Basic weekly shift planning

**Sheets:**

1. Anleitung - Instructions
2. Schichtplan - Shift schedule (data entry)
3. Übersicht - Summary (calculations)

**Key Features:**

- Weekly grid layout
- Employee dropdowns
- Shift time validation
- Hours calculation
- Compliance checks

**Formulas:**

- Working hours calculation
- Break validation
- ArbZG compliance check

### Example 2: Advanced Payroll Template

**Use Case:** Comprehensive payroll calculation

**Sheets:**

1. Anleitung - Instructions
2. Mitarbeiterdaten - Employee data
3. Zeiterfassung - Time tracking
4. Berechnungen - Calculations
5. Abrechnung - Payroll report
6. Übersicht - Dashboard with charts

**Key Features:**

- Multiple data entry sheets
- Complex calculations
- Charts for visualization
- Compliance validation
- Detailed reports

**Advanced Features:**

- Charts (bar, line)
- Conditional formatting
- Named ranges
- Data validation
- Pivot tables (optional)

## Quick Reference

### Common Commands

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

# Validate template
php v2/systems/excel-template-generator/scripts/template-validator.php template.json output.xlsx --verbose

# Score quality
php v2/systems/excel-template-generator/scripts/template-quality-scorer.php template.json --benchmark --csv

# Test quality
php v2/scripts/test-template-quality.php --template=template.json --verbose

# Enhance with Python
python3 v2/scripts/enhance-template.py output.xlsx enhancements.json enhanced.xlsx
```

### File Locations

- **Template Definitions:** `v2/systems/excel-template-generator/data/template-definitions/`
- **Examples:** `v2/systems/excel-template-generator/data/template-definitions/examples/`
- **Formula Library:** `v2/systems/excel-template-generator/data/template-formulas/`
- **Branding Config:** `v2/systems/excel-template-generator/config/template-branding.php`
- **Schema:** `v2/systems/excel-template-generator/data/template-definitions/schema.json`
- **Registry:** `v2/systems/excel-template-generator/data/template-registry.json`

### Key Resources

- **Best Practices:** `docs/systems/excel-generator/BEST_PRACTICES_GUIDE.md`
- **Schema Reference:** `v2/systems/excel-template-generator/data/template-definitions/schema.json`
- **Formula Library:** `v2/systems/excel-template-generator/data/template-formulas/hr-formulas.json`
- **Python Setup:** `docs/systems/excel-generator/PYTHON_SETUP.md`

## Next Steps

After completing this workflow:

1. Review generated template
2. Test in Excel
3. Get feedback (if possible)
4. Iterate based on feedback
5. Publish template

## Support

For questions or issues:

1. Check documentation in `docs/systems/excel-generator/`
2. Review example templates
3. Check schema for structure
4. Review best practices guide