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',
