Export data with PHPexcel in an organized table.

Topics: Developer Forum, Project Management Forum
Oct 1, 2014 at 4:05 AM
Hi everyone,

I am quite new to PHPExcel and learning PHP as I go. I managed to get PHPexcel to export my data from SQL via the code below. Right now its just outputting in basic style, meaning its just serialized the data from SQL and next record would show up in the next row. I added an image of what I would like to accomplish, is this something possible with PHPexcel or am I dreaming.

If anyone could send me a quick example of how to accomplish this it would be greatly appreciated.

Image
http://postimg.org/image/m3n60hn25/

Below is a working sample of a simple serialized export.
<?php

// connection with the database 
$dbhost = "localhost"; 
$dbuser = "IMC_COE2"; 
$dbpass = "XXX"; 
$dbname = "IMC_COE2"; 

mysql_connect($dbhost,$dbuser,$dbpass); 
mysql_select_db($dbname); 

// require the PHPExcel file 
require '/Excelphp/Classes/PHPExcel.php'; 

// simple query 

$query = 'SELECT client, team_name,support_team_prime,prime_comments,support_team_backup,backup_comments,escalation1,escalation1_comments,escalation2,escalation2_comments,escalation3,escalation3_comments,escalation4,escalation4_comments,note FROM tbl_address ORDER by team_name DESC'; 
$headings = array('Client Name','Team Name','Prime Contact','Comments','Backup Contacts','Comments','Escalation 1','Comments','Escalation 2','Comments','Escalation 3','Comments','Escalation 4','Comments','Additional notes'); 

if ($result = mysql_query($query) or die(mysql_error())) { 
    // Create a new PHPExcel object 
    $objPHPExcel = new PHPExcel(); 
    $objPHPExcel->getActiveSheet()->setTitle('List of Users'); 

    $rowNumber = 1; 
    $col = 'A'; 
    foreach($headings as $heading) { 
       $objPHPExcel->getActiveSheet()->setCellValue($col.$rowNumber,$heading); 
       $col++; 
    } 

    // Loop through the result set 
    $rowNumber = 2; 
    while ($row = mysql_fetch_row($result)) { 
       $col = 'A'; 
       foreach($row as $cell) { 
          $objPHPExcel->getActiveSheet()->setCellValue($col.$rowNumber,$cell); 
          $col++; 
       } 
       $rowNumber++; 
    } 

    // Freeze pane so that the heading line won't scroll 
    $objPHPExcel->getActiveSheet()->freezePane('A2'); 

    // Save as an Excel BIFF (xls) file 
    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); 

   header('Content-Type: application/vnd.ms-excel'); 
   header('Content-Disposition: attachment;filename="userList.xls"'); 
   header('Cache-Control: max-age=0'); 

   $objWriter->save('php://output'); 
   exit(); 
} 
echo 'a problem has occurred... no data retrieved from the database'; 
Oct 1, 2014 at 4:01 PM
Alright so far Ive got answers form other forums that I should be using setCellValue() and formarray(). having a real hard time finding examples online with what I am trying to accomplish. Would anyone be kind to provide me with an example of what the structure of the code would looks like pulling data from SQL and import specific value to certians cells to my choosing. Once I get that I think I may be able to work from there. At the moment im at a lost on where to start.
Oct 2, 2014 at 10:37 PM
Edited Oct 2, 2014 at 10:42 PM
Alright i'm getting closer to getting to the actual end result however its only showing 1 record at a time. Im sure im missing a code that I am not understanding, would anyone know what I am missing? I need it dump all the records from the database and keept

This is what im trying to get to:
http://postimg.org/image/m3n60hn25/

What I am getting now:
http://s28.postimg.org/a8bbb206l/Result_now.png

Here is my current code:
<?php  
/** PHPExcel */  
require_once '/Excelphp/Classes/PHPExcel.php';  

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

