How to Create Bar Chart from MySQL Data Using PhpExcel

Question

After searching how to create report and chart in excel using php, i find PHPExcel class. I try using it, to create my report.

Currently i have success implement it, but i have a problem to insert chat (bar). This is what i've done so far.

<?php
// Error reporting
error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);
date_default_timezone_set("Asia/Jakarta");

if (PHP_SAPI == 'cli')
    die('This excel should only be run from a Web Browser');

/** Include PHPExcel */
require_once '../Classes/PHPExcel.php';

/** Connection Database */
$myServer ="192.168.X.X";
$conn_inv = mysql_connect($myServer, 'root', 'password');
mysql_select_db('tiket');

/** Query Database */
$query = "SELECT     d.AIRL, COUNT(d.TICK) AS TICK, SUM(d.VALUE + d.TAX) AS JUM
FROM         ticket d INNER JOIN
                      invoice h ON h.INVNO = d.INVNO AND h.TYPE = d.TYPE
WHERE     (h.INVDATE >= '05/01/2015') AND (h.INVDATE <= '05/15/2015') AND (h.TYPE = 'TIX')
GROUP BY d.AIRL
ORDER BY JUM DESC";    

// Execute the database query
$result = mysql_query($query);

// Instantiate a new PHPExcel object
$objPHPExcel = new PHPExcel(); 

// memory Cache Handling
$cacheMethod = PHPExcel_CachedObjectStorageFactory:: cache_to_phpTemp;
$cacheSettings = array( 
    'memoryCacheSize' => '8MB'
);
PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);

// Set metadata
$objPHPExcel->getProperties()
    ->setCreator("REPORTIS")
    ->setLastModifiedBy("REPORTIS")
    ->setTitle("Summary Airline")
    ->setSubject("Summary Airline")
    ->setDescription(
        "Summary Airline XLSX, generated using PHP classes."
    )
    ->setKeywords("summary airline bayu buana")
    ->setCategory("Report");

// Set the active Excel worksheet to sheet 0
$objPHPExcel->setActiveSheetIndex(0); 

// Set sheet title
$objPHPExcel->getActiveSheet()->setTitle('Summary Airline');

// Set header
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(40);
$objPHPExcel->getActiveSheet()->getStyle('A1')
    ->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('A1')
    ->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);
$objPHPExcel->getActiveSheet()->getStyle('A1')
    ->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('A1')
    ->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('A1')
    ->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('A1')
    ->getBorders()->getLEFT()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('A1')
    ->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('A1')
    ->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet()->getStyle('A1')
    ->getFill()->getStartColor()->setARGB('FF008000');

$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(30);
$objPHPExcel->getActiveSheet()->getStyle('B1')
    ->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);
$objPHPExcel->getActiveSheet()->getStyle('B1')
    ->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('B1')
    ->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('B1')
    ->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('B1')
    ->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('B1')
    ->getBorders()->getLEFT()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('B1')
    ->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('B1')
    ->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet()->getStyle('B1')
    ->getFill()->getStartColor()->setARGB('FF008000');

$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(30);
$objPHPExcel->getActiveSheet()->getStyle('C1')
    ->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);
$objPHPExcel->getActiveSheet()->getStyle('C1')
    ->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('C1')
    ->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('C1')
    ->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('C1')
    ->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('C1')
    ->getBorders()->getLEFT()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('C1')
    ->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('C1')
    ->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet()->getStyle('C1')
    ->getFill()->getStartColor()->setARGB('FF008000');

$objPHPExcel->getActiveSheet()->SetCellValue('A1', 'AIRLINE'); 
$objPHPExcel->getActiveSheet()->SetCellValue('B1', 'JUMLAH TIKET');
$objPHPExcel->getActiveSheet()->SetCellValue('C1', 'COST');

