title: How to Create Bar Chart from MySQL Data Using PhpExcel
body: <p>After searching how to create report and chart in excel using php, i find PHPExcel class. I try using it, to create my report.</p>
  
  <p>Currently i have success implement it, but i have a problem to insert chat (bar). This is what i've done so far.</p>
  
  <pre><code>&lt;?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 &gt;= '05/01/2015') AND (h.INVDATE &lt;= '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' =&gt; '8MB'
  );
  PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);
  
  // Set metadata
  $objPHPExcel-&gt;getProperties()
      -&gt;setCreator("REPORTIS")
      -&gt;setLastModifiedBy("REPORTIS")
      -&gt;setTitle("Summary Airline")
      -&gt;setSubject("Summary Airline")
      -&gt;setDescription(
          "Summary Airline XLSX, generated using PHP classes."
      )
      -&gt;setKeywords("summary airline bayu buana")
      -&gt;setCategory("Report");
  
  // Set the active Excel worksheet to sheet 0
  $objPHPExcel-&gt;setActiveSheetIndex(0); 
  
  // Set sheet title
  $objPHPExcel-&gt;getActiveSheet()-&gt;setTitle('Summary Airline');
  
  // Set header
  $objPHPExcel-&gt;getActiveSheet()-&gt;getColumnDimension('A')-&gt;setWidth(40);
  $objPHPExcel-&gt;getActiveSheet()-&gt;getStyle('A1')
      -&gt;getFont()-&gt;setBold(true);
  $objPHPExcel-&gt;getActiveSheet()-&gt;getStyle('A1')
      -&gt;getFont()-&gt;getColor()-&gt;setARGB(PHPExcel_Style_Color::COLOR_WHITE);
  $objPHPExcel-&gt;getActiveSheet()-&gt;getStyle('A1')
      -&gt;getAlignment()-&gt;setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  $objPHPExcel-&gt;getActiveSheet()-&gt;getStyle('A1')
      -&gt;getBorders()-&gt;getTop()-&gt;setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
  $objPHPExcel-&gt;getActiveSheet()-&gt;getStyle('A1')
      -&gt;getBorders()-&gt;getBottom()-&gt;setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
  $objPHPExcel-&gt;getActiveSheet()-&gt;getStyle('A1')
      -&gt;getBorders()-&gt;getLEFT()-&gt;setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
  $objPHPExcel-&gt;getActiveSheet()-&gt;getStyle('A1')
      -&gt;getBorders()-&gt;getRight()-&gt;setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
  $objPHPExcel-&gt;getActiveSheet()-&gt;getStyle('A1')
      -&gt;getFill()-&gt;setFillType(PHPExcel_Style_Fill::FILL_SOLID);
  $objPHPExcel-&gt;getActiveSheet()-&gt;getStyle('A1')
      -&gt;getFill()-&gt;getStartColor()-&gt;setARGB('FF008000');
  
  $objPHPExcel-&gt;getActiveSheet()-&gt;getColumnDimension('B')-&gt;setWidth(30);
  $objPHPExcel-&gt;getActiveSheet()-&gt;getStyle('B1')
      -&gt;getFont()-&gt;getColor()-&gt;setARGB(PHPExcel_Style_Color::COLOR_WHITE);
  $objPHPExcel-&gt;getActiveSheet()-&gt;getStyle('B1')
      -&gt;getFont()-&gt;setBold(true);
  $objPHPExcel-&gt;getActiveSheet()-&gt;getStyle('B1')
      -&gt;getAlignment()-&gt;setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  $objPHPExcel-&gt;getActiveSheet()-&gt;getStyle('B1')
      -&gt;getBorders()-&gt;getTop()-&gt;setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
  $objPHPExcel-&gt;getActiveSheet()-&gt;getStyle('B1')
      -&gt;getBorders()-&gt;getBottom()-&gt;setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
  $objPHPExcel-&gt;getActiveSheet()-&gt;getStyle('B1')
      -&gt;getBorders()-&gt;getLEFT()-&gt;setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
  $objPHPExcel-&gt;getActiveSheet()-&gt;getStyle('B1')
      -&gt;getBorders()-&gt;getRight()-&gt;setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
  $objPHPExcel-&gt;getActiveSheet()-&gt;getStyle('B1')
      -&gt;getFill()-&gt;setFillType(PHPExcel_Style_Fill::FILL_SOLID);
  $objPHPExcel-&gt;getActiveSheet()-&gt;getStyle('B1')
      -&gt;getFill()-&gt;getStartColor()-&gt;setARGB('FF008000');
  
  $objPHPExcel-&gt;getActiveSheet()-&gt;getColumnDimension('C')-&gt;setWidth(30);
  $objPHPExcel-&gt;getActiveSheet()-&gt;getStyle('C1')
      -&gt;getFont()-&gt;getColor()-&gt;setARGB(PHPExcel_Style_Color::COLOR_WHITE);
  $objPHPExcel-&gt;getActiveSheet()-&gt;getStyle('C1')
      -&gt;getFont()-&gt;setBold(true);
  $objPHPExcel-&gt;getActiveSheet()-&gt;getStyle('C1')
      -&gt;getAlignment()-&gt;setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  $objPHPExcel-&gt;getActiveSheet()-&gt;getStyle('C1')
      -&gt;getBorders()-&gt;getTop()-&gt;setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
  $objPHPExcel-&gt;getActiveSheet()-&gt;getStyle('C1')
      -&gt;getBorders()-&gt;getBottom()-&gt;setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
  $objPHPExcel-&gt;getActiveSheet()-&gt;getStyle('C1')
      -&gt;getBorders()-&gt;getLEFT()-&gt;setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
  $objPHPExcel-&gt;getActiveSheet()-&gt;getStyle('C1')
      -&gt;getBorders()-&gt;getRight()-&gt;setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
  $objPHPExcel-&gt;getActiveSheet()-&gt;getStyle('C1')
      -&gt;getFill()-&gt;setFillType(PHPExcel_Style_Fill::FILL_SOLID);
  $objPHPExcel-&gt;getActiveSheet()-&gt;getStyle('C1')
      -&gt;getFill()-&gt;getStartColor()-&gt;setARGB('FF008000');
  
  $objPHPExcel-&gt;getActiveSheet()-&gt;SetCellValue('A1', 'AIRLINE'); 
  $objPHPExcel-&gt;getActiveSheet()-&gt;SetCellValue('B1', 'JUMLAH TIKET');
  $objPHPExcel-&gt;getActiveSheet()-&gt;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-&gt;getActiveSheet()-&gt;SetCellValue('A'.$rowCount, $airline);
      $objPHPExcel-&gt;getActiveSheet()-&gt;getStyle('A'.$rowCount)-&gt;getNumberFormat()-&gt;setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1); 
      $objPHPExcel-&gt;getActiveSheet()-&gt;getStyle('A'.$rowCount)-&gt;getBorders()-&gt;getTOP()-&gt;setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
      $objPHPExcel-&gt;getActiveSheet()-&gt;getStyle('A'.$rowCount)-&gt;getBorders()-&gt;getBOTTOM()-&gt;setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
      $objPHPExcel-&gt;getActiveSheet()-&gt;getStyle('A'.$rowCount)-&gt;getBorders()-&gt;getRIGHT()-&gt;setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
      $objPHPExcel-&gt;getActiveSheet()-&gt;getStyle('A'.$rowCount)-&gt;getBorders()-&gt;getLEFT()-&gt;setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
      $objPHPExcel-&gt;getActiveSheet()-&gt;getStyle('A'.$rowCount)-&gt;getAlignment()-&gt;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-&gt;getActiveSheet()-&gt;SetCellValue('B'.$rowCount, $ticket);
      $objPHPExcel-&gt;getActiveSheet()-&gt;getStyle('B'.$rowCount)-&gt;getNumberFormat()-&gt;setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
      $objPHPExcel-&gt;getActiveSheet()-&gt;getStyle('B'.$rowCount)-&gt;getBorders()-&gt;getTOP()-&gt;setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
      $objPHPExcel-&gt;getActiveSheet()-&gt;getStyle('B'.$rowCount)-&gt;getBorders()-&gt;getBOTTOM()-&gt;setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
      $objPHPExcel-&gt;getActiveSheet()-&gt;getStyle('B'.$rowCount)-&gt;getBorders()-&gt;getRIGHT()-&gt;setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
      $objPHPExcel-&gt;getActiveSheet()-&gt;getStyle('B'.$rowCount)-&gt;getBorders()-&gt;getLEFT()-&gt;setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
      $objPHPExcel-&gt;getActiveSheet()-&gt;getStyle('B'.$rowCount)-&gt;getAlignment()-&gt;setWrapText(true);
      $objPHPExcel-&gt;getActiveSheet()-&gt;SetCellValue('C'.$rowCount, $jum); 
      $objPHPExcel-&gt;getActiveSheet()-&gt;getStyle('C'.$rowCount)-&gt;getNumberFormat()-&gt;setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
      $objPHPExcel-&gt;getActiveSheet()-&gt;getStyle('C'.$rowCount)-&gt;getBorders()-&gt;getTOP()-&gt;setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
      $objPHPExcel-&gt;getActiveSheet()-&gt;getStyle('C'.$rowCount)-&gt;getBorders()-&gt;getBOTTOM()-&gt;setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
      $objPHPExcel-&gt;getActiveSheet()-&gt;getStyle('C'.$rowCount)-&gt;getBorders()-&gt;getRIGHT()-&gt;setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
      $objPHPExcel-&gt;getActiveSheet()-&gt;getStyle('C'.$rowCount)-&gt;getBorders()-&gt;getLEFT()-&gt;setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
      $objPHPExcel-&gt;getActiveSheet()-&gt;getStyle('C'.$rowCount)-&gt;getAlignment()-&gt;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-&gt;setTopLeftPosition('E1');
  $chart-&gt;setBottomRightPosition('J10');
  
  //  Add the chart to the worksheet
  $objPHPExcel-&gt;getActiveSheet()-&gt;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-&gt;setIncludeCharts(TRUE);
  $objWriter-&gt;save('php://output');
  
  ?&gt;
  </code></pre>
  
  <p>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.</p>
  
  <p>Many thanks,</p>
  
  <p>regards</p>