TP5.1execl导入数据

原创

壹小小俊 2022-10-12 14:40:19 博主文章分类：闲聊 ©著作权

文章标签 TP5.1execl导入数据 php json 读取数据 文章分类 虚拟化云计算

©著作权归作者所有：来自51CTO博客作者壹小小俊的原创作品，请联系作者获取转载授权，否则将追究法律责任

//控制器代码
public function import_qrcode(Request $request){
        $getData = $request->param();
        if(!isset($getData['session_key']) || !Cache::get($getData['session_key'])['id']){
            $json = json(['status'=>self::NO,'msg'=>$this->error[0],'data'=>[]]);
        }else{
            $config = [
                'ext' => 'xlsx'
            ];
            $file = $this->request->file('file');
            $info = $file->validate($config)->move('./uploads');
            if ($info) {
                $fileName = $info->getSaveName();
                $filePath = Env::get('root_path').'public'.DIRECTORY_SEPARATOR.'uploads'.DIRECTORY_SEPARATOR.$fileName;
                $data = import_excel($filePath);
                array_shift($data);
                $arr = [];
                $list = array();
                foreach ($data as $k => $v) {
                    $arr['sn'] = $v[0];
                    $arr['key'] = $v[1];
                    $arr['value'] = $v[2];
                    $arr['s_id'] = $getData['s_id'];
                    $arr['a_id'] = $getData['a_id'];
                    $arr['stoptime'] = $getData['stoptime'];
                    $arr['ctime'] = time();
                    $i = $arr['sn'] ^ $arr['key'];
                    if($i == $arr['value']){
                        $f = true;
                        foreach ($list as $vs) {
                            if ($vs['value'] == $arr['value']) {
                                $f = false;
                                break;
                            }
                        }
                        if ($f == true) {
                            $list[] = $arr;
                        }
                    }
                }
                if (file_exists($filePath)) {
                    unset($info);
                    unlink($filePath);  //删除文件
                }
                if (empty($list)) {
                    $json =  json(['status' => self::NO, 'msg' => $this->error[13]]);
                }else{
                    $bool = $this->model->add_all($list);
                    if($bool){
                        $json = json(['status'=>self::YES,'msg'=>$this->error[11]]);
                    }else{
                        $json = json(['status'=>self::NO,'msg'=>$this->error[12]]);
                    }
                }
            }
        }
        return $json;
    }

/**
 * 导入excel文件
 * @param  string $file excel文件路径
 * @return array        excel文件内容数组
 */
function import_excel($file){
    ini_set('max_execution_time', '0');
    // Vendor('PHPExcel.PHPExcel');
    require'../extend/PHPExcel/PHPExcel.php';
    try{
        $objReader = new \PHPExcel_Reader_Excel2007();//注意和导出的类不一样哦
    }catch (\Exception $e){
        $this->error($e->getMessage());
    }
    $objPHPExcel = $objReader->load($file); 
    $sheet = $objPHPExcel->getSheet(0); 
    // 取得总行数 
    $highestRow = $sheet->getHighestRow();     
    // 取得总列数      
    $highestColumn = $sheet->getHighestColumn(); 
    //循环读取excel文件,读取一条,插入一条
    $data=array();
    //从第一行开始读取数据
    for($j=0;$j<=$highestRow;$j++){
        //从A列读取数据
        for($k='A';$k<=$highestColumn;$k++){
            // 读取单元格
            $data[$j][]=$objPHPExcel->getActiveSheet()->getCell("$k$j")->getValue();
        } 
    }  
    return $data;
}