Execl 导入数据

 

注：PHP环境需要有zip扩展 

　　composer需要引入PHPEXCEL

public function getExeclContent($param){
        $filePath = $param['file_url'];
        if(!file_exists($filePath)){
            throw new BusinessException(ErrorCode::FILE_DOES_NOT_EXIST);
        }
        $extension = strtolower(pathinfo($filePath, PATHINFO_EXTENSION));//判断导入表格后缀格式
        //根据不同的格式采用不同的读取方式
        if ($extension == 'xlsx') {
            $objReader =\PHPExcel_IOFactory::createReader('Excel2007');
        }else if ($extension == 'xls'){
            $objReader =\PHPExcel_IOFactory::createReader('Excel5');
        }else{
            throw new BusinessException(ErrorCode::THIS_FORMAT_TEXT_IMPORT_IS_NOT_SUPPORTED_AT_THE_MOMENT);
        }
        $objPHPExcel =$objReader->load($filePath, $encode = 'utf-8');
        $sheet =$objPHPExcel->getSheet(0);//激活第一个sheet表
        $highestRow = $sheet->getHighestRow();//取得总行数
        $highestColumn =$sheet->getHighestColumn(); //取得总列数
        //验证表头数据是否对齐
        $A1 = $objPHPExcel->getActiveSheet()->getCell("A1")->getValue();
        $B1 = $objPHPExcel->getActiveSheet()->getCell("B1")->getValue();
        $C1 = $objPHPExcel->getActiveSheet()->getCell("C1")->getValue();
        $D1 = $objPHPExcel->getActiveSheet()->getCell("D1")->getValue();
        $E1 = $objPHPExcel->getActiveSheet()->getCell("E1")->getValue();
        if($A1!='能力目标名称'||$B1!='所属科目'||$C1!='所属一级能力类型'||$D1!='所属二级能力类型'||$E1!='备注'){
            throw new BusinessException(ErrorCode::EXCEL_TEXT_DATA_DOES_NOT_CORRESPOND);
        }
        for($i=2;$i<=$highestRow;$i++)
        {
            $row['name'] =$objPHPExcel->getActiveSheet()->getCell("A" .$i)->getValue();
            $row['subject_id'] =$objPHPExcel->getActiveSheet()->getCell("B" .$i)->getValue();
            $row['one_ability_type_id'] = $objPHPExcel->getActiveSheet()->getCell("C". $i)->getValue();
            $row['two_ability_type_id'] = $objPHPExcel->getActiveSheet()->getCell("D". $i)->getValue();
            $row['remark'] = $objPHPExcel->getActiveSheet()->getCell("E". $i)->getValue();
            $data[] = $row;
        }
        /**可忽略，为了展示效果*/
        header("Content-type: text/html; charset=utf-8");
        print_r("行数:{$highestRow}<br>");
        print_r("列数:{$highestColumn}<br>");
        print_r("表头：{$A1}---{$B1}---{$C1}<br>");
        print_r($data);
        return $data;
    }