PHP code example of kolay / xlsx-stream

    
        
<?php
require_once('vendor/autoload.php');

/* Start to develop here. Best regards https://php-download.com/ */

    

kolay / xlsx-stream example snippets

use Kolay\XlsxStream\Writers\SinkableXlsxWriter;
use Kolay\XlsxStream\Sinks\FileSink;

// Create writer with file sink
$sink = new FileSink('/path/to/output.xlsx');
$writer = new SinkableXlsxWriter($sink);

// Set headers
$writer->startFile(['Name', 'Email', 'Phone']);

// Write rows
$writer->writeRow(['John Doe', '[email protected]', '+1234567890']);
$writer->writeRow(['Jane Smith', '[email protected]', '+0987654321']);

// Or write multiple rows at once
$writer->writeRows([
    ['Bob Johnson', '[email protected]', '+1111111111'],
    ['Alice Brown', '[email protected]', '+2222222222'],
]);

// Finish and close file
$stats = $writer->finishFile();

echo "Generated {$stats['rows']} rows in {$stats['sheets']} sheet(s)";

use Kolay\XlsxStream\Writers\SinkableXlsxWriter;
use Kolay\XlsxStream\Sinks\S3MultipartSink;
use Aws\S3\S3Client;

// Create S3 client
$s3Client = new S3Client([
    'region' => 'us-east-1',
    'version' => 'latest',
    'credentials' => [
        'key' => env('AWS_ACCESS_KEY_ID'),
        'secret' => env('AWS_SECRET_ACCESS_KEY'),
    ],
]);

// Create S3 sink with 32MB parts
$sink = new S3MultipartSink(
    $s3Client,
    'my-bucket',
    'exports/report.xlsx',
    32 * 1024 * 1024 // 32MB parts for optimal performance
);

$writer = new SinkableXlsxWriter($sink);

// Configure for maximum performance
$writer->setCompressionLevel(1)      // Fastest compression
       ->setBufferFlushInterval(10000); // Flush every 10K rows

$writer->startFile(['ID', 'Name', 'Email', 'Status']);

// Stream millions of rows with constant 32MB memory
User::query()
    ->select(['id', 'name', 'email', 'status'])
    ->chunkById(1000, function ($users) use ($writer) {
        foreach ($users as $user) {
            $writer->writeRow([
                $user->id,
                $user->name,
                $user->email,
                $user->status
            ]);
        }
    });

$stats = $writer->finishFile();

namespace App\Jobs;

use Illuminate\Bus\Queueable;
use Illuminate\Contracts\Queue\ShouldQueue;
use Illuminate\Foundation\Bus\Dispatchable;
use Illuminate\Queue\InteractsWithQueue;
use Illuminate\Queue\SerializesModels;
use Kolay\XlsxStream\Writers\SinkableXlsxWriter;
use Kolay\XlsxStream\Sinks\S3MultipartSink;
use Aws\S3\S3Client;
use App\Models\User;

class ExportUsersJob implements ShouldQueue
{
    use Dispatchable, InteractsWithQueue, Queueable, SerializesModels;

    public function handle()
    {
        // Create S3 client from Laravel config
        $s3Config = config('filesystems.disks.s3');
        $s3Client = new S3Client([
            'region' => $s3Config['region'],
            'version' => 'latest',
            'credentials' => [
                'key' => $s3Config['key'],
                'secret' => $s3Config['secret'],
            ],
        ]);
        
        // Setup S3 streaming
        $filename = 'exports/users-' . now()->format('Y-m-d-H-i-s') . '.xlsx';
        $sink = new S3MultipartSink(
            $s3Client,
            $s3Config['bucket'],
            $filename,
            32 * 1024 * 1024
        );
        
        $writer = new SinkableXlsxWriter($sink);
        $writer->setCompressionLevel(1)
               ->setBufferFlushInterval(10000);
        
        // Export headers
        $writer->startFile([
            'ID',
            'Name',
            'Email',
            'Created At',
            'Status'
        ]);
        
        // Export data with chunking
        User::query()
            ->orderBy('id')
            ->chunkById(1000, function ($users) use ($writer) {
                foreach ($users as $user) {
                    $writer->writeRow([
                        $user->id,
                        $user->name,
                        $user->email,
                        $user->created_at->format('Y-m-d H:i:s'),
                        $user->status
                    ]);
                }
                
                // Clear Eloquent cache periodically (optional)
                // You can track rows externally if needed
            });
        
        $stats = $writer->finishFile();
        
        \Log::info('Export completed', [
            'filename' => $filename,
            'rows' => $stats['rows'],
            'sheets' => $stats['sheets'],
            'bytes' => $stats['bytes']
        ]);
    }
}

