# Streaming for Large Files

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

## Overview

The template generator supports streaming for large Excel files to minimize memory usage and improve performance when generating templates with 1000+ rows.

## Features

- **Memory-Efficient Generation**: Streams data directly to output or file
- **Automatic Detection**: Automatically uses streaming for files >5MB
- **Progress Tracking**: Optional progress callbacks during generation
- **Disk Caching**: Uses PhpSpreadsheet's disk caching for large files
- **Chunked Output**: Streams file in 8KB chunks for HTTP downloads

## Usage

### Automatic Streaming

Streaming is automatically enabled when:

- Template size is estimated to be >5MB
- `?stream=true` parameter is provided in API request

```php
// API endpoint automatically detects large files
POST /v2/systems/excel-template-generator/api/generate.php?stream=true&download=true
```

### Manual Streaming

```php
require_once __DIR__ . '/helpers/template-generator.php';

$generator = new OrdioTemplateGenerator($templateDefinition);
$generator->generate();

// Stream to file
$generator->saveToStream('/path/to/output.xlsx');

// Stream to output (HTTP download)
$generator->streamToOutput('template.xlsx');

// With progress callback
$progressCallback = function($percentage, $stage) {
    echo "Progress: {$percentage}% ({$stage})\n";
};

$generator->saveToStream('/path/to/output.xlsx', $progressCallback);
```

## API Endpoints

### Streaming Download

```
POST /v2/systems/excel-template-generator/api/generate.php?stream=true&download=true
```

Streams the file directly to the browser without loading it entirely into memory.

### Streaming with Progress

For progress tracking, use the progress callback in the generator:

```php
$generator->saveToStream($filename, function($percentage, $stage) {
    // Update progress indicator
    updateProgress($percentage, $stage);
});
```

## Performance Benefits

### Memory Usage

- **Standard Save**: Loads entire file into memory
- **Streaming Save**: Uses disk caching, minimal memory footprint
- **Memory Reduction**: Up to 80% reduction for large files

### Generation Time

- **Standard Save**: Faster for small files (<1MB)
- **Streaming Save**: Better for large files (>5MB)
- **Threshold**: Automatic switching at 5MB

## Testing

### Test Script

```bash
# Test with 1000 rows
php v2/scripts/test-streaming.php 1000

# Test with 5000 rows
php v2/scripts/test-streaming.php 5000

# Test with 10000 rows
php v2/scripts/test-streaming.php 10000
```

### Expected Results

For 1000 rows:

- File size: ~500KB - 1MB
- Generation time: 1-3 seconds
- Memory usage: <50MB

For 10000 rows:

- File size: ~5-10MB
- Generation time: 10-30 seconds
- Memory usage: <100MB (with streaming)

## Implementation Details

### Disk Caching

PhpSpreadsheet's disk caching is enabled for streaming:

- Cache method: `CACHE_TO_DISK`
- Cache time: 600 seconds (10 minutes)
- Temporary files: Automatically cleaned up

### Chunked Output

For HTTP streaming:

- Chunk size: 8KB
- Output buffering: Flushed after each chunk
- Content-Length: Set before streaming starts

### Memory Monitoring

The generator tracks:

- Peak memory usage
- Memory used during generation
- Memory freed after completion

## Best Practices

1. **Use Streaming for Large Files**: Enable streaming for templates with 1000+ rows
2. **Monitor Memory**: Track memory usage during generation
3. **Set Time Limits**: Increase `max_execution_time` for large files
4. **Progress Feedback**: Provide progress updates for long-running generations
5. **Error Handling**: Handle timeouts and memory errors gracefully

## Limitations

- **PhpSpreadsheet Constraints**: PhpSpreadsheet still loads some data into memory
- **Formula Calculation**: Complex formulas may require more memory
- **Style Caching**: Style cache is kept in memory (minimal impact)

## Troubleshooting

### Memory Errors

If you encounter memory errors:

1. Enable streaming mode explicitly
2. Increase PHP memory limit
3. Reduce template complexity
4. Process in batches

### Timeout Errors

For very large files:

1. Increase `max_execution_time`
2. Use background job processing
3. Consider splitting into multiple files

### Performance Issues

If streaming is slow:

1. Check disk I/O performance
2. Verify cache directory permissions
3. Monitor system resources
4. Consider using SSD storage