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.

Answer 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++;
}

Answer 2

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

Loop through your result set from MySQL
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.
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);
    }

友情链接