我正在使用(尝试使用)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";
?>
最佳答案
只是重新阅读您的代码，它看起来相当困惑。我认为它可以在很多方面得到改进，但是对于最少的更改，我建议您执行以下操作:
与其先创建多个要添加的行数组，不如在每次创建新行时添加一个新行。为此，您甚至不需要为键命名。因此，在遍历数据库结果之前，首先创建 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 格式，否则该函数不会对您有利。
如果您还有其他问题，请告诉我。
关于php - 将数组传递给 Excel，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/16702153/
我有多个ActiveRecord子类Item的实例数组，我需要根据最早的事件循环打印。在这种情况下，我需要打印付款和维护日期，如下所示:ItemAmaintenancerequiredin5daysItemBpaymentrequiredin6daysItemApaymentrequiredin7daysItemBmaintenancerequiredin8days我目前有两个查询，用于查找maintenance和payment项目(非排他性查询)，并输出如下内容:paymentrequiredin...maintenancerequiredin...有什么方法可以改善上述(丑陋的)代
我的代码目前看起来像这样numbers=[1,2,3,4,5]defpop_threepop=[]3.times{pop有没有办法在一行中完成pop_three方法中的内容？我基本上想做类似numbers.slice(0,3)的事情，但要删除切片中的数组项。嗯...嗯，我想我刚刚意识到我可以试试slice! 最佳答案 是numbers.pop(3)或者numbers.shift(3)如果你想要另一边。 关于ruby-多次弹出/移动ruby数组，我们在StackOverflow上找到一
我需要读入一个包含数字列表的文件。此代码读取文件并将其放入二维数组中。现在我需要获取数组中所有数字的平均值，但我需要将数组的内容更改为int。有什么想法可以将to_i方法放在哪里吗？ClassTerraindefinitializefile_name@input=IO.readlines(file_name)#readinfile@size=@input[0].to_i@land=[@size]x=1whilex 最佳答案 只需将数组映射为整数:@land边注如果你想得到一条线的平均值，你可以这样做:values=@input[x]
我正在使用puppet为ruby程序提供一组常量。我需要提供一组主机名，我的程序将对其进行迭代。在我之前使用的bash脚本中，我只是将它作为一个puppet变量hosts=>"host1,host2"我将其提供给bash脚本作为HOSTS=显然这对ruby不太适用——我需要它的格式hosts=["host1","host2"]自从phosts和putsmy_array.inspect提供输出["host1","host2"]我希望使用其中之一。不幸的是，我终其一生都无法弄清楚如何让它发挥作用。我尝试了以下各项:我发现某处他们指出我需要在函数调用前放置“function_”……这
这个问题在这里已经有了答案:Checktoseeifanarrayisalreadysorted?(8个答案)关闭9年前。我只是想知道是否有办法检查数组是否在增加？这是我的解决方案，但我正在寻找更漂亮的方法:n=-1@arr.flatten.each{|e|returnfalseife
我有一个这样的哈希数组:[{:foo=>2,:date=>Sat,01Sep2014},{:foo2=>2,:date=>Sat,02Sep2014},{:foo3=>3,:date=>Sat,01Sep2014},{:foo4=>4,:date=>Sat,03Sep2014},{:foo5=>5,:date=>Sat,02Sep2014}]如果:date相同，我想合并哈希值。我对上面数组的期望是:[{:foo=>2,:foo3=>3,:date=>Sat,01Sep2014},{:foo2=>2,:foo5=>5:date=>Sat,02Sep2014},{:foo4=>4,:dat
我正在尝试在Ruby中制作一个cli应用程序，它接受一个给定的数组，然后将其显示为一个列表，我可以使用箭头键浏览它。我觉得我已经在Ruby中看到一个库已经这样做了，但我记不起它的名字了。我正在尝试对soundcloud2000中的代码进行逆向工程做类似的事情，但他的代码与SoundcloudAPI的使用紧密耦合。我知道cursesgem，我正在考虑更抽象的东西。广告有没有人见过可以做到这一点的库或一些概念证明的Ruby代码可以做到这一点？ 最佳答案 我不知道这是否是您正在寻找的，但也许您可以使用我的想法。由于我没有关于您要完成的工作
我使用Ember作为我的前端和GrapeAPI来为我的API提供服务。前端发送类似:{"service"=>{"name"=>"Name","duration"=>"30","user"=>nil,"organization"=>"org","category"=>nil,"description"=>"description","disabled"=>true,"color"=>nil,"availabilities"=>[{"day"=>"Saturday","enabled"=>false,"timeSlots"=>[{"startAt"=>"09:00AM","endAt"=>
我没有找到太多关于如何执行此操作的信息，尽管有很多关于如何使用像这样的redirect_to将参数传递给重定向的建议:action=>'something',:controller=>'something'在我的应用程序中，我在路由文件中有以下内容match'profile'=>'User#show'我的表演Action是这样的defshow@user=User.find(params[:user])@title=@user.first_nameend重定向发生在同一个用户Controller中，就像这样defregister@title="Registration"@user=Use
我正在尝试按0-9和a-z的顺序创建数字和字母列表。我有一组值value_array=['0','1','2','3','4','5','6','7','8','9','a','b'，'光盘'，'e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','','u','v','w','x','y','z']和一个组合列表的数组，按顺序，这些数字可以产生x个字符，比方说三个list_array=[]和一个当前字母和数字组合的数组(在将它插入列表数组之前我会把它变成一个字符串，]current_combo['0','0','0']