此实例只是针对PHP语言所用,其它语言也是一样思路。
此实例所用到类:PHPExcel类;GitHub下载地址:https://github.com/PHPOffice/PHPExcel
上传Excel在这里不做解释。
这里针对此功能,进行上传文件并判断手机号是否重复,如果重复即提示,并导出重复号码。
PHP后台实例代码如下:case 'setnumberdate':
$file = $_REQUEST['file'];
$filename= $_SERVER['DOCUMENT_ROOT'].$file;
require_once dirname(__FILE__) . '/lib/Excel/PHPExcel/IOFactory.php';//引入上述下载类
$objReader = PHPExcel_IOFactory::load($filename);//获取文件读取操作对象
$sheets = $objReader->getAllSheets();
$sheet = $sheets[0];
$allRow = $sheet->getHighestRow(); // 取得总行数
$allColumn = $sheet->getHighestColumn(); // 取得总列数
$i=0;
for($currentRow=2;$currentRow<=$allRow;$currentRow++){
for($currentColumn='A';$currentColumn<=$allColumn;$currentColumn++){
$address=$currentColumn.$currentRow;//数据坐标
$cell =$sheet->getCell($address)->getValue();
if($currentColumn=='A'){
$number = $cell;
}
if ($currentColumn=='B') {
$province = $cell;
}
if ($currentColumn=='C') {
$city = $cell;
}
$is_number = "select number FROM s_liantong_numberpool where number='$number'";
$res = $obj_db_main->doquery_row($is_number);
if ($res) {
$phone_arr[] = $number;
break;
}else{
$insert_number = "INSERT into s_liantong_numberpool set number='{$number}'";
$obj_db_main->doquery_row($insert_number);
$i++;
}
}
}
/**
* 创建文件并保存-为保存到本地查看留备
*/
if (!empty($phone_arr)) {
$str = "手机号\t\n";
foreach ($phone_arr as $vo) {
$str .= $vo . "\t\n";
}
$filename = date('Ymd').rand(10000,99999) . '.xls';
$out_put_file = new _myfuncgettime;
$objPHPExcel=new PHPExcel();
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('A1','号码');
$len_phont = count($phone_arr);
for ($i=2; $i <= $len_phont; $i++) {
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue("A".$i, $phone_arr[$i]);
}
$objPHPExcel->setActiveSheetIndex(0);
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="'.$filename.'.xlsx"');
header('Cache-Control: max-age=0');
$objWriter=PHPExcel_IOFactory::createWriter($objPHPExcel,'Excel2007');
$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
$response = $out_put_file->saveExcelToLocalFile($objWriter,$filename);//此方法为:移动文件到指定目录,并返回文件地址
$Jsonstr['url']=$response;
$Jsonstr['rltcode']=1;
$Jsonstr['goodsum']=$len_phont;
echo json_encode($Jsonstr);
exit;
}
$Jsonstr['rltcode']=0;
$Jsonstr['goodsum'] = $i;
echo json_encode($Jsonstr);break;
这里使用AJAX方式传递数据,数据类型为JSON,后台返回也同样需要转换为JSON格式并返回。
上述方法当中 saveExcelToLocalFile 方法如下:function saveExcelToLocalFile($objWriter,$filename){
$filePath = '../uploadfile/uploadliantongphone/'.$filename;
$objWriter->save($filePath);
return $filePath;
}
数据后台返回之后,前端进行重复数据下载,思路:根据程序后台处理结果返回的URL进行访问即可下载:
前台代码如下:$("#upfile").click(function(){
//此为上传按钮,即把Excel文件进行后台数据判断
var f=$(this).attr("data-file");
var serviceid = "setnumberdate";
$.ajax({
type: "post",
url: './class_phone_admin.php',
data: {"file":f,"serviceid":serviceid},
dataType:"json",
async:true,
success: function(d){
if (d.rltcode == 0) {
alert(d.goodsum+"条数据全部导入完毕");
location.reload();
}else if (d.rltcode == 1) {
//*进行重复号码导出*//
alert(d.goodsum+"条数据有问题");
location.href="../"+d.url; //这里接收返回的Excel文件地址,进行下载重复号码文件
}else{
alert("数据导入失败,请核对数据正确性");
}
}
});
});
最后,效果如下:
PHP导入TXT文件实例:
上传文件都是一样的步骤,这里只针对读取文件做实例:
后台代码如下:case 'setaqynumberdate':
$province = $_REQUEST['province'];
$file = $_REQUEST['file'];
if($file){
$file_number = $_SERVER['DOCUMENT_ROOT'].$file;//获取文件绝对路径
$f=fopen(ltrim($file_number,'/'),'r'); //打开文件,并去空格
while(!feof($f)){ //判断是否到达数据结尾
$number=fgets($f); //取出一条数据
if ($number == '') {
continue;
}
$inset_sql = "INSERT into s_aqy_number (number,number_province) VALUES ('$number','$province')";
$obj_db_main->doexec($inset_sql);
}
fclose($f);
$Jsonstr['code'] = "已成功导入号码";
$Jsonstr['flag'] = true;
echo json_encode($Jsonstr);
exit;
}
$Jsonstr['code'] = "服务器错误,请稍后重试";
$Jsonstr['flag'] = flase;
echo json_encode($Jsonstr);
break;