Using PHPExcel to make automatic generated excel files

Using PHPExcel to make automatic generated excel files

  1. Blog
  2. 15 November 2026
  3. 3 min read

Stefan Bogdanescu

Founder & Senior Architect

Automating Excel Export with PHPExcel: A Practical Approach

Building dynamic reports is a common requirement in web applications, and leveraging database data to populate an Excel file is often the most efficient way to present tabular information to users. You have successfully queried your MySQL database to retrieve columns like name and age, but the transition from fetching rows to writing them into a spreadsheet structure requires a specific understanding of how PHPExcel handles worksheet objects.

The core challenge lies in iterating over the result set and mapping each row of data to corresponding cells in the Excel grid. PHPExcel does not automatically flush data from a database cursor into a sheet; you must explicitly instruct it where to place every value. This is achieved by setting values at specific coordinates, typically using zero-based indexing.

To begin, you need to instantiate the PHPExcel class and create a new workbook instance. Once the workbook exists, you can access the active worksheet, which serves as your canvas for data entry. The standard practice is to define headers in the first row and subsequent rows for the actual data records.

require_once 'Classes/PHPExcel.php';

$objPHPExcel = new PHPExcel();
$sheet = $objPHPExcel->getActiveSheet();

// Define headers in Row 1
$sheet->setCellValue('A1', 'NAME');
$sheet->setCellValue('B1', 'AGE');

Once the structure is established, you can loop through your database results. In your example, you are using the legacy mysql_* functions. It is crucial to note that these functions were deprecated in PHP 5.5 and removed in PHP 7.0 due to security vulnerabilities. If you are deploying this code today, you must migrate to mysqli or PDO. However, assuming the logic remains the same, the iteration pattern involves fetching a row inside a while loop and then writing those values to the next available row index.

$query = "SELECT * FROM bestelling";
$result = mysql_query($query) or die(mysql_error());

$sheet->setActiveSheetIndex(0); // Ensure we are on the first sheet

$rowIndex = 2; // Start writing data in the second row (after headers)

while($row = mysql_fetch_array($result)) {
    $name = $row['name'];
    $age = $row['age'];
    
    // Write data to specific columns. 
    // A is column 1, B is column 2. Row starts at 2.
    $sheet->setCellValue('A' . $rowIndex, $name);
    $sheet->setCellValue('B' . $rowIndex, $age);
    
    $rowIndex++;
}

This approach works well for small datasets, such as your example of ten people. However, performance becomes a significant trade-off when dealing with large volumes of data. Every call to setCellValue involves writing data to the internal memory structure of the PHPExcel object. If you are exporting thousands of rows, this method can lead to increased memory usage and slower execution times.

For high-volume exports, a more performant strategy is to use PHPExcel_Cell::setValueFromArray or, in modern implementations using PHPSpreadsheet, utilizing the array_push method with cell iterators. Additionally, if your dataset is large enough to cause memory issues before reaching the disk, consider generating the file directly on the server and stream it to the browser using a temporary file handle rather than loading the entire workbook into RAM at once.

Finally, do not forget to save the resulting object. Without calling save(), all your work will be lost when the script terminates. You typically need to specify the filename and path where the user should download the file from.

// Save the file to a specific location
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('path/to/exported_file.xlsx');

By explicitly managing row indices and column references, you gain full control over the layout of your exported data. This manual mapping ensures that your headers align perfectly with your data columns, creating a clean, professional-looking report for your end users.

Our network