fastadmin 表格数据导入

记录：fastadmin 表格数据导入
php代码
在Backend.php文件中增加读取文件数据并返回代码

    /**
     * 读取文件数据并返回
     * @return array
     */
    protected function readFile($file)
    {        
        if (!$file) {
            $this->error(__('Parameter %s can not be empty', 'file'));
        }
        
        $filePath = ROOT_PATH . DS . 'public' . DS . $file;
        // //获取上传文件后缀
        // $temp = explode("/",$file);
        // $filename = end($temp);
        // $filePath = ROOT_PATH . "public" .DS. "uploads" .DS. date('Ymd').DS.$filename;

        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') { 
            // // 使用iconv转换文件编码至UTF-8
            // $convertedFilePath = iconv('original-input-charset', 'UTF-8', file_get_contents($filePath));
        
            // // 将转换后的内容写回文件
            // file_put_contents($filePath, $convertedFilePath);
            $reader = new Xls();
        } else {
            $reader = new Xlsx();
        }

        //加载文件
        try {

            // $spreadsheet = IOFactory::load($filePath);
            // $worksheet = $spreadsheet->getActiveSheet();
 
            // foreach ($worksheet->getRowIterator() as $row) {
            //     $cellIterator = $row->getCellIterator();
            //     $cellIterator->setIterateOnlyExistingCells(false);
            //     foreach ($cellIterator as $cell) {
            //         if ($cell !== null) {
            //             $value = $cell->getValue();
            //             echo $value . ' ';
            //         }
            //     }
            //     echo PHP_EOL;
            // }


            $PHPExcel = $reader->load($filePath); //加载文件
            if (!$PHPExcel) {
                $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;
                }
            }

            //读取行数据
            $row = [];
            for ($currentRow = 2; $currentRow <= $allRow; $currentRow++) {
                $values = [];
                for ($currentColumn = 1; $currentColumn <= $maxColumnNumber; $currentColumn++) {
                    $val = $currentSheet->getCellByColumnAndRow($currentColumn, $currentRow)->getValue();
                    $values[] = is_null($val) ? '' : (string)$val;
                }

                $row[] = array_combine($fields, $values);
            }
        } catch (Exception $exception) {
            $this->error($exception->getMessage());
        }

        if (!$row) {
            $this->error(__('No rows were updated'));
        }
        return $row;
    }

处理导入数据文件

    /**
     * 重写import方法
     */
    public function import()
    {
        $file = $this->request->request('file'); //'file'为文件字段名

        $data = $this->readFile($file);
        $data = array_reverse($data);
        $chejian = new \app\admin\model\Chejian();
        $kaoheshuxing_arr = [
            '质量' => 'zhiliang',
            '产量' => 'chanliang',
        ];
        $data_insert = [];
        foreach ($data as $row){
            $ii = '';
            foreach (array_values($row) as $k => $v){
                if($v){
                    $ii = $v;
                }
            }
            if(empty($ii)){
                continue;
            }
            
            $vva = [];
            foreach ($row as $key => $vo) {               
                if(in_array($key,$this->sqlfieldname())){
                    if($key == '车间名称'){
                        if($vo){
                            $chejian_id_arr = [];
                            foreach(explode(',', $vo) as $vo_val){
                                $chejian_id = $chejian->where(['chejian_name'=>$vo_val])->value('id');
                                if(!$chejian_id){
                                    $this->error('车间：'.$vo_val.' 不存在，请先把此车间录入系统');
                                }
                                $chejian_id_arr[] = $chejian_id;
                            }
                            $vva[$this->sqlfield($key)] = implode(',', $chejian_id_arr);
                        }
                    }elseif($key == '考核属性'){
                        if($vo){
                            $flag_arr = [];
                            foreach(explode(',', $vo) as $vo_val){                                
                                if(!isset($kaoheshuxing_arr[$vo_val])){
                                    $this->error('考核属性错误：'.$vo_val.',考核属性只能是：质量,产量,销售,人资,采购,财务');
                                }
                                $flag_arr[] = $kaoheshuxing_arr[$vo_val];
                            }
                            $vva[$this->sqlfield($key)] = implode(',', $flag_arr);
                        }
                    }else{
                        $vva[$this->sqlfield($key)] = $vo;
                    }
                    
                }else{
                    $this->error('列名称错误：'.$key);
                }
            }
            if( $vva ){   
                if(isset($vva['nickname']) && $vva['nickname']){
                    $vva['jointime'] = time();
                    $data_insert[] = $vva;
                }
                unset($vva);
            }
        }
        if(!$data_insert){
            $this->error('未检测到有效的数据，姓名必填');
        }
        $res = $this->model->allowField(true)->validate(true)->saveAll($data_insert);
        unlink(ROOT_PATH.'public'.$file);
        if ($res === false) {
            $this->error('导入失败:'.$this->model->getError());
        }
        $this->success('导入成功');
    }

    // 验证-可传输字段
    public function sqlfieldname()
    {
        return ['姓名','手机号','身份证号','车间名称','考核属性','质量考核系数','产量考核系数'];
    }

    // 替换-可传输字段
    public function sqlfield($key)
    {

        $data= [
            '姓名'=>'nickname',
            '手机号'=>'mobile',
            '身份证号'=>'idnumber',
            '车间名称'=>'chejian_ids',
            '考核属性'=>'flag',
            '质量考核系数'=>'zhiliang_xishu',
            '产量考核系数'=>'chanliang_xishu',
        ];
        return $key?$data[$key]:$data;
    }

html代码

{:build_toolbar('import')}
<a href="__ROOT__/public/uploads/user.xlsx" class="btn btn-success btn-export" title="{:__('Export')}" ><i class="fa fa-download"></i>{:__('下载模版')}</a>