# PhpSpreadsheet Capabilities Research

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

## Overview

This document contains comprehensive research on PhpSpreadsheet 5.5.0+ capabilities, limitations, and best practices for generating high-quality Excel templates.

## Current Version

- **Installed Version:** 5.5.0 (as per composer.json)
- **Latest Available:** Check GitHub for latest releases
- **Documentation:** https://phpspreadsheet.readthedocs.io/

## Core Features Supported

### 1. Basic Spreadsheet Operations

✅ **Fully Supported:**

- Create new spreadsheets
- Multiple worksheets
- Cell values (text, numbers, dates, formulas)
- Cell formatting (fonts, colors, borders, alignment)
- Row/column dimensions (width, height)
- Merge cells
- Named ranges
- Sheet protection
- Document properties (metadata)

### 2. Formulas

✅ **Fully Supported:**

- All Excel formulas (SUM, IF, VLOOKUP, INDEX, MATCH, etc.)
- German locale formulas (SUMME, WENN, SVERWEIS, etc.)
- Array formulas (legacy CSE arrays)
- Dynamic arrays (Excel 365) - Limited support
- Named ranges in formulas
- Cross-sheet references
- External references

**Limitations:**

- Dynamic arrays (SPILL functions) have limited support
- LAMBDA functions may not be fully supported
- Some newer Excel 365 functions may not work

### 3. Data Validation

✅ **Fully Supported:**

- List (dropdown)
- Whole number
- Decimal
- Date
- Time
- Text length
- Custom (formula-based)
- Input messages
- Error messages
- Error styles (stop, warning, information)

**Current Implementation:**

- All validation types implemented in `template-generator.php`
- Supports ranges and individual cells
- Custom error messages in German

### 4. Conditional Formatting

✅ **Partially Supported:**

**Basic Types:**

- Cell value conditions (equals, greater than, less than, etc.)
- Formula-based conditions
- Text contains, begins with, ends with
- Date is, duplicate values, unique values

**Advanced Types (Limited/Manual):**

- Color scales - Requires manual addition or extension
- Data bars - Requires manual addition or extension
- Icon sets - Requires manual addition or extension

**Current Implementation:**

- Basic conditional formatting fully implemented
- Advanced types documented but require manual addition
- See `ADVANCED_CONDITIONAL_FORMATTING.md` for details

### 5. Styling

✅ **Fully Supported:**

- Fonts (name, size, bold, italic, underline, color)
- Fill (solid colors, patterns)
- Borders (all sides, various styles)
- Alignment (horizontal, vertical, wrap text, shrink to fit)
- Number formats (currency, percentage, date, time, custom)
- Cell protection (locked, hidden)

**Current Implementation:**

- Style caching implemented for performance
- Preset styles (header, data_cell, formula_cell, etc.)
- Ordio branding colors integrated

### 6. Charts

❌ **Not Natively Supported:**

PhpSpreadsheet does NOT have native chart generation support. Charts must be:

1. Added manually in Excel after generation
2. Generated using PhpSpreadsheet Charts extension (if available)
3. Generated using alternative tools (Python openpyxl, Node.js ExcelJS)

**PhpSpreadsheet Charts Extension:**

- Status: Check GitHub for availability
- May require separate installation
- Limited documentation

**Current Implementation:**

- Chart definitions documented in template schema
- Placeholder notes added to generated templates
- See `CHART_GENERATION.md` for details

### 7. Pivot Tables

❌ **Not Supported:**

PhpSpreadsheet does NOT support pivot table generation. Pivot tables must be:

1. Created manually in Excel after generation
2. Generated using alternative tools (Python openpyxl, Node.js ExcelJS)

**Current Implementation:**

- Pivot table definitions documented in template schema
- Placeholder notes added to generated templates
- See `PIVOT_TABLES.md` for details

### 8. Excel Tables (Structured References)

⚠️ **Limited Support:**

PhpSpreadsheet has limited support for Excel Tables:

- Can create basic table structure
- Structured references may not work fully
- Table formatting may be limited

**Research Needed:**

- Test `addTable()` method if available
- Verify structured reference support
- Test table styling capabilities

### 9. Row/Column Grouping

✅ **Supported:**

PhpSpreadsheet supports row and column grouping:

- `getRowDimension()->setOutlineLevel()`
- `getColumnDimension()->setOutlineLevel()`
- Collapse/expand functionality
- Multiple outline levels

**Current Implementation:**

- Not yet implemented in template generator
- Can be added to schema and generator

### 10. Sparklines

❌ **Not Supported:**

PhpSpreadsheet does NOT support sparklines (in-cell mini charts). Sparklines must be:

1. Added manually in Excel
2. Generated using alternative tools (Python openpyxl)

