Uploading data to EXCEL: PHP and PhpSpreadsheet - why doesn't it work the first time?

Question

PHP

The bottom line is that there is an exportmezved.php file that displays lists of active requests from system users.

<button id="mezved_to_excel" class="btn btn-primary float-right" onclick="location.href='../lib/exporttoexcel/mezvedtoexcel.php'"

When you click on this button in the mezvedtoexcel.php file, an Excel workbook is generated. And here is the problem , when you click on it, the browser does not offer to download the generated file. When pressed again, everything works perfectly.

Here is the code of mezvedtoexcel.php file

require '../config.php'; // здесь настройки подключения к базе данных
require 'vendor/autoload.php'; 
 
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\IOFactory;
 
//Создаем экземпляр класса электронной таблицы
$spreadsheet = new Spreadsheet();
//Получаем текущий активный лист
$sheet = $spreadsheet->getActiveSheet();
//устанавливаем перенос текста
$spreadsheet->getActiveSheet()->getStyle('A:I')->getAlignment()->setWrapText(true);
//устанавливаем выравнивание текста
$spreadsheet->getActiveSheet()->getStyle('A:I')->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_TOP);
//Формируем заголовок
$sheet->getStyle('A1')->applyFromArray([
    'font' => [
      'name' => 'Calibri',
      'size' => 14,
      'bold' => true,
    ],
    'alignment' => [
        'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
        'vertical' =>  \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER,
    ]
]); 
$sheet->getRowDimension(1)->setRowHeight(24);
$sheet->setCellValue('A1', 'Выгрузка межвед запросов от '.date('d.m.Y'));
$sheet->mergeCells('A1:I1');
//Записываем шапку
$sheet->setCellValue('A2', '№ п.п.');
$sheet->setCellValue('B2', 'ПКУ');
$sheet->setCellValue('C2', 'ФИО');
$sheet->setCellValue('D2', 'Адрес');
$sheet->setCellValue('E2', 'Дата заявки');
$sheet->setCellValue('F2', 'Вид МСП');
$sheet->setCellValue('G2', 'Специалист');
$sheet->setCellValue('H2', 'Дата запроса');
$sheet->setCellValue('I2', 'Содержание');
 
//формирование данных
$query = "SELECT mezved.*, orders.pku, orders.declarant_f, orders.declarant_n, orders.declarant_o, orders.address, orders.date_order, orders.id_msp, orders.id_user, msp.name AS msp_name, users.name AS user_name
            FROM mezved JOIN orders
                ON mezved.id_order = orders.id
            LEFT JOIN msp
                ON orders.id_msp = msp.id
            LEFT JOIN users
                        ON orders.id_user = users.id
            WHERE mezved.date_mezved_send IS NULL
            ORDER BY date_mezved DESC";
$result = $mysqli->query($query);
if ($result->num_rows > 0) {
    $n = 2;
    while($row = $result->fetch_assoc()){
        $rowNum = $n + 1;
        $sheet->setCellValue('A'.$rowNum, $n-1);
        $sheet->setCellValue('B'.$rowNum, $row['pku']);
        $sheet->setCellValue('C'.$rowNum, $row['declarant_f'].' '.$row['declarant_n'].' '.$row['declarant_o']);
        $sheet->setCellValue('D'.$rowNum, $row['address']);
        $sheet->setCellValue('E'.$rowNum, $row['date_order']);
        $sheet->setCellValue('F'.$rowNum, $row['msp_name']);
        $sheet->setCellValue('G'.$rowNum, $row['user_name']);
        $sheet->setCellValue('H'.$rowNum, $row['date_mezved']);
        $sheet->setCellValue('I'.$rowNum, $row['content_mezved']);
        $n++;
    }
}
//Установка ширины столбцов
$sheet->getColumnDimension('A')->setWidth(6);
$sheet->getColumnDimension('B')->setWidth(13);
$sheet->getColumnDimension('C')->setWidth(24);
$sheet->getColumnDimension('D')->setWidth(41);
$sheet->getColumnDimension('E')->setWidth(11);
$sheet->getColumnDimension('F')->setWidth(22);
$sheet->getColumnDimension('G')->setWidth(17);
$sheet->getColumnDimension('H')->setWidth(11);
$sheet->getColumnDimension('I')->setWidth(44);
//Установка границы ячеек
$rowCnt = $result->num_rows + 2;
$borderStyleArray = [
    'borders' => [
        'allBorders' => [
            'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
            'color' => ['rgb' => '000000'],
        ],
    ],
];
$sheet->getStyle('A2:I'.$rowCnt)->applyFromArray($borderStyleArray);
 
//Установка параметров страницы
$spreadsheet->getActiveSheet()->getPageSetup()
    ->setOrientation(\PhpOffice\PhpSpreadsheet\Worksheet\PageSetup::ORIENTATION_LANDSCAPE);
$spreadsheet->getActiveSheet()->getPageSetup()->setFitToWidth(1);
$spreadsheet->getActiveSheet()->getPageSetup()->setFitToHeight(0);
$spreadsheet->getActiveSheet()->getPageMargins()->setTop(0.4);
$spreadsheet->getActiveSheet()->getPageMargins()->setRight(0.4);
$spreadsheet->getActiveSheet()->getPageMargins()->setLeft(0.4);
$spreadsheet->getActiveSheet()->getPageMargins()->setBottom(0.4);
 
//здесь идет обновление записей в базе данных - проставляется дата выгрузки запросов, что бы повторно не попадали в выгрузку
$query = "UPDATE `mezved` SET `date_mezved_send` = '".date('Y-m-d')."' WHERE `date_mezved_send` IS NULL";
$result = $mysqli->query($query);
 
//Указываем имя файла
$filename = 'Запросы от '.date('Y-m-d H-i-s').'.xlsx';
 
 
//редирект на сохранение
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="'.$filename.'"');
header('Cache-Control: max-age=0');
// Если пользователь работает в IE 9, то может потребоваться следующее
//header('Cache-Control: max-age=1');
 
$writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
$writer->save('php://output');

The Excel file itself is formed correctly, the problem, as I understand it, is somewhere at the time of sending the save command

. Why does this happen, that it only works the second time. And the next day the same nonsense - only the second time (the server is running 24/7) I can not understand. Tried with different browsers, different computers - the problem is everywhere.

Answer 1

Try instead of outputting to php output save a file and then

echo file_get_contents(...);
unlink(...);