php导入excel数据

今天药忘吃喽~ 2022-08-18 12:10 260阅读 0赞

注意：本人使用此方法，经测试5万条数据（15个字段）可以成功。

需要修改nginx.conf中client\_max\_body\_size参数，修改为100M  
修改php.ini中post\_max\_size和upload\_max\_filesize参数，修改为100M  
 需要在www目录下创建tmp目录，并且修改成777权限（用于存放上传文件）

//这2行代码很重要，可以延长执行时间和使用内存大小

set\_time\_limit(0);  
ini\_set("memory\_limit", "1024M");

private function excelToTable($file,$tableid,$month_number,$table_head=1){
    		if(!empty($file['name'])){
    			
    			$file_types = explode ( ".", $file['name'] );
    			$excel_type = array('xls','csv','xlsx');
    			//判断是不是excel文件
    			if (!in_array(strtolower(end($file_types)),$excel_type)){
    				$this->show_msg("不是Excel文件，重新上传","/search/patentStatistics/uploadRawdata");
    			}
    
    			//设置上传路径
    			$savePath = _WWW_ . 'www/tmp/';
    
    			//以时间来命名上传的文件
    			$str = date ( 'Ymdhis' );
    			$file_name = $str.".".end($file_types);
    
    			//是否上传成功
    			$tmp_file = $file['tmp_name'];
    			if (!copy($tmp_file,$savePath.$file_name)){
    				$this->show_msg("上传失败","/search/patentStatistics/uploadRawdata");
    			}
    			
    			if($tableid=="1"){
    				$rawdata_obj = $this->rawdata_pctmodel;	
    			}elseif($tableid=="2"){
    				$rawdata_obj = $this->rawdata_applymodel;	
    			}elseif($tableid=="3"){
    				$rawdata_obj = $this->rawdata_authmodel;	
    			}elseif($tableid=="4"){
    				$rawdata_obj = $this->rawdata_validmodel;
    			}else{
    				$this->show_msg("您要导入的数据表不存在！","/search/patentStatistics/uploadRawdata");
    			}
    			
    			if($rawdata_obj)
    				$fields = $rawdata_obj->returnFields();
    			else
    				$this->show_msg("未能指定明确的表！","/search/patentStatistics/uploadRawdata");
    			
    			//定义导入失败记录的文档
    		    $logfile = $savePath.$str.'.txt';
    			
    			//读取excel，存成数组，该数组的key是从1开始
    			$res = $this->excelToArray($savePath.$file_name,end($file_types));
    			//echo 12321321;exit;
    			//如果有表头，则过滤掉第一行
    			if($table_head)
    				unset($res[1]);
    			
    			//循环写入，不一次性写入，防止有错误的记录；错误记录会记录下第一个字段到txt文档中去
    			foreach($res as $k =>$v){
    				foreach($fields as $key=>$val){
    					if($v[$key]===null){
    						$v[$key] = 'null';
    					}
    					$data[$val] = $v[$key];
    				}
    				//该字段比较特殊，必须导入表中都有该字段
    				$data['month_number'] = $month_number;
    				$result = $rawdata_obj->addSave($data);
    				unset($data);
    				if(!$result){
    					$this ->logFile($logfile,$v[0]);
    				}
    			}
    			if(file_get_contents($logfile))
    				return $logfile;
    			else
    				return true;
    		}
    	}

private function excelToArray($filename,$filetype,$encode='utf-8'){
    		if(strtolower($filetype)=='xls'){
    			$objReader = PHPExcel_IOFactory::createReader('Excel5');
    		}elseif(strtolower($filetype)=='xlsx'){
    			$objReader = PHPExcel_IOFactory::createReader('Excel2007');
    		}elseif(strtolower($filetype)=='csv'){
    			$objReader = PHPExcel_IOFactory::createReader('CSV');
    		}
    			
            $objReader->setReadDataOnly(true);
            $objPHPExcel = $objReader->load($filename);
            $objWorksheet = $objPHPExcel->getActiveSheet();
    		$highestRow = $objWorksheet->getHighestRow();
    		$highestColumn = $objWorksheet->getHighestColumn();
    		$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
    		$excelData = array();
    		for ($row = 1; $row <= $highestRow; $row++) {
    			for ($col = 0; $col < $highestColumnIndex; $col++) {
                    $excelData[$row][] =(string)$objWorksheet->getCellByColumnAndRow($col, $row)->getValue();
    			}
    		}
            return $excelData;
        }