Jump to content

PHPexcel to export SQL data to workbook and multiple sheets


kms
Go to solution Solved by dalecosp,

Recommended Posts

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);  ?>
Link to comment
Share on other sites

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 by dalecosp
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 by dalecosp
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 by kms
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 by dalecosp
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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');  
}
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.