PHPExcel how to set cell value dynamically

PHPExcel how to set cell value dynamically

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

Stefan Bogdanescu

Founder & Senior Architect

Dynamic Cell Assignment in PHPExcel

The standard example for working with PHPExcel often looks like this: $objPHPExcel->getActiveSheet()->setCellValue('A1', 'cell value here');. While perfectly valid for static data, it forces you to hardcode the coordinate A1. If your goal is to populate a spreadsheet based on a MySQL result set where row counts vary dynamically, relying on string concatenation for cell references like 'A' . $row is inefficient and prone to errors.

PHPExcel provides specific methods to bypass the need for manual reference string construction. The most robust approach is using setCellValueByColumnAndRow. This method accepts four arguments: the object instance, the column index (0-based), the row number (1-based), and the value. By passing integers directly, you decouple your data logic from Excel's naming convention of letters for columns.

// Assuming $objPHPExcel is already initialized
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, 1, $rowData['name']);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, 2, $rowData['email']);

In this example, column 0 corresponds to Excel's column A, and row 1 is the first actual data row (assuming row 1 contains headers). This approach allows you to iterate cleanly through your database results without needing to calculate or concatenate cell addresses.

Iterating Through Result Sets

When fetching data from a MySQL database, you typically loop through your result set. Inside that loop, you increment a row counter for each iteration. The column index remains constant if your table structure is fixed (e.g., always having an ID, Name, Email), but the row number increments with every record.

$rowIndex = 2; // Start at row 2 if row 1 contains headers

foreach ($resultSet as $row) {
    $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $rowIndex, $row['id']);
    $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, $rowIndex, $row['name']);
    
    // Increment row for next iteration
    $rowIndex++;
}

This pattern ensures that even if your query returns 10 rows or 10,000 rows, the code structure remains identical. You avoid the complexity of calculating column letters (A, B, C) using functions like chr(), which can be error-prone when dealing with columns beyond 'Z' where PHPExcel uses double-letter naming conventions (AA, AB).

Handling Variable Columns

While the above method works well for fixed schemas, dynamic reporting often requires handling variable column structures. If your database query returns data that changes per report type, hardcoding column indices becomes a liability. In such cases, you might need to construct the cell reference string dynamically using setCellValue.

$columnIndex = 0;
$rowIndex = 2;

foreach ($resultSet as $row) {
    // Construct 'A1', 'B2', etc. based on current index
    // Note: PHPExcel handles column letters automatically if you use setCellByColumnAndRow, 
    // but if using setCellValue with string refs, you might need a helper function for > Z columns.
    
    $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($columnIndex, $rowIndex, $row['dynamic_column']);
    $columnIndex++;
}

If you must use the traditional setCellValue method with a string reference, remember that standard concatenation ("A" . $row) only works up to column Z. For columns AA and beyond, you would need to implement logic to convert numeric indices to Excel-style labels, or stick strictly to setCellValueByColumnAndRow.

Performance Considerations

A common pitfall when generating large spreadsheets is writing to cells individually within a tight loop. While PHPExcel handles this reasonably well for moderate datasets (under 50,000 rows), performance degrades significantly as file size grows. Each setCellValue call involves object instantiation and internal array updates.

For massive result sets, consider batching writes or using alternative libraries like PhpSpreadsheet (the maintained successor to PHPExcel) which often offers better memory management. However, for standard reporting tasks, the setCellValueByColumnAndRow approach remains the most readable and maintainable solution within the PHPExcel ecosystem. It keeps your codebase clean by separating data iteration logic from coordinate calculation logic.

Our network