use Kolay\XlsxStream\Writers\SinkableXlsxWriter;

// Streams directly to S3 — credentials, region, bucket all from config
$writer = SinkableXlsxWriter::forDisk('s3', 'exports/users.xlsx');

// Or to a local disk — resolves to disk root + path
$writer = SinkableXlsxWriter::forDisk('local', 'exports/users.xlsx');

// Pass S3-specific options (ACL, ContentDisposition, Metadata) as the 3rd arg
$writer = SinkableXlsxWriter::forDisk('s3', 'reports/q4.xlsx', [
    'ACL' => 'public-read',
    'CacheControl' => 'max-age=3600',
]);

$writer->startFile(['ID', 'Name'])
       ->writeRow([1, 'Alice'])
       ->finishFile();

$writer = SinkableXlsxWriter::forDisk('s3', 'users-export.xlsx');
$writer->startFile(['ID', 'Name', 'Email', 'Created']);

$writer->writeRows(
    User::query()
        ->select(['id', 'name', 'email', 'created_at'])
        ->orderBy('id')
        ->lazy(1000)
        ->map(fn ($u) => [$u->id, $u->name, $u->email, $u->created_at])
);

$writer->finishFile();

function rowsFromApi(): Generator {
    $page = 1;
    while ($batch = Http::get('/api/orders', ['page' => $page++])->json()) {
        foreach ($batch as $order) {
            yield [$order['id'], $order['total'], $order['status']];
        }
    }
}

$writer->writeRows(rowsFromApi());

$writer = SinkableXlsxWriter::forDisk('s3', "exports/job-{$jobId}.xlsx");

$writer->onProgress(function (int $rows, int $bytes) use ($jobId) {
    Cache::put("export:{$jobId}", [
        'rows' => $rows,
        'bytes' => $bytes,
        'updated_at' => now(),
    ], 300);
})->setProgressInterval(5000);  // fire every 5K rows

$writer->startFile($headers);
$writer->writeRows($query->lazy());
$writer->finishFile();

$writer->writeRow([
    1,                                  // numeric
    'Acme Co.',                         // string
    new DateTime('2026-01-15 10:30'),   // date cell
    true,                               // boolean cell
    '12345678901234567890',             // big-int preserved as text
    '+90 555 123 4567',                 // phone preserved as text
]);

$writer = new SinkableXlsxWriter($sink);

$writer
    // Bold white text on dark blue, applied to the header row
    ->setHeaderStyle([
        'bold'  => true,
        'fill'  => '#4F81BD',
        'color' => '#FFFFFF',
        'size'  => 12,
    ])
    // Native Excel number formats per column (1-based index)
    ->setColumnFormat(1, 'integer')        // 12,345
    ->setColumnFormat(5, 'currency_try')   // ₺99,999.00
    ->setColumnFormat(6, 'percent')        // 12.50%
    ->setColumnFormat(7, 'date')           // 2026-01-15
    ->setColumnFormat(8, 'datetime')       // 2026-01-15 10:30:00
    ->setColumnFormat(9, '0.000000')       // raw Excel format code
    // Pin the header row, add filter dropdowns, auto-size columns
    ->freezeFirstRow()
    ->enableAutoFilter()
    ->setAutoColumnWidth();                // header-text + format-aware

$writer->startFile([
    'Order ID', 'Customer', 'Product', 'Region',
    'Price', 'Discount', 'Order Date', 'Created At', 'Score',
]);

