Pass array to Excel

Question

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.

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?

Script:

<?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'].'&sensor=true';
    $json = file_get_contents($url);
    $dataReceived = json_decode($json, TRUE);

    //echo '<pre>'; print_r($dataReceived['results']); echo '</pre>';

    $compiled = array();
    $index = 0;
    foreach ($dataReceived['results'] as $value) {
        $compiled[$index] = array(
            'ref' => $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 '<pre>'; print_r($value); echo '</pre>';
        $sortedData[$index] = array(
                'ref' => $value['ref'],
                'lat' => $key['latitude'],
                'long' => $key['longitude'],
                'route' => $value['route'],
                'locality' => $value['locality'], 
                'administrative_area_level_2' => $value['administrative_area_level_2'], 
                'administrative_area_level_1' => $value['administrative_area_level_1'], 
                'country' => $value['country'],
                'postal_code_prefix' => $value['postal_code_prefix'],
                'postal_town' => $value['postal_town'],
                'postal_code' => $value['postal_code'],
                'administrative_area_level_3' => $value['administrative_area_level_3'],
                'street_number' => $value['street_number'],
                'establishment' => $value['establishment'],
            ); 
        $index++;

    }   
    echo '<pre>';print_r($sortedData);echo "</pre>";
    /*$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->getProperties()->setCreator("Anon")
                             ->setLastModifiedBy("Anon")
                             ->setTitle("Crawler Data");


// Add some data
//echo date('H:i:s') . " Add some data\n";
$objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A1', 'ref')
            ->setCellValue('B1', 'latitude')
            ->setCellValue('C1', 'longitude')
            ->setCellValue('D1', 'route')
            ->setCellValue('E1', 'locality')
            ->setCellValue('F1', 'administrative_area_level_2')
            ->setCellValue('G1', 'administrative_area_level_1')
            ->setCellValue('H1', 'country')
            ->setCellValue('I1', 'postal_code_prefix')
            ->setCellValue('J1', 'postal_town')
            ->setCellValue('K1', 'postal_code')
            ->setCellValue('L1', 'administrative_area_level_3')
            ->setCellValue('M1', 'street_number')
            ->setCellValue('N1', 'establishment');


//$objPHPExcel->getActiveSheet()->fromArray($sortedData, null, 'A2');

// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->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->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";




?>

file now looks like:

<?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, &$row) {
        if ($row === NULL) {
            $row = $sheet->getHighestRow() + 1;
        }
        $sheet->fromArray(array($data), NULL, "$col$row");
        $row++;
    };
};
$doc = new PHPExcel();
$doc->getProperties()->setCreator("Anon")
    ->setLastModifiedBy("Anon")
    ->setTitle("Crawler Data");

$sheet = $doc->setActiveSheetIndex(0);
$sheet->fromArray($headers);
$addRow = $addRowCreate($sheet);
foreach ($data as $key) {
    $url = 'http://maps.googleapis.com/maps/api/geocode/json?latlng='.$key['latitude'].','.$key['longitude'].'&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' => $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' => $value['ref'],
                'lat' => $key['latitude'],
                'long' => $key['longitude'],
                'route' => $value['route'],
                'locality' => $value['locality'], 
                'administrative_area_level_2' => $value['administrative_area_level_2'], 
                'administrative_area_level_1' => $value['administrative_area_level_1'], 
                'country' => $value['country'],
                'postal_code_prefix' => $value['postal_code_prefix'],
                'postal_town' => $value['postal_town'],
                'postal_code' => $value['postal_code'],
                'administrative_area_level_3' => $value['administrative_area_level_3'],
                'street_number' => $value['street_number'],
                'establishment' => $value['establishment'],
            )); 
        $index++;
    }   
}

@mysql_close($con);


$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save(str_replace('.php', '.xls', __FILE__));

echo date('H:i:s') . " Done writing file.\r\n";
?>

Answer 1

Just re-reading your code and it looks rather chaotic. I think it can be improved in many areas, however with the least changes I suggest you do the following:

Instead of first creating multiple arrays of rows you want to add, add a new row each time you have created it. For that you do not even need to name the keys. So before you iterate through the database results, first create the headers (I named it $headers):

$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',
);

Also before the database iteration, create a helper-function:

$addRowCreate = function(PHPExcel_Worksheet $sheet, $col = 'A', $row = NULL) {
    return function(array $data) use ($sheet, $col, &$row) {
        if ($row === NULL) {
            $row = $sheet->getHighestRow() + 1;
        }
        $sheet->fromArray(array($data), NULL, "$col$row");
        $row++;
    };
};

This $addRowCreate helper function will allow you to create a add-row-function later on, like in just a second. Also before the database iteration, create the excel document (memory) and set it's properties. Also add the headers in the first row:

$doc = new PHPExcel();
$doc->getProperties()->setCreator("Anon")
    ->setLastModifiedBy("Anon")
    ->setTitle("Crawler Data");

$sheet = $doc->setActiveSheetIndex(0);
$sheet->fromArray($headers);

The next step is to create the add-row-function, which is very easy thanks to the helper above:

$addRow = $addRowCreate($sheet);

You now can use one row after the other by calling it once per row array. For testing purposes, just add another row and save it to disk:

$addRow($headers);
$writer = PHPExcel_IOFactory::createWriter($doc, 'Excel5');
$writer->save(basename(__FILE__, '.php') . '.xls');
die('test finished.');

You should now have create the xls file with two rows. You can then remove the test again and inside your loops use the $addRow function:

$addRow(array(
    'ref' => $value['ref'],
    'lat' => $key['latitude'],
    'long' => $key['longitude'],
    'route' => $value['route'],
    'locality' => $value['locality'],
    'administrative_area_level_2' => $value['administrative_area_level_2'],
    'administrative_area_level_1' => $value['administrative_area_level_1'],
    'country' => $value['country'],
    'postal_code_prefix' => $value['postal_code_prefix'],
    'postal_town' => $value['postal_town'],
    'postal_code' => $value['postal_code'],
    'administrative_area_level_3' => $value['administrative_area_level_3'],
    'street_number' => $value['street_number'],
    'establishment' => $value['establishment'],
));

Technically you can remove the string-array-keys, however I've kept them in so that you can better find the place where you need to change your code as the array already exists there-in.

After you've put this to the work, you can safely remove all the not-any-more-needed temporary arrays and counters.

The full usage-example (test):

require_once 'Classes/PHPExcel.php'; /* PHPExcel <http://phpexcel.codeplex.com/> */

$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, &$row) {
        if ($row === NULL) {
            $row = $sheet->getHighestRow() + 1;
        }
        $sheet->fromArray(array($data), NULL, "$col$row");
        $row++;
    };
};

