title: PHPExcel - Populate data from Array
body: <p>I have so far successfully exported data from Mysql into Excel via PHPExcel, made my tables, formatted, and everything is hunky dory.</p>
  
  <p>However, so far any attempt at making graphs from said tables failed miserably. Not only, after days on end of Google Search, I've not found a simple example/tutorial on how to populate the graph values from MySQL.</p>
  
  <p>It all boils down to this:</p>
  
  <pre><code>Taken from https://github.com/affinitybridge/phpexcel/blob/master/Tests/33chartcreate-pie.php
  
  /** PHPExcel */
  include 'PHPExcel.php';
  $objPHPExcel = new PHPExcel();
  $objWorksheet = $objPHPExcel-&gt;getActiveSheet();
  $objWorksheet-&gt;fromArray(
      array(
          array('',   2010,   2011,   2012),
          array('Q1',   12,   15,     21),
          array('Q2',   56,   73,     86),
          array('Q3',   52,   61,     69),
          array('Q4',   30,   32,     0),
      )
  );
  </code></pre>
  
  <p>How the hell do I populate $objWorksheet->fromArray from either a) Mysql or b) excel.</p>
  
  <p>If it's SQL, I would need to run this query for all the tables in my database.</p>
  
  <p>$sql = "SELECT <code>Functional_Area</code>, <code>Passed__Status</code> ,  <code>Blocked__Status</code> ,  <code>Failed__Status</code> FROM $tbl_name WHERE 1;</p>
  
  <p>Context: I have several Functional Areas, each of them has different values in Passed/Failed/Blocked. I need 1 Graph for each Functional Areas. On average I have 14 functional areas per each Table.</p>
  
  <p>I would then construct a Pie chart (or Bar chart, I'm not fussy) for each Functional Area.</p>
  
  <p>Option 2) Excel</p>
  
  <p>Since I already have said data in an excel, I would need to populate the data from:</p>
  
  <p><a href="https://i.stack.imgur.com/Momxy.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Momxy.png" alt="enter image description here"></a> </p>
  
  <p>Again, 1 graph for each ROW (ROW 5 (FA = EPG) will be 1 graph, ROW 6 (FA = VOD) will be another graph, etc...).</p>
  
  <p>This will have to be repeated for EACH worksheet...</p>
  
  <p>My attempt so far is below, this produces for each worksheet (and this is good) a graph, which however is empty (not good).</p>
  
  <pre><code>foreach (glob("*.xls") as $f) 
  {
      $inputFileName = $f;    
      echo "Found $inputFileName &lt;br/&gt;";  
  }
  
  
  //  Read your Excel workbook
  
          $inputFileType = PHPExcel_IOFactory::identify($inputFileName);
          $objReader = PHPExcel_IOFactory::createReader($inputFileType);
          $objPHPExcel = $objReader-&gt;load($inputFileName);
          $i = 0;
          foreach ($objPHPExcel-&gt;getWorksheetIterator() as $worksheet) 
              {
                  $arrayData[$worksheet-&gt;getTitle()] = $worksheet-&gt;toArray();
                  echo "Title: ".$worksheet-&gt;getTitle()."&lt;br/&gt;"; 
                  $wsTitle = $worksheet-&gt;getTitle();
  
                  $objWorksheet = $objPHPExcel-&gt;setActiveSheetIndexByName($wsTitle);
  
                  $highestRow         = $worksheet-&gt;getHighestRow(); // e.g. 10
                  $endofData = $highestRow -1;
                  $highestColumn      = $worksheet-&gt;getHighestColumn(); // e.g 'F'
                  $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
                  $colNumberStart = PHPExcel_Cell::columnIndexFromString("D");
                  $BeforeTOT = $colNumberStart - 2;            
                  $TOT = $colNumberStart - 1;             
                  echo "Highest Row is ==&gt; $highestRow &lt;br/&gt;End of Data - header is $endofData &lt;br/&gt; Highest Col is ==&gt;  $highestColumn &lt;br/&gt; Start col is ==&gt; $colNumberStart and Highest Col end is ==&gt; $highestColumnIndex &lt;br/&gt;";
  
  
  
  
                  //  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' . $i} = array(
                      new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$D$4', null, 1),   //  2010
                      new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$G$4', null, 1),   //  2011
                      new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$H$4', null, 1),   //  2012
                      new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$I$4', null, 1),   //  2012
                  );
  
                  displayArray(${'dataseriesLabels' . $i});
                  //  Set the X-Axis Labels
                  //      Datatype
                  //      Cell reference for data
                  //      Format Code
                  //      Number of datapoints in series
                  //      Data values
                  //      Data Marker
                  ${'xAxisTickValues' . $i} = array(
                      new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$D$4:$H$4', null, 19), //  Q1 to Q4
                  );
                  displayArray(${'xAxisTickValues' . $i});
                  //  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' . $i} = array(
                      new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$G$5:$I$5', null, 4),  
                  );
                  displayArray(${'dataSeriesValues' . $i});
                  //  Build the dataseries
                  ${'series' . $i} = new PHPExcel_Chart_DataSeries(
                      PHPExcel_Chart_DataSeries::TYPE_PIECHART,               // plotType
                      PHPExcel_Chart_DataSeries::GROUPING_PERCENT_STACKED,    // plotGrouping
                      range(0, count(${'dataseriesLabels' . $i})-1),                  // plotOrder
                      ${'dataseriesLabels' . $i},                                     // plotLabel
                      ${'xAxisTickValues' . $i},                                      // plotCategory
                      ${'dataSeriesValues' . $i}                                      // plotValues
                  );
  
                  displayArray(${'series' . $i});
                  //  Set the series in the plot area
                  ${'plotarea' . $i} = new PHPExcel_Chart_PlotArea(null, array(${'series' . $i}));
                  //  Set the chart legend
                  ${'legend' . $i} = new PHPExcel_Chart_Legend(PHPExcel_Chart_Legend::POSITION_TOPRIGHT, null, false);
                  ${'title' . $i} = new PHPExcel_Chart_Title('Test %age-Stacked Area Chart');
                  ${'yAxisLabel' . $i} = new PHPExcel_Chart_Title('Value ($k)');
                  //  Create the chart
                  ${'chart' . $i} = new PHPExcel_Chart(
                      'chart1',       // name
                      ${'title' . $i},        // title
                      ${'legend' . $i},       // legend
                      ${'plotarea' . $i},     // plotArea
                      true,           // plotVisibleOnly
                      0,              // displayBlanksAs
                      null,           // xAxisLabel
                      ${'yAxisLabel' . $i}    // yAxisLabel
                  );
                  //  Set the position where the chart should appear in the worksheet
                  ${'chart' . $i}-&gt;setTopLeftPosition('A7');
                  ${'chart' . $i}-&gt;setBottomRightPosition('H20');
                  //  Add the chart to the worksheet
                  $objWorksheet-&gt;addChart(${'chart' . $i});
                  //  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
  
                  // Save Excel 2007 file
                  echo date('H:i:s') , " Write to Excel2007 format" , EOL;
                  $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
                  $objWriter-&gt;setIncludeCharts(TRUE);
                  $objWriter-&gt;save(str_replace('.php', '.xlsx', __FILE__));
                  echo date('H:i:s') , " File written to " , str_replace('.php', '.xlsx', pathinfo(__FILE__, PATHINFO_BASENAME)) , EOL;
                  // Echo memory peak usage
                  echo date('H:i:s') , " Peak memory usage: " , (memory_get_peak_usage(true) / 1024 / 1024) , " MB" , EOL;
                  // Echo done
                  echo date('H:i:s') , " Done writing file" , EOL;
                  echo 'File has been created in ' , getcwd() , EOL;
                  $i++;
  }
  </code></pre>