PHP导入Excel考勤表

安装PHPExcel

使用composer安装:

composer require phpoffice/phpexcel

引入IOFactory.php

       require_once(ROOT_PATH . 'vendor/phpoffice/phpexcel/Classes/PHPExcel/IOFactory.php');

考勤表如下

Excel考勤表:

上传,接收,移动 Excel文件

    <div class="layui-form-item" style="width: 99%">

        <form action="{:url('index/Attesgerbdfb/attendancsdfsscel_in')}" enctype="multipart/form-data" method="post">
            <input type="file" name="excel" />
            <span>覆盖&nbsp;</span><input type="checkbox" value="1" name="yes" id="yes" checked>
            <span>&nbsp;&nbsp;不覆盖&nbsp;</span><input type="checkbox" value="2" name="no" id="no">
            <input type="submit" value="上传" />
        </form>
    </div>

$file = request()->file('excel');
ini_set('max_execution_time', '0');
$info = $file->move(ROOT_PATH . 'public' . DS . 'upload'.DS.'excel');
// 成功上传后 获取上传信息
$path =  $info->getSaveName();// 获取文件保存路径
$extension = $info->getExtension();
if( $extension =='xlsx' )
{
$objReader = PHPExcel_IOFactory::createReader('Excel2007');
}else{
$objReader = PHPExcel_IOFactory::createReader('Excel5');
}

加载文件

//加载excel文件
$filename = ROOT_PATH . 'public' . DS . 'upload'.DS.'excel'.DS.$path;

$objReader ->setReadDataOnly(true); //只读取数据,忽略所有空白行

$objPHPExcel = $objReader->load($filename);//获取excel文件

$sheetCount = $objPHPExcel->getSheetCount();// 获取工作表的数目;

获取Excel中想要的数据

// 组装获取单张sheet考勤数据的三个数组;
$one = array('A','B','D','G','I','K','M');
$two = array('P','Q','S','V','X','Z','AB');
$three = array('AE','AF','AH','AK','AM','AO','AQ');
//TODO sheet需要超过两个~
$arr = array();
$brr = array();
$crr = array();
for($i=2;$i<$sheetCount;$i++){
//不需要最后两张表;
$sheet = $objPHPExcel->getSheet($i); //激活当前的表
$highestRow = $sheet->getHighestRow(); // 取得总行数
$highestColumn = $sheet->getHighestColumn(); // 取得总列数

// 此sheet的人名 部门 日期
$arr[$i]['depart'] = $sheet->getCell("B4")->getValue();
$arr[$i]['username'] = $sheet->getCell("J4")->getValue();
$arr[$i]['date'] = $sheet->getCell("B5")->getValue();
$brr[$i]['depart'] = $sheet->getCell("Q4")->getValue();
$brr[$i]['username'] = $sheet->getCell("Y4")->getValue();
$brr[$i]['date'] = $sheet->getCell("Q5")->getValue();
$crr[$i]['depart'] = $sheet->getCell("AF4")->getValue();
$crr[$i]['username'] = $sheet->getCell("AN4")->getValue();
$crr[$i]['date'] = $sheet->getCell("AF5")->getValue();
 // 此sheet的某个员工的考勤记录
 $drr = array();
$err = array();
$frr = array();
for($j=13;$j<=$highestRow;$j++)
{
//当前sheet第一个人的考勤记录
foreach ($one as $k_one=>$v_one){
$d = $sheet->getCell("A".$j)->getValue();
 if(empty($d)){
 break;
   }
$x = $sheet->getCell($v_one.$j)->getValue();
if(is_float($x)){
$drr[$j][$k_one] = gmdate('H:i',\PHPExcel_Shared_Date::ExcelToPHP($x));
  }else{
$drr[$j][$k_one] = $sheet->getCell($v_one.$j)->getValue();
       }
        }
//当前sheet第二个人的考勤记录
foreach ($two as $k_two=>$v_two){
 $e = $sheet->getCell("P".$j)->getValue();
  if(empty($e)){
  break;
  }
 $y = $sheet->getCell($v_two.$j)->getValue();
   if(is_float($y)){
$err[$j][$k_two] = gmdate('H:i',\PHPExcel_Shared_Date::ExcelToPHP($y));
    }else{
    $err[$j][$k_two] = $sheet->getCell($v_two.$j)->getValue();
                 }

           }
 //当前sheet第三个人的考勤记录
   foreach ($three as $k_three=>$v_three){
$f = $sheet->getCell("AE".$j)->getValue();
  if(empty($f)){
       break;
    }
$z = $sheet->getCell($v_three.$j)->getValue();
if(is_float($z)){
$frr[$j][$k_three] = gmdate('H:i',\PHPExcel_Shared_Date::ExcelToPHP($z));
     }else{
$frr[$j][$k_three] = $sheet->getCell($v_three.$j)->getValue();
                }
             }
       }
 $arr[$i]['attend'] = $drr;
 $brr[$i]['attend'] = $err;
 $crr[$i]['attend'] = $frr;

 }
// $arr $brr $crr 整合数组
$zrr = array_merge($arr,$brr,$crr);

然后把得到的数据放入数据库中进行后续操作;