如何在PHPExcel中的一个单元格中插入具有相同ID的值

I have an excell file generated from php request. SO what i have inside excel file is: username, project, process. Here is first table username:

uid| username| salary|  
---+---------+-------+
1  | bob     | 0     |
2  | barikan | 0     |
3  | beny    | 0     |

Second table process:

projectNo| process | proc_leader|  
---------+---------+------------+
1610004  | ANM BLD | barikan    |
1610004  | BGD CUP | barikan    |
1610005  | ANM BLD | bob        |
1610006  | BGD CUP | beny       |

And project table:

projectNo| title|  
---------+------+
1610004  | TMNT |
1610005  | LEON |
1610006  | MAT  |

Inside my excel file i want to print something like this:

username|      TMNT        | LEON    | MAT     | 
--------+------------------+---------+---------+
barikan | ANM BLD, BGD CUP | N/A     | N/A     |
beny    | N/A              | N/A     | BGD CUP |
bob     | N/A              | ANM BLD |         |

I am able to print project and username:

$objPHPExcel = new PHPExcel();
$F = $objPHPExcel->getActiveSheet();
$F->setCellValue('A1', "Username");

$request="SELECT * FROM project";
$result=$conn->query($request);//get the result (ressource)

$Letter='B';
while($row = $result->fetch_assoc())
{//extract each record

    $projectNo=$row["projectNo"];
    $F->setCellValue($Letter.'1', ' '.$projectNo.' ');
    $F->getRowDimension('1')->setRowHeight(20);
    $F->getColumnDimension($Letter)->setWidth(30);
    ++$Letter;
}

$request2="SELECT *  FROM user WHERE username='barikan'";
//$request2="SELECT *  FROM user ORDER BY username ASC";
$result2= $conn->query($request2);//get the result (ressource)

$Number=2;
while($row2 = $result2->fetch_assoc())
{//extract each record
    //$projectNum=$row2["projectNo"];
    $username=$row2["username"];
    $F->setCellValue('A'.$Number, $row2["username"]);
    $F->getColumnDimension('A')->setWidth(20);
    ++$Number;
}

And still working on how to print processes for each username, but for now i just printing it for one name only:

$request3="SELECT process, projectNo FROM proc_leader INNER JOIN user pl ON (proc_leader = pl.username)
WHERE 
proc_leader.proc_leader = 'barikan' ";
//proc_leader.proc_leader = '$username' AND proc_leader.projectNo = '$projectNo'";
$result3= $conn->query($request3);//get the result (ressource)
$Let='B';
while($row3 = $result3->fetch_assoc())
{//extract each record
    $prNo=$row3["projectNo"];
    $F->setCellValue($Let.'2', ''.$prNo.'  '.$row3["process"].'');
    ++$Let;
    //++$Number;
}

So how to put process with the same id in same cell? Thank you

EDIT

Alright, thanx for idea to use group_concat, but when im running sql i dont get all names. Look, here is my proc_leader table: proc_leader

And what i got after running query: SELECT projectNo, group_concat(process) AS processes, proc_leader FROM proc_leader GROUP BY projectNo

result

Sousername "candy" is missing. Why? Thanks for help

查看全部
dongzhiyong8577
dongzhiyong8577
2016/10/14 07:46
  • excel
  • php
  • mysql
  • 点赞
  • 收藏
  • 回答
    私信
满意答案
查看全部

1个回复