1、在头部增加
use fastHttp; use PhpOfficePhpSpreadsheetCellCoordinate; use PhpOfficePhpSpreadsheetReaderCsv; use PhpOfficePhpSpreadsheetReaderXls; use PhpOfficePhpSpreadsheetReaderXlsx;
2、文件末尾增加方法
/** * 表格导入数据 * @return string|void * @throws Exception * Author: Wusn <958342972@qq.com> * DateTime: 2023/9/4 15:49 */ public function importf(){ if($this->request->isPost()){ // parent::import(); $post = $this->request->post('row/a'); $mid = $POST@['mid']; self::imports($mid); }else{ $this->assign('download_template', url('ldcms/document/downloads')); $this_html = <<<html <div class="form-inline"> <div class="input-group"> <input id="c-file" data-rule="required" class="form-control" size="50" name="file" type="text" value="" data-target="#msg-box"> <div class="input-group-addon no-border no-padding"> <span><button type="button" id="plupload" class="btn btn-danger plupload" data-input-id="c-file" data-url="ajax/upload" data-mimetype="xlsx,xls,csv" data-multiple="false"><i class="fa fa-upload"></i> 上传</button></span> </div> <sapn id="msg-box" class="msg-box n-right" for="c-file"></sapn> </div> </div> html; return Form::instance() ->setFormItem('down', '模板', 'custom','',[ 'extend_html'=>'<a href="'.url('ldcms/document/downloads').'" style="line-height: 34px; height: 34px;" target="_blank">点击下载</a>', 'value'=>'', ]) ->setFormItem('mid', '模型', 'string', '', [ 'class' => 'hidden', 'value' => $this->request->get('mid') ]) // ->setFormItem('cid', '栏目', 'select', 'required', ['content_list' => $this->category_for_name,'extend_html'=>'data-live-search="true"']) ->setFormItem('import', '上传文件', 'custom','',[ 'extend_html'=>$this_html, ]) ->fetch(); // return $this->view->fetch('import_file'); } } /** * 下载模板文件 * Author: Wusn <958342972@qq.com> * DateTime: 2023/8/29 17:51 */ public function downloads(){ // $file = ROOT_PATH . 'public' . DS . 'assets' . DS . 'addons' . DS . 'ldcms' . DS . 'template.csv'; // if (is_file($file)){ // Http::sendToBrowser($file,false); // }else{ // $this->error('文件不存在'); // } /* 输入到CSV文件 */ $html = ""; /* 输出表头 */ $filter = ['栏目','标题','内容','扩展字段1','扩展字段2']; foreach ($filter as $key => $title) { $html .= $title . " ,"; } $html .= " "; /* 输出CSV文件 */ header("Content-type:text/csv"); header("Content-Disposition:attachment; filename=template.csv"); echo $html; exit(); } /** * 导入方法 * @param $mid * @throws PDOException * @throws PhpOfficePhpSpreadsheetException * @throws hinkdbexceptionBindParamException * @throws hinkdbexceptionDataNotFoundException * @throws hinkdbexceptionModelNotFoundException * @throws hinkexceptionDbException * Author: Wusn <958342972@qq.com> * DateTime: 2023/9/4 15:57 */ protected function imports($mid) { $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')); } // halt($ext); 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, " "); $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 . " "); } else { fwrite($fp, '"' . str_replace(['"', ','], ['""', '","'], $line) . "" "); } $n++; } fclose($file) || fclose($fp); $reader = new Csv(); } elseif ($ext === 'xls') { $reader = new Xls(); } else { $reader = new Xlsx(); } //导入文件首行类型,默认是注释,如果需要使用字段名称请使用name $importHeadType = isset($this->importHeadType) ? $this->importHeadType : 'comment'; $table = $this->model->getQuery()->getTable(); $database = hinkConfig::get('database.database'); $fieldArr = []; $list = db()->query("SELECT COLUMN_NAME,COLUMN_COMMENT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ? AND TABLE_SCHEMA = ?", [$table, $database]); $add_field = array( ['COLUMN_NAME' => 'content', 'COLUMN_COMMENT' => '内容'], ['COLUMN_NAME' => 'cid', 'COLUMN_COMMENT' => '栏目'], ); $list = array_merge($list, $add_field); //获取扩展字段 $ext_field = ppdminmodelldcmsFields::instance()->where('mid', $mid)->select(); $ext_arr = []; foreach ($ext_field as $k => $v) { $ext_arr[] = [ 'COLUMN_NAME' => $v['field'], 'COLUMN_COMMENT' => $v['title'] ]; } $list = array_merge($list, $ext_arr); 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']; } } // dump($list); //对应栏目 $category_arr = []; foreach ($this->categorys as $value){ $category_arr[$value['id']] = $value['name']; } $category_arr_key = array_flip($category_arr); // dump($category_arr_key); // dump($category_arr); //加载文件 $insert = []; 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[] = trim($val); } } //dump($fields); for ($currentRow = 2; $currentRow <= $allRow; $currentRow++) { $values = []; for ($currentColumn = 1; $currentColumn <= $maxColumnNumber; $currentColumn++) { $val = $currentSheet->getCellByColumnAndRow($currentColumn, $currentRow)->getValue(); $values[] = is_null($val) ? '' : trim($val); } $row = []; $temp = array_combine($fields, $values); // dump($temp); foreach ($temp as $k => $v) { if(in_array($v,$category_arr) && $fieldArr[$k] == 'cid'){ $v = $category_arr_key[$v]; } if (isset($fieldArr[$k]) && $k !== '') { $row[$fieldArr[$k]] = $v; } } if ($row) { $row['mid'] = $mid; $insert[] = $row; } } } catch (Exception $exception) { $this->error($exception->getMessage()); } if (!$insert) { $this->error(__('No rows were updated')); } // halt($insert); //添加数据进数据库 foreach ($insert as $key => $item) { $data = $item; $data['show_time'] = isset($data['show_time'])&&!empty($data['show_time']) ? $data['show_time'] : time(); // dump($data); Db::startTrans(); try { $res = $this->model::create($data, true); $id = $res->id; $content_data['content'] = $data['content']; /*数据写入content*/ $content_data['id'] = $id; // dump($content_data); $this->model->saveContent($content_data); /*数据写入扩展表*/ $extend_data = $data; $extend_data['id'] = $id; $this->model->savaExtend($this->extend_table_name, $extend_data); Db::commit(); } catch (ValidateException | PDOException | Exception $e) { Db::rollback(); $this->error($e->getMessage()); } } $this->success(); }
3、在语言包中增加
'Importf' => '导入数据',
4、在后台对应的模板文件中增加上上传按钮
{if $mid!=1} <a class="btn btn-primary btn-customer-import {:$auth->check('ldcms/document/uploads')?'':'hide'}" data-params="mid={$mid}" title="{:__('Importf')}"><i class="fa fa-cloud-upload"></i> {:__('Importf')}</a> {/if}
5、当前页面对应的js文件中增加以下代码
import_url: 'ldcms/document/importf',
在//为表格当定时间 上方添加以下代码
// 导入数据 $(document).on('click', '.btn-customer-import', function () { var params = $(this).data('params'); var options = table.bootstrapTable('getOptions'); var url = options.extend.import_url+"?" + params; Fast.api.open(url, $(this).data("original-title") || $(this).attr("title") || __('Add'), $(this).data() || {}); // Fast.api.open('ldcms/security/importf', '导入数据') });
在edit:xxx 控制器方法中增加下方代码
importf:function () { Controller.api.bindevent(); },
注:上传excel文件时,有错误提示!请修改上传文件配置,需要将上传的文件类型后缀添加进去
修改文件 /application/extra/upload.php
修改第24行
/** * 可上传的文件类型 */ 'mimetype' => 'jpg,png,bmp,jpeg,gif,webp,zip,rar,wav,mp4,mp3,webm,csv',