<?php
include 'readfunc.php';
header('Content-Type:text/html;charset=utf-8');
/**
* 读取Excel指定行列内容
* @param string $excelfilename 1_标准报表.xls [type]
* @param string $sheetname 选择的工作表
* @param int $startrow 开始行
* @param int $endrow 结束行 [type]
* @param int $columns 列数 [type]
* @return array array[行数][改行数据]
*/
function readExcel($excelfilename,$sheetname,$startrow,$endrow,$columns){
$dir = dirname(__FILE__);
require $dir.'/PHPExcel/PHPExcel/IOFactory.php';
class MyReadFilter implements PHPExcel_Reader_IReadFilter
{
private $_startRow = 0; // 开始行
private $_endRow = 0; // 结束行
private $_columns = 0; // 列跨度
public function __construct($startRow, $endRow, $columns) {
$this->_startRow = $startRow;
$this->_endRow = $endRow;
$this->_columns = $columns;
}
public function readCell($column, $row, $worksheetName = '') {
if ($row >= $this->_startRow && $row <= $this->_endRow) {
if ($column<=$this->_columns) {
return true;
}
}
return false;
}
}
$filename = $dir.'/'.$excelfilename;
$fileType=PHPExcel_IOFactory::identify($filename);//获取文件类型
$objReader = PHPExcel_IOFactory::createReader($fileType);
$objReader->setLoadSheetsOnly($sheetname);//只读目标工作表
$objReader->setReadDataOnly(true);//只读取数据忽略样式
$filterSubset = new MyReadFilter($startrow,$endrow,$columns);
$objReader->setReadFilter($filterSubset);
$objPHPExcel=$objReader->load($filename);
$currentSheet = $objPHPExcel->getActiveSheet();
$data=array();
$highestColumn = $currentSheet->getHighestColumn(); //最后列数所对应的字母,例如第2行就是B
$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
for ($row = $startrow; $row <= $endrow; $row++) {
for ($col = 0; $col < $columns; $col++) {
$data[$row][] = (string) $currentSheet->getCellByColumnAndRow($col, $row)->getValue();
}
}
return $data;
}
// var_dump(readExcel('1_标准报表.xls','考勤记录',6,6,31));
$row_data = readExcel('1_标准报表.xls','考勤记录',8,8,31);
$shouldtimes = ['8:00','12:00','13:30','17:30','18:30','21:30'];
foreach ($row_data as $row => $days_data) {
foreach ($days_data as $day => $value) {
$data[]=dayRecord($value,$shouldtimes);
}
}
//readfunc.php
<?php
// $shouldtimes = ['8:00','12:00','13:30','17:30','18:30','21:30'];
// $str = '07:5512:0513:2817:07';
// $dayarr = dayRecord($str,$shouldtimes);
/**
* 根据一天打卡记录'07:5512:0513:2817:07'输出时间和是否合格
* @param string $dayclockstr
* @param array $shouldtimearr
* @return array 时间+是否合格
*/
function dayRecord($dayclockstr,$shouldtimearr){
array_walk($shouldtimearr, "arrtotime");
$isMatched = preg_match_all('/\d\d:\d\d/', $dayclockstr, $matches);
$matche = $matches[0];
$day=array_fill(0,6,["",false]);
array_walk($matche, "arrtotime");
foreach ($matche as $key => $time) {
switch (true) {
//上午上班
case ($shouldtimearr[0]-40<$time&&$time<$shouldtimearr[0]+40):
$day[0]=[$matches[0][$key],isOkayGo($time,$shouldtimearr[0])];
break;
//上午下班
case ($shouldtimearr[1]-40<$time&&$time<$shouldtimearr[1]+40):
$day[1]=[$matches[0][$key],isOkayLeave($time,$shouldtimearr[1])];
break;
//下午上班
case ($shouldtimearr[2]-40<$time&&$time<$shouldtimearr[2]+40):
$day[2]=[$matches[0][$key],isOkayGo($time,$shouldtimearr[2])];
break;
//下午下班
case ($shouldtimearr[3]-40<$time&&$time<$shouldtimearr[3]+40):
$day[3]=[$matches[0][$key],isOkayLeave($time,$shouldtimearr[3])];
break;
//加班上班
case ($shouldtimearr[4]-40<$time&&$time<$shouldtimearr[4]+40):
$day[4]=[$matches[0][$key],isOkayGo($time,$shouldtimearr[4])];
break;
//加班下班
case ($shouldtimearr[5]-40<$time&&$time<$shouldtimearr[5]+40):
$day[5]=[$matches[0][$key],isOkayLeave($time,$shouldtimearr[5])];
break;
default:
break;
}
}
return $day;
}
// var_dump($dayarr);
/**
* 将字符串转为int
* @param [string] $value '8:00'
* @return [int] 8*60
*/
function arrtotime(&$value){
$HandM=explode(':', $value);
$value = intval($HandM[0])*60+intval($HandM[1]);
}
/**
* @param $value 打卡时间int
* @param $shouldtime 应到时间int
* @return $flag boolean 是否迟到
*/
function isOkayGo($value,$shouldtime){
$flag=true;
if($value>$shouldtime){
$flag=false;
}
return $flag;
}
/**
* @param $value
* @param $shouldtime 应退时间int
* @return $flag 是否早退 boolean
*/
function isOkayLeave($value,$shouldtime){
$flag = true;
if($value < $shouldtime){
$flag = false;
}
return $flag;
}