欢迎各位兄弟 发布技术文章
这里的技术是共享的
引入的库文件见下面的 附件
function _readExcelFileSaveZhuqu($form,$form_state,$leibie)
{
$filepath = $form_state['storage']['upload']->filepath;
require("sites/all/libraries/PHPExcel/PHPExcel/IOFactory.php");
// drupal7 中似乎已经自动转码了 ,所以下一行的转码错误是不需要的
// $filepath = iconv('UTF-8','GBK',$filepath);
// Check prerequisites
if (!file_exists($filepath)) {
exit("not found $filepath \n");
}
// $reader = PHPExcel_IOFactory::createReader('Excel5'); //设置以Excel5格式(Excel97-2003工作簿)
// $PHPExcel = $reader->load($filepath); // 载入excel文件
// $sheet = $PHPExcel->getSheet(0); // 读取第一個工作表
//不用上面的三行代码,有下面的四行代码 ,这样 xls(Excel97-2003工作簿) xlxs(2007工作簿) 两种格式都能够处理了
//drupal7 中 $filepath 的路径为 public:// 开头
//当 xlsx 时,为什么要把这里的 public:// 的路径,就成真正的路径,然后PHPExcel程序才能读取
$filepath = str_replace('public://upload_assets','sites/default/files/upload_assets',$filepath);
$reader = PHPExcel_IOFactory::createReaderForFile($filepath); //设置以Excel5格式(Excel97-2003工作簿)
$PHPExcel = $reader->load($filepath); // 载入excel文件
$sheet = $PHPExcel->getActiveSheet(); //读取第一个工作表
$highestRow = $sheet->getHighestRow(); // 取得总行数
$highestColumm = $sheet->getHighestColumn(); // 取得总列数
/** 循环读取每个单元格的数据 */
$dataset = array();
for ($row = 2; $row <= $highestRow; $row++)
{//行数是以第1行开始
if($leibie == 'tel'){
$sql = "select n.nid as nid from node as n inner join content_field_tel as tel on n.nid=tel.nid
where n.type in ('kefuchat','zhuqutelqq') and tel.field_tel_value='%s'";
$nid = db_result(db_query($sql,trim($sheet->getCell('A'.$row)->getValue())));
if(!empty($nid))
{
continue;
}
$dataset[$row]['field_tel'] = $sheet->getCell('A'.$row)->getValue();
$dataset[$row]['field_shoujiguishudi'] = $sheet->getCell('C'.$row)->getValue();
$dataset[$row]['field_guanjianci'] = $sheet->getCell('J'.$row)->getValue();
$dataset[$row]['field_useripaddress'] = $sheet->getCell('E'.$row)->getValue();
$dataset[$row]['field_fangwe_laiyuan'] = $sheet->getCell('I'.$row)->getValue();
$dataset[$row]['field_visit_url'] = $sheet->getCell('H'.$row)->getValue();
//$dataset[$row]['field_user_visit_time'] = strtotime($sheet->getCell('L'.$row)->getValue());
//$dataset[$row]['field_user_visit_time'] = date("Y-m-d H:i:s",PHPExcel_Shared_Date::ExcelToPHP($sheet->getCell('L'.$row)->getValue()));
date_default_timezone_set('Asia/Chongqing');
$time = strtotime($sheet->getCell('L'.$row)->getValue());
date_default_timezone_set('UTC');
//这里的时间应该就是格林林治时区的时间
$dataset[$row]['field_user_visit_time'] = date("Y-m-d H:i:s",$time);
//drupal_set_message($sheet->getCell('L'.$row)->getValue());
//drupal_set_message($dataset[$row]['field_user_visit_time']);
}
else if($leibie == 'qq'){
$sql = "select n.nid as nid from node as n inner join content_field_xueyuan_qq as qq on n.nid=qq.nid
where n.type in ('kefuchat','zhuqutelqq') and qq.field_xueyuan_qq_value='%s'";
$nid = db_result(db_query($sql,trim($sheet->getCell('A'.$row)->getValue())));
if(!empty($nid))
{
continue;
}
$dataset[$row]['field_xueyuan_qq'] = $sheet->getCell('A'.$row)->getValue();
$dataset[$row]['field_guanjianci'] = $sheet->getCell('I'.$row)->getValue();
$dataset[$row]['field_useripaddress'] = $sheet->getCell('E'.$row)->getValue();
$dataset[$row]['field_fangwe_laiyuan'] = $sheet->getCell('H'.$row)->getValue();
$dataset[$row]['field_visit_url'] = $sheet->getCell('G'.$row)->getValue();
//$dataset[$row]['field_user_visit_time'] = strtotime($sheet->getCell('L'.$row)->getValue());
//$dataset[$row]['field_user_visit_time'] = date("Y-m-d H:i:s",PHPExcel_Shared_Date::ExcelToPHP($sheet->getCell('K'.$row)->getValue()));
date_default_timezone_set('Asia/Chongqing');
$time = strtotime($sheet->getCell('K'.$row)->getValue());
//这里的时间应该就是格林林治时区的时间存进数据库
date_default_timezone_set('UTC');
$dataset[$row]['field_user_visit_time'] = date("Y-m-d H:i:s",$time);
}
}
foreach($dataset as $key=>$value)
{
$zhuqutelqqNode = null;
$zhuqutelqqNode = (object)$zhuqutelqqNode;
$zhuqutelqqNode->uid = 1;
$zhuqutelqqNode->type = 'zhuqutelqq';
$zhuqutelqqNode->title = '抓取手机QQ'.date('YmdHis');
$zhuqutelqqNode->field_tel[0]['value'] = $value['field_tel'];
$zhuqutelqqNode->field_shoujiguishudi[0]['value'] = $value['field_shoujiguishudi'];
$zhuqutelqqNode->field_xueyuan_qq[0]['value'] = $value['field_xueyuan_qq'];
$zhuqutelqqNode->field_guanjianci[0]['value'] = $value['field_guanjianci'];
$zhuqutelqqNode->field_useripaddress[0]['value'] = $value['field_useripaddress'];
$zhuqutelqqNode->field_fangwe_laiyuan[0]['value'] = $value['field_fangwe_laiyuan'];
$zhuqutelqqNode->field_visit_url[0]['value'] = $value['field_visit_url'];
$zhuqutelqqNode->field_user_visit_time[0]['value'] = $value['field_user_visit_time'];
node_save($zhuqutelqqNode);
}
}
附件 | 大小 |
---|---|
PHPExcel.rar 好像只用到它 | 831.79 KB |
phpExcelReader.rar 好像用不到它 | 19.86 KB |