Download the data in EXCEL: PHP and PhpSpreadsheet — why does not work the first time?

The bottom line is there is a file exportmezved.php which displays a list of active requests users of the system.

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


Clicking on this button in the file mezvedtoexcel.php the formation of the Excel workbook. Here the problem, when you click on it, the browser offers to download the generated file. Press it again everything works perfectly.

Here is the code of the file mezvedtoexcel.php
require '../config.php'; // here the connection settings to the database
require 'vendor/autoload.php'; 

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

//Create an instance of class spreadsheet
$spreadsheet = new Spreadsheet();
//Get the current active sheet
$sheet = $spreadsheet->getActiveSheet();
//set the text wrapping
$spreadsheet->getActiveSheet()->getStyle('A:I')->getAlignment()->setWrapText(true);
//set text alignment
$spreadsheet->getActiveSheet()->getStyle('A:I')->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_TOP);
//Generated header
$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', 'Upload mezwed requests '.date('d.m.Y'));
$sheet->mergeCells('A1:I1');
//Write a header
$sheet->setCellValue('A2', 'n PP');
$sheet->setCellValue('B2', 'PKU');
$sheet->setCellValue('C2', 'name');
$sheet->setCellValue('D2', 'Address');
$sheet->setCellValue('E2', 'application date');
$sheet->setCellValue('F2', 'View SMEs');
$sheet->setCellValue('G2', 'Specialist');
$sheet->setCellValue('H2', 'request date');
$sheet->setCellValue('I2', 'Content');

//the data
$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
 Date_mezved ORDER BY 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++;
}
}
//Setting column width
$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);
//Setting cell borders
$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);

//Setting page parameters
$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);

//here goes the update records in the database, specifying the date of discharge requests that would not re-fell in unloading
$query = "UPDATE `mezved` SET `date_mezved_send` = '".date('Y-m-d')."' WHERE `date_mezved_send` IS NULL";
$result = $mysqli->query($query);

//Specify file name
$filename = 'Requests '.date('Y-m-d H-is').'.xlsx';


//redirect to the preservation
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="'.$filename.'"');
header('Cache-Control: max-age=0');
// If the user is running in IE 9, you may need the following
//header('Cache-Control: max-age=1');

$writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
$writer->save('php://output');


The Excel file is generated correctly, the problem is, as I understand it, somewhere at the time of sending of the request

Why is it that only works the second time. And the next day the same nonsense - only the second time (the server is running 24/7) can't understand. Tried with different browsers, different computers - the problem is everywhere.
April 19th 20 at 12:07
1 answer
April 19th 20 at 12:09
Try, instead of displaying output in php to save the file, and then
echo file_get_contents(...);
unlink(...);

Find more questions by tags PhpSpreadsheetExcelPHP