PHP code example of avadim / fast-excel-templator

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

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

    

avadim / fast-excel-templator example snippets

// Open template and set output file
use avadim\FastExcelTemplator\Excel;

$excel = Excel::template($tpl, $out);
// Get the first sheet
$sheet = $excel->sheet();

$fillData = [
    '{{COMPANY}}' => 'Comp Stock Shop',
    '{{ADDRESS}}' => '123 ABC Street',
    '{{CITY}}' => 'Peace City, TN',
];

// Set replacements of entire cell values for the sheet
// If the value is '{{COMPANY}}', then this value will be replaced,
// but if the value 'Company Name {{COMPANY}}', then this value will not be replaced 
$sheet->fill($fillData);

// Set replacements of any occurring substrings
// If the value is '{{DATE}}' or 'Date: {{DATE}}', then substring '{{DATE}}' will be replaced,
$replaceData = ['{{BULK_QTY}}' => 12, '{{DATE}}' => date('m/d/Y')];
$sheet->replace($replaceData);

// Transfer rows 1-6 from templates to output file
$sheet->transferRowsUntil(6);

// Get the row number 7 as a template and go to the next row in the template
$rowTemplate = $sheet->getRowTemplate(7);

// Fill row template and insert it into the output
foreach ($allData as $record) {
    $rowData = [
        // In the column A wil be written value from field 'number'
        'A' => $record['number'],
        // In the column B wil be written value from field 'description'
        'B' => $record['description'],
        // And so on...
        'C' => $record['price1'],
        'D' => $record['price2'],
    ];
    $sheet->insertRow($rowTemplate, $rowData);
}

// Method transferRows() without arguments transfers remaining rows from the template to the output file 
$sheet->transferRows();

// ...
// Save new file
$excel->save();

use avadim\FastExcelTemplator\Excel;

$excel = Excel::template($tpl, $out);
$sheet = $excel->sheet();

$sheet->rows(function ($sourceRowNum, $targetRowNum, $rowData) {
    // $rowData is an instance of the RowTemplate

    // skip the first row
    if ($sourceRowNum === 1) {
        return null;
    }
    // $rowData
    // if a value of cell 'A' then break
    if ($rowData->getValue('A') > 5) {
        return false;
    }
    // write value to cell 'B'; if the cell 'B' does not exist, it will be created
    $rowData->setValue('B', $rowData->getValue('A') * 2);
    // return modified row
    return $rowData;
});
$excel->save();

$sheet->rows(function ($sourceRowNum, $targetRowNum, $rowData) {
    // Clone the last cell of the row and add them to the end of the row and assign it the value 123
    $rowData->appendCell()->withValue(123);

    return $rowData;
});

$sheet->rows(function ($sourceRowNum, $targetRowNum, $rowData) {
    // Clone the cell 'B' and add them to the end
    $rowData->appendCell('B');

    // Clone the last cell three times
    $rowData->appendCell(null, 3)->withValues([111, 222, 333]);

    return $rowData;
});

$sheet->rows(function ($sourceRowNum, $targetRowNum, $rowData) {
    // Clone the cell 'A' to the cell 'E' and assign it the SUM()
    $rowData->cloneCell('A', 'E')
        ->withValues(['=SUM(A' . $targetRowNum . ':E' . $targetRowNum . ')']);

    return $rowData;
});

$sheet->rows(function ($sourceRowNum, $targetRowNum, $rowData) {
    // Remove cells B and D from this row, shifting the remaining cells left
    $rowData->removeCells(['B', 'D']);

    return $rowData;
});

// Capture two differently styled rows (7 and 8) as templates
$rowTemplates = $sheet->getRowTemplates(7, 8);

foreach ($allData as $record) {
    // insertRow() cycles through the templates: 7, 8, 7, 8, ...
    $sheet->insertRow($rowTemplates, ['A' => $record['number']]);
}

$template = $sheet->getRowTemplate(7);
$sheet->insertRow($template, ['A' => 1, 'B' => 'First value']);
$sheet->insertRow($template, ['A' => 2]); // B comes from template row 7

$excel = Excel::template($tpl, $out);

foreach ($excel->sheets() as $sheet) {
    $sheet->fill(['{{TITLE}}' => 'Report']);
    $sheet->transferRows();
}

$excel->save();

$excel = Excel::template($tpl, $out, ['strict_metadata' => true]);
foreach ($excel->sheets() as $sheet) {
    $sheet->fill(['{{TITLE}}' => 'Report'])->transferRows();
}
$excel->save(); // throws on a detected unsupported transformation

foreach ($excel->metadataDiagnostics() as $item) {
    // sheet: XML part path; code: diagnostic code; message: explanation
    error_log($item['sheet'] . ': ' . $item['code'] . ' — ' . $item['message']);
}

$excel->download('invoice.xlsx'); // sends download headers and outputs the file
$excel->output('invoice.xlsx');   // output() is an alias of download()