## 注意事项
* 导入的excel不能是从页面导出的excel,要新建一个
* 覆盖backend里面的import方法，需修改为public,以及引入相关类
* excel第一列为数据库字段名
* 导入excel日期列时需转换时间戳$v = date("Y-m-d H:i", ($v-25569) * 86400);
## 数据库增加导入权限
```
INSERT INTO `yibao`.`fa_auth_rule` (`id`, `type`, `pid`, `name`, `title`, `icon`, `url`, `condition`, `remark`, `ismenu`, `menutype`, `extend`, `py`, `pinyin`, `createtime`, `updatetime`, `weigh`, `status`)
VALUES ('156', 'file', '93', 'yibao_user/import', '导入', 'fa fa-circle-o', '', '', '', '0', NULL, '', 'dr', 'daoru', '1684381267', '1684381267', '0', 'normal');
```
## html页面
```
<a href="javascript:;" data-url="ajax/upload" id="btn-import-file" class="btn btn-info btn-import{:$auth->check('yibao_user/import')?'':'hide'}" title="{:__('Import')}" data-mimetype="csv,xls,xlsx" data-multiple="false" ><i class="fa fa-upload"></i> {:__('Import')}</a>
<a href="/template/yibaouser.xls" class="btn btn-primary">下载导入模板</a>
```
## 控制器
```
use Exception;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx;
use PhpOffice\PhpSpreadsheet\Reader\Xls;
use PhpOffice\PhpSpreadsheet\Reader\Csv;
use think\Db;
use think\db\exception\BindParamException;
use think\db\exception\DataNotFoundException;
use think\db\exception\ModelNotFoundException;
use think\exception\DbException;
use think\exception\PDOException;
use think\exception\ValidateException;
use think\response\Json;
/**
* 导入
*
* @return void
* @throws PDOException
* @throws BindParamException
*/
public 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();
}
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 = [];
//加载文件
$insert = [];
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($k=='begindate' || $k=='enddate'){
if(empty($v)){
$v = '1970-01-01';
}
}
$row[$k] = $v;
}
if ($row) {
$insert[] = $row;
}
}
if (!$insert) {
$this->error(__('No rows were updated'));
}
Db::startTrans();
try {
//是否包含admin_id字段
/* $has_admin_id = false;
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;
}
}
} */
$this->model->saveAll($insert);
} 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());
}
}
```
