ID,Referenca (SKU),Naziv proizvoda,Cena bez PDV,Količina
21,000021,"Fascikla PP  11 rupa 80 mic, 100/pak",705.00,0
23,000023,"Fascikla PP 11 rupa 40 mic, 100/pak",390.00,0
33,000033,Gumica Staedtler B40,44.00,0
    
< ? php
require_once(dirname(__FILE__).'/config/config.inc.php');
require_once(dirname(__FILE__).'/init.php');

set_time_limit(300);
ini_set('memory_limit', '512M');

$id_lang    = (int)Configuration::get('PS_LANG_DEFAULT');
$id_shop    = (int)Context::getContext()->shop->id;
$id_group   = (int)Configuration::get('PS_UNIDENTIFIED_GROUP');
$prefix     = _DB_PREFIX_;

$products = Db::getInstance()->executeS('
    SELECT 
        p.id_product,
        p.reference,
        pl.name,
        sa.quantity
    FROM '.$prefix.'product p
    LEFT JOIN '.$prefix.'product_lang pl 
        ON (p.id_product = pl.id_product 
            AND pl.id_lang = '.$id_lang.' 
            AND pl.id_shop = '.$id_shop.')
    LEFT JOIN '.$prefix.'stock_available sa 
        ON (p.id_product = sa.id_product 
            AND sa.id_product_attribute = 0 
            AND sa.id_shop = '.$id_shop.')
    WHERE p.active = 1
    AND p.reference != \'\'
    AND p.reference IS NOT NULL
    ORDER BY p.id_product ASC
');

$rows = [];
foreach ($products as $product) {
    $specific_price_output = null;

    $price_excl_tax = Product::getPriceStatic(
        (int)$product['id_product'],
        false,  // bez PDV
        null,   // bez kombinacije
        2,      // 2 decimale
        null,
        false,
        true,
        1,
        false,
        null,
        null,
        null,
        $specific_price_output,
        true,
        true
    );

    $rows[] = [
        'id_product' => (int)$product['id_product'],
        'reference'  => $product['reference'],
        'name'       => $product['name'],
        'price_excl' => $price_excl_tax,
        'quantity'   => (int)$product['quantity'],
    ];
}

require_once(dirname(__FILE__).'/vendor/autoload.php');

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Fill;

$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setTitle('Cenovnik');

$headers = ['ID', 'Referenca (SKU)', 'Naziv proizvoda', 'Cena bez PDV', 'Količina'];
foreach ($headers as $col => $header) {
    $sheet->setCellValueByColumnAndRow($col + 1, 1, $header);
}

$sheet->getStyle('A1:E1')->applyFromArray([
    'font' => ['bold' => true, 'color' => ['rgb' => 'FFFFFF']],
    'fill' => [
        'fillType' => Fill::FILL_SOLID,
        'startColor' => ['rgb' => '2E75B6']
    ],
]);

foreach ($rows as $i => $row) {
    $rowNum = $i + 2;
    $sheet->setCellValueByColumnAndRow(1, $rowNum, $row['id_product']);
    $sheet->setCellValueByColumnAndRow(2, $rowNum, $row['reference']);
    $sheet->setCellValueByColumnAndRow(3, $rowNum, $row['name']);
    $sheet->setCellValueByColumnAndRow(4, $rowNum, $row['price_excl']);
    $sheet->setCellValueByColumnAndRow(5, $rowNum, $row['quantity']);

    $sheet->getStyle('D'.$rowNum)
          ->getNumberFormat()
          ->setFormatCode('#,##0.00');
}

foreach (range('A', 'E') as $col) {
    $sheet->getColumnDimension($col)->setAutoSize(true);
}

$sheet->freezePane('A2');

$filename = 'cenovnik_sa_referencom_' . date('Y-m-d_His') . '.xlsx';
$filepath = dirname(__FILE__) . '/' . $filename;

$writer = new Xlsx($spreadsheet);
$writer->save($filepath);

header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="'.$filename.'"');
header('Cache-Control: max-age=0');
header('Content-Length: ' . filesize($filepath));

readfile($filepath);
unlink($filepath);
exit;