FastAdmin后端列表导入表格数据

后台添加数据的时候增加通过表格导入功能

如下图index.html页面增加导入和模板下载按钮代码如下

<div class="panel panel-default panel-intro"> {:build_heading()} <div class="panel-body"> <div id="myTabContent" class="tab-content"> <div class="tab-pane fade active in" id="one"> <div class="widget-body no-padding"> <div id="toolbar" class="toolbar"> <a href="javascript:;" class="btn btn-primary btn-refresh" title="{:__('Refresh')}" ><i class="fa fa-refresh"></i> </a> <a href="javascript:;" class="btn btn-success btn-add {:$auth->check('kecheng/english/add')?'':'hide'}" title="{:__('Add')}" ><i class="fa fa-plus"></i> {:__('Add')}</a> <!-- <a href="javascript:;" class="btn btn-success btn-edit btn-disabled disabled {:$auth->check('kecheng/english/edit')?'':'hide'}" title="{:__('Edit')}" ><i class="fa fa-pencil"></i> {:__('Edit')}</a>--> <a href="javascript:;" class="btn btn-danger btn-del btn-disabled disabled {:$auth->check('kecheng/english/del')?'':'hide'}" title="{:__('Delete')}" ><i class="fa fa-trash"></i> {:__('Delete')}</a> <!-- 增加的两个按钮--> {:build_toolbar('import')} <a href="__ROOT__/public/export/english.xlsx" class="btn btn-success btn-export" title="{:__('Export')}" ><i class="fa fa-download"></i>{:__('下载模版')}</a> <a href="javascript:;" class="btn btn-danger btn-del btn-disabled disabled {:$auth->check('kecheng/english/del')?'':'hide'}" title="{:__('Delete')}" ><i class="fa fa-trash"></i> {:__('Delete')}</a> <!-- 增加的两个按钮--> </div> <table id="table" class="table table-striped table-bordered table-hover table-nowrap" data-operate-edit="{:$auth->check('kecheng/english/edit')}" data-operate-del="{:$auth->check('kecheng/english/del')}" width="100%"> </table> </div> </div> </div> </div> </div>

对应的 js 文件设置处理导入逻辑的后台方法即import_url

// 初始化表格参数配置 Table.api.init({ extend: { index_url: 'kecheng/english/index' + location.search, add_url: 'kecheng/english/add'+ location.search, edit_url: 'kecheng/english/edit', del_url: 'kecheng/english/del', multi_url: 'kecheng/english/multi', import_url: 'kecheng/english/import'+ location.search,//此处设置处理导入的后台方法location.search是带url中所传的参数 table: 'kecheng_english', } });

重写app\admin\library\traits\Backend.php中的readFile方法

/** * 读取文件数据并返回 * @return array */ protected function readFile($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(); } // 加载文件 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[] = $this->convertToPlainText($val); // 将字段名转换为纯文本 } } // 读取行数据 $row = []; for ($currentRow = 2; $currentRow <= $allRow; $currentRow++) { $values = []; for ($currentColumn = 1; $currentColumn <= $maxColumnNumber; $currentColumn++) { $val = $currentSheet->getCellByColumnAndRow($currentColumn, $currentRow)->getValue(); $values[] = $this->convertToPlainText($val); // 将单元格值转换为纯文本 } $row[] = array_combine($fields, $values); } } catch (Exception $exception) { $this->error($exception->getMessage()); } if (!$row) { $this->error(__('No rows were updated')); } return $row; } /** * 将值转换为纯文本 * @param mixed $value * @return string */ protected function convertToPlainText($value) { if ($value instanceof \PhpOffice\PhpSpreadsheet\RichText\RichText) { // 如果是富文本对象，提取所有文本内容 $plainText = ''; foreach ($value->getRichTextElements() as $element) { $plainText .= $element->getText(); } return $plainText; } elseif (is_null($value)) { // 如果是空值，返回空字符串 return ''; } else { // 其他情况，强制转换为字符串 return (string) $value; } }

方法返回以第一行为key的数组。如下：

array(1) { [0] => array(12) { ["序号"] => string(1) "1" ["年级"] => string(24) "人教PEP三年级上册" ["单元"] => string(6) "Unit 1" ["单词"] => string(5) "ruler" ["音标"] => string(14) "/ˈruːlə(r)/" ["词义"] => string(6) "尺子" ["词组"] => string(26) "a ruler （一把尺子）" ["例句"] => string(78) "I need a ruler to draw a straight line.（我需要一把尺子画直线。）" ["正确选项"] => string(6) "尺子" ["错误选项1"] => string(6) "筷子" ["错误选项2"] => string(6) "绳子" ["错误选项3"] => string(6) "汽车" } }

表格结构如下：

定义import_url对应的后台方法示例如下：

/** * 重写import方法 */ public function import() { $file = $this->request->request('file'); //'file'为文件字段名 $data_file = $this->readFile($file); $course_id = $this->request->get('course_id'); if(empty($course_id)){ $this->error('课程参数错误'); } $data = []; foreach ($data_file as $key=>$value){ if(!empty($value['单词'])){ $data[] = [ 'course_id'=>$course_id, 'nianji' => empty($value['年级']) ? '' : $value['年级'], 'danyuan' => empty($value['单元']) ? '' : $value['单元'], 'danci' => $value['单词'], 'yinbiao' => empty($value['音标']) ? '' : $value['音标'], 'ciyi' => empty($value['词义']) ? '' : $value['词义'], 'cizu' => empty($value['词组']) ? '' : $value['词组'], 'liju' => empty($value['例句']) ? '' : $value['例句'], 'zhengque' => empty($value['正确选项']) ? '' : $value['正确选项'], 'cuowu_one' => empty($value['错误选项1']) ? '' : $value['错误选项1'], 'cuowu_two' => empty($value['错误选项2']) ? '' : $value['错误选项2'], 'cuowu_three' => empty($value['错误选项3']) ? '' : $value['错误选项3'], ]; } } if(empty($data)){ $this->error('可导入数据为空'); } $this->model->saveAll($data); $this->success('成功导入'.count($data).'条数据'); }