// Initialise the Excel row number
$rowCount = 2; 
// Iterate through each result from the SQL query in turn
// We fetch each database result row into $row in turn
while($row = mysql_fetch_array($result)){ 
    $airline = $row['AIRL'];
    $ticket = $row['TICK'];
    $jum = $row['JUM'];
    // Set cell An to the "name" column from the database (assuming you have a column called name)
    //    where n is the Excel row number (ie cell A1 in the first row)
    $objPHPExcel->getActiveSheet()->SetCellValue('A'.$rowCount, $airline);
    $objPHPExcel->getActiveSheet()->getStyle('A'.$rowCount)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1); 
    $objPHPExcel->getActiveSheet()->getStyle('A'.$rowCount)->getBorders()->getTOP()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
    $objPHPExcel->getActiveSheet()->getStyle('A'.$rowCount)->getBorders()->getBOTTOM()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
    $objPHPExcel->getActiveSheet()->getStyle('A'.$rowCount)->getBorders()->getRIGHT()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
    $objPHPExcel->getActiveSheet()->getStyle('A'.$rowCount)->getBorders()->getLEFT()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
    $objPHPExcel->getActiveSheet()->getStyle('A'.$rowCount)->getAlignment()->setWrapText(true);
    // Set cell Bn to the "age" column from the database (assuming you have a column called age)
    //    where n is the Excel row number (ie cell A1 in the first row)
    $objPHPExcel->getActiveSheet()->SetCellValue('B'.$rowCount, $ticket);
    $objPHPExcel->getActiveSheet()->getStyle('B'.$rowCount)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
    $objPHPExcel->getActiveSheet()->getStyle('B'.$rowCount)->getBorders()->getTOP()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
    $objPHPExcel->getActiveSheet()->getStyle('B'.$rowCount)->getBorders()->getBOTTOM()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
    $objPHPExcel->getActiveSheet()->getStyle('B'.$rowCount)->getBorders()->getRIGHT()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
    $objPHPExcel->getActiveSheet()->getStyle('B'.$rowCount)->getBorders()->getLEFT()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
    $objPHPExcel->getActiveSheet()->getStyle('B'.$rowCount)->getAlignment()->setWrapText(true);
    $objPHPExcel->getActiveSheet()->SetCellValue('C'.$rowCount, $jum); 
    $objPHPExcel->getActiveSheet()->getStyle('C'.$rowCount)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
    $objPHPExcel->getActiveSheet()->getStyle('C'.$rowCount)->getBorders()->getTOP()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
    $objPHPExcel->getActiveSheet()->getStyle('C'.$rowCount)->getBorders()->getBOTTOM()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
    $objPHPExcel->getActiveSheet()->getStyle('C'.$rowCount)->getBorders()->getRIGHT()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
    $objPHPExcel->getActiveSheet()->getStyle('C'.$rowCount)->getBorders()->getLEFT()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
    $objPHPExcel->getActiveSheet()->getStyle('C'.$rowCount)->getAlignment()->setWrapText(true);
    // Increment the Excel row counter
    $rowCount++; 
    $juml = $rowCount;
} 

//  Set the Labels for each data series we want to plot
//      Datatype
//      Cell reference for data
//      Format Code
//      Number of datapoints in series
//      Data values
//      Data Marker
$dataseriesLabels = array(
    new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$B$1', NULL, 1),   //  TIKET
    new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$C$1', NULL, 1),   //  JUM
);
//  Set the X-Axis Labels
//      Datatype
//      Cell reference for data
//      Format Code
//      Number of datapoints in series
//      Data values
//      Data Marker
$xAxisTickValues = array(
    new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$A$2:$A$'.$juml, NULL, 4), //  AIRLINE
);
//  Set the Data values for each data series we want to plot
//      Datatype
//      Cell reference for data
//      Format Code
//      Number of datapoints in series
//      Data values
//      Data Marker
$dataSeriesValues = array(
    new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$B$2:$B$'.$juml, NULL, 4),
    new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$C$2:$C$'.$juml, NULL, 4),
);

