# Template Generation Guide

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

## Overview

This guide explains how to use the Ordio Excel Template Generator system to create high-quality, Ordio-branded Excel templates for HR and workforce management use cases.

## Quick Start

### 1. Create Template Definition

Create a JSON file following the schema in `v2/systems/excel-template-generator/data/template-definitions/schema.json`:

```json
{
  "metadata": {
    "name": "My Template",
    "category": "shift_planning",
    "version": "1.0.0",
    "description": "Template description"
  },
  "sheets": [
    {
      "name": "Übersicht",
      "type": "overview",
      "order": 0,
      "cells": [
        {
          "address": "A1",
          "value": "Meine Vorlage",
          "style": { "preset": "title" },
          "data_type": "text"
        }
      ]
    }
  ]
}
```

### 2. Generate Excel File

**PHP (Server-side):**

```php
require_once 'v2/systems/excel-template-generator/helpers/template-generator.php';

$templateJson = file_get_contents('template-definition.json');
$generator = new OrdioTemplateGenerator($templateJson);
$spreadsheet = $generator->generate();
$generator->save('output.xlsx');
```

**JavaScript (Client-side):**

```javascript
// Load SheetJS first
const templateJson = {...}; // Template definition
const generator = new OrdioTemplateGenerator(templateJson);
generator.generate();
generator.download('output.xlsx');
```

### 3. Validate Template

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

## Template Definition Structure

### Metadata

```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": "2025-11-20",
    "last_modified": "2025-11-20",
    "use_cases": ["Use case 1"],
    "tags": ["tag1", "tag2"]
  }
}
```

### Sheets

```json
{
  "sheets": [
    {
      "name": "Sheet Name",
      "type": "instructions|overview|data_entry|calculation|report|reference",
      "order": 0,
      "cells": [...],
      "rows": [...],
      "columns": [...],
      "named_ranges": [...],
      "data_validation": [...],
      "conditional_formatting": [...]
    }
  ]
}
```

### Cells

```json
{
  "cells": [
    {
      "address": "A1",
      "value": "Cell Value",
      "style": { "preset": "header" },
      "data_type": "text|number|date|time|currency|percentage|formula",
      "format": "#,##0.00 €",
      "locked": false,
      "comment": "Cell comment"
    }
  ]
}
```

### Formulas

```json
{
  "formulas": {
    "formula_id": {
      "formula": "SUMME(A1:A10)",
      "description": "Sum formula",
      "parameters": [
        {
          "name": "range",
          "type": "range",
          "description": "Cell range"
        }
      ]
    }
  }
}
```

## Using Formula Library

Reference formulas from `v2/systems/excel-template-generator/data/template-formulas/hr-formulas.json`:

```json
{
  "cells": [
    {
      "address": "C2",
      "value": {
        "formula_id": "working_hours_calculation",
        "parameters": {
          "ANFANG": "A2",
          "ENDE": "B2"
        }
      },
      "data_type": "formula"
    }
  ]
}
```

## 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."
    }
  ]
}
```

## Conditional Formatting

### Error Highlighting

```json
{
  "conditional_formatting": [
    {
      "range": "C2:C10",
      "type": "cellValue",
      "operator": "greaterThan",
      "formula": "10",
      "style": {
        "fill": { "color": "#EF4444" },
        "font": { "color": "#FFFFFF", "bold": true }
      }
    }
  ]
}
```

## Branding

Use default Ordio branding:

```json
{
  "branding": {
    "use_default": true
  }
}
```

Or customize:

```json
{
  "branding": {
    "use_default": false,
    "primary_color": "#4D8EF3",
    "font_family": "Inter"
  }
}
```

## 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

## API Usage

### List Templates

```bash
curl https://www.ordio.com/v2/systems/excel-template-generator/api/list.php?category=shift_planning
```

### Generate Template

```bash
curl -X POST https://www.ordio.com/v2/systems/excel-template-generator/api/generate.php \
  -H "Content-Type: application/json" \
  -d @template-definition.json \
  --output template.xlsx
```

## Best Practices

1. **Follow Schema:** Always validate against `schema.json`
2. **Use Formula Library:** Reference formulas from `hr-formulas.json`
3. **German Compliance:** Include ArbZG and minimum wage validations
4. **Consistent Branding:** Use style presets for consistency
5. **Error Handling:** Use IFERROR/WENNFEHLER in formulas
6. **Documentation:** Include instructions sheet
7. **Testing:** Validate and test in multiple Excel versions

## Troubleshooting

### Template Definition Errors

**Error:** "Invalid JSON"

- Check JSON syntax with validator
- Ensure all strings are properly quoted

**Error:** "Missing required field: metadata"

- Ensure metadata section exists
- Check required fields: name, category, version

**Error:** "Invalid cell address"

- Use format: A1, B2, etc. (letter + number)
- No spaces or special characters

### Generation Errors

**Error:** "PhpSpreadsheet library not found"

- Install via Composer: `composer require phpoffice/phpspreadsheet`
- Check autoload path

**Error:** "Failed to create Spreadsheet"

- Check PHP memory limit
- Verify PhpSpreadsheet installation

### Formula Errors

**Error:** "Formula not found"

- Check formula_id exists in formulas section
- Verify formula reference syntax

**Error:** "Invalid formula"

- Use German locale formulas (SUMME, WENN, etc.)
- Check parameter references

## References

- **Schema:** `v2/systems/excel-template-generator/data/template-definitions/schema.json`
- **Branding:** `v2/systems/excel-template-generator/config/template-branding.php`
- **Formulas:** `v2/systems/excel-template-generator/data/template-formulas/hr-formulas.json`
- **Best Practices:** `docs/systems/excel-generator/EXCEL_BEST_PRACTICES.md`
- **Quality Rubric:** `docs/systems/excel-generator/TEMPLATE_QUALITY_RUBRIC.md`