PHPexcel to export SQL data to workbook and multiple sheets

Topics: Developer Forum, Project Management Forum, User Forum
Nov 19, 2013 at 12:05 PM
Edited Nov 20, 2013 at 12:04 PM
I could use some help. I have been working on a script for awhile now to export some data from SQL to Excel. I have a db that looks like below that has users and what departments they are in. Some user are in multiple departments. The script I have worked on so far will export the data fine from SQL and create the worksheets but only writes the data on the last sheet. I need it to create a sheet for every user and then put that users information on that sheet. So for an example it would create a sheet called Smith_Joe and then add the information that pertains to Smith_Joe. Here is what my db info looks like.
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
This is the code I have so far.
<?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']);
   
    $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);  ?>
Nov 21, 2013 at 1:08 PM
Anyone have an idea on how to get this to work or maybe another way to do it. Basically I have a SQL db with this kind of information in it.
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 it to create a workbook with sheet name named after the user which is the TeamName column. So I would have a workbook and it would look like.

Sheet1 (Named Smith_Joe)
Smith_Joe JOE4S 52200 Sales
Smith_Joe JOE4S 53002 Budget
Smith_Joe JOE4S 43302 Dev

Sheet2 (Named Black_BJ)
Black_BJ BJ3OO 43332 Mrkt

Sheet3 (Named Steh_Tray)
Steh_Tray ST44S 65573 Prod
Steh_Tray ST44S 52200 Sales

and so on to the next user and so on...

Any help would be great, thanks!!
Nov 21, 2013 at 1:21 PM
You must use fromArray when you shoot a user switching, before to create the new worksheet, empty the array before storing the first record of the new user.
Do not forget to include data for the last user in loop-out, of course.

Note: the header sent corresponds to an xls file, free_stmt, close are never run (this is after Exit)
Marked as answer by KMS77 on 11/27/2013 at 9:42 AM