How to Efficiently Read and Write CSV, XLSX, ODS with OpenSpout in PHP

Overview

OpenSpout is a community‑driven PHP library forked from box/spout, designed for fast, low‑memory (<3 MB) reading and writing of CSV, XLSX and ODS spreadsheet files.

Key Features

It uses optimized memory management and chunked processing, reading rows one‑by‑one instead of loading the whole file, supports multithreaded parallel processing, follows an object‑oriented API and is easy to integrate.

Core Capabilities

Efficient memory usage for very large files

Support for CSV, XLSX, ODS formats

Extensible via custom writers/readers

Thread‑safe, multi‑threaded processing

Clear, beginner‑friendly API

Active community maintenance

Typical Use Cases

Big‑data processing for data scientists extracting information from massive CSV/Excel files

Report generation from databases

Data migration between platforms

Building PHP‑based data‑analysis services

Installation

composer require openspout/openspout

Basic Usage – Reading a CSV File

<?php
/** @desc Read CSV file */
declare(strict_types=1);
require_once __DIR__ . '/../vendor/autoload.php';

use OpenSpout\Reader\CSV\Reader;
use OpenSpout\Reader\CSV\Options;

$options = new Options();
$options->FIELD_DELIMITER = ',';
$options->FIELD_ENCLOSURE = '@';

$reader = new Reader($options);
$filePath = './order_list_table.csv';
$reader->open($filePath);

$list = [];
foreach ($reader->getSheetIterator() as $sheet) {
    foreach ($sheet->getRowIterator() as $row) {
        $cells = $row->getCells();
        $list[] = [
            '订单号' => $cells[0]->getValue(),
            '资源购买账号id' => $cells[1]->getValue(),
            '商品名称' => $cells[2]->getValue(),
            '订单类型' => $cells[3]->getValue(),
            '付费方式' => $cells[4]->getValue(),
            '创建时间' => $cells[5]->getValue(),
        ];
    }
}
var_dump($list);
$reader->close();

Sample output shows each row as an associative array with fields such as 商品名称, 订单类型, 付费方式 and 创建时间.

Writing an XLSX File

<?php
/** demo01.php */
declare(strict_types=1);
require_once __DIR__ . '/../vendor/autoload.php';

use OpenSpout\Writer\XLSX\Writer;
use OpenSpout\Common\Entity\Row;
use OpenSpout\Common\Entity\Cell;

$writer = new Writer();
$filePath = './开源技术小栈.xlsx';
$writer->openToFile($filePath);

$cells = [
    Cell::fromValue('公众号'),
    Cell::fromValue('开源技术小栈'),
    Cell::fromValue('作者'),
    Cell::fromValue('Tinywan'),
];
$multipleRows = [
    new Row($cells),
    new Row($cells),
    new Row($cells),
    new Row($cells),
    new Row($cells),
];
$writer->addRows($multipleRows);

$values = ['公众号', '开源技术小栈', '作者', 'Tinywan'];
$rowFromValues = Row::fromValues($values);
$writer->addRow($rowFromValues);

$writer->close();

Writing reference

Read/Write Performance

Type   Action          2,000 rows   200,000 rows   2,000,000 rows
CSV    Read            < 1 sec      4 sec          2‑3 min
CSV    Write           < 1 sec      2 sec          2‑3 min
XLSX   Read (inline)   < 1 sec      35‑40 sec      18‑20 min
XLSX   Read (shared)   1 sec        1‑2 min        35‑40 min
XLSX   Write           1 sec        20‑25 sec      8‑10 min
ODS    Read            1 sec        1‑2 min        5‑6 min
ODS    Write           < 1 sec      35‑40 sec      5‑6 min