function excel($postdata) { require_once 'Excel_reader.php'; require_once 'Classes/PHPExcel.php'; $tmp = $postdata; $save_path = "./"; //暂时上传的文件路径 $file_name = $save_path . date('Ymdhis') . ".xls"; //上传后的文件保存路径和名称 if (copy($tmp, $file_name)) { } $PHPExcel = new PHPExcel(); $PHPReader = new PHPExcel_Reader_Excel2007(); //为了可以读取所有版本Excel文件 if (!$PHPReader->canRead($file_name)) { $PHPReader = new PHPExcel_Reader_Excel5(); if (!$PHPReader->canRead($file_name)) { echo '未发现Excel文件!'; return; } } //不需要读取整个Excel文件而获取所有工作表数组的函数,感觉这个函数很有用,找了半天才找到 $sheetNames = $PHPReader->listWorksheetNames($file_name); //读取Excel文件 $PHPExcel = $PHPReader->load($file_name); //获取工作表的数目 $sheetCount = $PHPExcel->getSheetCount(); for ($i = 0; $i < $sheetCount; $i++) { //选择第一个工作表 $currentSheet = $PHPExcel->getSheet($i); //取得一共有多少列 $allColumn = $currentSheet->getHighestColumn(); //取得一共有多少行 $allRow = $currentSheet->getHighestRow(); for ($currentRow = 1; $currentRow <= $allRow; $currentRow++) { for ($currentColumn = 'A'; $currentColumn <= $allColumn; $currentColumn++) { $address = $currentColumn . $currentRow; $data[$i][$currentRow][$currentColumn] = $currentSheet->getCell($address)->getValue(); //echo ."\t"; } } //$data[$i]=$currentSheet; } unlink($file_name); //删除上传的文件 return $data; }
public function index() { // $xq_list = $this->manage_model->get_all_xiaoqu_list(); // foreach ($xq_list as $xq) { // $name = str_replace("•","",$xq['name']); // //echo $xq['id'] . ": " . $this->encode($name) . '<br>'; // $xq['jianpin'] = $this->encode($name); // $this->manage_model->update_xiaoqu_jianpin($xq); // } // die('DONE'); require_once APPPATH . 'libraries/PHPExcel/PHPExcel.php'; $PHPExcel = new \PHPExcel(); $PHPReader = new \PHPExcel_Reader_Excel2007(); $file = 'E:/broker.xlsx'; //为了可以读取所有版本Excel文件 if (!$PHPReader->canRead($file)) { $PHPReader = new \PHPExcel_Reader_Excel5(); if (!$PHPReader->canRead($file)) { echo '未发现Excel文件!'; return; } } //不需要读取整个Excel文件而获取所有工作表数组的函数,感觉这个函数很有用,找了半天才找到 $sheetNames = $PHPReader->listWorksheetNames($file); //读取Excel文件 $PHPExcel = $PHPReader->load($file); //获取工作表的数目 $sheetCount = $PHPExcel->getSheetCount(); //选择第一个工作表 $currentSheet = $PHPExcel->getSheet(0); //取得一共有多少列 $allColumn = $currentSheet->getHighestColumn(); //取得一共有多少行 $allRow = $currentSheet->getHighestRow(); //循环读取数据,默认编码是utf8,这里转换成gbk输出 $data = array(); for ($currentRow = 2; $currentRow <= $allRow; $currentRow++) { $name = trim($currentSheet->getCell("A" . $currentRow)->getValue()); $tel = trim($currentSheet->getCell("B" . $currentRow)->getValue()); $data[] = array('username' => $tel, 'passwd' => sha1('888888'), 'tel' => $tel, 'company_name' => "顺达地产", 'rel_name' => $name, 'region_id' => 6, 'admin_group' => 2); } $this->manage_model->add_broker_batch($data); }
private function _phydata($fileinfo, $ac = 'phydata') { //import('Think.Cache'); //$Cache = \Cache::getInstance(); //查看是否截止上报 $school_year_info = D('SchoolYear')->get_info($this->school_year); if (empty($school_year_info) || $school_year_info['state'] == 207020) { $this->ajaxReturn(array('errno' => 101, 'errtitle' => '您选择的学年' . $this->school_year . '未开始,无法录入')); } //审核状态 $userinfo = session('userinfo'); $dictList = session('dictList'); //非补录数据需要验证上报状态 if ($ac != 'historyPhydata') { if (!empty($school_year_info['not_upload_time']) && time() > strtotime($school_year_info['not_upload_time'])) { $this->ajaxReturn(array('errno' => 102, 'errtitle' => '数据上报截止时间为' . $school_year_info['not_upload_time'])); } //朝阳区功能 //if($userinfo['org_id'] == 110105){ // $this->ajaxReturn(array('errno'=>109,'errtitle'=>'功能暂未开放!')); //} if ($userinfo['org_id'] != 110105 && $userinfo['user_kind'] != '109010') { $s_status = D('SchoolStatus')->get_status_info_one($this->school_year, $this->school_code); if ($s_status['s_status'] == 206020 || $s_status['s_status'] == 206030) { $this->ajaxReturn(array('errno' => 109, 'errtitle' => '您当前上报状态为' . $dictList['206'][$s_status['s_status']]['dict_name'] . ',如需重新上报或修改请等待区县撤销!')); } } } //读取excel文件内容 $fPath = '/Upload/' . $fileinfo['info']['savepath'] . $fileinfo['info']['savename']; //文件大小限制 $filesize = filesize($_SERVER['DOCUMENT_ROOT'] . $fPath); if ($filesize > 2097152) { @unlink($_SERVER['DOCUMENT_ROOT'] . $fPath); $this->ajaxReturn(array('errno' => 111, 'errtitle' => '请不要上传超过2M的文件!')); } import("Org.Util.PHPExcel"); import("Org.Util.PHPExcel.IOFactory"); $reader = \PHPExcel_IOFactory::createReader('Excel2007'); $PHPExcel = \PHPExcel_IOFactory::load($_SERVER['DOCUMENT_ROOT'] . $fPath); $reader->setReadDataOnly(true); $PHPReader = new \PHPExcel_Reader_Excel2007(); //为了可以读取所有版本Excel文件 if (!$PHPReader->canRead($_SERVER['DOCUMENT_ROOT'] . $fPath)) { $PHPReader = new \PHPExcel_Reader_Excel5(); if (!$PHPReader->canRead($_SERVER['DOCUMENT_ROOT'] . $fPath)) { @unlink($_SERVER['DOCUMENT_ROOT'] . $fPath); $this->ajaxReturn(array('errno' => 104, 'errtitle' => '未发现excel文件!')); } } ////不需要读取整个Excel文件而获取所有工作表数组的函数 $sheetNames = $PHPReader->listWorksheetNames($_SERVER['DOCUMENT_ROOT'] . $fPath); if ($userinfo['org_id'] != 110105) { $sheetNames = array($this->school_code); } else { if (empty($sheetNames)) { @unlink($_SERVER['DOCUMENT_ROOT'] . $fPath); $this->ajaxReturn(array('errno' => 1115, 'errtitle' => 'excel内容为空!')); } } /*表头*/ $key_base = array('grade_num', 'class_num', 'class_name', 'country_education_id', 'folk_code', 'name', 'sex', 'birthday', 'address'); $titContent = array('body_height' => '身高', 'body_weight' => '体重', 'vital_capacity' => '肺活量', 'wsm' => '50米跑', 'ldty' => '立定跳远', 'zwtqq' => '坐位体前屈', 'bbm_nv' => '800米跑(女)', 'yqm_nan' => '1000米跑(男)', 'ywqz_nv' => '一分钟仰卧起坐(女)', 'ytxs_nan' => '引体向上(男)', 'wsmwfp' => '50米×8往返跑', 'yfzts' => '一分钟跳绳', 'ywqz_ytxs' => '一分钟仰卧起坐'); $gradeItem = C('GRADE_ITEM_FIELD'); //判断验证学籍号还是教育ID号 if ($ac == 'phydata2') { $field = 'education_id'; $fieldTitle = '教育ID号'; } else { $field = 'country_education_id'; $fieldTitle = '全国学籍号'; } /*表头end*/ //循环sheet //记录错误内容 $errorLog = ''; //启动事务 M()->startTrans(); foreach ($sheetNames as $sk => $sname) { $this->school_code = $sname; $this->school_id = D('School')->get_list_by_schoolcode_year($this->school_code, $this->school_year, 'one'); if (empty($this->school_id)) { @unlink($_SERVER['DOCUMENT_ROOT'] . $fPath); $this->ajaxReturn(array('errno' => 1116, 'errtitle' => 'sheet名称为' . $sname . '的学校代码错误,找不到该学校')); break; } $this->school_id = $this->school_id['school_id']; $schoolids[] = $this->school_id; /*读取每一页内容*/ $sheet = $PHPExcel->getSheet($sk); //sheet1 $highestRow = $sheet->getHighestRow(); //总行数 $highestColumn = $sheet->getHighestColumn(); //总列数,字母表示 //判断是否空文件 if ($highestRow <= 1) { @unlink($_SERVER['DOCUMENT_ROOT'] . $fPath); $this->ajaxReturn(array('errno' => 103, 'errtitle' => '您上传的文件没有内容!')); } //将导入记录插入到导入历史表 //上传状态为初始状态204010 上传中 //is_error 初始状态为正确,待数据校验完毕后更改该列值 $importLogData = array('user_id' => $this->school_id, 'year_year' => $this->school_year, 'import_time' => date('Y-m-d H:i:s'), 'file_name' => $fileinfo['info']['name'], 'file_path' => $fPath, 'deal_status' => '204010', 'is_error' => '0', 'town_id' => $this->town_id, 'partition_field' => intval($this->town_id . $this->school_year)); //补录数据 if ($ac == 'historyPhydata') { $importLogData['is_examine'] = 0; } $import_id = D('ImportLog')->add($importLogData); $importids[] = $import_id; if (!$import_id) { //删除文件 @unlink($_SERVER['DOCUMENT_ROOT'] . $fPath); M()->rollback(); $this->ajaxReturn(array('errno' => 102, 'errtitle' => '保存上传记录失败,请稍候重试!')); } //缓存学校学生信息 //$stuinfos = S('base_data_' . $this->school_id); //if($stuinfos === false){ $stuinfos = D('StudentScore')->get_school_datas($importLogData['partition_field'], $this->school_id, $ac); // S('base_data_' . $this->school_id,serialize($stuinfos)); //}else{ // $stuinfos = unserialize($stuinfos); //dump($stuinfos);exit(); //} $titleArr = array(); //从第九列开始 $columnNum = 9; for ($column = 'J'; $column <= $highestColumn; $column++) { $val = $sheet->getCellByColumnAndRow($columnNum, 1)->getValue(); if ($val instanceof PHPExcel_RichText) { //富文本转换字符串 $val = $val->__toString(); } $val = trim($val); if ($val == '身高') { $titleArr[] = 'body_height'; } elseif ($val == '体重') { $titleArr[] = 'body_weight'; } elseif ($val == '肺活量') { $titleArr[] = 'vital_capacity'; } elseif ($val == '50米跑') { $titleArr[] = 'wsm'; } elseif ($val == '坐位体前屈') { $titleArr[] = 'zwtqq'; } elseif ($val == '一分钟跳绳') { $titleArr[] = 'yfzts'; } elseif (strpos($val, '800米跑') !== false) { $titleArr[] = 'bbm_nv'; } elseif (strpos($val, '1000米跑') !== false) { $titleArr[] = 'yqm_nan'; } elseif (strpos($val, '一分钟仰卧起坐') !== false) { $titleArr[] = 'ywqz_ytxs'; } elseif (strpos($val, '引体向上') !== false) { $titleArr[] = 'ytxs_nan'; } elseif (strpos($val, '立定跳远') !== false) { $titleArr[] = 'ldty'; } elseif (strpos($val, '往返跑') !== false) { $titleArr[] = 'wsmwfp'; } $columnNum++; } if (empty($titleArr)) { @unlink($_SERVER['DOCUMENT_ROOT'] . $fPath); M()->rollback(); $this->ajaxReturn(array('errno' => 105, 'errtitle' => '请务必使用本系统或者国家体质检测系统下载的上报模板并不要更改列格式!')); } //合并表头 $keys = array_merge($key_base, $titleArr); $titleCount = count($keys); $phyData = array(); if (count($sheetNames) > 1) { $sheetErr = $sname . '页 '; } else { $sheetErr = ''; } for ($row = 2; $row <= $highestRow; $row++) { //if($row == 100) exit($highestRow); for ($column = 0; $column < $titleCount; $column++) { $val = $sheet->getCellByColumnAndRow($column, $row)->getValue(); if ($val instanceof PHPExcel_RichText) { //富文本转换字符串 $val = $val->__toString(); } $val = trim($val); $phyData[$row][$keys[$column]] = $val; } if ($phyData[$row]['country_education_id'] == '' && $phyData[$row]['name'] == '') { break; } $errLogT1 = '第 ' . $row . ' 行'; $errLogT2 = ''; if ($phyData[$row]['country_education_id'] == '') { $errLogT2 .= $fieldTitle . '不能为空;'; } if ($phyData[$row]['name'] == '') { $errLogT2 .= '姓名不能为空;'; } $stuinfo = $stuinfos[$phyData[$row]['country_education_id']]; if (empty($stuinfo) || $stuinfo['in_school'] == 0 || $stuinfo['name'] != $phyData[$row]['name']) { if (empty($stuinfo) || $stuinfo['name'] != $phyData[$row]['name']) { $errLogT2 .= " 非当前学校数据或者数据格式错误,请核对学生姓名、" . $fieldTitle . "是否有误;"; } elseif ($stuinfo['in_school'] == 0) { $errLogT2 .= " 当前学生已设置为不在测,如需上报体质信息请设置该生是否在测为'是';"; } } else { $realGrade = $phyData[$row]['grade_num']; if ($stuinfo['school_length54'] == 1) { switch ($stuinfo['school_grade']) { case 21: $realGrade = 16; break; case 22: $realGrade = 21; break; case 23: $realGrade = 22; break; case 24: $realGrade = 23; break; default: $realGrade = $stuinfo['school_grade']; break; } } if (!in_array($realGrade, array('11', '12', '13', '14', '15', '16', '21', '22', '23', '24', '30', '31', '32', '33', '34'))) { $errLogT2 .= ' 年级编号错误 ;'; } if (in_array($realGrade, array('11', '12'))) { $titles = $gradeItem[11]; } elseif (in_array($realGrade, array('13', '14'))) { $titles = $gradeItem[13]; } elseif (in_array($realGrade, array('15', '16'))) { $titles = $gradeItem[15]; } else { $titles = $gradeItem[21]; } if (!in_array($phyData[$row]['sex'], array(1, 2))) { $errLogT2 .= ' 性别列用数字1和2表示 ;'; } if ($phyData[$row]['sex'] != substr($stuinfo['sex'], 4, 1)) { $errLogT2 .= ' 学生性别与cmis信息不一致 ;'; } $titleErr = 0; foreach ($titles as $va) { $tmpStr = $phyData[$row][$va]; if ($phyData[$row]['body_height'] == '免体') { break; } if ($realGrade > 16) { if ($tmpStr == '' && in_array($va, array('body_height', 'body_weight', 'vital_capacity', 'wsm', 'ldty', 'zwtqq'))) { $titleErr = 1; } else { if ($phyData[$row]['sex'] == 2 && $va == 'bbm_nv' && $tmpStr == '') { $titleErr = 1; } elseif ($phyData[$row]['sex'] == 2 && $va == 'ywqz_ytxs' && $tmpStr == '') { $titleErr = 1; } elseif ($phyData[$row]['sex'] == 1 && $va == 'yqm_nan' && $tmpStr == '') { $titleErr = 1; } elseif ($phyData[$row]['sex'] == 1 && $va == 'ytxs_nan' && $tmpStr == '') { $titleErr = 1; } } } else { if ($va == 'ywqz_ytxs') { //$tmpStr=$phyData[$row]['ywqz_ytxs']; $titleErr = $tmpStr == '' ? 1 : 0; } } if ($titleErr == 1) { $errLogT2 .= $titContent[$va] . ' 不能为空;'; $titleErr = 0; continue; } if (!is_numeric($tmpStr) && $tmpStr != '') { if (!preg_match("('|’|”|′|\")", $tmpStr)) { $errLogT2 .= $titContent[$va] . "[" . $tmpStr . "] 不是有效的体质健康数据,请确认; "; } else { $tmpStr = str_replace("’", '′', $tmpStr); $tmpStr = str_replace("”", '′′', $tmpStr); $tmpStr = str_replace("'", '′', $tmpStr); $tmpStr = str_replace("''", '′′', $tmpStr); $tmpStr = str_replace("\"", '′′', $tmpStr); } } if (!strpos($tmpStr, '′') && !strpos($tmpStr, '′′')) { $phyData[$row][$va] = floatval($tmpStr); } else { //50米跑只能用小数点分隔 if ($va == 'wsm') { $errLogT2 .= $titContent[$va] . "[" . $tmpStr . "] 必须使用小数点分隔; "; } else { $tmpFen = strpos($tmpStr, '′') ? intval(substr($tmpStr, 0, strpos($tmpStr, '′'))) : 0; $tmpMiao = strpos($tmpStr, '′') ? substr(strstr($tmpStr, '′'), 3, strlen($tmpStr) - 1) : '00'; $tmpMiao = strlen($tmpMiao) == 1 ? '0' . $tmpMiao : $tmpMiao; $end_result = $tmpFen == 0 ? intval($tmpMiao) : floatval($tmpFen . '.' . $tmpMiao); if (!$end_result || intval($tmpMiao) > 59) { $errLogT2 .= " 分秒时间格式错误或者秒大于59,请确认; "; } else { $phyData[$row][$va] = $end_result; } } } } } $errLogT3 = ' <br>'; if ($errLogT2 != '') { $errorLog .= $sheetErr . $errLogT1 . $errLogT2 . $errLogT3; continue; } $data = array('import_id' => $import_id, 'is_error' => 0, 'error_desc' => '', 'excel_num' => $row, 'education_id' => $stuinfo['education_id'], 'country_education_id' => $stuinfo['country_education_id'], 'student_no' => $stuinfo['studentno'], 'grade_num' => str_replace("'", "", $phyData[$row]['grade_num']), 'class_num' => str_replace("'", "", $phyData[$row]['class_num']), 'class_name' => str_replace("'", "", $phyData[$row]['class_name']), 'folk_code' => str_replace("'", "", $stuinfo['folk']), 'name' => $stuinfo['name'], 'sex' => $stuinfo['sex'] == '106020' ? '女' : '男', 'birthday' => date('Y-m-d', strtotime($stuinfo['birthday'])), 'student_source' => $stuinfo['student_source'], 'address' => str_replace("'", "", $phyData[$row]['address']), 'rewards_code' => 0, 'town_id' => $importLogData['town_id'], 'partition_field' => $importLogData['partition_field'], 'year_year' => $importLogData['year_year'], 'import_time' => date('Y-m-d H:i:s'), 'is_avoid' => '否'); //是否为补录数据 if ($ac == 'historyPhydata') { $data['examine'] = 0; } if ($phyData[$row]['body_height'] === '免体') { $data['is_avoid'] = '是'; } else { $data['body_height'] = floatval($phyData[$row]['body_height']); $data['body_weight'] = floatval($phyData[$row]['body_weight']); $data['vital_capacity'] = floatval($phyData[$row]['vital_capacity']); $data['wsm'] = floatval($phyData[$row]['wsm']); $data['ldty'] = floatval($phyData[$row]['ldty']); $data['zwtqq'] = floatval($phyData[$row]['zwtqq']); if ($phyData[$row]['wsmwfp'] != '') { $data['wsmwfp'] = floatval($phyData[$row]['wsmwfp']); } if ($phyData[$row]['yfzts'] != '') { $data['yfzts'] = (int) $phyData[$row]['yfzts']; } if ($stuinfo['sex'] == '106020' && $phyData[$row]['bbm_nv'] != '') { $data['bbm_yqm'] = floatval($phyData[$row]['bbm_nv']); } elseif ($stuinfo['sex'] == '106010' && $phyData[$row]['yqm_nan'] != '') { $data['bbm_yqm'] = floatval($phyData[$row]['yqm_nan']); } if ($realGrade < 21) { if ($phyData[$row]['ywqz_ytxs'] != '') { $data['ywqz_ytxs'] = (int) $phyData[$row]['ywqz_ytxs']; } } else { if ($stuinfo['sex'] == '106020' && $phyData[$row]['ywqz_ytxs'] != '') { $data['ywqz_ytxs'] = (int) $phyData[$row]['ywqz_ytxs']; } elseif ($stuinfo['sex'] == '106010' && $phyData[$row]['ytxs_nan'] != '') { $data['ywqz_ytxs'] = (int) $phyData[$row]['ytxs_nan']; } } } //$school_length54 = $stuinfo['school_length54']; $detail_id = D('import_detail_new')->add($data); if (!$detail_id) { $errLogT2 .= '添加学生记录失败!'; } if ($errLogT2 != '') { $errorLog .= $sheetErr . $errLogT1 . $errLogT2 . $errLogT3; } } ///M()->execute("execute sp_check_detail_main"); } if (!empty($errorLog)) { @unlink($_SERVER['DOCUMENT_ROOT'] . $fPath); //回滚 M()->rollback(); $this->ajaxReturn(array('errno' => '109', 'errtitle' => $errorLog)); } else { //数据上传成功后修改状态为 待校验:204020 $where['import_id'] = array('IN', $importids); D('ImportLog')->where($where)->setField('deal_status', '204020'); //朝阳区学校自动变更为已上报,无需点击上报按钮 if ($userinfo['org_id'] == 110105 || $userinfo['user_kind'] == 109010) { $data = array('s_status' => 206020, 'sub_time' => time()); $where['school_id'] = array('IN', $schoolids); D('SchoolStatus')->where($where)->save($data); } //提交 M()->commit(); $this->ajaxReturn(array('errno' => 0, 'errtitle' => '文件上传成功,请等待系统校验...', 'import_id' => implode(',', $importids))); } }