PhpSpreadsheet - Download file instead of saving it
When using PhpOffice\PhpSpreadsheet to generate dynamic reports, the standard workflow involves instantiating a writer and calling the save() method to write binary data directly to disk. This approach works fine for static files stored on the server. However, when you need to serve that generated Excel file immediately to the client via HTTP, you cannot rely on writing to a physical path. You must extract the resulting byte stream from the writer object and return it with the correct HTTP headers.
The core issue arises when switching between CSV and XLS/XLSX formats regarding delimiter handling. As noted in common troubleshooting scenarios, exporting as .csv often works out of the box because the format is text-based. If you define your own delimiters (like semicolons) in a custom CSV handler, they might be treated literally or ignored depending on the implementation. Conversely, .xls and .xlsx formats are binary. They do not use simple text delimiters; their structure relies entirely on the internal architecture of the spreadsheet library to manage columns and rows.
To force a download of an XLS or XLSX file generated by PhpSpreadsheet, you must leverage the StreamInterface capabilities introduced in later versions of the library. The modern approach avoids writing to disk entirely until the final response is sent.
Here is how you handle this using the Xlsx writer:
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\IOFactory;
// Create and populate the spreadsheet
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setCellValue('A1', 'Hello World !');
$sheet->setCellValue('B2', 'This is a test with semicolon: ;'); // Delimiters handled automatically
// Create the writer object, but do not call save() yet
$writer = new Xlsx($spreadsheet);
// Generate an instance of the StreamInterface that can hold the file content in memory
$output = $writer->save('php://temp');
// Close the stream to finalize the writing process
fclose($output);
// Set HTTP headers for a file download
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="export.xlsx"');
header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
header('Pragma: public');
// Output the content to the browser
echo $output->getContents();
exit;
Why This Works
The save() method in PhpSpreadsheet can accept a string path or a stream object. When you pass 'php://temp', you are directing the library to write the binary representation of the Excel file into an anonymous temporary stream rather than a file system location. Once the stream is populated and closed, $output->getContents() retrieves the raw binary data containing the formatted cells, rows, and styles.
Handling Delimiters in Binary Formats
The specific problem mentioned regarding semicolon delimiters not being interpreted usually stems from trying to force CSV behavior onto an XLS file or manually constructing the content string before passing it to a writer.
When using PhpOffice\PhpSpreadsheet with .xls or .xlsx formats, you generally do not need to manually configure delimiters like you would for CSV. The library handles cell formatting internally. If your data contains a semicolon (e.g., Name; Value), the library treats it as a single string value within that specific cell. It does not split the cell into two columns automatically unless you have explicitly defined multiple cells or used column splitting logic.
If you are seeing everything written into one column because of delimiter issues, ensure you are not manually concatenating CSV strings and attempting to write them to an XLSX file using a standard writer. The binary format expects a specific XML structure (for .xlsx) or OLE2 structure (for .xls). Passing raw text with delimiters directly to a writer without letting the library manage the grid will result in malformed sheets.
Trade-offs and Best Practices
Using php://temp keeps memory usage predictable as long as the spreadsheet size fits within available RAM, which is typical for most reporting needs. However, if the file becomes massive (hundreds of thousands of rows), you might encounter memory limits depending on your PHP configuration (memory_limit).
In a Laravel application, this logic can easily be placed in an Action or Controller method. You do not need to save the file to public/storage just to serve it; doing so introduces unnecessary I/O overhead and potential permission issues. By streaming the content directly to the output buffer, you ensure the download happens instantly upon request execution.
If you require a .xls extension specifically (the older binary format), you would swap the writer class to use PhpOffice\PhpSpreadsheet\Writer\Xls;. The logic remains identical: instantiate the writer with a stream, close it, and echo the contents.
