一、首先说一下自己以前用的方式:以前是采用PHPoffice类+import.class.php文件导入。代码如下:
1.html代码:
<a href="#" style="margin-right: 10px" id="leading_in" data-toggle="modal" data-target="#myModal_leading_in">
<span class="glyphicon glyphicon-log-in" style="margin-right: 5px;color: #00BD8B"></span>导入 </a>
<!-- 导入 模态框 -->
<div class="modal fade" id="myModal_leading_in" tabindex="-1" role="dialog" aria-labelledby="myModalLabel-add" aria-hidden="true">
<div class="modal-dialog">
<div class="modal-content">
<div class="modal-header">
<button type="button" class="close" data-dismiss="modal" aria-hidden="true">×</button>
<h4 class="modal-title" id="myModalLabel-add">导入</h4>
</div>
<form id="form_upload" enctype="multipart/form-data" action="__URL__/upload_iplib" method="post">
<div class="modal-body" style="margin-bottom:20px;">
<div class="form-group" >
<div class="col-sm-9" style="margin-bottom:10px;">
<a href="__ROOT__/DownLoad/iplib.xlsx" style="color:#22a7f0;" >下载模板</a>
</div>
</div><br/>
<div class="form-group">
<div class="col-sm-9">
<div class="widget-main">
<input type="file" id="id-input-file-2" name="file"/>
</div>
</div>
</div>
<div class="space-4"></div>
</div>
<div class="modal-footer">
<input type="submit" class="btn btn-info" value="确定">
<button type="button" class="btn btn-default" data-dismiss="modal">关闭</button></div>
</form>
</div><!-- /.modal-content -->
</div><!-- /.modal -->
</div>
2.PHP代码:
//IP地址库导入
public function upload_iplib(){
$upload = new \Think\Upload();// 实例化上传类
$upload->maxSize = 3145728 ;// 设置附件上传大小
$upload->exts = array('xls', 'xlsx');// 设置附件上传类型
$upload->rootPath = './Uploads/'; // 设置附件上传根目录
$upload->savePath = ''; // 设置附件上传(子)目录
$upload->saveName = 'time';
$upload->autoSub = false;
// 上传文件
$info = $upload->upload();
if(!$info) {// 上传错误提示错误信息
$this->error($upload->getError());
}
$result = Import::excel($info['file']['savename']);
$config = Policy::iplib_col();
$error_msg = "";
$status = 'success';
if(!empty($result)){
foreach ($result as $key => $value) {
$data = array();
foreach ($value as $k => $v) {
if(is_null($v)){
continue;
}
if (isset($config[$k])) {
if ($v!='') {
$data[$config[$k]] = Policy::col2value($k, $v);
}
}
}
if($result['status'] === false) {
$status = 'failed';
$error_msg .= $res['message']."(excel:".($key + 1).") ";
}else{
M('ip_address_library')->add($data);
}
}
$this->ajaxReturn(['status' => $status]);
}else{
$status='error';
$error_msg ='没有导入任何信息!';
$this->ajaxReturn(['status' => $status,'message' => $error_msg]);
}
}
3.import.class.php
<?php
/**
* Created by zhou.
* User: zhou
* Date: 2015/9/17
* Time: 15:16
*/
namespace Home\Common;
use PHPExcel;
use PHPExcel_IOFactory;
Vendor('Classes.PHPExcel');
class Import
{
public static function excel($filePath = "")
{
$filePath = "./Uploads/".$filePath; // 要读取的文件的路径
$PHPExcel = new PHPExcel(); // 拿到实例,待会儿用
$PHPReader = new \PHPExcel_Reader_Excel2007(); // Reader很关键,用来读excel文件
if (!$PHPReader->canRead($filePath)) { // 这里是用Reader尝试去读文件,07不行用05,05不行就报错。注意,这里的return是Yii框架的方式。
$PHPReader = new \PHPExcel_Reader_Excel5();
if (!$PHPReader->canRead($filePath)) {
$errorMessage = "Can not read file.";
return $this->render('error', ['errorMessage' => $errorMessage]);
}
}
$PHPExcel = $PHPReader->load($filePath); // Reader读出来后,加载给Excel实例
$currentSheet = $PHPExcel->getSheet(0); // 拿到第一个sheet(工作簿?)
$result = [];
foreach ($currentSheet->getRowIterator() as $row) { // 行迭代器
$cellIterator = $row->getCellIterator(); // 拿到行中的cell迭代器
$cellIterator->setIterateOnlyExistingCells(false); // 设置cell迭代器,遍历所有cell,哪怕cell没有值
$lineVal = [];
foreach ($cellIterator as $cell) {
if ($cell->getDataType() == \PHPExcel_Cell_DataType::TYPE_NUMERIC) { // 这里是比较cell中数据类型是不是number
$cellStyleFormat = $cell->getStyle( $cell->getCoordinate() )->getNumberFormat(); // 接下来这两句是拿到这个number的格式
$formatCode = $cellStyleFormat->getFormatCode(); // 如果是普通的数字,formatCode将为General, 如果是如6/12/91 12:00的时间格式,formatCode将为/d/yy h:mm(反正就是时间格式了)
if ($formatCode == 'yyyy/m/d\ h:mm;@') {
$value = \PHPExcel_Shared_Date::ExcelToPHP($cell->getValue()) - 28800;
} else {
$value = $cell->getValue();
}
} else {
$value = $cell->getValue();
}
array_push($lineVal, RemoveXSS($value));
}
array_push($result, $lineVal);
}
$config = $result[0];
$result_info = [];
foreach ($result as $key => $value) {
if ($key == 0) {
unset($result[$key]);
continue;
}
foreach ($value as $k => $v) {
$result_info[$key][$config[$k]] = $v;
}
}
return $result_info;
}
}
二、由于工作中碰到 要一下子导入5万条数据,页面会超时,导致NGINX挂死。所以修改方案,按照导入CSV格式的文件,先切割为1万行一个的小文件,再导入。代码如下:
1.html代码:
<a href="#" style="margin-left: 15px;cursor: pointer;" id="leading_in" data-toggle="modal" data-target="#myModal_leading_in">
<span class="glyphicon glyphicon-log-in" style="margin-right: 5px;color: #00BD8B"></span>导入 </a>
<!-- 导入 模态框 -->
<div class="modal fade" id="myModal_leading_in" tabindex="-1" role="dialog"
aria-labelledby="myModalLabel-add" aria-hidden="true">
<div class="modal-dialog">
<div class="modal-content">
<div class="modal-header">
<button type="button" class="close" data-dismiss="modal"
aria-hidden="true">×
</button>
<h4 class="modal-title" id="myModalLabel-add">导入</h4>
</div>
<form id="form_upload" enctype="multipart/form-data" action="__URL__/before_upload" method="post">
<div class="modal-body" style="margin-bottom:20px;">
<div class="form-group" >
<div class="col-sm-9" style="margin-bottom:10px;">
<a href="__PUBLIC__/Download/policy_isms.xlsx" style="color:#22a7f0;" >下载模板</a>
</div>
</div><br/>
<div class="form-group">
<div class="col-sm-9">
<div class="widget-main">
<input type="file" id="id-input-file-2" name="file"/>
<input type='hidden' name="count" value="0">
</div>
</div>
</div>
<div class="space-4"></div>
</div>
<div class="modal-footer">
<input type="submit" class="btn btn-info" value="确定">
<button type="button" class="btn btn-default" data-dismiss="modal">关闭</button>
</div>
</form>
</div><!-- /.modal-content -->
</div><!-- /.modal -->
</div>
2.jQuery代码,注意这里是有个递归过程,先走before_upload将文件进行上传和拆分,再传参数给前端,如果传过来的参数表明还有待导入的文件 则再走一遍import_result()函数,如果没有则提示导入成功!
需要要引入一个jquery 插件jquery.form.js
<script type="text/javascript" src="__PUBLIC__/js/jquery.form.js"></script>
var import_condition = {};
$(document).ready(function(){
$("#form_upload").ajaxForm({
success:function(data){
if(data.status === 1) {
confirm('没有数据', {
btn: ['确定'] //按钮
}, function () {
closeAll();
});
} else if (data.status === '3') {
import_condition.count = data.count;
import_result();
}else{
alert(data.info);
}
}
});
})
function import_result(){
$.post("__URL__/info_upload",import_condition , function(data){
if(data.status === 'success') {
alert('导入成功!');
} else if (data.status === '3') {
import_condition.count = data.count;
import_result();
}else{
alert(data.message)
}
});
}
3.PHP代码(上传并切割文件的方法):
//上传并分割csv文件
public function before_upload(){
$upload = new \Think\Upload();// 实例化上传类
$upload->maxSize = 0 ;// 设置附件上传大小
$upload->exts = array('csv');// 设置附件上传类型
$upload->rootPath = './Uploads/excel/'; // 设置附件上传根目录
$upload->savePath = ''; // 设置附件上传(子)目录
$upload->saveName = 'time';
$upload->autoSub = false;
// 上传文件
$info = $upload->upload();
if(!$info) {// 上传错误提示错误信息
$this->error($upload->getError());
}
$count = I('count');
$rootPath = C('DOWN_PATH');
$filePath = 'excel/';
if($info['file']['savename']){
$cmd = "cd ". $rootPath . $filePath . "\n";
$cmd .= "split -a 1 -l 10000 " . $info['file']['savename']." -d zp_";
//dump($cmd);die;
$result = system($cmd);
if ($result)
{
$status = true;
}
$cmd = "cd ".$rootPath.$filePath."\n";
$cmd .= "rm -rf ".$info['file']['savename'];
$res = system($cmd);
}
//dump($rootPath . $filePath.'zp_'.$count);die;
if(file_exists($rootPath . $filePath.'zp_'.$count)){
$this->ajaxReturn(array('status'=>'3','count'=>$count));
}else{
$this->ajaxReturn(array('status'=>'1'));
}
}
4.导入的方法:
public function info_upload(){
$count = I('count');
$rootPath = C('DOWN_PATH');
$cmd = "cd ".$rootPath."excel/"."\n";
$cmd.= 'mv zp_'.$count." zp_".$count.".csv"."\n";
$cmd .= "chmod -R 777 zp_".$count.".csv";
system($cmd);
$filename = "zp_".$count.".csv";
$file = fopen($rootPath."excel/".$filename,'r');
while(!feof($file)){
$dat[] = fgetcsv($file);
}
$dat = eval('return ' . iconv('EUC-CN', 'utf-8', var_export($dat, true)) . ';');
foreach ($dat as $key => $value) {
if (!$value) {
unset($dat[$key]);
}
}
fclose($file);
//dump($data);die;
$config = Policy::col();
$error_msg = "";
$status = 'success';
$top_data = array(
'0' => "指令ID",
'1' => "指令类型",
'2' => "机房名称",
'3' => "生效时间",
'4' => "过期时间",
'5' => "操作类型",
'6' => "域名",
'7' => "URL",
'8' => "关键字",
'9' => "源IP地址",
'10' => "目的IP地址",
'11' => "源端口",
'12' => "目的端口",
'13' => "传输层协议",
);
if($count == 0){
//文件被切割后只有第一个存在标题,需要去掉
unset($dat[0]);
}
//转化成需要的格式
foreach ($dat as $key => $value) {
foreach ($value as $k => $val) {
$result[$key-1][$top_data[$k]] = $val;
}
}
if(!empty($result)){
foreach ($result as $key => $value) {
$data = array();
foreach ($value as $k => $v) {
if(is_null($v)){
continue;
}
if (isset($config[$k])) {
if ($v!='') {
//转换成 字段名 =>value的形式
$data[$config[$k]] = Policy::col2value($k, $v);
//dump([$config[$k]]);die;
}
}
}
$data['effect_time'] = strtotime($data['effect_time']);
$data['expired_time'] = strtotime($data['expired_time']);
if($result['status'] === false) {
$status = 'failed';
$error_msg .= $res['message']."(excel:".($key + 1).") ";
}else{
//dump($data);die;
$rule = $data;
//去掉不是规则的data数组中的元素
unset($rule['type']);
unset($rule['house_id']);
unset($rule['effect_time']);
unset($rule['expired_time']);
unset($rule['operationtype']);
unset($rule['commandid']);
foreach ($rule as $key => $value) {
if($value !=''){
if($key == '关键字'){
$data['rule'][] = array('subtype'=>$key,'valueStart'=>str_replace(',', ',', $value),'keywordRange'=>'0,1,2');
}else{
//将规则统一归到$data['rule']里面
$data['rule'][] = array('subtype'=>$key,'valueStart'=>$value);
}
}
}
$data['level'] = $this->judge_level($data['rule'],$data['type']);
//去掉规则汉字的元素 因为规则已被归到$data['rule']里面
unset($data['域名']);
unset($data['URL']);
unset($data['关键字']);
unset($data['源IP地址']);
unset($data['目的IP地址']);
unset($data['源端口']);
unset($data['目的端口']);
unset($data['传输层协议']);
$data['idc_id'] = M('basic_idc')->select()[0]['idc_id'];
$data['owner'] = $_SESSION['username'];
$this->info_build_xml($data);
//停顿一毫秒,太快反应不过来会丢文件
usleep(100);
}
}
//循环完了该文件里的内容就删掉,防止与下次导入发生重合
$cmd = "cd ".$rootPath."excel/"."\n";
$cmd .= "rm zp_".$count.".csv";
system($cmd);
if(file_exists($rootPath."excel/".'zp_'.($count+1))){
$this->ajaxReturn(['status' => '3','count'=>$count+1]);
}else{
$this->ajaxReturn(['status' => $status]);
}
}else{
$status='error';
$error_msg ='没有导入任何信息!';
$this->ajaxReturn(['status' => $status,'message' => $error_msg]);
}
}