后台添加数据的时候增加通过表格导入功能
如下图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).'条数据');
}
