thinkphp excel导入数据

excel列表中时间添加

function getCellValue($cell, $date_format = "Y-m-d H:i:s")
    {
        $value = $cell->getValue();
        if($cell->getDataType() == \PHPExcel_Cell_DataType::TYPE_NUMERIC){
            //版本过低的话请加上 getParent 例：$cell->getParent()->getStyle($cell->getCoordinate())->getNumberFormat();
            $cell_style_format = $cell->getStyle($cell->getCoordinate())->getNumberFormat(); //不需要getParent
            $format_code = $cell_style_format->getFormatCode();
            if (preg_match('/^([$[A-Z]*-[0-9A-F]*])*[hmsdy]/i', $format_code)) { //判断是否为日期类型
                $value = gmdate($date_format, \PHPExcel_Shared_Date::ExcelToPHP($value)); //格式化日期
            }else{
                $value = \PHPExcel_Style_NumberFormat::toFormattedString($value, $format_code); //格式化数字
            }
        }
        return $value;
    }

导入处理

  public function importTrainPlan($coach_id,$file){
        $path = ROOT_PATH . '/public/upload/' . $file;
        $phpReader = new \PHPExcel_Reader_Excel2007();
        if (!$phpReader->canRead($path)) {
            $phpReader = new \PHPExcel_Reader_Excel5();
            if (!$phpReader->canRead($path)) {
                return ['code' => -1, 'msg' => '无法读取文件,请检查文件是否为excel'];
            }
        }

        $objPHPExcel = $phpReader->load($path);
        $currentSheet = $objPHPExcel->getSheet(0);         //读取excel文件中的第一个工作表
        $allRow = $currentSheet->getHighestRow();           //取得一共有多少行


        //从第一行开始读取数据
        $startRow = 2;
        $endRow = $startRow + 100;
        //以防出现很多的空行
        if ($endRow >= $allRow) $endRow = $allRow;
        try{
            //周日期
            $start_time = self::getCellValue($objPHPExcel->getActiveSheet()->getCell("B" . 2),'Y-m-d H:i:s');
            $end_time = self::getCellValue($objPHPExcel->getActiveSheet()->getCell("C" . 2), 'Y-m-d 23:59:59');

            //专项训练重点及要求
            $planData['special_train'] = $objPHPExcel->getActiveSheet()->getCell("B" . 5)->getValue();
         
          //语句处理


            for($i=13;$i<=$endRow;$i++) {
                //日期
                $date = self::getCellValue($objPHPExcel->getActiveSheet()->getCell("A" . $i));
                //判断是否为空，为空跳出循环，防止添加空数据，在确保改字段每行都有数据
                if(empty($date)){
                    continue;
                }
                //用户名
                $user_name = $objPHPExcel->getActiveSheet()->getCell("B" . $i)->getValue();
              

                $detail['plan_id']         = $trainPlanId;
                $detail['date']            = strtotime($date);
               
                if(strpos($user_name,'/') !== false){
                    $user_nameRe = explode('/',$user_name);
                    $user_id =[];
                    foreach ($user_nameRe as $key =>$value){
                        $user_id[] = Db::name('member')
                            ->where('user_name',$value)->value('id');
                    }
                    $detail['member_id'] = implode($user_id,',');

                }else{
                    $detail['member_id'] = Db::name('member')
                        ->where('user_name',$user_name)->value('id');
                }
                $detail['is_delete']       = 0;

               //语句处理

            }
            return ['code' => 1, 'msg' => '导入成功'];
        }catch (Exception $exception){
            return ['code' => -1, 'msg' => '导入成功'];
        }


    }