$writer = new SinkableXlsxWriter($sink);

$writer->setHeaderStyle(['bold' => true, 'fill' => '#4F81BD', 'color' => '#FFFFFF']);
$writer->startFile(['ID', 'Name', 'Email']);

foreach ($users->lazy() as $user) {
    $writer->writeRow([$user->id, $user->name, $user->email]);
}

// Different header style + different columns for the next sheet
$writer
    ->clearColumnFormats()
    ->setHeaderStyle(['bold' => true, 'fill' => '#9BBB59', 'color' => '#FFFFFF'])
    ->setColumnFormat(3, 'currency_try')
    ->newSheet('Orders', ['Order ID', 'Customer', 'Total']);

foreach ($orders->lazy() as $order) {
    $writer->writeRow([$order->id, $order->customer_id, $order->total]);
}

$stats = $writer->finishFile();
// $stats['sheet_details'] → [['name' => 'Report', ...], ['name' => 'Orders', ...]]

$writer = new SinkableXlsxWriter($sink);
$writer->startFile(['Column1', 'Column2']);

// Write 2 million rows - will create 2 sheets automatically
for ($i = 1; $i <= 2000000; $i++) {
    $writer->writeRow(["Row $i", "Data $i"]);
}

$stats = $writer->finishFile();
// $stats['sheets'] = 2

// Ultra-fast mode for maximum speed
$writer->setCompressionLevel(1)        // Minimal compression
       ->setBufferFlushInterval(50000); // Large buffer

// Balanced mode (default)
$writer->setCompressionLevel(6)        // Balanced compression
       ->setBufferFlushInterval(10000); // Medium buffer

// Maximum compression (slower, smaller files)
$writer->setCompressionLevel(9)        // Maximum compression
       ->setBufferFlushInterval(1000);  // Small buffer for streaming

$sink = new S3MultipartSink(
    $s3Client,
    'my-bucket',
    'path/to/file.xlsx',
    32 * 1024 * 1024,
    [
        'ACL' => 'public-read',
        'ContentType' => 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
        'ContentDisposition' => 'attachment; filename="report.xlsx"',
        'Metadata' => [
            'generated-by' => 'kolay-xlsx-stream',
            'timestamp' => time()
        ]
    ]
);

try {
    $writer = new SinkableXlsxWriter($sink);
    $writer->startFile(['Column1', 'Column2']);
    
    // Write data...
    
    $stats = $writer->finishFile();
} catch (\Exception $e) {
    // The sink will automatically abort and cleanup on error
    // S3 multipart uploads are automatically aborted
    // Partial files are deleted
    
    \Log::error('Export failed: ' . $e->getMessage());
}

return [
    's3' => [
        'part_size' => 32 * 1024 * 1024,  // S3 multipart chunk size
        'retry_attempts' => 3,             // Retry failed uploads
        'retry_delay_ms' => 100,           // Delay between retries
    ],
    
    'writer' => [
        'compression_level' => 1,          // 1-9 (1=fastest, 9=smallest)
        'buffer_flush_interval' => 10000,  // Rows to buffer
        'max_rows_per_sheet' => 1048575,   // Excel limit - 1
    ],
    
    'memory' => [
        'file_buffer_size' => 1024 * 1024, // 1MB file write buffer
    ],
    
    'logging' => [
        'enabled' => true,
        'channel' => null,                  // null = default channel
        'log_progress' => false,            // Log progress updates
        'progress_interval' => 10000,       // Log every N rows
    ],
];

// Only these stay in memory:
$rowBuffer = '';        // Current batch of rows (flushed every 10K rows)
$deflateContext = ...;  // Compression context (minimal)
$zipMetadata = [];      // File entry metadata (bytes per file)

// Memory components:
$buffer = '';           // 32MB max (uploaded when full)
$parts = [];           // Part metadata (ETag + number per part)
// Growth: ~1KB per part × (filesize / 32MB) parts
// Example: 100MB file = 3 parts = ~3KB metadata

bash
php artisan vendor:publish --tag=xlsx-stream-config