$doc = new PHPExcel();
$doc->getProperties()->setCreator("Anon")
    ->setLastModifiedBy("Anon")
    ->setTitle("Crawler Data");

$sheet = $doc->setActiveSheetIndex(0);
$sheet->fromArray($headers);

$addRow = $addRowCreate($sheet);

$addRow($headers);
$writer = PHPExcel_IOFactory::createWriter($doc, 'Excel5');
$writer->save(basename(__FILE__, '.php') . '.xls');
die('test finished.');

---

Old Answer:

However, this only puts the headings in the excel file and not the data from the array. What am i doing wrong?

You are actually only storing the headings into the file. You do not store any data.

How can I get this to work?

Bring the array in the right format and uncomment the following line in your code:

//$objPHPExcel->getActiveSheet()->fromArray($sortedData, null, 'A2');

Take care that $sortedData is the needed 2D array, see the examples in this dicsussion:

Array
(

    [0] => Array
        (
            [0] => Relative CellA1
            [1] => Relative CellB1
            [2] => Relative CellC1
        )
    [1] => Array
        (
            [0] => Relative CellA2
            [1] => Relative CellB2
            [2] => Relative CellC2
        )

)

Unless you don't get the array into that kind of 2D format, the function will not work in your favor.

Please let me know if you've got any more questions.