project-manager/api/app/common/util/PHPExcel.php

<?php

namespace app\common\util;

use app\api\controller\BaseAuthorized;
use PHPExcel_IOFactory;
use think\facade\Db;

class PHPExcel{
    public static function import($path,$login_staff)
    {
        $LeaveModel = new \app\common\model\Leave();
        $result = [
            'code'=>999,
            'msg'=>'错误'
        ];
        //实例化PHPExcel类
        $path = public_path().'storage/'.$path;
        $PHPExcel = new \PHPExcel();
        //默认用excel2007读取excel，若格式不对，则用之前的版本进行读取
        $PHPReader = new \PHPExcel_Reader_Excel2007();
        if (!$PHPReader->canRead($path)) {
            $PHPReader = new \PHPExcel_Reader_Excel5();
            if (!$PHPReader->canRead($path)) {
                $result['msg'] = '请上传excel文件';
                return $result;
            }
        }

        //读取Excel文件
        $PHPExcel = $PHPReader->load($path);

        //读取excel文件中的第一个工作表
        $sheet = $PHPExcel->getSheet(0);

        //取得最大的列号
        $allColumn = $sheet->getHighestColumn();

        //取得最大的行号
        $allRow = $sheet->getHighestRow();

        #清除表
        //从第二行开始插入,第一行是列名
        $month = [];
        $month2 = [];
        $i = 0;
        //检测数据
        for ($currentRow = 4; $currentRow <= $allRow; $currentRow++){
            $number = $PHPExcel->getActiveSheet()->getCell("A" . $currentRow)->getValue();
            if(!$number){
                break;
            }
            $array = [
                'month'=>$PHPExcel->getActiveSheet()->getCell("B" . $currentRow)->getValue(),
                'phone'=>$PHPExcel->getActiveSheet()->getCell("C" . $currentRow)->getValue(),
//                'name'=>$PHPExcel->getActiveSheet()->getCell("D" . $currentRow)->getValue(),
                'days'=>$PHPExcel->getActiveSheet()->getCell("E" . $currentRow)->getValue(),
                'real_days'=>$PHPExcel->getActiveSheet()->getCell("F" . $currentRow)->getValue(),
                'late'=>$PHPExcel->getActiveSheet()->getCell("G" . $currentRow)->getValue(),
                'leave'=>$PHPExcel->getActiveSheet()->getCell("H" . $currentRow)->getValue(),
                'absenteeism'=>$PHPExcel->getActiveSheet()->getCell("I" . $currentRow)->getValue(),
                'late_deduct'=>$PHPExcel->getActiveSheet()->getCell("J" . $currentRow)->getValue(),
                'leave_deduct'=>$PHPExcel->getActiveSheet()->getCell("K" . $currentRow)->getValue(),
                'absenteeism_deduct'=>$PHPExcel->getActiveSheet()->getCell("L" . $currentRow)->getValue(),
                'other_deduct'=>$PHPExcel->getActiveSheet()->getCell("M" . $currentRow)->getValue(),
                'sum_deduct'=>$PHPExcel->getActiveSheet()->getCell("N" . $currentRow)->getValue(),
                'other_reward'=>$PHPExcel->getActiveSheet()->getCell("O" . $currentRow)->getValue(),
                'remark'=>$PHPExcel->getActiveSheet()->getCell("P" . $currentRow)->getValue(),
                'create_time'=>date('Y-m-d'),
                'valid'=>1,
                'enterprise_id'=>$login_staff->enterprise_id,
            ];

            $month[$i][$array['month']] = $array['phone'];
            $month2[$i][$array['phone']] = $array['month'];
            $i++;

            $user = \app\common\model\User::where('phone',$array['phone'])->find();
            if (!$user){
                $msg = '导入数据错误,请检查报表格式或者手机号数据·1';
                $result['msg'] = $msg;
                return $result;
            }
            $staff = \app\common\model\Staff::where(['user_id'=>$user->id,'enterprise_id'=>$login_staff->enterprise_id])->find();
            if (!$staff){
                $msg = '导入数据错误,请检查报表格式或者手机号数据·2';
                $result['msg'] = $msg;
                return $result;
            }

            if ($array['days']>31){
                $msg = $staff->name.'应出勤天数不得超过31天';
                $result['msg'] = $msg;
                return $result;
            }

            if ($array['late_deduct']+$array['leave_deduct']+$array['absenteeism_deduct']+$array['other_deduct']!=$array['sum_deduct']){
                $msg = $staff->name.'扣款合计不正确';
                $result['msg'] = $msg;
                return $result;
            }
        }

        if (count(self::get_repeat_data($month))){
            $msg = '导入数据重复';
            $result['msg'] = $msg;
            return $result;
        }

        for ($currentRow = 4; $currentRow <= $allRow; $currentRow++) {
            //获取B列的值
            $number = $PHPExcel->getActiveSheet()->getCell("A" . $currentRow)->getValue();
            if(!$number){
                $msg = '导入成功';
                $result['code'] = 0;
                $result['msg'] = $msg;
                break;
            }

            $data = [
                'month'=>$PHPExcel->getActiveSheet()->getCell("B" . $currentRow)->getValue(),
                'phone'=>$PHPExcel->getActiveSheet()->getCell("C" . $currentRow)->getValue(),
//                'name'=>$PHPExcel->getActiveSheet()->getCell("D" . $currentRow)->getValue(),
                'days'=>$PHPExcel->getActiveSheet()->getCell("E" . $currentRow)->getValue(),
                'real_days'=>$PHPExcel->getActiveSheet()->getCell("F" . $currentRow)->getValue(),
                'late'=>$PHPExcel->getActiveSheet()->getCell("G" . $currentRow)->getValue(),
                'leave'=>$PHPExcel->getActiveSheet()->getCell("H" . $currentRow)->getValue(),
                'absenteeism'=>$PHPExcel->getActiveSheet()->getCell("I" . $currentRow)->getValue(),
                'late_deduct'=>$PHPExcel->getActiveSheet()->getCell("J" . $currentRow)->getValue(),
                'leave_deduct'=>$PHPExcel->getActiveSheet()->getCell("K" . $currentRow)->getValue(),
                'absenteeism_deduct'=>$PHPExcel->getActiveSheet()->getCell("L" . $currentRow)->getValue(),
                'other_deduct'=>$PHPExcel->getActiveSheet()->getCell("M" . $currentRow)->getValue(),
                'sum_deduct'=>$PHPExcel->getActiveSheet()->getCell("N" . $currentRow)->getValue(),
                'other_reward'=>$PHPExcel->getActiveSheet()->getCell("O" . $currentRow)->getValue(),
                'remark'=>$PHPExcel->getActiveSheet()->getCell("P" . $currentRow)->getValue(),
                'create_time'=>date('Y-m-d'),
                'valid'=>1,
                'enterprise_id'=>$login_staff->enterprise_id,
//              'time'=>self::get_date_by_excel($PHPExcel->getActiveSheet()->getCell("F" . $currentRow)->getValue()),
            ];

            $user = \app\common\model\User::where('phone',$data['phone'])->find();
            $staff = \app\common\model\Staff::where(['user_id'=>$user->id,'enterprise_id'=>$login_staff->enterprise_id])->find();
            $data['staff_id'] = $staff->id;
            $data['name'] = $staff->name;
            $where['enterprise_id']=$login_staff->enterprise_id;
            $where['month']=$data['month'];
            $where['staff_id']=$data['staff_id'];
            $LeaveModel->where($where)->delete();
            #插入表
            $LeaveModel = new \app\common\model\Leave();
            $LeaveModel->save($data);
        }
        return $result;
    }

    //获取二维数组的重复数据
    public static function get_repeat_data($array){
        //这里的$array在数据库查询的时候要group by排序一下
        //计算出数组的总数量
        $count = count($array);
        $repeat_data = [];
        //循环从0开始逐次+1
        for($i=0;$i<$count;$i++){
            //每次都比第一层循环+1，如果第一层循环0数据，第二层就循环1数据，以此类推
            for($j=$i+1;$j<$count;$j++){
                //比较两次数据是否相等
                if($array[$i] == $array[$j]){
                    $repeat_data[$i]=$array[$i];
                    $repeat_data[$j]=$array[$j];
                }
            }
        }
        return $repeat_data;
    }
}