PHP code example of simtecsystem / cakephp-excel

    
        
<?php
require_once('vendor/autoload.php');

/* Start to develop here. Best regards https://php-download.com/ */

    

simtecsystem / 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);