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