Basic Usage

This guide covers the fundamental operations with Derafu Spreadsheet: loading, manipulating, and saving spreadsheet data.

Dependency Injection

Import the package composition to inject the shared factory, caster, loader and dumper through their interfaces in Derafu\Spreadsheet\Contract:

imports:
    - { resource: '../vendor/derafu/spreadsheet/resources/config/spreadsheet-services.yaml' }

parameters:
    derafu.spreadsheet.format_handlers: []
    derafu.spreadsheet.loader.format: xlsx
    derafu.spreadsheet.dumper.format: xlsx

SpreadsheetLoaderInterface and SpreadsheetDumperInterface use the same SpreadsheetFactoryInterface and SpreadsheetCasterInterface instances. Replacing the caster registration affects both reading and writing.

The format parameters configure the defaults used by string operations when the format argument is omitted or null. loadFromFile() detects the format from the filename when no override is supplied. The dumper’s configured default also applies to dumpToFile() when no format override is supplied.

The empty handler map retains all built-in formats. A non-empty map replaces the entire catalogue; it does not extend the built-in map:

parameters:
    derafu.spreadsheet.format_handlers:
        records: Derafu\Spreadsheet\Format\JsonHandler
    derafu.spreadsheet.loader.format: records
    derafu.spreadsheet.dumper.format: records

Handlers remain classes instantiated by the factory for each request, using their default constructor arguments. This map does not configure individual handler instances. The YAML does not install optional format libraries or PDF engines; see Format Handlers for their requirements.

For HTTP downloads, import the optional Nyholm composition described in HTTP Responses.

Loading a Spreadsheet

You can load a spreadsheet from a file or from a string:

<?php

use Derafu\Spreadsheet\SpreadsheetLoader;

// Create a loader.
$loader = new SpreadsheetLoader();

// From a file (format auto-detected from extension).
$spreadsheet = $loader->loadFromFile('data.xlsx');

// From a string (an explicit format overrides the configured default).
$csvString = "header1,header2\nvalue1,value2";
$spreadsheet = $loader->loadFromString($csvString, 'csv');

Working with Sheets

A spreadsheet contains one or more sheets, which you can access and manipulate:

// Get all sheet names.
$sheetNames = $spreadsheet->getSheetNames();

// Get a specific sheet.
$sheet = $spreadsheet->getSheet('Sheet1');

// Create a new sheet with data.
$spreadsheet->createSheet('NewSheet', [
    ['Header1', 'Header2', 'Header3'],
    ['Value1', 'Value2', 'Value3'],
    ['Value4', 'Value5', 'Value6']
]);

// Set active sheet.
$spreadsheet->setActiveSheet('NewSheet');

// Get active sheet.
$activeSheet = $spreadsheet->getActiveSheet();

// Check if a sheet exists.
if ($spreadsheet->hasSheet('SomeSheet')) {
    // ...
}

// Remove a sheet.
$spreadsheet->removeSheet('SomeSheet');

Working with Rows and Cells

Once you have a sheet, you can access and modify its data:

// Get all rows.
$rows = $sheet->getRows();

// Get a specific row.
$firstRow = $sheet->getRow(0);

// Add a new row.
$sheet->addRow(['New', 'Row', 'Data']);

// Get a specific cell.
$value = $sheet->getCell(0, 0); // Row 0, Column 0.
// or with named columns (for associative sheets).
$value = $sheet->getCell(0, 'column_name');

// Update a cell.
$sheet->setCell(0, 0, 'New Value');

// Get header row.
$headers = $sheet->getHeaderRow();

// Get data rows (excludes header for indexed sheets).
$dataRows = $sheet->getDataRows();

Typed Row Readers

SheetInterface::getRowReader(int $index): ?ArrayReader exposes a row through Derafu\Array\ArrayReader. It uses the same row indexes as getRow(), including the header row of an indexed sheet. A missing row returns null; an existing empty row returns an empty reader.

