PHPFixing
  • Privacy Policy
  • TOS
  • Ask Question
  • Contact Us
  • Home
  • PHP
  • Programming
  • SQL Injection
  • Web3.0

Monday, July 4, 2022

[FIXED] How to include the graph inside the codeigniter export excel

 July 04, 2022     charts, codeigniter, graph, pchart, php     No comments   

Issue

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');


Answered By - Kennedy
Answer Checked By - David Goodson (PHPFixing Volunteer)
  • Share This:  
  •  Facebook
  •  Twitter
  •  Stumble
  •  Digg
Newer Post Older Post Home

0 Comments:

Post a Comment

Note: Only a member of this blog may post a comment.

Total Pageviews

Featured Post

Why Learn PHP Programming

Why Learn PHP Programming A widely-used open source scripting language PHP is one of the most popular programming languages in the world. It...

Subscribe To

Posts
Atom
Posts
Comments
Atom
Comments

Copyright © PHPFixing