nimmneun / onesheet
OneSheet is a fast and lightweight single/multi sheet excel/xlsx file writer for PHP 5.4+, PHP 7 & PHP 8 with styling and cell auto-sizing support.
Installs: 298 125
Dependents: 2
Suggesters: 0
Security: 0
Stars: 41
Watchers: 8
Forks: 6
Open Issues: 1
Requires
- php: >=5.4.4
- ext-zip: *
Requires (Dev)
- phpunit/phpunit: ^4
Suggests
- ext-gd: Required for accurate cell width sizing only.
- ext-mbstring: Required for cell width auto sizing
- dev-master
- 1.2.5
- 1.2.4
- 1.2.3
- 1.2.2
- 1.2.1
- 1.2.0
- 1.1.3
- 1.1.2
- 1.1.1
- 1.1.0
- 1.0.3
- 1.0.2
- 1.0.1
- 0.7.1
- 0.7.0
- 0.6.12
- 0.6.11
- 0.6.10
- 0.6.9
- 0.6.8
- 0.6.7
- 0.6.6
- 0.6.5
- 0.6.4
- 0.6.3
- 0.6.2
- 0.6.1
- 0.6.0
- 0.5.1
- 0.5.0
- 0.4.12
- 0.4.11
- 0.4.10
- 0.4.9
- 0.4.8
- 0.4.7
- 0.4.6
- 0.4.5
- 0.4.4
- 0.4.3
- 0.4.2
- 0.4.1
- 0.4.0
- 0.3.1
- 0.3.0
- 0.2.1
- 0.1.0
- dev-maintenance/update-ci-toolchain
This package is auto-updated.
Last update: 2024-10-30 01:53:41 UTC
README
OneSheet is a simple single/multi sheet excel/xlsx file writer for PHP 5, PHP 7 & PHP 8 with cell auto-sizing and styling support.
What it does
- Write a single/multiple spreadsheet(s) fast and with a small memory footprint.
- Freeze the first [n] rows to have a fixed table header/headline per sheet.
- Use different fonts, styles, borders and background colors on a row level.
- Set your own custom column width per column.
- Autosize column widths to fit cell contents. If no fonts are found, rough estimates are used.
- Define minimum and maximum column widths to keep exceptionally large or small cell contents in check.
What it doesn't
- No cell individualisation, everything is applied at a row level.
- No calculated / formula cells.
- No conditional formatting.
- No number formats.
- No charts.
Install via composer
$ composer require nimmneun/onesheet
Manual installation
If you can't or don't want to use composer for some reason, download & extract onsheet and require the file autoload.php from the releases root folder.
<?php // path to onesheet autoload file on your server / webspace e.g.: require_once '/srv/fancydomain.com/libs/onesheet/autoload.php';
Minimal working example
<?php require_once '../vendor/autoload.php'; $onesheet = new \OneSheet\Writer('/optional/fonts/directory'); $onesheet->addRow(array('hello', 'world')); $onesheet->writeToFile('hello_world.xlsx');
Available Writer operations
Writer::setFreezePaneCellId(string $cellId)
Writer::setPrintTitleRange(int $startRow, int $endRow)
Writer::switchSheet(string $sheetName)
Writer::setFixedColumnWidths(array $columnWidths)
Writer::setColumnWidthLimits(float $minWidth, float $maxWidth)
Writer::enableCellAutosizing()
Writer::disableCellAutosizing()
Writer::addRows(array $rows, Style $style)
Writer::addRow(array $row, Style $style)
Writer::writeToFile(string $fileName)
Writer::writeToBrowser(string $fileName)
Adding font styles
Style::setFontName(string $name)
Style::setFontSize(int $size)
Style::setFontColor(string $color)
Style::setFontBold()
Style::setFontItalic()
Style::setFontUnderline()
Style::setFontStrikethrough()
Adding background colors (fills)
Style::setFillColor(string $color)
Adding borders
Style::setSurroundingBorder(string $style, string $color)
Style::setBorderLeft(string $style, string $color)
Style::setBorderRight(string $style, string $color)
Style::setBorderTop(string $style, string $color)
Style::setBorderBottom(string $style, string $color)
Style::setBorderDiagonalUp(string $style, string $color)
Style::setBorderDiagonalDown(string $style, string $color)
Cell auto-sizing
... is cool, but comes with heavy performance impacts - especially when dealing with multibyte characters like ä, ß, Æ, ポ.
Keep in mind though ... you can improve runtimes for larger datasets by disabling it after adding a decent number of rows.
Intel Xeon E3-1220, Debian GNU/Linux 9.13, PHP 7.2.27-1+020200123.34+debian91.gbp63c0bc
Additional examples
<?php require_once '../vendor/autoload.php'; // create a header style $headerStyle = (new \OneSheet\Style\Style()) ->setFontSize(13) ->setFontBold() ->setFontColor('FFFFFF') ->setFillColor('777777'); // create a data style $dataStyle1 = (new \OneSheet\Style\Style()) ->setFontName('Segoe UI') ->setFontSize(10); // create a second data style $dataStyle2 = (new \OneSheet\Style\Style()) ->setFontName('Arial') ->setFillColor('F7F7F7'); // prepare some dummy header data $dummyHeader = array('Strings', 'Ints', 'Floats', 'Dates', 'Times', 'Uids'); // prepare some dummy data $dummyData = array(); for ($i = 1; $i <= 100; $i++) { $dummyData[] = array( substr(md5(microtime()), rand(11,22)), rand(333,333333), microtime(1), date(DATE_RSS, time() + $i*60*60*24), date('H:i:s', time() + $i), uniqid('', true) ); } // create new OneSheet instance $onesheet = new \OneSheet\Writer(); // add header with style $onesheet->addRow($dummyHeader, $headerStyle); // freeze everything above cell A2 (the first row will be frozen) $onesheet->setFreezePaneCellId('A2'); // enable autosizing of column widths and row heights $onesheet->enableCellAutosizing(); // add dummy data row by row and switch between styles foreach ($dummyData as $key=> $data) { if ($key % 2) { $onesheet->addRow($data, $dataStyle1); } else { $onesheet->addRow($data, $dataStyle2); } } // ignore the coming rows for autosizing $onesheet->disableCellAutosizing(); // add an oversized dummy row $onesheet->addRow(array('no one cares about my size and I dont even have a special style!')); // add the all the dummy rows once more, because we can =) $onesheet->addRows($dummyData); // Override column widths for columns 6, 7, 8 (column 0 is the first) $onesheet->setFixedColumnWidths(array(5 => 10, 6 => 10, 7 => 10)); // write everything to the specified file $onesheet->writeToFile(str_replace('.php', '_onesheet.xlsx', __FILE__));
Writing to multiple sheets
<?php require_once '../vendor/autoload.php'; $boldHeader = (new OneSheet\Style\Style())->setFontBold(); // create initial writer instance with sheet name $writer = new \OneSheet\Writer(null, 'Invoices'); $writer->enableCellAutosizing(); // enable for current sheet $writer->addRow(['InvoiceNo', 'Amount', 'CustomerNo'], $boldHeader); $writer->addRow(['']); // add empty row bcs fancy :D $writer->addRow(['I-123', 123.45, 'C-123']); // create new sheet with specific sheet name $writer->switchSheet('Refunds'); $writer->enableCellAutosizing(); // enable for current sheet $writer->addRow(['RefundNo', 'Amount', 'InvoiceNo'], $boldHeader); $writer->addRow(['']); // add empty row bcs fancy :D $writer->addRow(['R-123', 123.45, 'I-123']); // create another sheet with specific sheet name $writer->switchSheet('Customers'); $writer->enableCellAutosizing(); // enable for current sheet $writer->addRow(['CustomerNo', 'FirstName', 'LastName'], $boldHeader); $writer->addRow(['']); // add empty row bcs fancy :D $writer->addRow(['C-123', 'Bob', 'Johnson']); // send file to browser for downloading $writer->writeToBrowser();
Issues, bugs, features and ...
Feel free to report any sightings =).