Keywords: PHP | Excel | PDF | Export | HTML Table
Abstract: This article explores various methods to export HTML tables to Excel and PDF formats in PHP, focusing on the PHPExcel library for Excel export and PrinceXML for PDF. It includes step-by-step code examples, comparisons with other approaches like CSV and client-side exports, and best practices for implementation.
Introduction
In web applications, exporting data from HTML tables to formats like Excel and PDF is a common requirement for reporting and data analysis. This article provides an in-depth analysis of server-side and client-side methods to achieve this in PHP.
Overview of Export Methods
There are multiple approaches to export HTML tables, including server-side processing with PHP libraries and client-side solutions using JavaScript.
Server-Side Export
Server-side methods involve generating files on the server and sending them to the client. This ensures data consistency and security.
Using PHPExcel Library
PHPExcel is a powerful library for creating Excel files. First, install it via Composer. Then, use it to generate XLSX files from database data.
// Example code: Using PHPExcel
require_once 'vendor/autoload.php';
$spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setCellValue('A1', 'ID');
$sheet->setCellValue('B1', 'Name');
// ... more code to populate data
$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
$writer->save('export.xlsx');Note: The original mysql_* functions are deprecated; use PDO or MySQLi for database queries.
Using CSV Functions
For simple exports, PHP's built-in CSV functions can be used. This method is lightweight but limited in formatting.
header('Content-Type: text/csv');
header('Content-Disposition: attachment; filename="export.csv"');
$output = fopen('php://output', 'w');
fputcsv($output, array('ID', 'Name', 'Subject', 'Held Classes', 'Attended Classes'));
// ... fetch data and write rows
fclose($output);Simple HTML Output
Another method is to output HTML with Excel-specific headers, which Excel can interpret.
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment; filename="export.xls"');
echo "<table><tr><td>ID</td><td>Name</td></tr></table>";Client-Side Export
Client-side methods use JavaScript to export data without server interaction, suitable for static data.
Using DataTables and HTML5 API
DataTables provides buttons for export, leveraging HTML5 APIs. This requires including the necessary libraries.
// JavaScript code: Using DataTables
$('#example').DataTable({
dom: 'Bfrtip',
buttons: [
'copy', 'csv', 'excel', 'pdf', 'print'
]
});This method is efficient for tables rendered with DataTables but may not handle large datasets well.
Implementation Details
For the PHPExcel approach, integrate it with the original query. Ensure to escape inputs to prevent SQL injection.
// Rewritten code: Based on original query
$pdo = new PDO("mysql:host=localhost;dbname=test", "username", "password");
$stmt = $pdo->prepare("SELECT * FROM attendance WHERE year = ? AND branch = ? AND sem = ? AND sec = ?");
$stmt->execute([$_SESSION['year'], $_SESSION['branch'], $_SESSION['sem'], $_SESSION['sec']]);
$data = $stmt->fetchAll(PDO::FETCH_ASSOC);
// Then use PHPExcel to create fileAdvantages and Disadvantages
Server-side methods offer better control and security, while client-side methods are faster for users. PHPExcel provides rich formatting but requires additional libraries.
Conclusion
Exporting HTML tables to Excel and PDF in PHP can be efficiently achieved using libraries like PHPExcel and PrinceXML. Choose the method based on requirements such as data size, formatting needs, and security considerations.