use Derafu\Spreadsheet\Sheet;

$sheet = new Sheet('Products', [
    ['sku' => '0012', 'quantity' => 12, 'amount' => '19.90'],
], true);

$reader = $sheet->getRowReader(0);
if ($reader !== null) {
    $sku = $reader->requireString('sku');          // '0012'.
    $quantity = $reader->requireInt('quantity');   // 12.
    $amount = $reader->requireAsFloat('amount');   // 19.9.
}

Each call creates a snapshot of the stored row, preserving numeric, named and sparse column keys. Later calls to setCell(), setRow() or setRows() appear in a fresh reader; readers already obtained keep their array snapshot. Object-valued cells retain their original object references.

Reader creation does not cast cells or change the sheet. It exposes the types already stored, including those produced by the existing loading and casting pipeline. Strict reads reject mismatched types; conversion methods such as requireAsFloat() are explicit. See typed array readers for dot paths, literal-key precedence, null handling and exceptions.

Custom implementations of SheetInterface must implement getRowReader().

Data Types

Thanks to the automatic type casting, you can work with native PHP types:

// Numbers are already cast to int/float.
$number = $sheet->getCell(0, 0); // e.g., 123 (int).

// Dates are cast to DateTimeImmutable.
$date = $sheet->getCell(0, 1); // e.g., DateTimeImmutable object.
echo $date->format('Y-m-d'); // "2025-03-12".

// Booleans are properly typed.
$bool = $sheet->getCell(0, 2); // e.g., true (bool).

// You can set any type and it will be properly stored.
$sheet->setCell(0, 3, new DateTimeImmutable());
$sheet->setCell(0, 4, ['array', 'values']);
$sheet->setCell(0, 5, ['nested' => ['object' => 'structure']]);

Associative vs. Indexed Sheets

Derafu Spreadsheet supports both associative (column names as keys) and indexed (numeric keys) sheets:

// Check if a sheet is associative.
if ($sheet->isAssociative()) {
    // Work with associative data.
    $rowData = $sheet->getRow(0);
    echo $rowData['column_name'];
} else {
    // Work with indexed data.
    $rowData = $sheet->getRow(0);
    echo $rowData[0]; // First column.
}

    // Convert between formats.
$associativeSheet = $sheet->toAssociative(); // First row becomes header.
$indexedSheet = $sheet->toIndexed(); // Keys become first row.

Saving a Spreadsheet

Once you’re done modifying the data, you can save it to a file or get it as a string:

use Derafu\Spreadsheet\SpreadsheetDumper;

// Create a dumper.
$dumper = new Dumper();

// Save to a file (format detected from extension).
$dumper->dumpToFile($spreadsheet, 'output.xlsx');

// Convert to a different format.
$dumper->dumpToFile($spreadsheet, 'output.csv');

// Get as a string.
$jsonString = $dumper->dumpToString($spreadsheet, 'json');

Creating a Spreadsheet from Scratch

You can also create a spreadsheet from scratch:

use Derafu\Spreadsheet\Spreadsheet;

// Create an empty spreadsheet.
$spreadsheet = new Spreadsheet();

// Create a sheet with data.
$spreadsheet->createSheet('Sheet1', [
    ['Name', 'Age', 'Email'],
    ['John Doe', 30, '[email protected]'],
    ['Jane Smith', 25, '[email protected]']
]);

// Create from array.
$data = [
    'Users' => [
        ['Name', 'Age', 'Email'],
        ['John Doe', 30, '[email protected]'],
        ['Jane Smith', 25, '[email protected]']
    ],
    'Products' => [
        ['ID', 'Name', 'Price'],
        [1, 'Product A', 29.99],
        [2, 'Product B', 49.99]
    ]
];

$spreadsheet = Spreadsheet::fromArray($data);

// Save it.
$dumper = new Dumper();
$dumper->dumpToFile($spreadsheet, 'new_spreadsheet.xlsx');