In today's digital world, web applications often require the ability to generate and provide downloadable Excel files for users. PHP, with its versatile libraries and frameworks, makes this task straightforward. In this blog post, we will explore five different methods to download Excel files using PHP, offering solutions for a range of use cases from simple data output to complex data manipulation.
Using PHPExcel (PhpSpreadsheet)
PHPExcel, now known as PhpSpreadsheet after the stewardship by the PHPExcel community, is one of the most widely used libraries for generating Excel files. Here’s how to use it:
- First, install PhpSpreadsheet via Composer:
- Then, create an Excel document:
require 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setCellValue('A1', 'Hello World !');
// Create a writer object
$writer = new Xlsx($spreadsheet);
$filename = 'sample';
// Redirect output to a client's web browser (Xlsx)
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="' . $filename . '.xlsx"');
header('Cache-Control: max-age=0');
$writer->save('php://output');
📝 Note: Ensure the server has the necessary write permissions.
Native CSV Export
If your Excel file needs to contain only simple tabular data, you might not need the full power of PHPExcel. PHP provides native functions to generate CSV files, which can be directly opened by Excel:
- Set the headers to force a download:
- Use `fputcsv` to write data into a stream for output:
header('Content-Type: text/csv');
header('Content-Disposition: attachment; filename="simple-report.csv"');
$output = fopen('php://output', 'wb');
$data = [
['First Name', 'Last Name', 'Email'],
['John', 'Doe', 'john@example.com'],
['Jane', 'Smith', 'jane@example.com']
];
foreach ($data as $row) {
fputcsv($output, $row);
}
fclose($output);
Using Laravel Excel
If you’re working with Laravel, Laravel Excel is an excellent choice, providing an easy-to-use API:
- First, install Laravel Excel:
- Create an export class to handle the data and format:
use App\Exports\UsersExport;
use Maatwebsite\Excel\Facades\Excel;
Route::get('download-excel', function(){
return Excel::download(new UsersExport, 'users.xlsx');
});
Using PHP-ExcelWriter
Before the advent of PHPExcel, PHP-ExcelWriter was a popular library for Excel manipulation:
- Download PHP-ExcelWriter:
- Use the following code to generate an Excel file:
require 'excelwriter.inc.php';
$excel = new ExcelWriter('MyExcel.xls');
$myArr = array(
array('Name', 'Description'),
array('John', 'Manager'),
array('Doe', 'Developer')
);
if ($excel->writeRow($myArr)) {
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment; filename="data.xls"');
$excel->close();
exit;
}
Exporting as HTML and Opening in Excel
Another workaround is to generate an HTML table with a specific structure that Excel can interpret:
- Create an HTML table:
- Set headers for download and display inline in the browser:
<table border="1">
<tr>
<th>Firstname</th>
<th>Lastname</th>
</tr>
<tr>
<td>John</td>
<td>Doe</td>
</tr>
<tr>
<td>Jane</td>
<td>Smith</td>
</tr>
</table>
echo '<html><body>'.htmlspecialchars($table_html).'</body></html>';
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: inline; filename="report.xls"');
💡 Note: The HTML method is less robust than Excel-specific solutions but can be useful for quick, simple tables.
Throughout this exploration of exporting Excel sheets using PHP, we've covered various methods, each with its strengths. PhpSpreadsheet offers robust handling of complex Excel files, native CSV provides simplicity for basic tabular data, Laravel Excel integrates well with Laravel frameworks, PHP-ExcelWriter suits older applications, and the HTML export method serves as a quick and dirty option for basic needs.
What are the advantages of using PhpSpreadsheet for Excel downloads?
+
PhpSpreadsheet allows for complex Excel file creation, supports multiple Excel formats, and provides extensive formatting and styling options which are not available with simpler methods like CSV export or HTML generation.
Is it possible to generate large Excel files without running into memory issues?
+
Yes, both PhpSpreadsheet and native PHP can handle large datasets. PhpSpreadsheet offers a ‘Memory drawing’ feature for large sets of data. CSV export also works well for large datasets due to its simplicity.
Can I include formulas in my Excel files?
+
Yes, PhpSpreadsheet supports the insertion of formulas into cells, allowing for dynamic content and data manipulation within Excel itself.
What should I do if the download file is corrupt?
+
Ensure the headers are set correctly, the filename includes the correct extension, and the data output is formatted properly. Also, check for file system permissions and server configurations.