**Alternative:**

- Use conditional formatting to simulate sparklines
- Use small charts positioned in cells
- Document sparkline requirements for manual addition

### 11. Slicers & Timelines

❌ **Not Supported:**

PhpSpreadsheet does NOT support slicers or timelines (interactive filtering). These must be:

1. Added manually in Excel
2. Generated using alternative tools (Python openpyxl with VBA)

**Workaround:**

- Use data validation dropdowns for filtering
- Document slicer requirements for manual addition

### 12. Macros/VBA

❌ **Not Supported:**

PhpSpreadsheet does NOT support VBA macros. Macros must be:

1. Added manually in Excel
2. Generated using alternative tools (Python xlwings, Node.js with VBA injection)

**Security Considerations:**

- Macros require user approval in Excel
- May trigger security warnings
- Not recommended for downloadable templates

## Performance Characteristics

### Memory Usage

**Large Files:**

- PhpSpreadsheet loads entire spreadsheet into memory
- Memory usage: ~1-2 MB per 1000 cells (with formatting)
- Can be optimized with cell caching

**Optimization Strategies:**

1. **Cell Caching:**

   ```php
   use PhpOffice\PhpSpreadsheet\Settings;
   use PhpOffice\PhpSpreadsheet\CachedObjectStorageFactory;

   $cacheMethod = CachedObjectStorageFactory::cache_to_discISAM;
   Settings::setCacheStorageMethod($cacheMethod);
   ```

2. **Read Filters:**

   - Load only necessary data when reading
   - Use `IReadFilter` interface

3. **Minimize Styling:**

   - Apply styles to ranges, not individual cells
   - Use style caching (already implemented)

4. **Chunked Writing:**
   - Write data in chunks for large datasets
   - Save incrementally if needed

### Execution Time

**Typical Performance:**

- Small templates (< 100 rows): < 0.1s
- Medium templates (100-500 rows): 0.1-1s
- Large templates (500-1000 rows): 1-5s
- Very large templates (1000+ rows): 5-30s (with caching)

**Bottlenecks:**

- Cell-by-cell operations
- Excessive styling
- Complex formulas
- Large merged cell ranges

## Limitations Summary

### Critical Limitations (No Workaround)

1. **Charts** - Must be added manually or use alternative tool
2. **Pivot Tables** - Must be added manually or use alternative tool
3. **Sparklines** - Must be added manually or use alternative tool
4. **Slicers/Timelines** - Must be added manually or use alternative tool
5. **VBA Macros** - Must be added manually or use alternative tool

### Partial Limitations (Workarounds Available)

1. **Advanced Conditional Formatting** - Can document requirements, manual addition
2. **Excel Tables** - Limited support, may need manual enhancement
3. **Dynamic Arrays** - Limited support, use legacy array formulas

### Performance Limitations

1. **Memory Usage** - Can be optimized with caching
2. **Large Files** - May require chunked processing
3. **Complex Formulas** - May slow down generation

## Best Practices

### 1. Formula Optimization

- Use named ranges for complex references
- Minimize volatile functions (NOW, TODAY, RAND)
- Use IFERROR/WENNFEHLER for error handling
- Prefer INDEX/MATCH over VLOOKUP for performance

### 2. Styling Optimization

- Apply styles to ranges, not individual cells
- Use style caching (already implemented)
- Minimize unique styles
- Use preset styles for consistency

### 3. Data Validation

- Use dropdown lists for user input
- Provide clear error messages
- Use custom formulas for complex validation

### 4. Conditional Formatting

- Use basic conditional formatting when possible
- Document advanced formatting requirements
- Provide clear instructions for manual addition

### 5. Template Structure

- Organize sheets logically
- Use clear sheet names (German, max 31 chars)
- Provide instructions sheet
- Use consistent styling throughout

## Recommendations

### For Maximum Quality Templates

1. **Use PhpSpreadsheet for Core Generation:**

   - Formulas, data validation, basic formatting
   - Structure and organization
   - Named ranges and references

2. **Use Alternative Tools for Advanced Features:**

   - Python openpyxl for charts
   - Python openpyxl for pivot tables
   - Document requirements for manual addition

3. **Hybrid Approach:**
   - Generate base template with PhpSpreadsheet
   - Enhance with Python/Node.js scripts for charts
   - Final polish in Excel if needed

### For Single Template Generation

Since we're generating one template per use case:

- Quality over speed
- Can use multiple tools
- Can do manual post-processing if needed
- Focus on best possible output

## Next Steps

1. Test Excel Tables support
2. Implement row/column grouping
3. Research PhpSpreadsheet Charts extension availability
4. Evaluate alternative tools for charts/pivot tables
5. Create integration strategy for hybrid approach