接到一个资料不完整的Excel数据表如下：
表中要完善《电话》《详细地址》《坐标》(即百度地图上的经纬度) 由于提供的条件有限，完成它有两种方法，
方法一：复制表中《售点名称》粘去百度反查坐标，如下： http://api.map.baidu.com/lbsapi/getpoint/index.html
这个完全是个体力活，因为查出了，还要复制贴到excel表上去(或者以后台方式登陆添加)，最后也要写代码到库表上去。
方法二：当然是程序实现
程序实现中：我们用到了PHPExcel 类，请去官方下载http://phpexcel.codeplex.com/
用到的两个URL，geocoder_v2要开发者申请一个Key，请自行在百度中申请。
功能：(实际应用中的表结构和excel的单元格引用请自行修改)：
- 1.写进指定库表bud_shop
- 2.更新原来资料不全的excel文件
- 3.也可以生成新的excel文件
代码如下：
<?php
set_time_limit(0);
require_once('Classes/PHPExcel.php');
require_once('Classes/PHPExcel/Reader/Excel2007.php');
require_once('Classes/PHPExcel/Writer/Excel2007.php');
require_once('Classes/PHPExcel/IOFactory.php');
class ShopInfo {
private $file = '';
private $type = '';
private $city = '';
private $key = '';
private $heightRow;
private $startNum;
public $resource = array();
public $trade = array();
public $errInfo = array();
public function __construct($file, $type, $city = null, $start, $letter = null){
try {
$slug = preg_match('/^([a-z0-9_\-]+)\.([a-z]){3,4}$/',$file);
if ( ! $slug) throw new Exception("ERROR: file name is error!");
if ( ! is_int($start)) throw new Exception('ERROR: $start format error!');
$this->file = $file;
$this->type = $type;
$this->city = $city;
$this->key = '7fe02e6686a18266776b53be78d8dd42';
$this->startNum = $start;
$excel = new PHPExcel_Reader_Excel2007();
$objExcel = $excel->load($file);
$sheet = $objExcel->getSheet(0);
$highestRow = $sheet->getHighestRow();
$this->heightRow = $highestRow;
$highestColumn = ($letter) ? $letter : $sheet->getHighestColumn();
for ($row = $start; $row <=$highestRow; $row++) {
$temp = $sheet->rangeToArray( $letter.$row.':'.$highestColumn.$row,NULL,TRUE,FALSE);
$this->resource[] = $temp[0][0];
}
unset($excel);
unset($objExcel);
unset($sheet);
} catch (Exception $e) {
echo $e->getMessage();
exit;
}
}
/*
* 处理百度相关信息
*/
public function process() {
$trade = array();
$errInfo = array();
$i = 0;
foreach ($this->resource as $name) {
$name = urlencode($name);
$urlCode = 'http://api.map.baidu.com/geocoder/v2/?address='.$name.'&city='.$this->city.'&output=json&ak='.$this->key.'&callback=?';
$urlInfo = 'http://api.map.baidu.com/?qt=s&c=289&wd='.$name.'&rn=10&ie=utf-8&oue=1&res=api';
//获取经纬度
$ch = curl_init();
curl_setopt($ch, CURLOPT_URL, $urlCode);
curl_setopt($ch, CURLOPT_HEADER, 0);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
$jsonCode = curl_exec($ch);
curl_close($ch);
unset($ch);
//获取相关信息
$ch = curl_init();
curl_setopt($ch, CURLOPT_URL, $urlInfo);
curl_setopt($ch, CURLOPT_HEADER, 0);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
$jsonInfo = curl_exec($ch);
curl_close($ch);
unset($ch);
if ($jsonCode && $jsonInfo) {
$codeArr = json_decode($jsonCode);
$infoArr = json_decode($jsonInfo);
/*取百度名称值 或者 取excel表值*/
//$trade[$i]['name'] = isset($infoArr->content[0]->name) ? $infoArr->content[0]->name : '';
$trade[$i]['name'] = urldecode($name);
$trade[$i]['address'] = isset($infoArr->content[0]->addr) ? $infoArr->content[0]->addr : '';
$trade[$i]['phone'] = isset($infoArr->content[0]->tel) ? $infoArr->content[0]->tel : '';
if(empty($trade[$i]['name'])) {
$noInsert[] = urldecode($name);
continue;
}
if (count($codeArr->result)) {
$trade[$i]['lng'] = isset($codeArr->result) ? $codeArr->result->location->lng : '';
$trade[$i]['lat'] = isset($codeArr->result) ? $codeArr->result->location->lat : '';
} else {
$trade[$i]['lng'] = '';
$trade[$i]['lat'] = '';
}
if(empty($trade[$i]['name']))
$errInfo['name'][] = urldecode($name);
if(!count($codeArr->result))
$errInfo['code'][] = urldecode($name);
$i++;
}
}
$this->trade = $trade;
$this->errInfo = $errInfo;
return $this->trade;
}
/*
* 写进库表
*/
public function insert() {
if (count($this->trade)) {
$pdo = new PDO('mysql:host=localhost;dbname=bud2013-test','root','123456',array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
//$pdo = new PDO('mysql:host=192.168.33.2;dbname=bud2009','root','mysql50',array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
//$sql = "INSERT INTO bud_campaign (title, area, address, contact, position, link, is_focus,created_at, updated_at) VALUES (?,?,?,?,?,?,?,?,?)";
$sql = "INSERT INTO bud_shop (name, area, address, contact, position, created_at, updated_at) VALUES (:name, :area, :address, :contact, :position, :created_at, :updated_at)";
$preSql = $pdo->prepare($sql);
$dateArr = array();
foreach ( $this->trade as $k){
$dateArr[':name'] = $k['name'];
$dateArr[':area'] = 'east';
$dateArr[':address'] = $k['address'];
$dateArr[':contact'] = $k['phone'];
$dateArr[':position'] = $k['lng'].','.$k['lat'];
$dateArr[':created_at'] = time();
$dateArr[':updated_at'] = time();
$preSql->execute($dateArr);
}
}
}
/*
* 更新excel文件
*/
public function save(){
if (count($this->trade)) {
$objReader = PHPExcel_IOFactory::createReader($this->type);
$objExcel = $objReader->load($this->file);
$objExcel->setActiveSheetIndex(0);
$objActSheet = $objExcel->getActiveSheet();
$row = $this->startNum;
foreach ($this->trade as $arr) {
$objActSheet->setCellValue('E'.$row,$arr['phone']);
$objActSheet->setCellValue('F'.$row,$arr['address']);
$objActSheet->setCellValue('H'.$row,$arr['lng'].','.$arr['lat']);
$row++;
}
$objWriter = PHPExcel_IOFactory::createWriter($objExcel,$this->type);
$objWriter->save('test.xlsx');
}
}
/*
* 生成新的excel文件
*/
public function create(){
if (count($this->trade)) {
$objExcel = new PHPExcel();
$objWriter = new PHPExcel_Writer_Excel2007($objExcel);
$objExcel->setActiveSheetIndex(0);
$objActSheet = $objExcel->getActiveSheet();
$objActSheet->setCellValue('A1','jian');
$objWriter->save('demo.xlsx');
}
}
}
$file = 'test.xlsx';
$type = 'Excel2007';
$city = '上海';
$start = 2;
$letter = 'b';
$shop = new ShopInfo($file, $type, $city, $start, $letter);
$shop->process();
//$shop->insert();
$shop->save();
最终效果图：(插入到库表)
效果图：(更新原Excel文件)
当然你会发现库表中有些记录的字段值中没有内容，但也不能怪程序问题，因为是以《售点名称》在百度API上查出的，有可能没有电话或者详细地址，这个时候只能自行修正！
发表评论
要发表评论，您必须先登录。