因为用原来的导入比较慢,所以进行部分的更改。
不需要引入包,直接复制就可以用,仅限于fastadmin!!!
/**
* 导入
*/
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') {
$reader = new Csv();
} elseif ($ext === 'xls') {
$reader = new Xls();
} else {
$reader = new Xlsx();
}
$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') {
$v['COLUMN_COMMENT'] = explode(':', $v['COLUMN_COMMENT'])[0]; //字段备注有:时截取
$fieldArr[$v['COLUMN_COMMENT']] = $v['COLUMN_NAME'];
} else {
$fieldArr[$v['COLUMN_NAME']] = $v['COLUMN_NAME'];
}
}
// 导入数据批次大小
$batchSize = 1000;
$totalInserted = 0;
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;
}
}
$insertData = [];
for ($currentRow = 2; $currentRow <= $allRow; $currentRow++) {
$range = 'A' . $currentRow . ':' . $allColumn . $currentRow;
$rowData = $currentSheet->rangeToArray($range, null, true, false);
if (!empty($rowData)) {
$values = $rowData[0];
$row = [];
$temp = array_combine($fields, $values);
foreach ($temp as $k => $v) {
if (isset($fieldArr[$k]) && $k !== '') {
$row[$fieldArr[$k]] = $v;
}
}
if ($row) {
$insertData[] = $row;
}
// 达到批次大小或到达文件末尾时执行批量插入
if (count($insertData) >= $batchSize || $currentRow == $allRow) {
$this->batchInsertData($insertData,$fieldArr);
$totalInserted += count($insertData);
$insertData = [];
}
}
}
} catch (Exception $exception) {
$this->error($exception->getMessage());
}
$this->success(__('Total inserted rows: %d', $totalInserted));
}
// 批量插入数据到数据库
protected function batchInsertData($data,$fieldArr)
{
try {
// 是否包含 admin_id 字段
$hasAdminId = false;
foreach ($fieldArr as $name => $key) {
if ($key == 'admin_id') {
$hasAdminId = true;
break;
}
}
if ($hasAdminId) {
$auth = Auth::instance();
foreach ($data as &$val) {
if (!isset($val['admin_id']) || empty($val['admin_id'])) {
$val['admin_id'] = $auth->isLogin() ? $auth->id : 0;
}
}
}
// 使用事务批量插入数据
Db::startTrans();
$this->model->saveAll($data);
Db::commit();
} catch (PDOException $exception) {
$msg = $exception->getMessage();
if (preg_match("/.+Integrity constraint violation: 1062 Duplicate entry '(.+)' for key '(.+)'/is", $msg, $matches)) {
$msg = "导入失败,包含【{$matches[1]}】的记录已存在";
};
Db::rollback();
$this->error($msg);
} catch (Exception $e) {
Db::rollback();
$this->error($e->getMessage());
}
}
如果导入超时,修改一下服务器的配置。