PHP code example of robotusers / cakephp-excel
1. Go to this page and download the library: Download robotusers/cakephp-excel library . Choose the download type require .
2. Extract the ZIP file and open the index.php.
3. Add this code to the index.php.
<?php
require_once('vendor/autoload.php');
/* Start to develop here. Best regards https://php-download.com/ */
robotusers / cakephp-excel example snippets
//Application.php
public function bootstrap()
{
...
$this->addPlugin('Robotusers/Excel');
}
use Robotusers/Excel/Registry;
$registry = Registry::instance();
$table = $registry->get('path/to/records.xlsx', 'Albums');
$row = $table->find()->first()->toArray();
//this is how a simple row looks like:
[
'_row' => 1,
'A' => 'Led Zeppelin',
'B' => 'Led Zeppelin II',
'C' => '1969'
]
use Robotusers/Excel/Registry;
$registry = Registry::instance();
$table = $registry->get('path/to/records.xlsx', 'Albums', [
'primaryKey' => 'id',
'columnMap' => [
'A' => 'band',
'B' => 'album',
'C' => 'year'
],
'columnTypeMap' => [
'C' => 'date'
]
]);
$row = $table->find()->first()->toArray();
//this is how a simple row looks like:
[
'id' => 1,
'band' => 'Led Zeppelin',
'album' => 'Led Zeppelin II',
'year' => object(Cake\I18n\Date) {
'time' => '1969-01-01T00:00:00+00:00',
'timezone' => 'UTC'
}
]
$row = $table->newEntity([
'band' => 'Genesis',
'album' => 'Selling England by the Pound',
'year' => '1973'
]);
$table->save($row);
$table->writeSpreadsheet();
use Robotusers/Excel/Registry;
$table = $registry->get('path/to/records.xlsx', 'Albums', [
'startRow' => 2,
'endRow' => 3,
'startColumn' => 'B',
'endColumn' => 'B'
]);
$row = $table->find()->first()->toArray();
//this is how a simple row looks like:
[
'_row' => 1,
'B' => 'Machine Head'
]
use Robotusers/Excel/Registry;
$table = $registry->get('path/to/records.xlsx', 'Albums', [
'startRow' => 2,
'endRow' => 3,
'startColumn' => 'B',
'endColumn' => 'B',
'keepOriginalRows' => true
]);
$row = $table->find()->first()->toArray();
//this is how a simple row looks like:
[
'_row' => 2,
'B' => 'Machine Head'
]
//AlbumsTable.php
public function initialize()
{
$this->addBehavior('Robotusers/Excel.Excel', [
'columnMap' => [
'A' => 'band',
'B' => 'album',
'C' => 'year'
]
]);
}
use Cake\Filesystem\File;
$file = new File('path/to/file.xls');
$spreadsheet = $table->getManager()->getSpreadsheet($file); // \PhpOffice\PhpSpreadsheet\Spreadsheet instance
$worksheet = $spreadsheet->getActiveSheet(); // \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet instance
$table->setWorksheet($worksheet)->readSpreadsheet();
$table->setFile($file)->writeSpreadsheet();
use Robotusers\Excel\Excel\Manager;
$table = TableRegistry::get('SomeTable');
$manager = new Manager();
$file = new File('file.xlsx');
$spreadsheet = $manager->getSpreadsheet($file);
$worksheet = $spreadsheet->getActiveSheet();
$manager->read($worksheet, $table, [
'columnMap' => [
'A' => 'band',
'B' => 'album',
'C' => 'year'
]
]);
//manipulate your data...
//here you have to tell where properties should be placed
$manager->write($table, $worksheet, [
'propertyMap' => [
'band' => 'A',
'album' => 'B',
'year' => 'C'
]
]);
//to actually save the file you have to call save()
$writer = $manager->save($spreadsheet, $file);