php - 将数组传递给 Excel

Question

我正在使用(尝试使用)PHPExcel 将数组传递给 excel，我已经定义了标题，我希望数组中的每个数组都在单独的行中。

但是，这只会将标题放入 excel 文件中，而不是数组中的数据。我做错了什么？我怎样才能让它工作？

脚本:

<?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";




?>

文件现在看起来像:

<?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

只是重新阅读您的代码，它看起来相当困惑。我认为它可以在很多方面得到改进，但是对于最少的更改，我建议您执行以下操作:

与其先创建多个要添加的行数组，不如在每次创建新行时添加一个新行。为此，您甚至不需要为键命名。因此，在遍历数据库结果之前，首先创建 header (我将其命名为 $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',
);

同样在数据库迭代之前，创建一个辅助函数:

$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++;
    };
};

此 $addRowCreate 辅助函数将允许您稍后创建一个添加行函数，就像在一秒钟内。同样在数据库迭代之前，创建 excel 文档(内存)并设置它的属性。还要在第一行添加标题:

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

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

下一步是创建 add-row-function，由于上面的助手，这非常很容易:

$addRow = $addRowCreate($sheet);

您现在可以通过对每个行数组调用一次来逐行使用。出于测试目的，只需添加另一行并将其保存到磁盘:

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

您现在应该已经创建了包含两行的 xls 文件。然后您可以再次删除测试并在您的循环中使用$addRow 函数:

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

从技术上讲，您可以删除字符串数组键，但我将它们保留在其中，以便您可以更好地找到需要更改代码的位置，因为数组已经存在于其中。

将其投入使用后，您可以安全地删除所有不再需要的临时数组和计数器。

完整的用法示例(测试):

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.');

---

旧答案:

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?

以正确的格式导入数组并取消注释代码中的以下行:

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

注意 $sortedData 是所需的二维数组，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
        )

)

除非您不将数组转换为那种 2D 格式，否则该函数不会对您有利。

如果您还有其他问题，请告诉我。