//  Build the dataseries
$series = new PHPExcel_Chart_DataSeries(
    PHPExcel_Chart_DataSeries::TYPE_LINECHART,      // plotType
    PHPExcel_Chart_DataSeries::GROUPING_STACKED,    // plotGrouping
    range(0, count($dataSeriesValues)-1),           // plotOrder
    $dataseriesLabels,                              // plotLabel
    $xAxisTickValues,                               // plotCategory
    $dataSeriesValues                               // plotValues
);

//  Set the series in the plot area
$plotarea = new PHPExcel_Chart_PlotArea(NULL, array($series));
//  Set the chart legend
$legend = new PHPExcel_Chart_Legend(PHPExcel_Chart_Legend::POSITION_TOPRIGHT, NULL, false);

$title = new PHPExcel_Chart_Title('Test Stacked Line Chart');
$yAxisLabel = new PHPExcel_Chart_Title('Value ($k)');


//  Create the chart
$chart = new PHPExcel_Chart(
    'chart1',       // name
    $title,         // title
    $legend,        // legend
    $plotarea,      // plotArea
    true,           // plotVisibleOnly
    0,              // displayBlanksAs
    NULL,           // xAxisLabel
    $yAxisLabel     // yAxisLabel
);

//  Set the position where the chart should appear in the worksheet
$chart->setTopLeftPosition('E1');
$chart->setBottomRightPosition('J10');

//  Add the chart to the worksheet
$objPHPExcel->getActiveSheet()->addChart($chart);

header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="Summary Airline.xlsx"');
header('Cache-Control: max-age=0');

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->setIncludeCharts(TRUE);
$objWriter->save('php://output');

?>

if change this code $objWriter->setIncludeCharts(TRUE) into comment like this //$objWriter->setIncludeCharts(TRUE); then the script running well and table in excel created except for bar chart. What i need is bar chart created as well. Please help me to solve this problem.

Many thanks,

regards

Answer 1

I asked this question 15 days ago, and seems nobody post an answer yet. So here i post the answer after i solve my problem. I hope this can be usefull for other who want create report in excel with bar chart using PHPExcel.

How to create chart in excel using PHPExcel: 1. First you need to do query for fetching result from your database 2. After that, create an array which contain query result. You can do this by using array_push function. 3. And create two array for create new array from the original based on your requirement to fit the form array like PHPExcel example chart.

That's it. Here is the code for solving my problem (it may be different for different case. but basically, you must create an array from query result to create chart in excel using PHPExcel).

<?php
$conn_ins = mssql_connect($SERVER, $USER, $PASS);
mssql_select_db($DB);
$dis = mssql_query("SELECT * FROM District1 WHERE active = 'Y'");
$tes = array();
$dit = mssql_num_rows($dis);
while($rd = mssql_fetch_array($dis)){

    /** Connection Database */
    $myServer ="server";
    $conn_inv = mssql_connect($myServer, $user, $password);
    mssql_select_db($db);

    /** Query Database */
    for($bln=1;$bln<=12;$bln++){
        $query = "SELECT     STICKER, MONTH(INVDATE) AS BULAN, SUM(INVVAL) AS JUM
        FROM         INVOICE
        WHERE     (YEAR(INVDATE) = '2015') AND (SRK = 'X') AND (STICKER = '$rd[nm_district]') 
        AND (MONTH(INVDATE) = '$bln') GROUP BY MONTH(INVDATE), STICKER";    
        // Execute the database query
        $result = mssql_query($query);
        $row=mssql_fetch_assoc($result);

        if($row['STICKER'] <> ""){
            array_push($tes, array('STICKER'=>$rd['nm_district'],'BULAN'=>$bln,'JUM'=>$row['JUM']));
        }elseif($row['STICKER'] == ""){
            array_push($tes, array('STICKER'=>$rd['nm_district'],'BULAN'=>$bln,'JUM'=>0.00));
        }
    }

}

//CREATE NEW ARRAY

$arr2=array();
$arr3=array();