// Set properties  
$objPHPExcel->getProperties()->setCreator("Maarten Balliauw")  
->setLastModifiedBy("Maarten Balliauw")  
->setTitle("Office 2007 XLSX Test Document")  
->setSubject("Office 2007 XLSX Test Document")  
->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")  
->setKeywords("office 2007 openxml php")  
->setCategory("Test result file");  

    $objPHPExcel->getActiveSheet()->mergeCells('B2:D2');  
    $objPHPExcel->getActiveSheet()->mergeCells('B4:D4');  
    $objPHPExcel->getActiveSheet()->mergeCells('B7:D7');  
    $objPHPExcel->getActiveSheet()->mergeCells('B12:D12');  
    $objPHPExcel->getActiveSheet()->mergeCells('C3:D3');  
    $objPHPExcel->getActiveSheet()->mergeCells('B13:D13');  
    $objPHPExcel->getActiveSheet()->getStyle('C3:D3')->getAlignment()->setWrapText(true);
    $objPHPExcel->getActiveSheet()->getStyle('C5:D5')->getAlignment()->setWrapText(true);
    $objPHPExcel->getActiveSheet()->getStyle('C6:D6')->getAlignment()->setWrapText(true);
    $objPHPExcel->getActiveSheet()->getStyle('C8:D8')->getAlignment()->setWrapText(true);
    $objPHPExcel->getActiveSheet()->getStyle('C9:D9')->getAlignment()->setWrapText(true);
    $objPHPExcel->getActiveSheet()->getStyle('C10:D10')->getAlignment()->setWrapText(true);
    $objPHPExcel->getActiveSheet()->getStyle('C11:D11')->getAlignment()->setWrapText(true);
    $objPHPExcel->getActiveSheet()->getStyle('B13:D13')->getAlignment()->setWrapText(true);
    $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);  
    $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setAutoSize(false);  
    $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(12);  
    $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(50);  
    $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(50);  

//Setting for borders   
$styleArray = array('borders' => array('outline' => array('style' => PHPExcel_Style_Border::BORDER_THIN,'color' => array('argb' => 'FFA0A0A0'),),),);

$objPHPExcel->getActiveSheet()->getStyle('B2:B13')->applyFromArray($styleArray);
$objPHPExcel->getActiveSheet()->getStyle('C2:C13')->applyFromArray($styleArray);
$objPHPExcel->getActiveSheet()->getStyle('D2:D13')->applyFromArray($styleArray);
$objPHPExcel->getActiveSheet()->getStyle('B2:D2')->applyFromArray($styleArray);
$objPHPExcel->getActiveSheet()->getStyle('B3:D3')->applyFromArray($styleArray);
$objPHPExcel->getActiveSheet()->getStyle('B4:D4')->applyFromArray($styleArray);
$objPHPExcel->getActiveSheet()->getStyle('B5:D5')->applyFromArray($styleArray);
$objPHPExcel->getActiveSheet()->getStyle('B6:D6')->applyFromArray($styleArray);
$objPHPExcel->getActiveSheet()->getStyle('B7:D7')->applyFromArray($styleArray);
$objPHPExcel->getActiveSheet()->getStyle('B8:D8')->applyFromArray($styleArray);
$objPHPExcel->getActiveSheet()->getStyle('B9:D9')->applyFromArray($styleArray);
$objPHPExcel->getActiveSheet()->getStyle('B10:D10')->applyFromArray($styleArray);
$objPHPExcel->getActiveSheet()->getStyle('B11:D11')->applyFromArray($styleArray);
$objPHPExcel->getActiveSheet()->getStyle('B12:D12')->applyFromArray($styleArray);
$objPHPExcel->getActiveSheet()->getStyle('B13:D13')->applyFromArray($styleArray);

