sample script to download excel file in pimcore

sample.php

<?php

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


$page = 1;
$limit = 100;

$sql = "TODO";

$sql .= " LIMIT $limit OFFSET ";

$db = \Pimcore\Db::get();
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();

$isHeaderSet = false;
$isEmptyExcel = true;

do {
    $query = $sql . (($page - 1) * $limit);
    $results = $db->fetchAllAssociative($query);

    if (empty($results)) {
        break;
    }

    $isEmptyExcel = false;

    if (!$isHeaderSet) {
        $column = 'A';
        foreach (array_keys($results[0]) as $header) {
            $sheet->setCellValue($column . '1', $header);
            $column++;
        }
        $headerRange = 'A1:' . $column . '1';
        $style = $sheet->getStyle($headerRange);
        $font = $style->getFont();
        $font->setBold(true);

        $isHeaderSet = true;
    }

    $row = 2;
    foreach ($results as $result) {
        $column = 'A';
        foreach ($result as $value) {
            $sheet->setCellValue($column . $row, $value);
            $column++;
        }
        $row++;
    }

    $page++;
} while (true);

if ($isEmptyExcel) {
    $sheet->setCellValue('A1', 'No records found!');
} else {
    $sheet->getColumnDimension('A')->setAutoSize(true);
    $lastColumn = $sheet->getHighestColumn();
    for ($col = 'B'; $col <= $lastColumn; $col++) {
        $sheet->getColumnDimension($col)->setAutoSize(true);
    }
}

$assetsDirectory = PIMCORE_WEB_ROOT . '/var/tmp';
$excelFilePath = $assetsDirectory . "/test.xlsx";

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

return $this->respondWithSuccess('File generated!', [
    'redirectUrl' => $this->generateUrl('routename', [
        'filename' => $filename,
        'token' => $data['token_download']
    ])
]);

sample code to download file:
public function indexAction(Request $request) { $response = new Response(file_get_contents($filePath)); $response->headers->set('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); $response->headers->set('Content-Disposition', "attachment; filename=$name"); return $response; }