$arr2[0][0]="";
foreach($tes as $key => $val){
 if(!empty($val)){
    $arr2[0][$val['BULAN']]=$val['BULAN'];
    //group each STICKER
    $arr3[$val['STICKER']][]=$val['JUM'];
 }
}

//transfer the grouped data to arr2
foreach($arr3 as $key => $val){
  $tmp_arr=array($key);
  $arr2[]=array_merge($tmp_arr,$val);
}
?>

<?php
// Error reporting
error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);
date_default_timezone_set("Asia/Jakarta");

if (PHP_SAPI == 'cli')
    die('This excel should only be run from a Web Browser');

/** Include PHPExcel */
require_once '../Classes/PHPExcel.php';
$objPHPExcel = new PHPExcel();
$objWorksheet = $objPHPExcel->getActiveSheet();
$objWorksheet->fromArray($arr2);

$dataseriesLabels = array(
    new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$B$1', NULL, 1),   //  JAN
    new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$C$1', NULL, 1),   //  FEB
    new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$D$1', NULL, 1),   //  MAR
    new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$E$1', NULL, 1),   //  APR
    new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$F$1', NULL, 1),   //  MEI
    new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$G$1', NULL, 1),   //  JUN
    new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$H$1', NULL, 1),   //  JUL
    new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$I$1', NULL, 1),   //  AGT
    new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$J$1', NULL, 1),   //  SEP
    new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$K$1', NULL, 1),   //  OKT
    new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$L$1', NULL, 1),   //  NOV
    new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$M$1', NULL, 1),   //  DES
);

$xAxisTickValues = array(
    new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$A$2:$A$4', NULL, 4),  //  Q1 to Q4
);

$dataSeriesValues = array(
    new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$B$2:$B$4', NULL, 4),
    new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$C$2:$C$4', NULL, 4),
    new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$D$2:$D$4', NULL, 4),
    new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$E$2:$E$4', NULL, 4),
    new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$F$2:$F$4', NULL, 4),
    new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$G$2:$G$4', NULL, 4),
    new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$H$2:$H$4', NULL, 4),
    new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$I$2:$I$4', NULL, 4),
    new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$J$2:$J$4', NULL, 4),
    new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$K$2:$K$4', NULL, 4),
    new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$L$2:$L$4', NULL, 4),
    new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$M$2:$M$4', NULL, 4),
);

//  Build the dataseries
$series = new PHPExcel_Chart_DataSeries(
    PHPExcel_Chart_DataSeries::TYPE_BARCHART,       // plotType
    PHPExcel_Chart_DataSeries::GROUPING_CLUSTERED,  // plotGrouping
    range(0, count($dataSeriesValues)-1),           // plotOrder
    $dataseriesLabels,                              // plotLabel
    $xAxisTickValues,                               // plotCategory
    $dataSeriesValues                               // plotValues
);

$series->setPlotDirection(PHPExcel_Chart_DataSeries::DIRECTION_BAR);

//  Set the series in the plot area
$plotarea = new PHPExcel_Chart_PlotArea(NULL, array($series));
//  Set the chart legend
$legend = new PHPExcel_Chart_Legend(PHPExcel_Chart_Legend::POSITION_RIGHT, NULL, false);

$title = new PHPExcel_Chart_Title('Test Bar Chart');
$yAxisLabel = new PHPExcel_Chart_Title('Value ($k)');


//  Create the chart
$chart = new PHPExcel_Chart(
    'chart1',       // name
    $title,         // title
    $legend,        // legend
    $plotarea,      // plotArea
    true,           // plotVisibleOnly
    0,              // displayBlanksAs
    NULL,           // xAxisLabel
    $yAxisLabel     // yAxisLabel
);

//  Set the position where the chart should appear in the worksheet
$chart->setTopLeftPosition('A7');
$chart->setBottomRightPosition('M25');

//  Add the chart to the worksheet
$objWorksheet->addChart($chart);

// Save Excel 2007 file
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="Summary.xlsx"');
header('Cache-Control: max-age=0');

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->setIncludeCharts(TRUE);
$objWriter->save('php://output');
?>