PHPExcel how to set cell value dynamically

Question

How to set cell/column value dynamically using PHPExcel library?

I am fetching result set from MySQL database and I want to write data in excel format using PHPExcel library. Looking at example

$objPHPExcel->getActiveSheet()->setCellValue('A1', 'cell value here');

indicates that we have to hard code cell/column reference As 'A1', then it writes to cell/column A1. How I can increment cell/column and/or row reference based on rows and corresponding column values from result set?

Please guide.

Solution 1

I asume you have connected to your database already.

$sql = "SELECT * FROM my_table";
$result = mysql_query($sql);

$row = 1; // 1-based index
while($row_data = mysql_fetch_assoc($result)) {
    $col = 0;
    foreach($row_data as $key=>$value) {
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $value);
        $col++;
    }
    $row++;
}

Solution 2

I don't have much experience working with php but from a logic standpoint this is what I would do.

1. Loop through your result set from MySQL
2. In Excel you should already know what A,B,C should be because those are the columns and you know how many columns you are returning.
3. The row number can just be incremented with each time through the loop.

Below is some pseudocode illustrating this technique:

    for (int i = 0; i < MySQLResults.count; i++){
         $objPHPExcel->getActiveSheet()->setCellValue('A' . (string)(i + 1), MySQLResults[i].name); 
        // Add 1 to i because Excel Rows start at 1, not 0, so row will always be one off
         $objPHPExcel->getActiveSheet()->setCellValue('B' . (string)(i + 1), MySQLResults[i].number);
         $objPHPExcel->getActiveSheet()->setCellValue('C' . (string)(i + 1), MySQLResults[i].email);
    }

Comments

[Álvaro González]

A note from the manual: In PHPExcel column index is 0-based while row index is 1-based. That means 'A1' ~ (0,1)

[liz]

You'll also need to reset the $col variable after each for loop. Add '$col = 0;' after '$row++;'

[Steven Scott]

Results might not be one to one, but many data rows written to columns, which would need a loop to move through the columns.