anourvalar / office
Generate documents from existing Excel & Word templates | Export tables to Excel (Grids)
Installs: 45 614
Dependents: 0
Suggesters: 0
Security: 0
Stars: 229
Watchers: 4
Forks: 21
Open Issues: 0
Requires
- php: ^8.1
Requires (Dev)
- friendsofphp/php-cs-fixer: ^3.26
- phpstan/phpstan: ^1.10
- phpunit/phpunit: ^10.0
- squizlabs/php_codesniffer: ^3.7
- vimeo/psalm: ^5.17
This package is auto-updated.
Last update: 2024-10-21 12:28:18 UTC
README
Installation
Minimal
composer require anourvalar/office
Phpspreadsheet is required to work with Excel (xlsx).
composer require phpoffice/phpspreadsheet "^1.29"
Zipstream-php is required to work with Word (docx).
composer require maennchen/zipstream-php "^3.1"
Mpdf is required to work with PDF.
composer require mpdf/mpdf: "^8.1"
Generate a document from an XLSX (Excel) template
One-dimensional table (basic usage)
template1.xlsx:
$data = [ // scalar 'vat' => 'No', 'total' => [ 'price' => 2004.14, 'qty' => 3, ], // one-dimensional table 'products' => [ [ 'name' => 'Product #1', 'price' => 989, 'qty' => 1, 'date' => new \DateTime('2022-03-30'), ], [ 'name' => 'Product #2', 'price' => 1015.14, 'qty' => 2, 'date' => new \DateTime('2022-03-31'), ], ], ]; // Save to the file (new \AnourValar\Office\SheetsService()) ->generate( 'template1.xlsx', // template filename $data // markers ) ->saveAs( 'generated_document.xlsx', // filename \AnourValar\Office\Format::Xlsx // save format ); // Output to the browser header('Content-type: ' . \AnourValar\Office\Format::Xlsx->contentType()); header('Content-Disposition: attachment; filename="generated_document.xlsx"'); echo (new \AnourValar\Office\SheetsService()) ->generate('template1.xlsx', $data) ->save(\AnourValar\Office\Format::Xlsx); // Available formats: // \AnourValar\Office\Format::Xlsx // \AnourValar\Office\Format::Pdf // \AnourValar\Office\Format::Html // \AnourValar\Office\Format::Ods
generated_document.xlsx:
The same template with empty data
Two-dimensional table
template2.xlsx:
$data = [ 'best_manager' => 'Sveta', // two-dimensional table 'managers' => [ 'titles' => [[ 'William', 'James', 'Sveta' ]], 'values' => [ [ // additional row 'month' => 'January', 'amount' => [700, 800, 900], // additional columns ], [ 'month' => 'February', 'amount' => [7000, 8000, 9000], ], [ 'month' => 'March', 'amount' => [70000, 80000, 90000], ], ], ], ]; // Save as XLSX (Excel) (new \AnourValar\Office\SheetsService()) ->generate('template2.xlsx', $data) ->saveAs('generated_document.xlsx'); // second argument (format) is optional
generated_document.xlsx:
Additional Features
template3.xlsx:
$data = [ 'foo' => 'Hello', 'bar' => function (SheetsInterface $driver, $column, $row) { $driver->insertImage('logo.png', $cell, ['width' => 100, 'offset_y' => -45]); return 'Logo!'; // replace marker "[bar]" with "Logo!" } ]; (new \AnourValar\Office\SheetsService()) ->hookValue(function (SheetsInterface $driver, $column, $row, $value, $sheetIndex) { // Hook will be called for every cell which is changing $value .= ' world'; return $value; }) ->generate( 'template3.ods', // ods template $data, true // cells auto format instead of template setup ) ->saveAs('generated_document.xlsx'); // Available hooks: // hookLoad: Closure(SheetsInterface $driver, string $templateFile, Format $templateFormat) // hookBefore: Closure(SheetsInterface $driver, array &$data) // hookValue: Closure(SheetsInterface $driver, string $column, int $row, $value, int $sheetIndex) // hookAfter: Closure(SheetsInterface $driver)
generated_document.xlsx:
Dynamic templates
$data = [ 'group1' => [ 'name' => 'Group 1', 'products' => [ ['name' => 'Product 1', 'stock' => 101], ['name' => 'Product 2', 'stock' => 102], ], ], 'group2' => [ 'name' => 'Group 2', 'products' => [ ['name' => 'Product 3', 'stock' => 103], ['name' => 'Product 4', 'stock' => 104], ], ], ]; (new \AnourValar\Office\SheetsService()) ->hookLoad(function ($driver, string $templateFile, $templateFormat) { // create empty document instead of using existing return $driver->create(); }) ->hookBefore(function ($driver, array &$data) { // place markers on-fly $row = 1; foreach (array_keys($data) as $group) { // group's title $driver ->setValue("A$row", "[{$group}.name]") ->mergeCells("A$row:B$row") ->setStyle("A$row", ['align' => 'center', 'bold' => true]); $row++; // group's products $driver ->setValue("A$row", "[$group.products.name]") ->setValue("B$row", "[$group.products.stock]"); $row++; } }) ->generate('', $data) ->saveAs('generated_document.xlsx');
Dynamic template overview
generated_document.xlsx:
Merge (union) few documents to a single file
$dataA = ['foo' => 'hello']; $dataB = ['foo' => 'world']; $documentA = (new \AnourValar\Office\SheetsService())->generate('template.xlsx', $dataA); $documentB = (new \AnourValar\Office\SheetsService())->generate('template.xlsx', $dataB); $mixer = new \AnourValar\Office\Mixer(); $mixer($documentA, $documentB)->saveAs('generated_document.xlsx');
Access the PhpSpreadsheet directly (default driver)
(new \AnourValar\Office\SheetsService()) ->hookBefore(function (\AnourValar\Office\Drivers\PhpSpreadsheetDriver $driver, array &$data) { $spreadsheet = $driver->spreadsheet; // @see \PhpOffice\PhpSpreadsheet\Spreadsheet $spreadsheet->createSheet()->setTitle('Foo Bar'); // adding a new Worksheet }) ->generate('template.xlsx', []) ->saveAs('generated_document.xlsx');
Generate a document from an DOCX (Word) template
(new \AnourValar\Office\DocumentService) ->generate('template.docx', ['foo' => 'bar']) ->saveAs('generated_document.docx');
template.docx:
generated_document.docx:
Export table (Grid)
Simple usage
$data = [ ['William', 3000], ['James', 4000], ['Sveta', 5000], ]; // Save as XLSX (Excel) (new \AnourValar\Office\GridService()) ->generate( ['Name', 'Sales'], // headers $data // data ) ->saveAs('generated_grid.xlsx');
generated_grid.xlsx:
Advanced usage (generators)
$headers = [ ['title' => 'Name', 'width' => 30], ['title' => 'Sales'], ]; $data = function () { yield ['name' => 'William', 'sales' => 3000]; yield ['name' => 'James', 'sales' => 4000]; yield ['name' => 'Sveta', 'sales' => 5000]; }; // Save as XLSX (Excel) (new \AnourValar\Office\GridService()) ->hookHeader(function (GridInterface $driver, mixed $header, $key, $column) { if (isset($header['width'])) { $driver->setWidth($column, $header['width']); // column with fixed width } else { $driver->autoWidth($column); // column with auto width } return $header['title']; }) ->hookRow(function (GridInterface $driver, mixed $row, $key) { return [ $row['name'], $row['sales'], ]; }) ->hookAfter(function ( GridInterface $driver, string $headersRange, string $dataRange, string $totalRange, array $columns ) { $driver->setSheetTitle('Foo'); $driver->setStyle( $headersRange, // A1:B1 ['bold' => true, 'background_color' => 'EEEEEE'] ); $driver->setStyle( $totalRange, // A1:B4 ['borders' => true, 'align' => 'left'] ); }) ->generate($headers, $data) ->saveAs('generated_grid.xlsx');
generated_grid.xlsx:
Performance
By default, GridService uses PhpSpreadsheetDriver which gives a lot of features and flexability. The only cons are performance and memory consumtion.
ZipDriver as an alternative is simpler, but much more faster:
$data = [ ['William', 3000], ['James', 4000], ['Sveta', 5000], ]; // Save as XLSX (Excel) (new \AnourValar\Office\GridService(new \AnourValar\Office\Drivers\ZipDriver())) ->generate(['Name', 'Sales'], $data) ->saveAs('generated_grid.xlsx');