excel批量导入考生
import代码修改
// 检测重命名,重名的话，根据后面的数字，自动加1,存在10个重名的概率非常小，所以，只检测最后一位，最后发现太难了，还是检测重复，手动修改编号的好
$username_arr=Db::name('user')->column('username');
if(in_array($row['username'],$username_arr) ){
// halt($row['username'].'已存在');
// 获取字符串最后一位
// $last=$row['username']{ strlen(trim($row['username']))-1};
// halt($last);
// if(is_numeric($last)){
// $realname=substr($row['username'], 0, -1);
// $bianhao=(int)$last+1;
// $row['username']=$realname.$bianhao;
// }
// $this->error(__('No rows were updated'));
throw new Exception($row['username'].'考生重名了,请排查做好区分后，再此导入');
// $this->error();
// halt($row['username']);
}
// halt($username_arr);
// 为手机号生成密码，不存在手机号的时候，密码设置为123456
if(isset($row['mobile'])&&$row['mobile']){
$row['password']=$row['mobile'];
}else{
$row['password']="123456";
}
// 设置考生的状态
$row['status']='normal';
$row['group_id']=1;
// halt($row);
import函数全文
/**
* 导入
*/
protected function import()
{
$file = $this->request->request('file');
if (!$file) {
$this->error(__('Parameter %s can not be empty', 'file'));
}
$filePath = ROOT_PATH . DS . 'public' . DS . $file;
if (!is_file($filePath)) {
$this->error(__('No results were found'));
}
//实例化reader
$ext = pathinfo($filePath, PATHINFO_EXTENSION);
if (!in_array($ext, ['csv', 'xls', 'xlsx'])) {
$this->error(__('Unknown data format'));
}
if ($ext === 'csv') {
$file = fopen($filePath, 'r');
$filePath = tempnam(sys_get_temp_dir(), 'import_csv');
$fp = fopen($filePath, "w");
$n = 0;
while ($line = fgets($file)) {
$line = rtrim($line, "\n\r\0");
$encoding = mb_detect_encoding($line, ['utf-8', 'gbk', 'latin1', 'big5']);
if ($encoding != 'utf-8') {
$line = mb_convert_encoding($line, 'utf-8', $encoding);
}
if ($n == 0 || preg_match('/^".*"$/', $line)) {
fwrite($fp, $line . "\n");
} else {
fwrite($fp, '"' . str_replace(['"', ','], ['""', '","'], $line) . "\"\n");
}
$n++;
}
fclose($file) || fclose($fp);
$reader = new Csv();
} elseif ($ext === 'xls') {
$reader = new Xls();
} else {
$reader = new Xlsx();
}
//导入文件首行类型,默认是注释,如果需要使用字段名称请使用name
$importHeadType = isset($this->importHeadType) ? $this->importHeadType : 'comment';
$table = $this->model->getQuery()->getTable();
$database = \think\Config::get('database.database');
$fieldArr = [];
$list = db()->query("SELECT COLUMN_NAME,COLUMN_COMMENT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ? AND TABLE_SCHEMA = ?", [$table, $database]);
foreach ($list as $k => $v) {
if ($importHeadType == 'comment') {
$fieldArr[$v['COLUMN_COMMENT']] = $v['COLUMN_NAME'];
} else {
$fieldArr[$v['COLUMN_NAME']] = $v['COLUMN_NAME'];
}
}
//加载文件
$insert = [];
try {
if (!$PHPExcel = $reader->load($filePath)) {
$this->error(__('Unknown data format'));
}
$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++) {
$val = $currentSheet->getCellByColumnAndRow($currentColumn, $currentRow)->getValue();
$values[] = is_null($val) ? '' : $val;
}
$row = [];
$temp = array_combine($fields, $values);
foreach ($temp as $k => $v) {
if (isset($fieldArr[$k]) && $k !== '') {
$row[$fieldArr[$k]] = $v;
}
}
// 检测重命名,重名的话，根据后面的数字，自动加1,存在10个重名的概率非常小，所以，只检测最后一位
$username_arr=Db::name('user')->column('username');
if(in_array($row['username'],$username_arr) ){
// halt($row['username'].'已存在');
// 获取字符串最后一位
// $last=$row['username']{ strlen(trim($row['username']))-1};
// halt($last);
// if(is_numeric($last)){
// $realname=substr($row['username'], 0, -1);
// $bianhao=(int)$last+1;
// $row['username']=$realname.$bianhao;
// }
// $this->error(__('No rows were updated'));
throw new Exception($row['username'].'考生重名了,请排查做好区分后，再此导入');
// $this->error();
// halt($row['username']);
}
// halt($username_arr);
// 为手机号生成密码，不存在手机号的时候，密码设置为123456
if(isset($row['mobile'])&&$row['mobile']){
$row['password']=$row['mobile'];
}else{
$row['password']="123456";
}
// 设置考生的状态
$row['status']='normal';
$row['group_id']=1;
// halt($row);
if ($row) {
$insert[] = $row;
}
}
} catch (Exception $exception) {
$this->error($exception->getMessage());
}
if (!$insert) {
$this->error(__('No rows were updated'));
}
try {
//是否包含admin_id字段
$has_admin_id = false;
// halt($fieldArr);
foreach ($fieldArr as $name => $key) {
if ($key == 'admin_id') {
$has_admin_id = true;
break;
}
}
if ($has_admin_id) {
$auth = Auth::instance();
foreach ($insert as &$val) {
if (!isset($val['admin_id']) || empty($val['admin_id'])) {
$val['admin_id'] = $auth->isLogin() ? $auth->id : 0;
}
}
}
// halt(1111);
// halt($insert);
$result=$this->model->saveAll($insert);
// halt($result->toArray());
// halt($result);
$ids=[];
//5.对象数组，需要用循环输出
foreach ($result as $value) {
//获取对象数组中，每一个数据对象的原始数据
$data = $value -> getData();
// halt($data);
$id[]=$data['id'];
$ids_str=implode($id,',');
}
Db::name('daoru')->insert(['ids_str'=>$ids_str,'createtime'=>time(),'memo'=>'导入300考生']);
} catch (PDOException $exception) {
$msg = $exception->getMessage();
if (preg_match("/.+Integrity constraint violation: 1062 Duplicate entry '(.+)' for key '(.+)'/is", $msg, $matches)) {
$msg = "导入失败，包含【{$matches[1]}】的记录已存在";
};
$this->error($msg);
} catch (Exception $e) {
$this->error($e->getMessage());
}
$this->success();
}
先导出cvs,再保存为xlxs格式，必填字段如下
| 用户名 | 昵称 | 手机号 | 性别 | 所属部门 |
|---|---|---|---|---|
| ceshi1 | ceshi | 13956976397 | 0 | 14 |
| admin1 | admin | 13888888888 | 0 | 14 |
1.其中，用户名要用编号区分，昵称可以相同，所以保留昵称，必填
2.手机号必填项，如果没有手机号，密码就是123456
3.性别，男对应1，女对应0，必须填数字
4.先增加部门，再填入部门的id，必须填数字
如何把新增的考生，加入到新的计划里。
1.新增导入记录表，字段，id，ids_str,createtime,memo，plan_ids
$ids=[];
//5.对象数组，需要用循环输出
foreach ($result as $value) {
//获取对象数组中，每一个数据对象的原始数据
$data = $value -> getData();
// halt($data);
$id[]=$data['id'];
$ids_str=implode($id,',');
}
Db::name('daoru')->insert(['ids_str'=>$ids_str,'createtime'=>time(),'memo'=>'导入300考生']);
2.一键mvc生成 kaoshi/daoru
3.一键菜单
4.复制ids
5.新增加入计划的按钮。
直接到导入考生管理里面，加一个操作按钮
具体方法：https://blog.csdn.net/weixin_41037929/article/details/107170494
https://ask.fastadmin.net/article/323.html
1.在表格的单元定义中加入buttons定义，例如：
buttons: [{
name: 'upload',
title:__('Upload'),
text: '',
icon: 'fa fa-cloud-upload',
classname: 'btn btn-xs btn-info btn-dialog',
url: 'kaoshi/daoru/addtoplan',
}]
2.加入计划
public function addtoplan($ids=null){
$row = $this->model->get($ids);
// halt($row);
if (!$row) {
$this->error(__('No Results were found'));
}
if ($this->request->isPost()) {
$params = $this->request->post("row/a");
if ($params) {
$params = $this->preExcludeFields($params);
$result = false;
Db::startTrans();
try {
//是否采用模型验证
if ($this->modelValidate) {
$name = str_replace("\\model\\", "\\validate\\", get_class($this->model));
$validate = is_bool($this->modelValidate) ? ($this->modelSceneValidate ? $name . '.edit' : $name) : $this->modelValidate;
$row->validateFailException(true)->validate($validate);
}
$result = $row->allowField(true)->save($params);
// 这里就是加入计划的真正逻辑
$plan_id = explode(',', $params['plan_ids']);
foreach($plan_id as $k=>$v){
$userplan = [];
$user_ids = explode(',', $params['ids_str']);
if ($params['ids_str']=='' || count($user_ids) < 1) {
$this->error('当前考生ids为空！');
}
foreach ($user_ids as $key => $value) {
$count=Db::name('KaoshiUserPlan')->where('plan_id',$v)->where('user_id',$value)->count();
if(!$count){
array_push($userplan, ['plan_id' => $v, 'user_id' => $value]);
}
}
$result = Db::name('KaoshiUserPlan')->insertAll($userplan);
}
Db::commit();
} catch (ValidateException $e) {
Db::rollback();
$this->error($e->getMessage());
} catch (PDOException $e) {
Db::rollback();
$this->error($e->getMessage());
} catch (Exception $e) {
Db::rollback();
$this->error($e->getMessage());
}
if ($result !== false) {
$this->success();
} else {
$this->error(__('是不允许重复加入计划!'));
}
}
$this->error(__('Parameter %s can not be empty', ''));
}
$this->view->assign("row", $row);
return $this->view->fetch();
}
3.新增考试aatoplan.html
<form id="edit-form" class="form-horizontal" role="form" data-toggle="validator" method="POST" action="">
<div class="form-group">
<label class="control-label col-xs-12 col-sm-2">{:__('当前考生的id列表')}:</label>
<div class="col-xs-12 col-sm-8">
<textarea id="c-ids_str" data-rule="required" class="form-control" rows="5" name="row[ids_str]" cols="50" readonly>{$row.ids_str|htmlentities}</textarea>
</div>
</div>
<!-- 新增备选的考试计划 -->
<div class="form-group">
<label for="c-type" class="control-label col-xs-12 col-sm-2">{:__('选择考试计划')}:</label>
<div class="col-xs-12 col-sm-8">
<input id="c-type" data-rule="required" class="form-control selectpage" data-multiple="true" data-source="kaoshi/examination/plan/index" placeholder="类型为自定义,可任意输入,无需确认,自动添加" data-primary-key="id" data-field="plan_name" name="row[plan_ids]" type="text" value="">
</div>
</div>
<div class="form-group layer-footer">
<label class="control-label col-xs-12 col-sm-2"></label>
<div class="col-xs-12 col-sm-8">
<button type="submit" class="btn btn-success btn-embossed disabled">{:__('OK')}</button>
<button type="reset" class="btn btn-default btn-embossed">{:__('Reset')}</button>
</div>
</div>
</form>
