PHP、TP5上传并获取Excel内容(包括动态列的获取)
public function upload(){
$res=$this->request->param();
header("Content-type:text/html;charset=utf-8");
vendor("PHPExcel.Classes.PHPExcel");
vendor("PHPExcel.Classes.PHPExcel.IOFactory");
$name=$_FILES['file']['name'];
$suffixName=explode('.',$name)[1];
$filePath='public/uploads/'.$name;
if($suffixName=="xls"||$suffixName=="xlsx"){
if(is_uploaded_file($_FILES["file"]["tmp_name"])){
$move=move_uploaded_file($_FILES["file"]["tmp_name"],$filePath);
if ($move===true){
$ExcleArr=$this->analysisExcel($filePath,$res['order']);
if($ExcleArr==1){
$req['code']=1;
}else{
$req['code']=0;
}
}else{
$req['code']=0;
}
}else{
$req['code']=0;
}
}else{
$req['code']=0;
}
return json($req);
}
function analysisExcel($filePath,$order){
$objReader='';
$file_type=explode('.',$filePath)[1];
if($file_type=='xls'){
$objReader=\PHPExcel_IOFactory::createReader('Excel5');
}
if($file_type=='xlsx'){
$objReader=new \PHPExcel_Reader_Excel2007();
}
$objPHPExcel=$objReader->load($filePath,$encode='utf-8');
$sheet=$objPHPExcel->getSheet(0);
$highestRow=$sheet->getHighestRow();
$highestColumn=$sheet->getHighestColumn();
$data=[];
$regex=" /\ |\/|\~|\!|\@|\#|\\$|\%|\^|\&|\*|\(|\)|\_|\+|\{|\}|\:|\<|\>|\?|\[|\]|\,|\.|\/|\;|\'|\`|\-|\=|\\\|\|/";
for($i=3;$i<=$highestRow;$i++){
//B列过后动态列获取内容所用
$zimu="B";
$maga=array(array("name"=>"你好"),array("name"=>"李焕英"));
//去除表格特殊符号获取内容
$data[$i]['company']=preg_replace($regex,'',$objPHPExcel->getActiveSheet()->getCell("A".$i)->getValue());
//字母转换大写并去除特殊符号
$data[$i]['companyid']=strtoupper(preg_replace($regex,'',$objPHPExcel->getActiveSheet()->getCell("B".$i)->getValue()));
//获取B列以后动态列的内容
foreach($maga as $k=>$v){
$zimu++;
//去除表格中文并去除特殊符号
$data[$i][$v['id']]=preg_replace('/([\x80-\xff]*)/i','',preg_replace($regex,'',$objPHPExcel->getActiveSheet()->getCell($zimu.$i)->getValue()));
}
}
if(!empty($data)){
$req=1;
}else{
$req=2;
}
return $req;
}