PHPexcel to export SQL data to workbook and multiple sheets

[kms]

I have been working on some PHP code to take some SQL data and put it into a Excel workbook with multiple sheets. The data example is below. I want each user and there access to be put on a separate sheet with the sheet name or the user.  I am able to get the data from SQL and then create the sheets with the users name but the data is only being copied on the last sheet with all other sheets blank.  Can some lend a hand and let me know where I am going wrong with the code below?

 

Example Data:

TeamName   UserID   Deparment   Description
---------------------------------------------------
Smith_Joe   JOE4S    52200      Sales
Smith_Joe   JOE4S    52201      Sales\Dep
Black_BJ    BJ3OO    43332      Mrkt

My Code:

<?php 
    session_start();
    $userid = $_SESSION['userid'];


            $serverName = "sqlserver, 1433";
    $connectionInfo = array("UID"=>"userid", "PWD"=>"password", "Database"=>"database");
    $conn = sqlsrv_connect( $serverName, $connectionInfo);
    if( $conn === false )
    {
         echo "Could not connect.\n";
         die( print_r( sqlsrv_errors(), true));
    }


        require_once 'Classes/PHPExcel.php';
    include 'Classes/PHPExcel/Writer/Excel2007.php';




    // Create new PHPExcel object
    $objPHPExcel = new PHPExcel();

    // Set document properties
    $objPHPExcel->getProperties()->setCreator("Budget SYstem")
     ->setLastModifiedBy("System")
     ->setTitle("System")
     ->setSubject("System Security")
     ->setDescription("System Security Report")
     ->setKeywords("office 2007")
     ->setCategory("Security");

    // Create the worksheet
    $objPHPExcel->setActiveSheetIndex(0);

    $objPHPExcel->getActiveSheet()->setCellValue('A1', "TeamName")
     ->setCellValue('B1', "UserID")
     ->setCellValue('C1', "Departments")
     ->setCellValue('D1', "Description");



    /* Set up and execute the query. */
    $tsql = "SELECT MemberOfTeamID AS 'TeamName', SUBSTRING(UserID, CHARINDEX('\', UserID)+1, 250) AS 'UserID', Left(ProfileID, PatIndex('%[^0-9,.,-+-,^a-z]%', ProfileID)) AS 'Departments',SUBSTRING(ProfileID, PatIndex('%[^0-9,.,-+-]%', ProfileID), 8000) AS Description FROM UserProfile u INNER JOIN UserTeamAssign ut ON u.UserID = ut.UserorTeamID WHERE UserID LIKE '%$UsedrID%' AND u.ProfileID != 'Planners' AND u.ProfileID <> '00 Access_All_Models' AND u.ProfileID <> '00 Required for Global Rates' AND MemberOfTeamID <> 'AdminTeam'";


    $stmt = sqlsrv_query( $conn, $tsql);

    $dataArray= array();

    $uid = $row['UserID'];

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

     if ($row['UserID'] != $uid){

     $objWorksheet = new PHPExcel_Worksheet($objPHPExcel);
    $objPHPExcel->addSheet($objWorksheet);
    $objWorksheet->setTitle(''. $row['TeamName']);
    $objPHPExcel->setActiveSheetIndexByName($row['TeamName']);
    $row_array['TeamName'] = $row['TeamName'];
    $row_array['UserID'] = $row['UserID'];
    $row_array['Departments'] = $row['Departments'];
    $row_array['Description'] = $row['Description'];
   array_push($dataArray,$row_array);

   } 


   $row_array['TeamName'] = $row['TeamName'];
   $row_array['UserID'] = $row['UserID'];
   $row_array['Departments'] = $row['Departments'];
   $row_array['Description'] = $row['Description'];

   array_push($dataArray,$row_array);
   $uid = $row['UserID'];

   }

    $objPHPExcel->getActiveSheet()->fromArray($dataArray, NULL, 'A2');

    // Save Excel 2007 file
    #echo date('H:i:s') . " Write to Excel2007 format\n";
    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
    ob_end_clean();
    // We'll be outputting an excel file
    header('Content-type: application/vnd.ms-excel');
    // It will be called file.xls
    header('Content-Disposition: attachment; filename="security.xlsx"');
    $objWriter->save('php://output');
    Exit;


        /* Free statement and connection resources. */
    sqlsrv_free_stmt( $stmt);
    sqlsrv_close( $conn);  ?>

[dalecosp]

You only do this:

$objPHPExcel->setActiveSheetIndex(0);

 

the one time --- is this:

 $objWorksheet = new PHPExcel_Worksheet($objPHPExcel);
$objPHPExcel->addSheet($objWorksheet);
$objWorksheet->setTitle(''. $row['TeamName']);
$objPHPExcel->setActiveSheetIndexByName($row['TeamName']);

 

Supposed to be adding the sheets?

 

I don't see that you done any checking to see if these calls are failing ... might be a place to start...

Edited November 15, 2013 by dalecosp

[kms]

Thanks for the reply.

 

Yes that code creates the sheets and names them after the user name. But no data is ever written to any of the sheets but the last one.  Are you saying I should not be using,

$objPHPExcel->setActiveSheetIndexByName($row['TeamName']);

but should be using,

$objPHPExcel->setActiveSheetIndex(0);

I thought the code I was using would set the active sheet to be written to. I think I am having more than just one problem though sense I am not getting any data on any sheet except the last one.

[dalecosp]

I'm not sure; in my interaction with PHPExcel I keep my nose buried in the docs most of the time. I did check on one script that *reads* from multiple sheets, and it works using a numeric array and

setActiveSheetIndex($sheetnum);

... so I can't say for sure whether setActiveSheetIndexByName() works for me or not.

 

