Getting Started

Minimal Steps

Installation:

composer require bassim/big-xlsx-bundle:dev-master

Bassim\BigXlsxBundle\BassimBigXlsxBundle::class => ['all' => true],

First Use Case: Generate a simple XLSX with one sheet:

use Symfony\Component\HttpFoundation\Response;
use Bassim\BigXlsxBundle\Service\BigXlsxService;

class ExportController extends Controller
{
    public function exportAction(BigXlsxService $service)
    {
        $data = [["ID", "Name"], [1, "Test"]];
        $service->addSheet(0, "Sheet1", $data);
        $file = $service->getFile();

        return new Response(
            $file,
            200,
            ['Content-Type' => 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet']
        );
    }
}

Where to Look First

Service: BigXlsxService is the core class for sheet management.
README: Focus on Basic Usage and Adding a custom sheet sections.
Dependencies: Understand it wraps phpexcel (now phpoffice/phpexcel) for low-memory operations.

Implementation Patterns

Core Workflow

Initialize Service: Inject BigXlsxService into your controller/service.
```
$service = $this->get('bassim_big_xlsx.service');
```

Add Sheets: Use addSheet(index, title, data) for structured data.

$service->addSheet(0, "Users", [
    ["id", "name", "email"],
    [1, "John", "john@example.com"],
    [2, "Jane", "jane@example.com"]
]);

Custom Sheets: Access the underlying PHPExcel object for advanced formatting:

$phpExcel = $service->getPHPExcel();
$sheet = $phpExcel->createSheet(1);
$sheet->setCellValue('A1', 'Custom Data');

Streaming Large Data: For millions of rows, chunk data into batches (e.g., 10,000 rows per sheet):

$batchSize = 10000;
$users = User::all()->toArray();
foreach (array_chunk($users, $batchSize) as $index => $batch) {
    $service->addSheet($index, "Users_$index", $batch);
}

Output: Stream the file directly to the client:

return new StreamedResponse(
    function () use ($service) {
        echo $service->getFile();
    },
    200,
    ['Content-Type' => 'application/vnd.ms-excel']
);

Integration Tips

Symfony Forms: Use FormType to collect data before exporting.
Doctrine: Fetch entities in chunks with ->chunkBy() to avoid memory overload.
Queues: Offload generation to a background job (e.g., Symfony Messenger) for very large exports.

Twig: Pass the service to templates for dynamic exports:

{% extends 'base.html.twig' %}
{% block body %}
    <a href="{{ path('export_users') }}">Export Users</a>
{% endblock %}

Gotchas and Tips

Pitfalls

Memory Leaks:
- Issue: phpexcel (underlying library) can still consume high memory if not managed.
- Fix: Use addSheet() for large datasets instead of manually adding rows to a single sheet.
- Workaround: Clear PHPExcel objects after use:
```
$phpExcel = $service->getPHPExcel();
// ... custom operations ...
unset($phpExcel); // Free memory
```

Sheet Index Conflicts:

Issue: Adding sheets with duplicate indices silently overwrites them.

Fix: Validate indices before adding:

if ($service->getPHPExcel()->getSheetCount() > $index) {
    throw new \RuntimeException("Sheet index $index already exists.");
}

Deprecated phpexcel:
- Issue: The bundle uses codeplex/phpexcel (now phpoffice/phpexcel), which is unmaintained.
- Fix: Fork the bundle and update dependencies or use alternatives like box/spout for modern projects.

Encoding Issues:

Issue: Non-ASCII characters (e.g., é, ü) may render incorrectly.

Fix: Set encoding explicitly:

$phpExcel->getProperties()
    ->setCreator("Your Name")
    ->setTitle("Report")
    ->setSubject("Encoded Report")
    ->setDescription("UTF-8 encoded");

Large File Handling:
- Issue: Files >100MB may time out or fail to download.
- Fix: Use chunked streaming and compression:
```
$response = new Response($file);
$response->headers->set('Content-Encoding', 'gzip');
```

Debugging Tips

Log PHPExcel Warnings: Enable PHPExcel logging to diagnose issues:

$phpExcel = $service->getPHPExcel();
$phpExcel->getLogger()->setLogFile('phpunit.xml'); // Or any writable path

Validate Data Structure: Ensure all rows in $data have the same number of columns:

$columnCount = count($data[0]);
foreach ($data as $row) {
    if (count($row) !== $columnCount) {
        throw new \InvalidArgumentException("Row has invalid column count.");
    }
}

Check File Permissions: If using getFile() to save locally, ensure the directory is writable:

$filePath = $service->getFile();
if (!file_exists(dirname($filePath))) {
    mkdir(dirname($filePath), 0777, true);
}

Extension Points

Custom Styling: Extend the service to add default styles:

// In a custom service class
class ExtendedBigXlsxService extends BigXlsxService
{
    public function addSheet($index, $title, $data)
    {
        parent::addSheet($index, $title, $data);
        $sheet = $this->getPHPExcel()->getActiveSheet();
        $sheet->getStyle('A1:Z1')->applyFromArray([
            'font' => ['bold' => true],
            'alignment' => ['horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER]
        ]);
    }
}

Batch Processing: Create a decorator to handle pagination:

class PaginatedBigXlsxService
{
    protected $service;

    public function __construct(BigXlsxService $service)
    {
        $this->service = $service;
    }

    public function addPaginatedSheet($index, $title, $query, $perPage = 10000)
    {
        $page = 1;
        while (true) {
            $data = $query->skip(($page - 1) * $perPage)
                ->take($perPage)
                ->get()
                ->toArray();
            if (empty($data)) break;
            $this->service->addSheet($index + $page, "$title (Page $page)", $data);
            $page++;
        }
    }
}

Event Listeners: Trigger events before/after sheet addition:

# config/services.yaml
services:
    App\EventListener\XlsxSheetListener:
        tags:
            - { name: kernel.event_listener, event: big_xlsx.sheet_added, method: onSheetAdded }

// src/EventListener/XlsxSheetListener.php
class XlsxSheetListener
{
    public function onSheetAdded(SheetAddedEvent $event)
    {
        // Modify $event->getData() or $event->getSheet()
    }
}

Fallback to CSV: Implement a fallback for unsupported environments:

try {
    $file = $service->getFile();
} catch (\Exception $e) {
    // Fallback to CSV
    $csv = fopen('php://temp', 'w');
    fputcsv($csv, array_keys($data[0]));
    foreach ($data as $row) {
        fputcsv($csv, $row);
    }
    rewind($csv);
    return new Response(stream_get_contents($csv),

Big Xlsx Bundle Laravel Package