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);


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