php-sheets

A small PHP spreadsheet: cells named like A1, formulas that are ordinary closures, and a CSV of the grid when you are done.

composer require keaton-php/php-sheets

No dependencies. At the end of this README, a six-month subscription plan written as one list of cells.

Every example below is a complete file. To run one, save it next to your vendor/ folder and run php <file>.php.

The plain-PHP version

A small invoice: three lines of work, a 10% discount, then sales tax. In plain PHP that is an array and some arithmetic:

<?php

$items = [
    ['Design', 10, 85],
    ['Development', 24, 120],
    ['Hosting', 12, 15.50],
];
$discountRate = 0.10;
$taxRate = 0.0825;

$subtotal = 0;
foreach ($items as [$name, $qty, $rate]) {
    $subtotal += $qty * $rate;
}
$discount = -round($subtotal * $discountRate, 2);
$tax = round(($subtotal + $discount) * $taxRate, 2);
$total = round($subtotal + $discount + $tax, 2);

echo 'Subtotal: ', number_format($subtotal, 2), "\n";
echo 'Discount: ', number_format($discount, 2), "\n";
echo 'Tax:      ', number_format($tax, 2), "\n";
echo 'Total:    ', number_format($total, 2), "\n";

// The client asks for 30 hours of development instead of 24
$items[1][1] = 30;

echo 'Total:    ', number_format($total, 2), "\n";

Subtotal: 3,916.00
Discount: -391.60
Tax:      290.76
Total:    3,815.16
Total:    3,815.16

It works, until an input changes. The client asks for 30 hours of development instead of 24, and the last line still says 3,815.16: $total was worked out before the change. To get the new total you run all the arithmetic again, top to bottom. For one invoice that is fine. For a pricing model with forty numbers that feed each other, it is how a stale number ends up in front of a customer.

The same invoice as a sheet

<?php

require __DIR__ . '/vendor/autoload.php';

use Keaton\PhpSheets\Sheet;

$invoice = new Sheet();

// Quantity and rate for each line: Design, Development, Hosting
$invoice->B2 = 10;  $invoice->C2 = 85;
$invoice->B3 = 24;  $invoice->C3 = 120;
$invoice->B4 = 12;  $invoice->C4 = 15.50;

$invoice->C7 = 0.10;    // discount rate
$invoice->C8 = 0.0825;  // tax rate

// Formulas: closures that read other cells
$invoice->D6 = fn(Sheet $s) => $s->B2 * $s->C2 + $s->B3 * $s->C3 + $s->B4 * $s->C4;
$invoice->D7 = fn(Sheet $s) => -round($s->D6 * $s->C7, 2);
$invoice->D8 = fn(Sheet $s) => round(($s->D6 + $s->D7) * $s->C8, 2);
$invoice->D9 = fn(Sheet $s) => round($s->D6 + $s->D7 + $s->D8, 2);

echo 'Subtotal: ', number_format($invoice->D6, 2), "\n";
echo 'Discount: ', number_format($invoice->D7, 2), "\n";
echo 'Tax:      ', number_format($invoice->D8, 2), "\n";
echo 'Total:    ', number_format($invoice->D9, 2), "\n";

// The client asks for 30 hours of development instead of 24
$invoice->B3 = 30;

echo 'Total:    ', number_format($invoice->D9, 2), "\n";

Subtotal: 3,916.00
Discount: -391.60
Tax:      290.76
Total:    3,815.16
Total:    4,516.62

The same numbers, until the change. This time the last line is right, because the total is not a number you worked out once: it is a formula, and a formula is worked out again every time you read it.

A cell is a property named like a spreadsheet cell ($invoice->B3). A formula is a closure that receives the sheet, so it reads other cells the same way ($s->B3).

Why that's better

Nothing goes stale

You just saw it: change B3 and every total that depends on it follows, with nothing to re-run.

Formulas can come before their inputs

A formula is worked out from the cells as they are when you read it, so it can be written before its inputs. An empty cell reads as null, which PHP's arithmetic treats as 0, so until its inputs arrive the total is just 0. (A formula that cannot be worked out from an empty cell, like a division, is the exception; the hard parts below show what happens then.)

<?php

require __DIR__ . '/vendor/autoload.php';

use Keaton\PhpSheets\Sheet;

$sheet = new Sheet();

// The total first, its inputs after
$sheet->A3 = fn(Sheet $s) => $s->A1 + $s->A2;
$sheet->A1 = 10;
$sheet->A2 = 20;

echo $sheet->A3, "\n";

30

Mistakes are loud

A formula that reads something that is not a cell reference throws when you write it, and the write is undone. A plain array would hand you null and a warning, and carry on:

<?php

require __DIR__ . '/vendor/autoload.php';

use Keaton\PhpSheets\Sheet;

$sheet = new Sheet();
$sheet->A1 = 10;

try {
    // A cell is named by where it is, not by what it holds
    $sheet->A2 = fn(Sheet $s) => $s->subtotal * 2;
} catch (InvalidArgumentException $e) {
    echo $e->getMessage(), "\n";
}

var_dump(isset($sheet->A2));

Invalid cell reference: SUBTOTAL
bool(false)