PHPackages                             kolay/xlsx-stream - PHPackages - PHPackages  [Skip to content](#main-content)[PHPackages](/)[Directory](/)[Categories](/categories)[Trending](/trending)[Leaderboard](/leaderboard)[Changelog](/changelog)[Analyze](/analyze)[Collections](/collections)[Log in](/login)[Sign up](/register)

1. [Directory](/)
2. /
3. [File & Storage](/categories/file-storage)
4. /
5. kolay/xlsx-stream

ActiveLibrary[File & Storage](/categories/file-storage)

kolay/xlsx-stream
=================

Streaming XLSX reader and writer for PHP and Laravel. Constant memory regardless of file size, direct S3 multipart streaming, optional born-indexed random access.

v3.3.0(1mo ago)4512.6k↑25.2%1[1 issues](https://github.com/turgutahmet/kolay-xlsx-stream/issues)MITPHPPHP ^8.1CI passing

Since Sep 7Pushed 2w ago3 watchersCompare

[ Source](https://github.com/turgutahmet/kolay-xlsx-stream)[ Packagist](https://packagist.org/packages/kolay/xlsx-stream)[ Docs](https://github.com/turgutahmet/kolay-xlsx-stream)[ RSS](/packages/kolay-xlsx-stream/feed)WikiDiscussions main Synced 2w ago

READMEChangelog (10)Dependencies (26)Versions (22)Used By (0)

Kolay XLSX Stream
=================

[](#kolay-xlsx-stream)

[![Latest Version on Packagist](https://camo.githubusercontent.com/2bfbd3a532c298ec3c65aa20cb42dd38305e7a193d39207a33521aa58a896230/68747470733a2f2f696d672e736869656c64732e696f2f7061636b61676973742f762f6b6f6c61792f786c73782d73747265616d2e7376673f7374796c653d666c61742d737175617265)](https://packagist.org/packages/kolay/xlsx-stream)[![Tests](https://camo.githubusercontent.com/6b5bb3ca54ff16aa7cf9846cd2e8cfe5526297a43f2225a229e73ae2af0a4cb4/68747470733a2f2f696d672e736869656c64732e696f2f6769746875622f616374696f6e732f776f726b666c6f772f7374617475732f74757267757461686d65742f6b6f6c61792d786c73782d73747265616d2f74657374732e796d6c3f6272616e63683d6d61696e266c6162656c3d7465737473267374796c653d666c61742d737175617265)](https://github.com/turgutahmet/kolay-xlsx-stream/actions/workflows/tests.yml)[![Total Downloads](https://camo.githubusercontent.com/a422ffb96f6ac6c2d04f515f62d6421609892a96d050bc24a6544c83398b53aa/68747470733a2f2f696d672e736869656c64732e696f2f7061636b61676973742f64742f6b6f6c61792f786c73782d73747265616d2e7376673f7374796c653d666c61742d737175617265)](https://packagist.org/packages/kolay/xlsx-stream)[![License](https://camo.githubusercontent.com/23855a715c9b625b916f560f3ccbb3b7b2311d5dc8d119d583180f8b66c375c6/68747470733a2f2f696d672e736869656c64732e696f2f7061636b61676973742f6c2f6b6f6c61792f786c73782d73747265616d2e7376673f7374796c653d666c61742d737175617265)](https://packagist.org/packages/kolay/xlsx-stream)[![PHP Version](https://camo.githubusercontent.com/d92901701ad2a8845384b083375dd5ccec5017a2aa7b0f177416092f6f7787e8/68747470733a2f2f696d672e736869656c64732e696f2f7061636b61676973742f7068702d762f6b6f6c61792f786c73782d73747265616d2e7376673f7374796c653d666c61742d737175617265)](https://packagist.org/packages/kolay/xlsx-stream)

Bidirectional XLSX streaming for PHP and Laravel — and the only library in any language that makes the spreadsheet itself **queryable**. Write millions of rows straight to S3 with zero disk I/O, read them back with bounded memory, seek to any row in O(1), and ask for a column's sum, median, p99 or distinct count **without reading a single row** — over HTTP range requests, from a file Excel opens like any other.

- **Write**: ~289K rows/s locally at 6 MB peak RAM; direct S3 multipart streaming at bounded memory — synchronous by default (flat ~part-size working set, true O(1) regardless of file size), optional parallel upload window, no temp files
- **Read**: ~127K rows/s full scans with bounded memory, any file size
- **Seek**: `rowAt(1_000_000)` in milliseconds via the born-indexed sidecar
- **Query**: `columnStats` / `rowsWhere` / `findRow` / `groupStats` with Parquet-style block pruning; `median` / `quantile` / `countDistinct`from embedded sketches with **zero row reads**
- **Open format**: the sidecar is a published spec ([SPEC.md](SPEC.md)) with a byte-pinned conformance suite

Why this package?
-----------------

[](#why-this-package)

Most PHP Excel libraries load whole documents into RAM (unusable at scale), spill temp files before uploading to S3, and can only read forward — reaching row 900,000 means scanning 899,999 rows first.

This package streams in both directions with constant memory, and its born-indexed mode embeds a small binary sidecar (`xl/_kxs/index.bin`) that vanilla readers ignore but this library uses for random access, block-pruned queries and sidecar-only analytics. Excel, LibreOffice, Numbers, PhpSpreadsheet and OpenSpout all open the files normally.

Performance
-----------

[](#performance)

### The trajectory — same canonical workloads, every release

[](#the-trajectory--same-canonical-workloads-every-release)

v1.x (Sep 2025)v2.2 (May 2026)v3.0 (May 2026)v3.1 (Jul 2026)v3.2 (Jul 2026)v3.3 (Jul 2026)Write, local~182K rows/s~210K~215K**~289K**~289K~289KWrite, S3~9K rows/s~107K~107K+36% same-link A/B+ parallel window**O(1) memory (was O(file))**Read, local——~70K rows/s~106K**~127K**~127KRandom access——O(1) `rowAt`+ block-pruned queries+ within-block skip (~19×)+ bounded ranged fetchQuery engine———`columnStats`/`rowsWhere`+ `groupStats`**AND / `topRows` / `explain` / by-name**Analytics———0-request sumsmedian/p99/distinct 0-request+ `Bucket::month` GROUP BYIntegrity—————**`verify()` + S3 per-part checks**Peak RAM (write/read)0-2 MB / —~6 MB / —6 / 24 MB6 / 24 MB6 / **6 MB**6 / 6 MBAbsolute S3 throughput tracks the network path far more than the library (the same 1M-row export measured 59K–153K rows/s across sessions) — the honest S3 claim is the same-day A/B: **v3.2's writer is +36% over v3.0.2 on an identical link**. Uploads are **synchronous by default** — part memory stays flat at ~part-size no matter how large the file, and throughput is steady; a **parallel upload window is opt-in**(`concurrency`) for high-latency links where hiding per-request round-trips outweighs its higher (sawtooth) memory. Benchmark on your own link.

### Cross-package, 100K rows (May 2026, latest stables)

[](#cross-package-100k-rows-may-2026-latest-stables)

WriteTimerows/sReadTimerows/s**kolay/xlsx-stream****0.65s****153K****kolay/xlsx-stream****1.75s****57K**avadim/fast-excel-writer5.23s19Kavadim/fast-excel-reader4.60s22Kopenspout5.77s17Kfast-excel8.50s12Kfast-excel7.30s14Kopenspout9.90s10Kphpspreadsheet30.62s3Kphpspreadsheet29.95s3K(Numbers predate the v3.1/v3.2 speedups. PhpSpreadsheet plays a different game — full Excel feature support at memory-bound cost.)

**Every historical table** (per-version scaling runs from 100 rows to 4.5M, random-access speedups, memory profiles, methodology) lives in [BENCHMARK.md](BENCHMARK.md). All numbers come from the committed `bench/` harnesses — fresh process per run, medians, generation cost subtracted; re-run them yourself.

### File size limits

[](#file-size-limits)

The writer emits **ZIP32** archives. Each output is bounded by:

- **4 GB compressed** total archive size
- **4 GB uncompressed** per ZIP entry (single sheet)
- **65,535 entries** in the central directory

These ceilings are far above any realistic single-export workload (4.5 M rows ≈ 178 MB compressed). If a workload approaches them the writer aborts with a clear `ZIP32 limit exceeded` exception instead of silently truncating size fields and producing a corrupt file — split the export across multiple files or sheets as a workaround. ZIP64 writer support is tracked for a future release.

### Compression level

[](#compression-level)

`setCompressionLevel(int $level)` accepts 1–9. The default is **5**(v3.1+): measured on XLSX-shaped XML, level 5 produces a file within ~0.2 % of level 6's size at ~20 % less wall time — level 6 spends its extra effort on entropy (unique cell refs) that doesn't compress anyway. Pick by use case:

Use caseLevelTradeoffQueue job, fastest export1fastest, ~20 % larger fileBalanced default5knee of the size/speed curve for XLSX dataMarginally smaller6~0.2 % smaller than 5, measurably slowerArchive, smallest file9much slower, ~6 % smaller fileFor S3 uploads, a lower level typically wins because compute is the bottleneck. Level 9 only helps if you're storing the file long-term.

### Comparison with Other Libraries

[](#comparison-with-other-libraries)

Package1M Rows Write1M Rows ReadMemory (Read)Disk UsageRandom AccessS3 SupportPHPSpreadsheet❌ Crashes❌ Crashes~8 GBFull file❌IndirectSpout / OpenSpout~60 sec~30 sec~100MB+Full file❌IndirectLaravel Excel~90 sec~60 sec~500MB+Full file❌Indirect**Kolay XLSX Stream (Local)**✅ **4.65 sec**✅ **14.30 sec**✅ **24 MB**✅ **Zero**✅ **O(1)\***N/A**Kolay XLSX Stream (S3)**✅ **9.13 sec**✅ **16.60 sec**✅ **24 MB**✅ **Zero**✅ **O(1)\***✅ **Direct***\*With opt-in `withRandomAccessIndex()` on the writer. Per-lookup work is bounded by the writer-chosen sync period (default 10,000 rows), independent of file size. `rowCount()` is constant straight from the index header. Tune for latency-sensitive seeks with `withRandomAccessIndex(every: 1000)` or `every: 100` for very dense random reads (file size grows ~1% per 10× density).*

### When to use this package vs alternatives

[](#when-to-use-this-package-vs-alternatives)

For most Laravel exports — use [fast-excel](https://github.com/rap2hpoutre/fast-excel). Simpler API, supports CSV/ODS, includes import functionality, battle-tested across millions of installs.

**Use `kolay/xlsx-stream` when:**

- You need to stream directly to S3 with no temporary disk usage — Lambda, Cloud Run, Fargate, read-only filesystems
- Your dataset exceeds available memory — 1M+ rows on small instances, multi-million-row exports on standard ones
- You need **O(1) random access** into large XLSX files via `rowAt(N)` / `rowRange(a, b)` (born-indexed mode — first random-access XLSX primitive in PHP)
- You want HTTP-streamed downloads via `PhpStreamSink::output()` — zero temp file, immediate first byte to the client

**Use [PhpSpreadsheet](https://github.com/PHPOffice/PhpSpreadsheet) when:**

- You need formulas, charts, conditional formatting, or pivot tables
- File size is small enough for in-memory operations (< 50 K rows)
- You're editing existing workbooks rather than producing new ones

**Use [OpenSpout](https://github.com/openspout/openspout) when:**

- You need ODS or CSV alongside XLSX
- You're already in a non-Laravel ecosystem and want a streaming writer/reader without S3 specifics

Requirements
------------

[](#requirements)

- PHP 8.1+
- Laravel 10, 11, 12 or 13
- AWS SDK (only if using S3 streaming or the S3 reader)

> Upgrading from v2.x? Reader and random-access APIs are purely additive — no breaking changes. See [CHANGELOG.md](CHANGELOG.md) for the full v3.2 highlights.
>
> Upgrading from v1.x? See [UPGRADE.md](UPGRADE.md) for the v2.0 migration guide as well.

Installation
------------

[](#installation)

```
composer require kolay/xlsx-stream
```

### Publish Configuration (Optional)

[](#publish-configuration-optional)

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

Use cases
---------

[](#use-cases)

Each scenario below links to the how-to section further down.

**1. The million-row queued export that stopped eating RAM.**A Laravel queue job streams `FromQuery`-style data straight to S3 — no temp file, no reopen-per-chunk, ~6 MB writer footprint, progress callbacks for the UI. Add `withRandomAccessIndex()` and the artifact is instantly seekable for every scenario below. → *Direct S3 Streaming*, *Laravel Job Example*, *Progress Reporting*.

**2. "Download report" endpoints that start instantly.**Stream the workbook into the HTTP response as it's generated (`PhpStreamSink` → `php://output`) — first bytes reach the browser while row 1,000,000 is still being written. → *Streaming directly to an HTTP response*.

**3. Importing customer uploads without fear.**Read files produced by Excel/openpyxl/PhpSpreadsheet with bounded memory (shared-strings tables up to 64 MB compressed), correct dates via `autoDetectDates()`, validate row-by-row, and batch-insert. → *Reading XLSX Files*, *Reading dates and times*.

**4. Parallel imports: wall clock = slowest worker.**`shards(8)` splits a born-indexed sheet into eight independently decompressible, JSON-serializable ranges — dispatch one queue job per shard, zero coordination. → *Parallel reads*.

**5. Admin file preview without importing to a database.**Paginate a 4M-row S3 export in a UI: `rowCount()` is O(1), page 40,000 costs the same as page 1 via `rowRange()`, "jump to ID" is `findRow()`— two range requests on a sorted column. → *Random-Access Reading*.

**6. Dashboard numbers straight from the file.**"Total payroll", "median salary", "p99 order value", "distinct customers" — answered from the sidecar with zero row reads (`columnStats`, `quantile`, `countDistinct`), and per-month breakdowns via `groupStats()` reading only group-boundary blocks. The file IS the report backend. → *Queryable XLSX*, *Grouped aggregates*.

**7. Styled corporate reports, still streaming.**Header styling, per-row highlight styles, ₺/date/weekday number formats, frozen header, autofilter, auto column widths — all single-pass compatible. → *Header & Column Styling*.

**8. Tight environments: Lambda, small pods, multi-tenant SaaS.**Constant memory on both directions means the same code runs in a 128 MB function and a shared worker without per-tenant memory math.

Quick Start
-----------

[](#quick-start)

### Basic Usage - Local File

[](#basic-usage---local-file)

```
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', 'john@example.com', '+1234567890']);
$writer->writeRow(['Jane Smith', 'jane@example.com', '+0987654321']);

// Or write multiple rows at once
$writer->writeRows([
    ['Bob Johnson', 'bob@example.com', '+1111111111'],
    ['Alice Brown', 'alice@example.com', '+2222222222'],
]);

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

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

### Direct S3 Streaming (Zero Disk I/O)

[](#direct-s3-streaming-zero-disk-io)

```
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();
```

### Reading XLSX Files *(v3.0+)*

[](#reading-xlsx-files-v30)

```
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);
}
```

The reader supports both files written by this package (zero indirection via inline strings) and files produced by other writers (PhpSpreadsheet, openpyxl, Apache POI, Excel itself) — the shared-strings table is loaded transparently when present.

> **Memory:** Reader peak RAM is bounded — measured delta from baseline stays under 4 MB regardless of file size (CI-pinned via `MemoryFootprintTest`). The PHP runtime adds a ~20 MB baseline, so total RSS lands around 22-24 MB on real workloads.
>
> **Lifecycle:** Reader resources are released automatically when the object goes out of scope (`__destruct` calls `close()`). For long-lived workers processing many files, calling `$reader->close()`or `unset($reader)` between iterations frees underlying handles eagerly.

### Reading dates and times *(v3.0+)*

[](#reading-dates-and-times-v30)

Excel stores dates as numeric serials (e.g. `46148` for 2026-05-06). The reader returns those as numeric strings by default — opt into automatic conversion per column:

```
$reader = StreamingXlsxReader::fromFile('orders.xlsx');
$reader->castColumn(2, 'date');                          // → DateTimeImmutable (date)
$reader->castColumn(3, 'datetime');                      // → DateTimeImmutable (with time)
$reader->castColumn(4, 'int');                           // → int
$reader->castColumn(5, fn ($v) => (int) $v * 100);       // custom callable

// Bulk
$reader->castColumns([0 => 'int', 2 => 'date', 3 => 'datetime']);

foreach ($reader->rows(skip: 1) as $row) {
    $row[2]; // DateTimeImmutable
}
```

> **Always use `rows(skip: 1)` with casts.** Casts run on every row the generator yields, including row 1 (the header). A header string like `"id"` cast as `'int'` returns `null` because `is_numeric("id")` is false. Read the header separately via `$reader->header()` (cast-free) and skip it on data iteration.

> **Timezone:** Excel serials are timezone-naive. The reader returns datetimes in **UTC by default** so the same file produces the same result on every server regardless of `date_default_timezone_get()`. If your file's dates were authored in a specific timezone, set it explicitly:
>
> ```
> $reader->castTimezone('Europe/Istanbul');
> ```
>
>
>
> Mac-origin Excel files using the 1904 epoch (rare): `$reader->use1904Epoch();`

Built-in cast names: `date`, `datetime`, `int`, `float`, `bool`. Pass any callable for custom transformations (parse to a value object, trim, normalise, etc.).

### Streaming directly to an HTTP response *(v3.0+)*

[](#streaming-directly-to-an-http-response-v30)

Use `PhpStreamSink::output()` to stream a workbook into the active HTTP response — no temp file, constant memory, immediate first byte to the client. Pairs naturally with Laravel's `Response::stream()`:

```
use Kolay\XlsxStream\Sinks\PhpStreamSink;
use Kolay\XlsxStream\Writers\SinkableXlsxWriter;

return response()->stream(function () {
    $writer = new SinkableXlsxWriter(PhpStreamSink::output());
    $writer->startFile(['id', 'name', 'email']);
    User::query()->lazy()->each(fn ($u) =>
        $writer->writeRow([$u->id, $u->name, $u->email])
    );
    $writer->finishFile();
}, 200, [
    'Content-Type' => 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
    'Content-Disposition' => 'attachment; filename="users.xlsx"',
]);
```

The sink also has `temp()` (in-memory until 2 MB, then a tmp file) and `memory()` (in-memory only) factories for capturing workbooks for later inspection — handy in tests.

### Random-Access Reading *(v3.0+)*

[](#random-access-reading-v30)

Files written with `withRandomAccessIndex()` can be seeked into in O(1). The opt-in costs ~0.03 % file size and adds a single hidden ZIP part (`xl/_kxs/index.bin`) that vanilla XLSX readers ignore.

```
// 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
}
```

`rowAt()` and `rowRange()` work even on files **without** an index — they fall back to a sequential O(N) scan from the first row. Only the cost differs; the API contract is identical.

> **The physics of deflate streaming** — seekability requires `Z_FULL_FLUSH` markers, and each marker resets the compressor's dictionary, which in principle costs compression ratio. In practice the cost is negligible at our default cadence: a sync point every 10K rows resets a 32 KB window once per ~1 MB of XML, measured at **+0.04 %** file size on the 500K-row benchmark workload (predicted ceiling ≤0.5 %). You would only notice dictionary-reset overhead at extreme settings like `every: 100` — if you shrink the period for query granularity, re-measure your file sizes.

> **Performance tip:** When you need many adjacent rows, prefer `rowRange($from, $to)` over a loop of `rowAt()` calls. `rowRange()`seeks once and reuses a single inflate stream; repeated `rowAt()`re-seeks on every call. For 1000 nearby rows the difference is ~1000× — a single ~ms seek versus 1000 × ms per call.

### Queryable XLSX — zone maps & aggregates *(v3.1+)*

[](#queryable-xlsx--zone-maps--aggregates-v31)

Born-indexed files can additionally carry **per-block column statistics**(min/max/sum/count for every ~10K-row block — the same idea as Parquet row-group stats, embedded in a plain .xlsx that Excel still opens normally). Track the columns you'll query when writing:

```
$writer->withRandomAccessIndex()
       ->withColumnStats([1, 4]);   // 1-based: track "ID" and "Amount"
```

The reader then answers three kinds of questions **without scanning row data**:

```
$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
```

Ops: `=`, `=`, `between`. Predicates match numeric cells (ints, floats, dates as serials); on files without stats the same calls degrade gracefully to a full-scan filter with identical results.

### Grouped aggregates & approximate analytics *(v3.2+)*

[](#grouped-aggregates--approximate-analytics-v32)

Two more layers on the same sidecar:

```
// 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)
```

The sketches are a merging t-digest (~1-4 KB/column, p01/p99 within 0.2% rank error) and a HyperLogLog (2 KB, ±5% pinned) per column — both merge associatively, which is what future segment/partition stitching builds on. The full binary layout is public: **KXSI is an open spec**(see [SPEC.md](SPEC.md)) with committed conformance vectors under `tests/SpecVectors/`, so other implementations can verify byte-for-byte.

### The query engine grows up *(v3.3+)*

[](#the-query-engine-grows-up-v33)

The sidecar turns into a small SQL-shaped engine — all answered by reading only the blocks that can match, and all addressable by header name (not just 1-based index):

```
// 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));
```

### String lookups — find a record by code on S3 *(v3.4+)*

[](#string-lookups--find-a-record-by-code-on-s3-v34)

`withStringStats([...])` adds per-block **lexicographic** zone maps (`STRZ`), so a string predicate prunes to the one block that can hold the value — a point lookup in a multi-GB S3 file in a couple of range requests:

```
$writer->withStringStats([8]);                    // writer side, before startFile()

$reader->findRow('kod', 'INV-2024-00871');        // one matching row, block-pruned
$reader->rowsWhere('kod', 'prefix', 'INV-2024');  // =, =, between, prefix
```

Collation is **unsigned UTF-8 byte order** (= Unicode code-point order), the only sound basis for a streaming zone map — NOT locale. In Turkish, `İ`/`ı`sort by their bytes, not `tr_TR` rules; use this for exact / prefix / range lookups (codes, SKUs, IDs), not locale-correct sorting.

### Data profiling & exact analytics *(v3.4+)*

[](#data-profiling--exact-analytics-v34)

The sidecar grows a profiling layer — a full per-column report, exact quantiles with a deterministic certificate, histograms, frequent values, and correlation — answered from the index cached at open, reading **no data rows** (`profile()` reads only the header once, bounded, to name columns):

```
// 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);
```

Every number comes from the sidecar (zero row I/O), so it is not free of CPU — `profile()` runs each column's sketch math; "one range request" is a statement about I/O, not latency. A percentile's certificate width tracks **row-order locality**, not value clustering: a column sorted by (or covarying with) the sheet's order certifies tightly, a scattered one is honestly reported as `[0, N]`. The certificate resolution is bounded by the block size — on a sorted column its width is ≈ the sync interval (`every`), so it is a knob: a smaller `every` tightens the certificate and thins the exact-scan pruning, at the cost of a larger sidecar. `histogram()` also takes `mode: 'depth'` for equi-depth bins (each ≈ equal count), which reads a skewed column far better than the default equi-width. See [SPEC.md](SPEC.md) §4–§6 for the format.

### Integrity — verified reads & writes *(v3.3+)*

[](#integrity--verified-reads--writes-v33)

For data that matters (payroll, HR, audit exports):

```
// 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);
```

### Bigger, cheaper writes *(v3.3+)*

[](#bigger-cheaper-writes-v33)

```
// 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);
```

> **S3 writes are now O(1) memory.** Multipart uploads default to synchronous (`concurrency: 1`): part memory stays flat at ~part-size no matter the file size. (Earlier versions defaulted to a parallel window that could grow memory toward the whole file.) Parallel is still an opt-in for high-latency links — see [UPGRADE.md](UPGRADE.md).

### Parallel reads — shard a sheet across queue workers *(v3.1+)*

[](#parallel-reads--shard-a-sheet-across-queue-workers-v31)

Every sync point in a born-indexed file is an independently decompressible boundary, so a sheet can be split into self-contained row ranges. The shard plan is plain JSON-friendly data — dispatch one queue job per shard and each worker streams only its slice, with zero coordination:

```
// 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
    }
}
```

A 4M-row import's wall clock becomes `max(worker time)` instead of the sum. Shard boundaries snap to sync points (balanced to within one sync period); on non-indexed files `shards()` returns a single whole-sheet shard — same contract, no parallelism.

### Laravel Job Example

[](#laravel-job-example)

```