You're sure that $row['TeamName'] is a valid sheet name at this point? It seems like I used the numeric approach because I couldn't figure out the sheet name translation ...

Edited November 15, 2013 by dalecosp

[kms]

Yes I can confirm that the workbook gets created and that it does create all the sheets with the name from $row['TeamName'] which ends up to be smith_joe and so on.  I did not use the numeric approach because the sheets are being created as the script goes and was not sure how I would correlate the numeric number with the user name of the sheet.

[dalecosp]

As I tried to imply, I wondered at the time if it was a translation problem between PHP and the MS-interface; I might try playing with trim() or strtoupper(), etc. and see if that helps.

 

Sorry that I'm basically clueless here.

[kms]

I just can't seem to get what I need.  Basically I pull data from sql which is user and what department they belong to. Some user are in multiple departments.  The sql data looks like this:

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

I need to have one workbook with each user with their own sheet. So Smith_Joe would have his own sheet with the department and other data listed on that sheet and Black_BJ would have his own sheet with he information on it.

 

I changed my while loop but now I get all the sheets with names but each row is listed of separate sheets. So for an example with the code below. 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.

 

Here is the change I made.

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

	$res		=	$row;
	$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);

Edited November 19, 2013 by kms

[dalecosp]

Don't call addSheet() unless you're on a new person.  

You could set a "last known person" type variable and only do addSheet() if it's changed?

[kms]

Okay this is what I got so far but it only writes the first record for each user.  What I am not sure of is how to set the $uid variable so the if statement work correctly.  Here is the code so far.

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

{

 if ($row['UserID'] != $uid)
  {
	
	$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' , $uid);
	$uid = $row['UserID'];
}
	else
{
	$objWorksheet->setCellValue('A2' , $tn);
	$objWorksheet->setCellValue('B2' ,  $userid);
	$objWorksheet->setCellValue('C2', $dep);
	$objWorksheet->setCellValue('D2' , $des);
	$uid = $row['UserID'];

}

}

So basically it is doing a if statement on the UserID. If the userid's do not match do the addsheet() part of the code and if they do match skip to the else statement.  Just do not think I have the right syntax in place.

[dalecosp]

You're overwriting the cells (A2, B2, etc.) They need to change for rows to be added

[kms]

Thanks so much for helping I no it is not easy.  

 

You are right about that boy, oh boy.

 

I changed the code to:

	$objWorksheet->setCellValue('A2' , $tn);
	$objWorksheet->setCellValue('B2' ,  $userid);
	$objWorksheet->setCellValue('C2', $dep);
	$objWorksheet->setCellValue('D2' , $des);
	$uid = $row['UserID'];
}
	else
{
	$objWorksheet->setCellValue('A3' , $tn);
	$objWorksheet->setCellValue('B3' ,  $userid);
	$objWorksheet->setCellValue('C3', $dep);
	$objWorksheet->setCellValue('D3' , $des);
	

}

}

Now I get the fist row only written twice on each sheet. So on the sheet for Smith_Joe it shows:

 

Smith_Joe JOE4S 52200 Sales

Smith_Joe JOE4S 52200 Sales

 

and that is all.  It is getting the right person on the right sheet though.

[dalecosp]

Now I get the fist row only written twice on each sheet.

Your lines for $tn, $dep, and $des need to be after the while (above the conditional), because they're only being set once (they need to be set each $row, regardless whether the user is new or not...).

 

You may also want to keep a counter, so that you don't have to hardcode the cell co-ordinates

Edited November 19, 2013 by dalecosp

[kms]

Okay I made a change to the code the change is below.  Now when I run the script I get the sheets with the names and the first on is correct right now and right information but the next sheet no only has the first sheet info on it it has the right information on it as well and it keeps building like that. So for an example it looks like this;

 

Sheet1 (Correct)

Smith_Joe JOE4S 52200 Sales
Smith_Joe JOE4S 53002 Budget
Smith_Joe JOE4S 43302 Dev

 

Sheet2  (Notice Smith_Joe is on this sheet and should not be)

Smith_Joe JOE4S 52200 Sales

Smith_Joe JOE4S 53002 Budget
Smith_Joe JOE4S 43302 Dev
Black_BJ BJ3OO 43332 Mrkt

 

Sheet3  (Notice Smith_Joe and Black_BJ is on this should and should not be)

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

 

and so on until the last page has all the details from everyone on it.  I am just not getting why it is not going through the if statement one row at a time.

[dalecosp]

Well, I think you're making progress

 

Okay I made a change to the code the change is below.

I don't see it ... the changed code, that is ...

 

It sounds like more issues with the loop. You might try commenting your code, loops, etc. and see if you can find any glaring logic errors.

[kms]

Sorry here it is:

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


{


 if ($row['UserID'] != $uid)
  {
	
	$tn 		= 	$row['TeamName'];
	
	

	$objWorksheet = new PHPExcel_Worksheet($objPHPExcel);
	$objPHPExcel->addSheet($objWorksheet);
	$objWorksheet->setTitle(''. $tn);
	$uid = $row['UserID'];
	
	
   
	
}
	
   $row_array['TeamName'] = $row['TeamName'];
   $row_array['UserID'] = $row['UserID'];
   $row_array['Departments'] = $row['Departments'];
   $row_array['Description'] = $row['Description'];

   array_push($dataArray,$row_array);
   
$objWorksheet->fromArray($dataArray, NULL, 'A2');  
}

[dalecosp]

I *think* you need to reset $dataArray for each new user; by not doing so you keep appending the new user data to the $dataArray and then you write it to the worksheet.

 

if ($row['UserID'] != $uid) {
$tn = $row['TeamName'];
$dataArray = array();

 

 

HTH,

[kms]

Thank you! I believe that was it, solved.

 

Thanks for helping.

[dalecosp]

You're welcome!