主要是公式解析的代码，还有错误模板下载
/**
* 导入报名信息
*/
public function import()
{
// echo '11';die;
ini_set('memory_limit', '4096M');
ini_set('max_execution_time', '180');
$sig_tetr_id = input('post.tetr_id/d'); //研修id
$tetrinfo = Training::get($sig_tetr_id);
if (!$tetrinfo) {
$this->error('研修信息错误');
}
$file = $this->request->request('file');
$domain = $this->request->domain();
$file = str_replace($domain, '', $file);
if (!$file) {
$this->error(__('文件未找到', 'file'));
}
$filePath = ROOT_PATH . DS . 'public' . DS . $file;
if (!is_file($filePath)) {
$this->error(__('未知的数据格式!'));
}
//实例化reader
$ext = pathinfo($filePath, PATHINFO_EXTENSION);
if (!in_array($ext, ['csv', 'xls', 'xlsx'])) {
$this->error(__('请上传正确格式的文件'));
}
$reader = new Xlsx();
//加载文件
$insert = [];
try {
$reader->setReadDataOnly(true);
if (!$PHPExcel = $reader->load($filePath)) {
$this->error(__('未知的数据格式!'));
}
$currentSheet = $PHPExcel->getSheet(0); //读取文件中的第一个工作表
$allColumn = $currentSheet->getHighestDataColumn(); //取得最大的列号
$allRow = $currentSheet->getHighestRow(); //取得一共有多少行
$maxColumnNumber = Coordinate::columnIndexFromString($allColumn);
$fields = [];
for ($currentRow = 1; $currentRow <= 1; $currentRow++) {
for ($currentColumn = 1; $currentColumn <= $maxColumnNumber; $currentColumn++) {
$val = $currentSheet->getCellByColumnAndRow($currentColumn, $currentRow)->getValue();
$fields[] = $val;
}
}
for ($currentRow = 2; $currentRow <= $allRow; $currentRow++) {
$values = [];
for ($currentColumn = 1; $currentColumn <= $maxColumnNumber; $currentColumn++) {
$cell = $currentSheet->getCellByColumnAndRow($currentColumn, $currentRow);
$val = $cell->getCalculatedValue(); // 获取公式的计算结果
if($cell->getDataType()==\PHPExcel_Cell_DataType::TYPE_NUMERIC){
$cellstyleformat=$cell->getStyle($cell->getCoordinate())->getNumberFormat();
$formatcode=$cellstyleformat->getFormatCode();
if (preg_match('/^(\[\$[A-Z]*-[0-9A-F]*\])*[hmsdy]/i', $formatcode)) {
$val=date("Y-m-d",\PHPExcel_Shared_Date::ExcelToPHP($val));
}else{
$val= \PHPExcel_Style_NumberFormat::toFormattedString($val,$formatcode);
}
}
$values[] = is_null($val) ? '' : trim($val); //清除数据中的空格；
}
var_export($values);
$rowStr = rtrim(ltrim(implode("", $values)));
$values = array_slice($values, 0, 13); //确认前八位
//'sig_price','sig_payment_way',
if (!empty($rowStr)) {
$fields = [
'num', 'stu_name', 'stu_cardid', 'stu_sex', 'stu_province', 'stu_city', 'stu_area',
'stu_mobile', 'stu_emp_name'
];
$temp = array_combine($fields, $values);
$insert[] = $temp;
}
}
} catch (Exception $exception) {
$this->error($exception->getMessage());
}
// die;
if (!$insert) {
$this->error(__('未更新任何行'));
}
\think\Db::startTrans();
try {
//检验学员信息，
$insert = trim_twoarray($insert);
$list = Training::checkSignParams($insert);
$error = [];
$success = [];
foreach ($list as $key => $value) {
if ($value['code'] == false) {
$value['stu_cardid'] = trim($value['stu_cardid']) . "\t";
$error[] = $value;
} else {
$value['stu_username'] = strtoupper(trimall($value['stu_cardid']));
$v['stu_cardid'] = strtoupper(trimall($value['stu_cardid']));
$value['stu_name'] = trimall($value['stu_name']);
$success[] = $value;
}
}
$studentModel = new \app\admin\model\Student();
$signModel = new Sign();
$Examconfig = new Examconfig();
$examInfo = $Examconfig->order('exac_id', 'desc')->find();
$stuIdArr = [];
$signData = [];
$errnewData = [];
if ($success) {
//处理学员数据
foreach ($success as $k => $v) {
$findStudent = $studentModel
->where(['stu_cardid' => encrypt($v['stu_cardid'])])
->whereOr(['stu_mobile' => encrypt($v['stu_mobile'])])
->find();
if (!$findStudent) {
$res = \app\admin\model\Student::create(
$v,
array(
'stu_name', 'stu_cardid', 'stu_sex', 'stu_city',
'stu_mobile', 'stu_email', 'stu_address', 'stu_password', 'stu_source', 'stu_emp_id', 'stu_pos_id',
'stu_dutid', 'stu_provinceid', 'stu_province', 'stu_cityid', 'stu_areaid', 'stu_area', 'stu_username'
)
)
->validate('Student.add');
$stuId = $res->stu_id;
if ($res == false) {
$this->error($studentModel->getError());
}
} else {
$stuId = $findStudent->stu_id;
}
$stuIdArr[] = $stuId;
$is_sign = $signModel->where(['sig_student_id' => $stuId, 'sig_tetr_id' => $sig_tetr_id])->find();
if ($is_sign) {
$v['msg'] = '学员:' . $v['stu_cardid'] . '的 ' . $tetrinfo['tetr_name'] . '研修已报名过;';
$v['stu_cardid'] = trim($v['stu_cardid']) . "\t";
$v['stu_mobile'] = trim($v['stu_mobile']) . "\t";
$errnewData[] = $v;
continue;
}
$signData[$k]['sig_payment_type'] = 1; //统一
$signData[$k]['sig_payment_way'] = 'backend';
$signData[$k]['sig_examinations'] = $examInfo->exac_examinations ?? '2';
$signData[$k]['sig_cityid'] = $v['stu_cityid'];
$signData[$k]['sig_areaid'] = $v['stu_areaid'];
$signData[$k]['sig_course_id'] = $tetrinfo['tetr_cou_id'];
$signData[$k]['stu_emp_name'] = $v['stu_emp_name'];
$signData[$k]['sig_student_id'] = $stuId;
$signData[$k]['sig_date'] = $v['sig_date'];
$signData[$k]['sig_tetr_id'] = $sig_tetr_id;
}
$studentModel->where(['stu_id' => ['IN', $stuIdArr]])->update(['stu_issign' => '1']);
if ($signData) {
$result = $signModel->allowField(true)->saveAll($signData);
if ($result == false) {
$this->error('错误' . $signModel->getError());
}
}
\think\Db::commit();
}
$allErrorData = array_merge($error, $errnewData);
if ($allErrorData) {
foreach ($allErrorData as $ky => &$v) {
$v['num'] = $ky + 1;
}
//制作excel进行导出；
$excelkey = [
'1' => '序号', '2' => '学员姓名', '3' => '学员身份证号码', '4' => '学员性别(男，女，保密)', '5' => '省', '6' => '市', '7' => '区',
'8' => '联系电话', '9' => '单位', '10' => '错误信息'
];
$excelval = [
'1' => 'num', '2' => 'stu_name', '3' => 'stu_cardid', '4' => 'stu_sex', '5' => 'stu_province', '6' => 'stu_city', '7' => 'stu_area',
'8' => 'stu_mobile', '9' => 'stu_emp_name', '10' => 'msg'
];
$return = $this->outPutExcel($allErrorData, $excelkey, $excelval, '报名错误数据', false, '20', '');
if ($return['code'] != '1') {
$this->error('功能异常,请联系管理员');
}
$this->success('导入成功:' . count($signData) . '条数据，失败：' . count($allErrorData) . '条数据', $return, '2');
}
$this->success('导入成功:' . count($signData) . '条数据，失败：0条数据');
} catch (\think\Exception $exception) {
\think\Db::rollback();
$msg = $exception->getMessage();
if (preg_match("/.+Integrity constraint violation: 1062 Duplicate entry '(.+)' for key '(.+)'/is", $msg, $matches)) {
$msg = "导入失败，包含【{$matches[1]}】的记录已存在";
};
$this->error($msg . '__' . $exception->getLine());
} catch (\think\Exception $e) {
\think\Db::rollback();
$this->error($e->getTrace() . $e->getMessage() . $e->getLine());
}
$this->success('成功');
}
发表评论 取消回复