CODEWITHSUNDEEP
phpphpexcel
user2983450
user2983450

Reputation: 11

PHPExcel Multiple sheets

Basically I need to take this SQL data and have it create a workbook with different sheet names for each user and the data that belongs to that user. So Smith_Joe would have a sheet named Smith_Joe and in that sheet it would show his information. Here is the sql data:

TeamName      UserID     Deparment     Description
---------------------------------------------------
Smith_Joe    JOE4S       52200          Sales
Smith_Joe    JOE4S       53002          Budget
Smith_Joe    JOE4S       43302          Dev
Black_BJ     BJ3OO       43332          Mrkt
Steh_Tray    ST44S       65573          Prod
Steh_Tray    ST44S       52200          Sales

So far I have not had much luck but here is the code I have so far:

while($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)){


    $tn         =   $row['TeamName'];
    $userid     =   $row['UserID'];
    $dep        =   $row['Departments'];
    $des        =   $row['Description'];


    $objWorksheet = new PHPExcel_Worksheet($objPHPExcel);
    $objPHPExcel->addSheet($objWorksheet);
    $objWorksheet->setTitle(''. $tn);


    $objWorksheet->setCellValue('A2' , $tn);
    $objWorksheet->setCellValue('B2' ,  $userid);
    $objWorksheet->setCellValue('C2', $dep);
    $objWorksheet->setCellValue('D2' , $des);

The problem I am getting is I get all the sheets with names but each row is listed on separate sheets. So for an example with the code above. I get Smith_Joe with 3 sheets with one record per sheet. Black_BJ get one sheet and Steh_Tray gets 2 sheets. They all should just have one sheet each with all their information on it.

Upvotes: 0

Views: 11899

Answers (2)

m_grif
m_grif

Reputation: 79

You can first select distinct TeamName, then foreach TeamName in sql query select the data from the table and write to the sheet eg.

 // write query to select distinct TeamName here
$stmt = ...sql query select distinct TeamName from table

 // Then loop through

while($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC))
{
    $tn = $row['TeamName'];

    $objWorksheet = new PHPExcel_Worksheet($objPHPExcel);
    $objPHPExcel->addSheet($objWorksheet);
    $objWorksheet->setTitle(''. $tn);

    // then select all results for that TeamName
    $stmt2 = ... sql query select results where TeamName=$tn
    // then for each row returned write to file
    $i = 2; // set row iterator
    while($row2 = sqlsrv_fetch_array($stmt2, SQLSRV_FETCH_ASSOC))
    {
       $userid = $row2['UserID'];
       $dep = $row2['Departments'];
       $des = $row2['Description'];
       $objWorksheet->setCellValue('A' . $i , $tn);
       $objWorksheet->setCellValue('B' . $i ,  $userid);
       $objWorksheet->setCellValue('C' . $i, $dep);
       $objWorksheet->setCellValue('D' . $i , $des);
       $i++;
    }
}

Upvotes: 0

Sterling Archer
Sterling Archer

Reputation: 22395

Put the

$objWorksheet = new PHPExcel_Worksheet($objPHPExcel);
$objPHPExcel->addSheet($objWorksheet);
$objWorksheet->setTitle(''. $tn);

Outside of the while loop. Each row is getting a new sheet because of this

Upvotes: 2

Related Questions