Exporting PHP Tables to Excel and PDF: A Modern Approach
Your current implementation relies on mysql_* functions, which are deprecated and removed in modern PHP versions, posing a significant security risk due to SQL injection vulnerabilities. While you need an endpoint like excel.php to handle the export logic, simply dumping raw data into a file isn't the most robust solution for generating formatted spreadsheets or PDFs. The industry standard has shifted away from direct database exports toward dedicated libraries that handle formatting, headers, and binary data streams correctly.
To generate an Excel file dynamically, the most reliable method is to leverage PhpSpreadsheet. This library allows you to construct a real .xlsx file object in memory and force a download via HTTP headers. It avoids the clutter of older CSV-only solutions or fragile HTML-to-Excel conversions.
Here is how you should structure your excel.php file. First, ensure you have the required Composer dependency installed:
composer require phpoffice/phpspreadsheet
Inside excel.php, you need to retrieve the data source and convert it into a spreadsheet object before triggering the download. The critical step is setting the correct HTTP headers so the browser treats the response as an attachment rather than displaying it in a new tab.
<?php
require __DIR__ . '/vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
// Replace this with your secure data retrieval logic.
// Do not use mysql_query() or mysql_real_escape_string().
// Use PDO or mysqli with prepared statements instead.
$year = $_SESSION['year'] ?? '';
$branch = $_SESSION['branch'] ?? '';
$sem = $_SESSION['sem'] ?? '';
$sec = $_SESSION['sec'] ?? '';
// Example using PDO (hypothetical secure query)
try {
$pdo = new PDO('mysql:host=localhost;dbname=your_database', 'user', 'pass');
$stmt = $pdo->prepare("SELECT * FROM attendance WHERE year = :year AND branch = :branch AND sem = :sem AND sec = :sec");
$stmt->execute([
':year' => $year,
':branch' => $branch,
':sem' => $sem,
':sec' => $sec
]);
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
// Create the spreadsheet object
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
// Define headers
$headers = ['idno', 'name', 'subject', 'held_classes', 'attended_classes'];
foreach ($headers as $colIndex => $header) {
$sheet->setCellValue('A' . ($colIndex + 1), $header);
}
// Populate data rows
$rowCounter = 2;
foreach ($rows as $data) {
$sheet->setCellValue('A' . $rowCounter, $data['idno']);
$sheet->setCellValue('B' . $rowCounter, $data['name']);
$sheet->setCellValue('C' . $rowCounter, $data['subject']);
$sheet->setCellValue('D' . $rowCounter, $data['held_classes']);
$sheet->setCellValue('E' . $rowCounter, $data['attended_classes']);
$rowCounter++;
}
// Set headers for file download
header("Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
header("Content-Disposition: attachment;filename=attendance_report.xlsx");
header("Cache-Control: max-age=0");
// Output to client
$writer = new Xlsx($spreadsheet);
$writer->save('php://output');
exit; // Important: Stop script execution after sending file
} catch (\Exception $e) {
http_response_code(500);
die("Error generating file: " . $e->getMessage());
}
?>
Handling PDF Exports
Generating a PDF directly from HTML tables is notoriously difficult because browsers render HTML differently than PDF engines. Attempting to convert your existing HTML output into a PDF using server-side libraries often results in broken layouts or missing styles. The recommended approach for server-side PDF generation in PHP is Dompdf.
Dompdf takes an HTML string, applies CSS, and renders it into a PDF document. You would replace the Excel logic in excel.php (or create a separate route) to use Dompdf:
composer require dompdf/dompdf
In this scenario, you would pass your raw HTML stringâgenerated from the same query resultsâto Dompdf's loadHtml() method and save it to php://output. However, be aware that complex CSS often breaks in PDFs. It is frequently safer to generate a clean HTML table specifically for DOMPDF rather than passing the full page with background images like bg.jpg mentioned in your original code snippet.
Critical Implementation Details
- Security First: The original code uses
mysql_*functions, which are removed since PHP 5.5. You must migrate to PDO or MySQLi. Furthermore, never concatenate user input directly into SQL strings even withmysql_real_escape_string; always use prepared statements. - Session Management: Ensure your session is started (
session_start()) before accessing$_SESSIONvariables in the export logic. If you redirect toexcel.php, passing data via URL parameters can be safer if sessions are not strictly required on that specific page, but it adds length to URLs. - Headers Must Be First: The HTTP headers (
Content-Type,Content-Disposition) must be sent before any echo statements or HTML output. If you include a<body>tag in the same script as the logic, the file download will fail because the browser receives HTML text instead of binary data.
Conclusion on Export Strategies
While there are older methods involving forcing Excel to open CSV files or using ob_start('zlib_encode') for compression, these lack the structure needed for professional reports. Using a dedicated library like PhpSpreadsheet ensures that your exported files retain formatting, calculated columns (if any), and proper data types. For PDFs, Dompdf provides the necessary bridge between your HTML data representation and the PDF format, though you should strip unnecessary styling to ensure compatibility.
For more resources on building robust Laravel applications or migrating legacy PHP code, see https://laravelcompany.com.
