How to export data to an Excel file using PHPExcel
Exporting survey results or database records directly into a spreadsheet is a common requirement for web applications. While modern frameworks often handle this with specific packages, understanding the core mechanics of libraries like PHPExcel allows you to build robust export features without heavy dependencies. The following guide details how to connect to a MySQL database and populate a dynamic Excel file, based on patterns found in legacy projects like LimeSurvey.
Connecting to the Database
The foundation of any data export is retrieving the correct information from your storage layer. In many older PHP applications, you will encounter code using the deprecated mysql_* functions. While these examples illustrate the logic, they must be migrated to MySQLi or PDO for any production environment due to security vulnerabilities and lack of support in newer PHP versions.
Here is a functional example using MySQLi to connect to your database safely:
<?php
$host = "localhost";
$user = "survey";
$pass = "password";
$dbname = "database";
// Use mysqli instead of the deprecated mysql_* functions
$conn = new mysqli($host, $user, $pass, $dbname);
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// Prepare a simple SELECT query
$sql = "SELECT * FROM questions";
$result = $conn->query($sql);
?>
Once the connection is established and the query executes successfully, you need to initialize the Excel object. This is where the PHPExcel library comes into play.
require_once 'vendor/autoload.php'; // Or path to PHPExcel/Classes/PHPExcel.php
$objPHPExcel = new \PHPExcel();
$objPHPExcel->setActiveSheetIndex(0);
$column = 'A';
$rowCount = 1;
?>
Mapping Database Fields to Columns
The first step in populating the sheet is to define the headers. You must iterate over the result set's field count to assign each database column to a specific Excel cell. The code below demonstrates how to loop through mysql_num_fields (or use num_fields() in MySQLi) and map the field names to cells starting at column 'A'.
$objPHPExcel->getActiveSheet()->setCellValue($column . $rowCount, "ID"); // Example header
$column++;
?>
In a dynamic scenario, you would replace hardcoded headers with the actual field names returned by the database driver. For instance, using MySQLi:
while ($row = $result->fetch_assoc()) {
foreach ($row as $key => $value) {
// Logic to handle NULL or empty values goes here
}
}
?>
Handling Data and Formatting
A critical aspect of data export is sanitizing the content. Raw database entries often contain characters that might break Excel formulas or display incorrectly if not handled properly. The source material suggests using strip_tags() on text fields to remove HTML tags before writing them to the sheet. This is a prudent step when dealing with user-generated content from surveys.
You must also account for empty strings and null values. If a database field is NULL, you should write an empty string to Excel rather than leaving the cell blank, which can sometimes cause alignment issues in certain spreadsheet applications.
if (!isset($row[$j])) {
$value = NULL;
} elseif ($row[$j] != "") {
$value = strip_tags($row[$j]);
} else {
$value = "";
}
?>
Sending the File to the Browser
The final step involves configuring HTTP headers to force the browser to treat the response as a downloadable Excel file. The Content-Type should be set to application/vnd.ms-excel, and Content-Disposition must specify an attachment with a filename.
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="results.xls"');
header('Cache-Control: max-age=0'); // Prevent caching
$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
?>
Important Considerations
While this approach works well for smaller datasets, there are significant limitations to keep in mind. The legacy mysql_* functions shown in the source code are deprecated and removed from PHP 7.0+. You must refactor your connection logic to use MySQLi or PDO immediately if you plan to maintain this codebase.
Additionally, PHPExcel is no longer actively developed. For new projects, consider migrating to PhpSpreadsheet, which is the modern successor that offers better compatibility with newer PHP versions and supports more file formats like .xlsx by default. The logic for mapping fields and writing cells remains similar, but the class namespaces and initialization methods will differ.
When exporting large datasets (thousands of rows), memory usage can spike quickly. If you encounter "Out of memory" errors, consider implementing chunkingâprocessing data in batches and merging smaller filesâor streaming the output to reduce peak memory consumption. Always ensure that user input triggering these exports is validated to prevent SQL injection when constructing your SELECT statements.
