phpcodeignitergraphchartspchart

How to include the graph inside the codeigniter export excel


I have the userlist with comment count.I have export excel using below libaraies and codes. How to include graph inside the excel sheet.

Here is my output: E.g.:

Sno   USER     CommentCount
1     User1    10
2     User2    12
3     User3    21

How to form graph or chart inside the excel using codeigniter.

Library function:

class Export{    
    function to_excel($array, $filename) {
        header('Content-type: application/vnd.ms-excel');
        header('Content-Disposition: attachment; filename='.$filename.'.xls');         
        $h = array();
        foreach($array as $row){
            foreach($row as $key=>$val){
                if(!in_array($key, $h)){
                    $h[] = $key;   
                }
            }
        }
        echo '<table><tr>';
        foreach($h as $key) {
            $key = ucwords($key);
            echo '<th>'.$key.'</th>';
        }
        echo '</tr>';
        foreach($array as $row){
            echo '<tr>';
            foreach($row as $val)
            $this->writeRow($val);   
        }
        echo '</tr>';
        echo '</table>';
    }
    function writeRow($val) {
        echo '<td>'.utf8_decode($val).'</td>';              
    }
}

Controller code:

public function userlist(){      
    $sql = $this->export_model->user_export();      
    $this->export->to_excel($sql, 'UserList'); 
}

Solution

  • finally I got it using PHP EXCEL . Code is below

    include "Classes/PHPExcel.php";
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="graphdemo.xlsx"');
        header('Cache-Control: max-age=0');
        $workbook = new PHPExcel();
    
        $objRichText = new PHPExcel_RichText();
        $objBold = $objRichText->createTextRun('Name');
        $objBold->getFont()->setBold(true);
        $workbook->getActiveSheet()->getCell('A1')->setValue($objRichText);
    
        $objRichText = new PHPExcel_RichText();
        $objBold = $objRichText->createTextRun('Sharing Count');
        $objBold->getFont()->setBold(true);
        $workbook->getActiveSheet()->getCell('B1')->setValue($objRichText);  
    
        $workbook->setActiveSheetIndex(0);
        $sheet = $workbook->getActiveSheet();
        $sheet->getPageMargins()->setTop(0.6);
        $sheet->getPageMargins()->setBottom(0.6);
        $sheet->getPageMargins()->setHeader(0.4);
        $sheet->getPageMargins()->setFooter(0.4);
        $sheet->getPageMargins()->setLeft(0.4);
        $sheet->getPageMargins()->setRight(0.4);
        $workbook->getProperties()->setTitle("Demo");
        $workbook->getProperties()->setCreator("Demo");
        $workbook->getProperties()->setLastModifiedBy("Demo");
        $workbook->getProperties()->setCompany("Demo");
    
    
        $Requete = "SELECT  COUNT(*) As cnt , name FROM `users` GROUP BY  user_id  ";
        $result  = mysql_query($Requete);  
        while ($row = mysql_fetch_array($result)){   
            $bname[] = $row["name"];
            $bk_co[] = $row["cnt"];         
        } 
        $data =$bname;  
        $row = 2;
        foreach($data as $point) {
            $sheet->setCellValueByColumnAndRow(0, $row++, $point);
        }    
        $data = $bk_co;
        $row = 2;
        foreach($data as $point) {
            $sheet->setCellValueByColumnAndRow(1, $row++, $point);
        }
    
        $values = new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$B$1:$B$10');
        $categories = new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$A$1:$A$10');
        $series = new PHPExcel_Chart_DataSeries(
        PHPExcel_Chart_DataSeries::TYPE_BARCHART,        
        PHPExcel_Chart_DataSeries::GROUPING_CLUSTERED,   
        array(0),                                        
        array(),                                         
        array($categories),                             
        array($values)                                   
        );
        $series->setPlotDirection(PHPExcel_Chart_DataSeries::DIRECTION_COL);
        $layout = new PHPExcel_Chart_Layout();
        $plotarea = new PHPExcel_Chart_PlotArea($layout, array($series));
        $chart = new PHPExcel_Chart('sample', null, null, $plotarea);
        $chart->setTopLeftPosition('F2');
        $chart->setBottomRightPosition('N25');
        $sheet->addChart($chart);
        $writer = PHPExcel_IOFactory::createWriter($workbook, 'Excel2007');
        $writer->setIncludeCharts(TRUE);
        $writer->save('php://output');