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