1. Go to this page and download the library: Download kolay/xlsx-stream library. Choose the download type require.
2. Extract the ZIP file and open the index.php.
3. Add this code to the index.php.
<?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 (default 8 MB parts, synchronous uploads = O(1) memory)
$sink = new S3MultipartSink(
$s3Client,
'my-bucket',
'exports/report.xlsx'
);
// On a HIGH-LATENCY link (cross-region, slow uplink) you can opt into a
// parallel upload window — it overlaps per-part round-trips at the cost of
// a higher (sawtooth) memory profile. On bandwidth-bound links it's no
// faster, so measure before flipping it on:
// new S3MultipartSink($s3Client, 'my-bucket', 'key.xlsx', concurrency: 4);
// (or set XLSX_STREAM_S3_CONCURRENCY=4 for forDisk() writers.)
$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 flat ~part-size memory (default ~8 MB)
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();
use Kolay\XlsxStream\Readers\StreamingXlsxReader;
// From a local file
foreach (StreamingXlsxReader::fromFile('/path/to/big.xlsx')->rows() as $row) {
DB::table('users')->insert($row);
}
// Directly from S3 — bounded RAM (~24 MB), no temp file
$reader = StreamingXlsxReader::fromS3($s3Client, 'my-bucket', 'imports/big.xlsx');
foreach ($reader->rows(skip: 1) as $row) { // skip the header row
User::create([
'id' => $row[0],
'name' => $row[1],
'email' => $row[2],
]);
}
// Bulk insert via chunked()
foreach ($reader->chunked(1000, skip: 1) as $batch) {
User::insert($batch);
}
// Producer side — opt-in once during configuration
$writer = new SinkableXlsxWriter(new FileSink('/path/to/report.xlsx'));
$writer->withRandomAccessIndex(every: 10000); // sync point every 10K rows
$writer->startFile(['ID', 'Name', 'Email']);
foreach ($users as $u) {
$writer->writeRow([$u->id, $u->name, $u->email]);
}
$writer->finishFile();
// Consumer side — same StreamingXlsxReader, gains rowAt / rowRange / O(1) rowCount
$reader = StreamingXlsxReader::fromFile('/path/to/report.xlsx');
$reader->rowCount(); // O(1) — read straight from the index header
$reader->rowAt(250_001); // O(period) — fresh inflate from nearest sync point
foreach ($reader->rowRange(100_000, 100_500) as $rowNumber => $row) {
// ... process 500 rows starting at row 100,000 without scanning the prefix
}
$writer->withRandomAccessIndex()
->withColumnStats([1, 4]); // 1-based: track "ID" and "Amount"
$reader = StreamingXlsxReader::fromS3($s3, 'bucket', 'huge-export.xlsx');
// 1. Aggregates straight from the ~KB sidecar — on S3 this is ONE
// range request against a multi-GB file:
$stats = $reader->columnStats(4);
// ['min' => ..., 'max' => ..., 'sum' => ..., 'avg' => ...,
// 'count' => ..., 'other' => ..., 'sorted' => 'asc'|'desc'|null]
// 2. Range queries that skip every block whose [min,max] can't match
// (exports are usually ID/date-sorted, so this touches a handful
// of blocks out of hundreds):
foreach ($reader->rowsWhere(4, 'between', 1000, 2000) as $rowNumber => $row) { ... }
foreach ($reader->rowsWhere(1, '>=', 4_000_000) as $rowNumber => $row) { ... }
// 3. Point lookups — on a sorted column this reads exactly one block,
// i.e. two S3 range requests end to end:
$hit = $reader->findRow(1, 3_141_592); // ['row' => N, 'values' => [...]] or null
// GROUP BY over S3 without reading interior blocks: on a sorted group
// column, group-pure blocks contribute their precomputed sums — only
// blocks straddling a group boundary are fetched. 20 groups over 1M
// rows: ~57 ms, interior blocks provably never read.
$byMonth = $reader->groupStats(groupBy: 6, aggregate: 5,
bucket: fn ($serial) => (int) ($serial / 30.44));
// Approximate analytics with ZERO row reads and ZERO extra requests —
// the answers live in the sidecar the reader already fetched at open:
$writer->withColumnSketches([4, 3]); // writer side, once
$reader->median(4); // p50 salary
$reader->quantile(4, 0.99); // p99 order amount
$reader->countDistinct(3); // ~distinct emails (±3% at 100K)
// One call makes an export fully queryable (index + zone maps + sketches):
$writer->queryable([1, 3, 4]); // writer side, before startFile()
// Multi-predicate AND — intersects each predicate's surviving blocks, so
// two differently-clustered columns read far fewer blocks than either alone:
$reader->rowsWhereAll([
['region', '=', 3],
['amount', 'between', 500, 5000],
]);
// ORDER BY amount DESC LIMIT 10 — on a sorted column, one seek + early exit:
$reader->topRows('amount', 10, desc: true);
// Plan a query WITHOUT running it — zero I/O, straight from the sidecar:
$reader->estimatedRows('amount', '>=', 1000); // ['upper' => .., 'estimate' => ..]
$reader->explain([['region', '=', 3], ['amount', '>=', 500]]);
// ['strategy' => 'zone-map-prune', 'candidateBlocks' => .., 'runs' => ..,
// 'estimatedRows' => [...], 'estimatedBytes' => ..] // the S3 range budget
// GROUP BY month over a date column — Bucket:: helpers keep the pushdown:
use Kolay\XlsxStream\Readers\Bucket;
$reader->groupStats('order_date', 'total', Bucket::month()); // one row per YYYYMM
// A uniform, reproducible random sample without a full scan (≈k block reads):
$reader->sampleRows(1000, seed: 42);
// Know when a query silently falls back to a full scan (no index for it):
$reader->onFullScan(fn (array $ctx) => logger()->warning('full scan', $ctx));
// Writer side, before startFile() — opt in to the extra sketches:
$writer->withColumnSketches([2, 3]) // t-digest + HyperLogLog (quantiles, distinct)
->withTopValues([4]) // frequent-items sketch (heavy hitters)
->withCorrelations([2, 3]) // pairwise Pearson co-moments
->withArgPointers([2]); // rows holding each block's min/max
// One call: a data-profiling report of a multi-GB S3 file, no row scan.
$profile = $reader->profile();
// ['data_rows' => .., 'columns' => [2 => ['name' => 'amount', 'min' => ..,
// 'percentiles' => ['p50' => ['value' => .., 'rank_lo' => .., 'rank_hi' => ..],
// 'p95' => [..]], 'histogram' => [..], 'distinct' => .., 'top_values' => ..]],
// 'correlations' => ['2,3' => 0.87]]
// A quantile that PROVES its number: the estimate fenced by a zone-map
// rank certificate (rank_hi − rank_lo is the residual uncertainty).
$reader->explainQuantile('amount', 0.95);
// ['estimate' => .., 'rank_lo' => .., 'rank_hi' => ..,
// 'exact_would_scan_blocks' => .., 'exact_est_bytes' => ..]
// The EXACT quantile — bracketed by the certificate, then only the blocks
// that could hold it are read; a budget degrades to the estimate, never throws:
$reader->exactQuantile('amount', 0.95, maxScanBlocks: 8);
// ['value' => .., 'exact' => true, 'blocksScanned' => 4, 'exceeded' => false]
// Distribution shape, heavy hitters, the row of an extreme, missing values:
$reader->histogram('amount', bins: 20); // {lo, hi, count} bins from the CDF
$reader->topValues('status'); // ['exact' => bool, 'values' => [..]]
$reader->argMax('amount'); // ['row' => 138, 'value' => 9999.0]
$reader->countEmpty('amount'); // non-numeric/missing data cells
$reader->correlation('amount', 'score'); // exact Pearson r
// Quantile of a ROW RANGE or a GROUP — from per-superblock digests. GROUP
// BY is numeric-keyed (a date column bucketed here); a string group key is
// a v3.5 STRZ candidate, so group by a numeric id or a Bucket:: helper:
use Kolay\XlsxStream\Readers\Bucket;
$reader->quantile('amount', 0.5, from: 1000, to: 5000);
$reader->groupQuantile('order_date', 'amount', 0.9, Bucket::month()); // p90 per month
// Scan tuning: late materialization is on by default when the planner
// predicts a selective predicate; force it for A/B measurement.
$reader->useLateMaterialization(true);
// Read side: check every block against the CRC the writer pinned at each
// sync point. One inflate pass, O(1) memory; names the block that went bad.
$report = $reader->verify();
// ['ok' => true, 'sheets' => [['ok' => true, 'corrupt_blocks' => [], ...]]]
// Write side: S3 verifies each part's Content-MD5 and rejects a corrupted
// one — a bad byte never enters the object.
$sink = new S3MultipartSink($s3, $bucket, $key, verifyParts: true);
// Compact output: drop the optional r attributes on cells/rows (ECMA-376
// allows it). ~52–62% smaller compressed sheets; opens in Excel/LibreOffice/
// Numbers. Classic output is byte-identical when off.
$writer->compact();
// Group-aligned blocks: align index blocks to a sorted group column so
// groupStats() folds each block from the sidecar — zero row reads.
$writer->syncAtGroupBoundaries(2);
// Planner (e.g. the job that receives the upload)
$reader = StreamingXlsxReader::fromS3($s3, 'bucket', 'import.xlsx');
foreach ($reader->shards(8) as $shard) {
ProcessXlsxShard::dispatch('bucket', 'import.xlsx', $shard);
}
// Worker (each job opens its own reader/connection)
public function handle(): void
{
$reader = StreamingXlsxReader::fromS3($this->s3(), $this->bucket, $this->key);
foreach ($reader->rowsForShard($this->shard) as $rowNumber => $row) {
if ($rowNumber === 1) continue; // header rides in the first shard
// ... import the row
}
}
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 = 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(5) // Balanced compression (the default)
->setBufferFlushInterval(10000); // Medium buffer
// Maximum compression (slower, smaller files)
$writer->setCompressionLevel(9) // Maximum compression
->setBufferFlushInterval(1000); // Small buffer for streaming
// Setters override config per writer instance:
$writer->setCompressionLevel(9) // wins over the config value
->setBufferFlushInterval(50000);
$writer->withRandomAccessIndex(every: 10000)
->withColumnStats([1, 4])
->withColumnSketches([1, 4]);
$writer->onProgress(fn ($rows, $bytes) => ...)->setProgressInterval(10000);
// Full control: construct the sink directly. concurrency defaults to 1
// (synchronous, O(1) memory); raise it only for high-latency links where
// you've measured a win — parallel uploads hold more (sawtooth) memory.
new S3MultipartSink($s3, $bucket, $key, partSize: 8 * 1024 * 1024, concurrency: 4);