title: Pass array to Excel
body: <p>I am using (trying to use) PHPExcel to pass an array to excel, I have defined the headings and i want every array in the array to in a separate row.</p>
  
  <p>However, this only puts the headings in the excel file and not the data from the array. What am i doing wrong?How can I get this to work?</p>
  
  <p>Script:</p>
  
  <pre><code>&lt;?php
  $databasehost = "localhost";
  $databasename = "dummydata";
  $databasetable = "import1";
  $databasetable2 = "data1";
  $databaseusername ="dummydata";
  $databasepassword = "dummydata";
  
  
  $con = @mysql_connect($databasehost,$databaseusername,$databasepassword) or die(mysql_error());
  @mysql_select_db($databasename) or die(mysql_error());
  
  $query = mysql_query("SELECT * from $databasetable;");
  
  $data = array();
  
  $index = 0;
  while($row = mysql_fetch_assoc($query))
  {
       $data[$index] = $row;
       $index++;
  }
  
  foreach ($data as $key) {
      $url = 'http://maps.googleapis.com/maps/api/geocode/json?latlng='.$key['latitude'].','.$key['longitude'].'&amp;sensor=true';
      $json = file_get_contents($url);
      $dataReceived = json_decode($json, TRUE);
  
      //echo '&lt;pre&gt;'; print_r($dataReceived['results']); echo '&lt;/pre&gt;';
  
      $compiled = array();
      $index = 0;
      foreach ($dataReceived['results'] as $value) {
          $compiled[$index] = array(
              'ref' =&gt; $key['id']);
              foreach ($value['address_components'] as $value2)
              {
                  $compiled[$index][$value2['types'][0]] = $value2['long_name'];
              }
          $index++;
      }
      $sortedData = array();
      $index = 0;
      foreach ($compiled as $value) {
          //echo '&lt;pre&gt;'; print_r($value); echo '&lt;/pre&gt;';
          $sortedData[$index] = array(
                  'ref' =&gt; $value['ref'],
                  'lat' =&gt; $key['latitude'],
                  'long' =&gt; $key['longitude'],
                  'route' =&gt; $value['route'],
                  'locality' =&gt; $value['locality'], 
                  'administrative_area_level_2' =&gt; $value['administrative_area_level_2'], 
                  'administrative_area_level_1' =&gt; $value['administrative_area_level_1'], 
                  'country' =&gt; $value['country'],
                  'postal_code_prefix' =&gt; $value['postal_code_prefix'],
                  'postal_town' =&gt; $value['postal_town'],
                  'postal_code' =&gt; $value['postal_code'],
                  'administrative_area_level_3' =&gt; $value['administrative_area_level_3'],
                  'street_number' =&gt; $value['street_number'],
                  'establishment' =&gt; $value['establishment'],
              ); 
          $index++;
  
      }   
      echo '&lt;pre&gt;';print_r($sortedData);echo "&lt;/pre&gt;";
      /*$query = mysql_query("INSERT INTO $databasetable2
                  ref, lat, long, route, locality, administrative_area_level_2, administrative_area_level_1, country, postal_code_prefix, postal_town)
                  VALUES
                  ()");*/
  }
  
  @mysql_close($con);
  
  /**
   * PHPExcel
   *
   * Copyright (C) 2006 - 2011 PHPExcel
   *
   * This library is free software; you can redistribute it and/or
   * modify it under the terms of the GNU Lesser General Public
   * License as published by the Free Software Foundation; either
   * version 2.1 of the License, or (at your option) any later version.
   *
   * This library is distributed in the hope that it will be useful,
   * but WITHOUT ANY WARRANTY; without even the implied warranty of
   * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
   * Lesser General Public License for more details.
   *
   * You should have received a copy of the GNU Lesser General Public
   * License along with this library; if not, write to the Free Software
   * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA
   *
   * @category   PHPExcel
   * @package    PHPExcel
   * @copyright  Copyright (c) 2006 - 2011 PHPExcel (http://www.codeplex.com/PHPExcel)
   * @license    http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt    LGPL
   * @version    ##VERSION##, ##DATE##
   */
  
  /** Error reporting */
  error_reporting(E_ALL);
  ini_set('display_errors', '1');
  date_default_timezone_set('Europe/London');
  
  /** PHPExcel */
  require_once 'Classes/PHPExcel.php';
  
  
  // Create new PHPExcel object
  //echo date('H:i:s') . " Create new PHPExcel object\n";
  $objPHPExcel = new PHPExcel();
  
  // Set properties
  //echo date('H:i:s') . " Set properties\n";
  $objPHPExcel-&gt;getProperties()-&gt;setCreator("Anon")
                               -&gt;setLastModifiedBy("Anon")
                               -&gt;setTitle("Crawler Data");
  
  
  // Add some data
  //echo date('H:i:s') . " Add some data\n";
  $objPHPExcel-&gt;setActiveSheetIndex(0)
              -&gt;setCellValue('A1', 'ref')
              -&gt;setCellValue('B1', 'latitude')
              -&gt;setCellValue('C1', 'longitude')
              -&gt;setCellValue('D1', 'route')
              -&gt;setCellValue('E1', 'locality')
              -&gt;setCellValue('F1', 'administrative_area_level_2')
              -&gt;setCellValue('G1', 'administrative_area_level_1')
              -&gt;setCellValue('H1', 'country')
              -&gt;setCellValue('I1', 'postal_code_prefix')
              -&gt;setCellValue('J1', 'postal_town')
              -&gt;setCellValue('K1', 'postal_code')
              -&gt;setCellValue('L1', 'administrative_area_level_3')
              -&gt;setCellValue('M1', 'street_number')
              -&gt;setCellValue('N1', 'establishment');
  
  
  //$objPHPExcel-&gt;getActiveSheet()-&gt;fromArray($sortedData, null, 'A2');
  
  // Set active sheet index to the first sheet, so Excel opens this as the first sheet
  $objPHPExcel-&gt;setActiveSheetIndex(0);
  
  
  // Save Excel 2007 file
  //echo date('H:i:s') . " Write to Excel2007 format\n";
  
   //!!!!!!!!!!!!!!!!!!!-------- I Change 'Excel2007' to 'Excel5' ------!!!!!!!!!!!!!!!!!!!!!!!!
  $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
   //!!!!!!!!!!!!!!!!!!!-------- I Change '.xlsx' to '.xls'              ------!!!!!!!!!!!!!!!!!!!!!!!!
  $objWriter-&gt;save(str_replace('.php', '.xls', __FILE__));
  
  
  // Echo memory peak usage
  //echo date('H:i:s') . " Peak memory usage: " . (memory_get_peak_usage(true) / 1024 / 1024) . " MB\r\n";
  
  // Echo done
  //echo date('H:i:s') . " Done writing file.\r\n";
  
  
  
  
  ?&gt;
  </code></pre>
  
  <p>file now looks like:</p>
  
  <pre><code>&lt;?php
  require_once 'Classes/PHPExcel.php';
  date_default_timezone_set('Europe/London');
  
  $databasehost = "localhost";
  $databasename = "ryansmur_crawler";
  $databasetable = "import1";
  $databasetable2 = "data1";
  $databaseusername ="ryansmur_admin";
  $databasepassword = "Penelope1";
  
  
  $con = @mysql_connect($databasehost,$databaseusername,$databasepassword) or die(mysql_error());
  @mysql_select_db($databasename) or die(mysql_error());
  
  $query = mysql_query("SELECT * from $databasetable;");
  
  $data = array();
  
  $index = 0;
  while($row = mysql_fetch_assoc($query))
  {
       $data[$index] = $row;
       $index++;
  }
  $headers = array(
      'ref', 'lat', 'long', 'route', 'locality', 'administrative_area_level_2',
      'administrative_area_level_1', 'country', 'postal_code_prefix',
      'postal_town', 'postal_code', 'administrative_area_level_3',
      'street_number', 'establishment',
  );
  $addRowCreate = function(PHPExcel_Worksheet $sheet, $col = 'A', $row = NULL) {
      return function(array $data) use ($sheet, $col, &amp;$row) {
          if ($row === NULL) {
              $row = $sheet-&gt;getHighestRow() + 1;
          }
          $sheet-&gt;fromArray(array($data), NULL, "$col$row");
          $row++;
      };
  };
  $doc = new PHPExcel();
  $doc-&gt;getProperties()-&gt;setCreator("Anon")
      -&gt;setLastModifiedBy("Anon")
      -&gt;setTitle("Crawler Data");
  
  $sheet = $doc-&gt;setActiveSheetIndex(0);
  $sheet-&gt;fromArray($headers);
  $addRow = $addRowCreate($sheet);
  foreach ($data as $key) {
      $url = 'http://maps.googleapis.com/maps/api/geocode/json?latlng='.$key['latitude'].','.$key['longitude'].'&amp;sensor=true';
      $json = file_get_contents($url);
      $dataReceived = json_decode($json, TRUE);
  
      $compiled = array();
      $index = 0;
      foreach ($dataReceived['results'] as $value) {
          $compiled[$index] = array(
              'ref' =&gt; $key['id']);
              foreach ($value['address_components'] as $value2)
              {
                  $compiled[$index][$value2['types'][0]] = $value2['long_name'];
              }
          $index++;
      }
      $sortedData = array();
      $index = 0;
      foreach ($compiled as $value) {
          $addRow(array(
                  'ref' =&gt; $value['ref'],
                  'lat' =&gt; $key['latitude'],
                  'long' =&gt; $key['longitude'],
                  'route' =&gt; $value['route'],
                  'locality' =&gt; $value['locality'], 
                  'administrative_area_level_2' =&gt; $value['administrative_area_level_2'], 
                  'administrative_area_level_1' =&gt; $value['administrative_area_level_1'], 
                  'country' =&gt; $value['country'],
                  'postal_code_prefix' =&gt; $value['postal_code_prefix'],
                  'postal_town' =&gt; $value['postal_town'],
                  'postal_code' =&gt; $value['postal_code'],
                  'administrative_area_level_3' =&gt; $value['administrative_area_level_3'],
                  'street_number' =&gt; $value['street_number'],
                  'establishment' =&gt; $value['establishment'],
              )); 
          $index++;
      }   
  }
  
  @mysql_close($con);
  
  
  $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
  $objWriter-&gt;save(str_replace('.php', '.xls', __FILE__));
  
  echo date('H:i:s') . " Done writing file.\r\n";
  ?&gt;
  </code></pre>