# Chart Generation in Excel Templates

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

## Overview

Chart generation in Excel templates is supported through the template definition schema, but PhpSpreadsheet does not have native chart generation capabilities. Charts must be added manually in Excel or through a separate extension.

## Current Implementation

### Template Definition

Charts can be defined in template JSON files using the `charts` array in sheet definitions:

```json
{
  "sheets": [
    {
      "name": "Report",
      "charts": [
        {
          "type": "column",
          "title": "Monatliche Übersicht",
          "series": [
            {
              "name": "Umsatz",
              "categories": "A2:A13",
              "values": "B2:B13"
            }
          ],
          "position": "F1",
          "width": 600,
          "height": 400
        }
      ]
    }
  ]
}
```

### Chart Types Supported

- `column` - Column chart
- `bar` - Bar chart
- `line` - Line chart
- `pie` - Pie chart
- `scatter` - Scatter chart
- `area` - Area chart

### Chart Definition Schema

```json
{
  "type": "string (column|bar|line|pie|scatter|area)",
  "title": "string (Chart title)",
  "series": [
    {
      "name": "string (Series name)",
      "categories": "string (Cell range, e.g., A2:A13)",
      "values": "string (Cell range, e.g., B2:B13)"
    }
  ],
  "position": "string (Top-left cell, e.g., F1)",
  "width": "number (Width in pixels, default: 600)",
  "height": "number (Height in pixels, default: 400)",
  "colors": {
    "primary": "string (Hex color, default: Ordio blue)",
    "secondary": "string (Hex color)"
  }
}
```

## Manual Chart Addition

After generating an Excel template, charts can be added manually:

1. Open the generated Excel file
2. Select the data range for the chart
3. Insert → Chart → Choose chart type
4. Customize chart styling to match Ordio branding
5. Position chart according to `position` field in definition

## Future Enhancement: PhpSpreadsheet Charts Extension

If the PhpSpreadsheet Charts extension becomes available, chart generation can be automated:

1. Install extension: `composer require phpoffice/phpspreadsheet-charts`
2. Update `generateChart()` method in `template-generator.php`
3. Implement chart generation using extension API

## Ordio Branding for Charts

When adding charts manually, use Ordio brand colors:

- **Primary Color:** #4D8EF3 (Ordio Blue)
- **Secondary Color:** #6B7280 (Ordio Text Secondary)
- **Accent Color:** #10B981 (Ordio Success Green)
- **Background:** #FFFFFF (White)
- **Grid Lines:** #E5E7EB (Light Gray)

## Best Practices

1. **Chart Placement:** Place charts after data tables, typically starting at column F or later
2. **Chart Size:** Use standard sizes (600x400px) for consistency
3. **Data Labels:** Include data labels for clarity
4. **Legends:** Position legends appropriately (right or bottom)
5. **Titles:** Use descriptive German titles
6. **Colors:** Stick to Ordio brand colors for consistency

## Example Template with Chart

See `v2/systems/excel-template-generator/data/template-definitions/examples/shift-planning-basic.json` for an example template that includes chart definitions.

## Limitations

- Charts are not automatically generated - manual addition required
- Chart styling must be applied manually
- Chart interactivity (e.g., drill-down) requires manual setup
- Dynamic chart updates require Excel formulas or VBA

## Workaround

For templates requiring charts:

1. Generate template without charts
2. Add charts manually in Excel
3. Save as template file (.xltx)
4. Distribute template file instead of regenerating