Product Export from Website to CSV/Excel/XML/JSON

Implementing product export from website to CSV/Excel/XML/JSON

Product catalog export is needed for various tasks: uploading to marketplaces, sending to partners, backup, integration with ERP/1C, analytics. Each recipient wants their own format and their own subset of fields. The task is to build flexible export that handles all these scenarios without code duplication.

Architecture: Builder + Writer

ExportBuilder
  └─> defines field set, filters, sorting
  └─> iterates database data in chunks
  └─> passes rows to ExportWriter

ExportWriter (one of):
  ├─> CsvWriter
  ├─> ExcelWriter
  ├─> XmlWriter
  └─> JsonWriter

interface ExportWriterInterface
{
    public function open(string $filePath): void;
    public function writeHeader(array $columns): void;
    public function writeRow(array $row): void;
    public function close(): string; // returns file path
}

CSV Writer

class CsvWriter implements ExportWriterInterface
{
    private $handle;

    public function open(string $filePath): void
    {
        $this->handle = fopen($filePath, 'w');
        // BOM for correct opening in Excel
        fwrite($this->handle, "\xEF\xBB\xBF");
    }

    public function writeHeader(array $columns): void
    {
        fputcsv($this->handle, $columns, ';', '"');
    }

    public function writeRow(array $row): void
    {
        fputcsv($this->handle, $row, ';', '"');
    }

    public function close(): string
    {
        fclose($this->handle);
        return $this->filePath;
    }
}

Excel Writer (streaming, memory-efficient)

For large catalogs PhpSpreadsheet consumes huge amounts of memory. Use openspout/openspout — works streaming:

class ExcelWriter implements ExportWriterInterface
{
    private \OpenSpout\Writer\XLSX\Writer $writer;

    public function open(string $filePath): void
    {
        $this->writer = new \OpenSpout\Writer\XLSX\Writer();
        $this->writer->openToFile($filePath);
    }

    public function writeHeader(array $columns): void
    {
        $cells = array_map(fn($c) => \OpenSpout\Common\Entity\Cell::fromValue($c), $columns);
        $this->writer->addRow(new \OpenSpout\Common\Entity\Row($cells, null));
    }

    public function writeRow(array $row): void
    {
        $cells = array_map(fn($v) => \OpenSpout\Common\Entity\Cell::fromValue($v), $row);
        $this->writer->addRow(new \OpenSpout\Common\Entity\Row($cells, null));
    }

    public function close(): string
    {
        $this->writer->close();
        return $this->filePath;
    }
}

openspout writes rows directly to the .xlsx ZIP stream — memory consumption ~10 MB regardless of file size.

XML Writer

class XmlWriter implements ExportWriterInterface
{
    private \XMLWriter $xml;

    public function open(string $filePath): void
    {
        $this->xml = new \XMLWriter();
        $this->xml->openUri($filePath);
        $this->xml->startDocument('1.0', 'UTF-8');
        $this->xml->startElement('products');
    }

    public function writeHeader(array $columns): void
    {
        $this->columns = $columns; // save for writeRow
    }

    public function writeRow(array $row): void
    {
        $this->xml->startElement('product');
        foreach (array_combine($this->columns, $row) as $key => $value) {
            $this->xml->startElement($key);
            $this->xml->text((string) $value);
            $this->xml->endElement();
        }
        $this->xml->endElement();
    }

    public function close(): string
    {
        $this->xml->endElement(); // products
        $this->xml->endDocument();
        $this->xml->flush();
        return $this->filePath;
    }
}

JSON Writer (NDJSON for streaming processing)

For large exports instead of one large JSON array — NDJSON (one line = one object):

class NdjsonWriter implements ExportWriterInterface
{
    private $handle;
    private array $columns;

    public function writeRow(array $row): void
    {
        $obj = array_combine($this->columns, $row);
        fwrite($this->handle, json_encode($obj, JSON_UNESCAPED_UNICODE) . "\n");
    }
}

Standard JSON array is also supported via JsonWriter, but for files >50 MB NDJSON is preferred.

Builder: query formation and iteration

class ProductExportBuilder
{
    private array $fields = ['sku', 'name', 'price', 'qty'];
    private array $filters = [];
    private int   $chunkSize = 1000;

    public function withFields(array $fields): self
    {
        $this->fields = $fields;
        return $this;
    }

    public function withFilter(string $field, mixed $value): self
    {
        $this->filters[$field] = $value;
        return $this;
    }

    public function export(ExportWriterInterface $writer, string $filePath): ExportResult
    {
        $writer->open($filePath);
        $writer->writeHeader($this->fields);

        $total = 0;
        $query = $this->buildQuery();

        $query->chunk($this->chunkSize, function ($products) use ($writer, &$total) {
            foreach ($products as $product) {
                $row = array_map(fn($f) => $this->resolveField($product, $f), $this->fields);
                $writer->writeRow($row);
                $total++;
            }
        });

        $filePath = $writer->close();
        return new ExportResult($total, $filePath);
    }

    private function buildQuery(): \Illuminate\Database\Eloquent\Builder
    {
        $query = Product::query()->orderBy('id');
        foreach ($this->filters as $field => $value) {
            $query->where($field, $value);
        }
        return $query;
    }

    private function resolveField(Product $product, string $field): mixed
    {
        return match ($field) {
            'category'    => $product->category?->name,
            'images'      => implode(',', $product->images->pluck('url')->all()),
            'attributes'  => $this->formatAttributes($product),
            default       => $product->{$field},
        };
    }
}

Asynchronous export of large files

For catalogs >10,000 items export runs in background:

class ExportProductsJob implements ShouldQueue
{
    public int $timeout = 600;

    public function handle(ProductExportBuilder $builder): void
    {
        $filePath = storage_path("exports/products_{$this->exportId}.{$this->format}");
        $writer   = ExportWriterFactory::make($this->format);

        $result = $builder
            ->withFields($this->config['fields'])
            ->withFilter('source_id', $this->config['source_id'] ?? null)
            ->export($writer, $filePath);

        ExportFile::find($this->exportId)->update([
            'status'       => 'ready',
            'file_path'    => $filePath,
            'total_rows'   => $result->total,
            'completed_at' => now(),
        ]);

        // Notify user of readiness
        $this->user->notify(new ExportReadyNotification($this->exportId));
    }
}

Configurable export templates

Different recipients want different field sets. Templates are stored in DB:

{
  "name": "For Yandex.Market",
  "format": "xml",
  "fields": ["sku", "name", "price", "qty", "description", "category", "images", "brand"],
  "filters": {"in_stock": true},
  "transform": {
    "price": "round:2",
    "images": "first_only"
  }
}

File distribution

public function download(int $exportId): BinaryFileResponse
{
    $export = ExportFile::findOrFail($exportId);
    $this->authorize('download', $export);

    if ($export->status !== 'ready') {
        abort(202, 'Export is not ready yet');
    }

    return response()->download(
        storage_path($export->file_path),
        "products_{$export->created_at->format('Y-m-d_H-i')}.{$export->format}",
        ['Content-Type' => $this->mimeType($export->format)]
    );
}

Timeline

• CSV + Excel (openspout) + Builder with chunking — 2 days
• XML + JSON/NDJSON + async Job + notification — +1 day
• Export templates in DB, field transforms, download — +1 day