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).'条数据');}