Php to Excel : fill the output Excel file with a loop

Question

I want to create an Excel file (with PhpExcel) and fill it with the content of a MySQL query.

I only have one column so the result will look like this :

$sheet->setCellValueByColumnAndRow(0, $i, $content)

So I have to loop inside my query and create a counter to fill each row corresponding to each item of the column ptlum of my content.
So the goal is to have the following result :

1 AX001
2 AX003
3 AX012

The code is the one :

$column = 1;
while($data = mysql_fetch_assoc($result)) {

    $workbook = new PHPExcel;
    $sheet = $workbook->getActiveSheet();
    $sheet->setCellValueByColumnAndRow(0, $column, $data['ptlum']);
    //echo($column. " "  . $data['ptlum']. " ");
    $column = $column + 1; //or $column++;

The problem is that my Excel file is empty.. If i put a number instead of $column in the setCellValueByColumnAndRow line it works. But the variable does not work.. On the other hand if I put "$column = 1;" inside the loop, my Excel file will always contain one only row..

Have you an idea ?

Thank you very much !

score 2 · Accepted Answer · edited May 23 '17 at 12:07

2

You just have to change the call to setCellValueByColumnAndRow for each column and increment it:

$sql = "SELECT ptlum FROM `ptlum` WHERE nomcom = 'AIGREFEUILLE' and RIGHT (date,4) < 2015 and RIGHT(date,4) > 0 ";
$result = mysql_query($sql);

$row = 1; // 1-based index
$column = 1;
while($data = mysql_fetch_assoc($result)) {
    $sheet->setCellValueByColumnAndRow($column, $row, $data['ptlum']);
    $column = $column + 1; //or $column++; if you prefer
}

As you see, you can retrieve the field/column you want with mysql_fetch_assoc, returning an associative array.

Also, you don't have to include the field(s) of your WHERE condition(s) in the SELECT.

Then finally, you should replace the deprecated mysql_* function by their equivalents mysqli_*, as explained here.

Edit:

For your "new" problem, this code should work:

$sql = "SELECT ptlum FROM `ptlum` WHERE nomcom = 'AIGREFEUILLE' and RIGHT (date,4) < 2015 and RIGHT(date,4) > 0 ";
$result = mysql_query($sql);

$row = 1; // 1-based index
$column = 1;
$workbook = new PHPExcel;
$sheet = $workbook->getActiveSheet();
while($data = mysql_fetch_assoc($result)) {
    $sheet->setCellValueByColumnAndRow($column, $row, $data['ptlum']);
    $column = $column + 1; //or $column++; if you prefer
}

First, don't instanciate your workbook and sheet in each loop, do it before, once.

Second, you had your arguments in the wrong order, it's column then row, not the inverse, as explicited in the method name.

edited May 23 '17 at 12:07

Community

1
1

answered Nov 25 '15 at 11:32

Veve

6,643
5
39
58

Thank you for your answer. Actually I have not the correct echo.. First my query is based on two fields (ptlum and RIGHT(date,4)) and I don't managed to get only the field ptlum in my echo.. I just want to increment **i** which is my column index (not row I'm sorry) and ptlum. I tried your snippet with an echo ($i . " " . $value. " ") but it does not work.. I have the following result: http://imagik.fr/images/2015/11/25/phpexcel.png – Julien Nov 25 '15 at 13:56
I have a result like 1 AA15 2 AA15 3 AA15 4 AA15 instead of 1 AA15 2 AA16 3 AA17 4 AA18. What's more the other field is written (DATE) whereas I only want the **ptlum** field.. Thanks ! – Julien Nov 25 '15 at 13:57
@Julien look at my edit, is it what you wanted? The results are on one row. – Veve Nov 25 '15 at 14:03
@Julien look at the explainations I've added, it might help ;) – Veve Nov 25 '15 at 15:40
I'm sorry I have another problem.. The echo instruction works very well but the output Excel file is empty.. I updated my first message, could you take a look ? Thanks again Veve ! – Julien Nov 25 '15 at 16:14
@Julien look at my edit, it should work. I suggest you to make another question instead of (almost) completely change your question the next time ;) – Veve Nov 25 '15 at 16:27
Ok I understand, it works thank you ! I'm sorry, next time I'll make another question. Thanks again ! – Julien Nov 25 '15 at 16:29
@Julien no problem, glad it helped you :) – Veve Nov 25 '15 at 16:45

score 0 · Answer 2 · answered Nov 25 '15 at 14:16

Maybe this is the thing you wanted:

$sql = "SELECT ptlum, RIGHT(date,4)  FROM `ptlum` WHERE nomcom = 'AIGREFEUILLE' and RIGHT (date,4) < 2015 and RIGHT(date,4) > 0 ";
$result = mysql_query($sql);

$i = 0;
while($data = mysql_fetch_assoc($result)) {
    $sheet->setCellValueByColumnAndRow(0, $i, $i+1); //1-based index
    $sheet->setCellValueByColumnAndRow(1, $i, $data['ptlum']);
    $i++;
}

Php to Excel : fill the output Excel file with a loop

2 Answers2