//Background color on cells 
$objPHPExcel->getActiveSheet()->getStyle('B2:D2')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FFCCE5FF');
$objPHPExcel->getActiveSheet()->getStyle('B4:D4')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FFCCE5FF');
$objPHPExcel->getActiveSheet()->getStyle('B7:D7')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FFCCE5FF');
$objPHPExcel->getActiveSheet()->getStyle('B12:D12')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FFCCE5FF');
$objPHPExcel->getActiveSheet()->getStyle('B7:D7')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FFCCE5FF');
$objPHPExcel->getActiveSheet()->getStyle('B3')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FFE0E0E0');
$objPHPExcel->getActiveSheet()->getStyle('B5')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FFE0E0E0');
$objPHPExcel->getActiveSheet()->getStyle('B6')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FFE0E0E0');
$objPHPExcel->getActiveSheet()->getStyle('B8')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FFE0E0E0');
$objPHPExcel->getActiveSheet()->getStyle('B9')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FFE0E0E0');
$objPHPExcel->getActiveSheet()->getStyle('B10')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FFE0E0E0');
$objPHPExcel->getActiveSheet()->getStyle('B11')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FFE0E0E0');

    // Add some data  
    $objPHPExcel->setActiveSheetIndex(0)  
                ->setCellValue('A1', '')   
                ->setCellValue('B1', '')  
                ->setCellValue('C1', '')  
                ->setCellValue('D1', '')  
                ->setCellValue('E1', '')
                ->setCellValue('A14', '')   
                ->setCellValue('B14', '')  
                ->setCellValue('C14', '')  
                ->setCellValue('D14', '')  
                ->setCellValue('E14', '')
                ->setCellValue('B3', 'Client:')
                ->setCellValue('B5', 'Prime:')
                ->setCellValue('B4', 'Support group contacts')              
                ->setCellValue('B6', 'Backup:')
                ->setCellValue('B7', 'Escalations') 
                ->setCellValue('B8', 'Escalation 1:') 
                ->setCellValue('B9', 'Escalation 2:') 
                ->setCellValue('B10', 'Escalation 3:') 
                ->setCellValue('B11', 'Escalation 4:') 
                ->setCellValue('B12', 'Notes');      

    //  SQl database connections
    $db = mysql_connect("localhost", "IMC_COE2", "IMC123");  
    mysql_select_db("IMC_COE2",$db);  

    $sqlgroups="select client, team_name,support_team_prime,prime_comments,support_team_backup,backup_comments,escalation1,escalation1_comments,escalation2,escalation2_comments,escalation3,escalation3_comments,escalation4,escalation4_comments,note from tbl_address ORDER by team_name";  
    $resultgroups=mysql_query($sqlgroups);  
        $numrows=mysql_num_rows($resultgroups);  
        if ($numrows>0)  
        {  
            $count=2;  
            while($data=mysql_fetch_array($resultgroups))  
            {  
                $count+=1;  
 
                $objPHPExcel->setActiveSheetIndex(0)              
                            ->setCellValue('C3', $data['client'])  
                            ->setCellValue('B2', $data['team_name'])  
                            ->setCellValue('C5', $data['support_team_prime'])  
                            ->setCellValue('D5', $data['prime_comments'])  
                            ->setCellValue('C6', $data['support_team_backup'])  
                            ->setCellValue('D6', $data['backup_comments'])  
                            ->setCellValue('C8', $data['escalation1'])
                            ->setCellValue('D8', $data['escalation1_comments'])
                            ->setCellValue('C9', $data['escalation2'])
                            ->setCellValue('D9', $data['escalation2_comments'])
                            ->setCellValue('C10', $data['escalation3'])
                            ->setCellValue('D10', $data['escalation3_comments'])
                            ->setCellValue('C11', $data['escalation4'])
                            ->setCellValue('D11', $data['escalation4_comments'])
                            ->setCellValue('B13', $data['note']); 
 
            }  
        }          

    // Rename sheet  
    $objPHPExcel->getActiveSheet()->setTitle('Directory Tool Full dump');
    
    // Set active sheet index to the first sheet, so Excel opens this as the first sheet
    $objPHPExcel->setActiveSheetIndex(0);
    
    // Redirect output to a client’s web browser (Excel5) 
    ob_end_clean(); 
    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');  
    header('Content-Type: application/vnd.ms-excel'); 
    header('Content-Disposition: attachment;filename="Export-Directory Tool.xls"'); 
    header('Cache-Control: max-age=0'); 
    $objWriter->save('php://output');  
    exit;  
    ?> 
Oct 3, 2014 at 11:15 AM
Edited Oct 3, 2014 at 11:16 AM
Alright well im getting help at http://stackoverflow.com/questions/26133953/phpexcel-help-how-to-export-data-with-phpexcel-in-an-custom-table-layout?noredirect=1#comment41037845_26133953 so for those struggling with similar issues you might gets answers over there instead.

If anyone has any better feedback please dont be shy.