Laravel开发excel信息查询系统过程（后台）
2019-09-23 admin laravel 2159
以前的查询项目使用了原生php，通过phpexcel导入表格数据，并提出所有字段给用户选择两个，作为查询条件。
虽然可以实现查询功能，但html,js,php代码混杂，后期修改较为繁琐，手写SQL安全性也无法保证。
因此，准备用全栈框架laravel改写。为提升稳定性和安全性，前后台用户分离，前台使用laravel默认创建的登陆模板，后台使用laravel-admin快速创建。思路如下:
一、导入数据
使用excel3.1导入。先创建后台bgsx数据表和相关controller，用来管理各种查询项目数据。字段为：xmmc项目名称，bm表名，cxtj查询条件，sjsl数据数量，sfkf是否开放，beizhu备注。模型如下：
namespace App\Models;
use Illuminate\Database\Eloquent\Model;
use Encore\Admin\Facades\Admin;
class bgsxModel extends Model
{
protected $table = 'bgsx';
//以字符串的形式存储，定义它的访问器和修改器：
public function getcxtjAttribute($value)
{
return json_decode($value,true);//从数据库读取json字符串，解码成php数组
}
public function setcxtjsAttribute($value)
{
$value = json_encode($value);//编码转换成json字符串存储格式,保存在数据库
$this->attributes['cxtj'] = $value;
}
}导入过程，用户填写项目名称（唯一），提交后，使用model读取excel，获取字段创建临时表（如有重复先删除表）；然后分批导入到临时表，导入完成后，将临时表重命名为md5(xmmc)，防止重复。最后，将项目名称和新表名及查询条件，写入bgsx表。
创建临时表LssjbModel放在App\Model目录下，以导入到lssjb数据表：
namespace App\Models;
use Illuminate\Database\Eloquent\Model;
use Encore\Admin\Facades\Admin;
class LssjbModel extends Model
{
protected $table = 'lssjb';
protected $guarded = [];//禁止批量赋值的字段
public $timestamps = false;//禁用自动创建时间
}导入excel到临时表，在App\Models目录下建立LssjImport，按 1000 条为基准取出导入的model如下：
namespace App\Models;
use App\Models\LssjbModel;
use Maatwebsite\Excel\Concerns\ToModel;
use Maatwebsite\Excel\Concerns\WithBatchInserts;
use Maatwebsite\Excel\Concerns\WithChunkReading;
use Maatwebsite\Excel\Concerns\WithHeadingRow;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;//创建表
class LssjImport implements ToModel, WithBatchInserts, WithChunkReading, WithHeadingRow{
public function model(array $row)
{
if (!Schema::hasTable('lssjb')) {//如果临时数据表不存在，则创建
$keys = array_keys($row);
Schema::create('lssjb', function(Blueprint $table) use ($keys)//create接收两个参数：表名，$table（Blueprint 实例）的闭包。
{
$table->increments('id');//主键
foreach($keys as $key => $value){
if($key == 0){
$table->string($keys[$key])->nullable();//->unique(); 唯一，根据字段酌情使用
}else{
$table->string($keys[$key])->nullable();
}
}
});
}
return new LssjbModel($row);//批量赋值到数据库
}
//批量导入1000条
public function batchSize(): int {
return 1000;
}
//以1000条数据基准切割数据
public function chunkSize(): int {
return 1000;
}
}提交数据时的流程：用户输入项目名称-->选择excel上传-->读取excel-->创建临时数据表-->通过model分批导入数据-->将临时数据表改名为正式表。
保存成功后，默认将所有字段设置为查询条件。如果需要修改，可用右键选择刚才上传的项目，点击编辑，修改查询条件。
相关的controller有：BgsxController.php
namespace App\Admin\Controllers;
use App\Models\bgsxModel;
use App\Http\Controllers\Controller;
use App\Admin\Controllers\CktableController;
use Encore\Admin\Controllers\HasResourceActions;
use Encore\Admin\Form;
use Encore\Admin\Grid;
use Encore\Admin\Layout\Content;
use Encore\Admin\Show;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;
class BgsxController extends Controller
{
use HasResourceActions;
public function index(Content $content)
{
return $content
->header('查询项目列表')
->description('如需添加新项目请点【上传】')
->body($this->grid());
}
public function show($id, Content $content)
{
return $content
->header('Detail')
->description('description')
->body($this->detail($id));
}
public function edit($id, Content $content)
{
return $content
->header('编辑项目')
->description('description')
->body($this->form($id)->edit($id));
}
public function create(Content $content)
{
return $content
->header('新项目数据')
->description('description')
->body($this->form());
}
protected function grid()
{
$grid = new Grid(new bgsxModel);
$grid->id('ID')->sortable();
//$grid->bm('表名');
$grid->xmmc('项目名称');
$grid->cxtj('查询条件');
$grid->sjl('数据量');
$grid->sfkf('是否开放');
$grid->beizhu('查询说明');
$grid->created_at('创建时间')->sortable();//可排序
$grid->updated_at('更新时间')->sortable();
$grid->quickSearch('xmmc');//快捷搜索
$grid->tools(function (Grid\Tools $tools) {$tools->append(new ImportController());});//上传按钮
$grid->actions(function ($actions) {$actions->add(new CktableController);});//添加查看项目数据按钮
$grid->disableCreation();//禁用新增按钮
//筛选功能
$grid->filter(function ($filter) {
$filter->disableIdFilter(); // 去掉默认的id过滤器
$filter->like('bm','表名'); // 按字段模糊筛选
$filter->like('xmmc','项目名称'); // 按字段模糊筛选
$filter->between('created_at','创建时间')->datetime(); // 设置created_at字段的范围筛选
$filter->between('updated_at','更新时间')->datetime(); // 设置created_at字段的范围筛选
});
return $grid;
}
protected function detail($id)
{
$show = new Show(bgsxModel::findOrFail($id));
$show->id('ID');
$show->bm('表名');
$show->xmmc('项目名称');
$show->cxtj('查询条件')->as(function ($cxtjs) {
$cxtj = implode(',',$cxtjs);
return $cxtj;
});
$show->sjl('数据量');
$show->sfkf('是否开放');
$show->beizhu('查询说明');
$show->created_at('创建时间');
$show->updated_at('更新时间');
return $show;
}
protected function form()
{
if (isset(func_get_args()[0])){//获取传递的id
$id = func_get_args()[0];
$bm = DB::table('bgsx')->where('id', $id)->value('bm');
$columns = Schema::getColumnListing($bm);//获取所有字段
array_splice($columns,0,1);//去除id
$tjsz=array();
foreach($columns as $zdm){
$tjsz[$zdm] = $zdm;
}
$form = new Form(new bgsxModel);
$form->text('bm', '表名')->readonly()->required()->default('lssjb');
$form->text('xmmc', '项目名称')->creationRules(['required', 'min:3', 'max:16', "unique:bgsx"]);//提交表单时检查数据是否已经存在
$form->multipleSelect('cxtj', '查询条件')->options($tjsz);
$form->display('创建时间');
$form->display('更新时间');
$form->disableReset(); //关闭撤销键
}else{
$form = new Form(new bgsxModel);
$form->text('bm', '表名')->readonly()->required()->default('lssjb');
$form->text('xmmc', '项目名称')->creationRules(['required', 'min:3', 'max:16', "unique:bgsx"]);//提交表单时检查数据是否已经存在
$form->multipleSelect('cxtj', '查询条件');
$form->display('创建时间');
$form->display('更新时间');
$form->disableReset(); //关闭撤销键
}
return $form;
}
}ImportController.php
namespace App\Admin\Controllers;
use App\Models\bgsxModel;
use App\Models\LssjbModel;
use App\Models\LssjImport;
use Maatwebsite\Excel\Facades\Excel;
use Maatwebsite\Excel\HeadingRowImport;
use Illuminate\Support\Facades\Schema;//创建表
use Maatwebsite\Excel\Imports\HeadingRowFormatter;
HeadingRowFormatter::default('none');//不格式化标题数据，这两行是关键，少了就无法读取标题导入了。
use Encore\Admin\Controllers\HasResourceActions;
use Encore\Admin\Form;
use Encore\Admin\Grid;
use Encore\Admin\Layout\Content;
use Encore\Admin\Show;
use Illuminate\Support\Facades\DB;
use Encore\Admin\Actions\Action;
use Illuminate\Http\Request;
class ImportController extends Action
{
use HasResourceActions;
public $name = '新增项目';
protected $selector = '.import-post';
public function handle(Request $request)
{
set_time_limit(60);//导入时长限制1分钟之内完成
ini_set('memory_limit', '100M');//我有内存 我豪啊
$file = $request->file('file');//上传的文件
$bm = 'lssjb';//临时表名
$xmmc = str_replace(' ','',$request->xmmc);//项目名称
$zsbm = md5($xmmc);//生成唯一正式表名
$beizhu = $request->beizhu;//备注的查询说明
if (Schema::hasTable($bm)) {//如果临时数据表已存在，则删除
Schema::drop($bm);
}
Excel::import(new LssjImport, $file);//用model分批导入到临时数据表lssjb
$sjl = LssjbModel::count();//数据量
if ($sjl > 0){
if (Schema::hasTable($zsbm)) {//如果该项目正式数据表已存在，则删除
Schema::drop($zsbm);
}
Schema::rename($bm, $zsbm); //导入成功，更改临时表为正式表名
//写入bgsx
$cxtjs = Schema::getColumnListing($zsbm);//获取所有字段
array_splice($cxtjs,0,1);//去除id
$creatbgsx = new bgsxModel;
$creatbgsx->bm = $zsbm;
$creatbgsx->xmmc = $xmmc;
$creatbgsx->sjl = $sjl;
$creatbgsx->sfkf = '关闭';//默认关闭查询
$creatbgsx->cxtj = json_encode($cxtjs,true);//默认所有字段作为查询条件，编码转换成json字符串存储格式
$creatbgsx->beizhu = $beizhu;
$creatbgsx->save();
return $this->response()->success('导入完成，请设置查询条件！')->refresh();
}else{
return $this->response()->error('数据导入失败，请检查Excel内容是否合规！');
}
}
public function form()
{
$this->text('xmmc', '项目名称')->rules('required|min:3|max:16');
$this->text('beizhu', '查询说明')->rules('required|min:3|max:160');
$this->file('file', '选择excel文件')->required()->rules('mimes:xls,xlsx');
}CktableController.php
namespace App\Admin\Controllers;
use Encore\Admin\Actions\RowAction;
class CktableController extends RowAction
{
public $name = '查看数据';
/**
* @return string
*/
public function href()
{
$key = $this->getKey();
return "/admin/cxbgsj?id=$key";
}
}用于后台编辑各项目具体数据的TableController.php
namespace App\Admin\Controllers;
use App\Models\TableModel;
use App\Models\TableEditModel;
use App\Http\Controllers\Controller;
use Encore\Admin\Controllers\HasResourceActions;
use Encore\Admin\Form;
use Encore\Admin\Grid;
use Encore\Admin\Layout\Content;
use Encore\Admin\Show;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;
use Encore\Admin\Grid\Displayers\DropdownActions;
class TableController extends Controller
{
use HasResourceActions;
/**
* Index interface.
*
* @param Content $content
* @return Content
*/
public function index(Content $content)
{
if(isset($_GET["id"])){//初次进入，保存表名
$id = $_GET['id'];
$bm = DB::table('bgsx')->where('id', $id)->value('bm');
$zwbm = DB::table('bgsx')->where('id', $id)->value('xmmc');
session(['bm' => $bm]);//存储bm到session
return $content
->header($zwbm)
->description($bm)
->body($this->grid());
}else{
$bm = session('bm');//后面列表跳转等，读取bm
return $content
->header('项目名称：')
->description($bm)
->body($this->grid());
}
}
/**
* Show interface.
*
* @param mixed $id
* @param Content $content
* @return Content
*/
public function show($id,Content $content)
{
$bm = session('bm');//从session中获取bm
return $content
->header('详情')
->description('description')
->body($this->detail($id));
}
/**
* Edit interface.
*
* @param mixed $id
* @param Content $content
* @return Content
*/
public function edit($id, Content $content)
{
return $content
->header('修改')
->description('查询条件')
->body($this->form()->edit($id));
}
/**
* Create interface.
*
* @param Content $content
* @return Content
*/
public function create(Content $content)
{
return $content
->header('设置')
->description('查询条件')
->body($this->form());
}
protected function grid()
{
$bm = session('bm');//从session中获取bm
$grid = new Grid(new TableModel($bm));
$zdsz = Schema::getColumnListing($bm);//获取所有字段
unset ($zdsz['0']);//去除id，行内编辑加->editable()
foreach ($zdsz as $zdm){
$grid->$zdm($zdm)->sortable()->display(function($asset_note) {return str_limit($asset_note, 70, '...');});
}
$grid->quickSearch($zdsz);//快捷搜索
$zwbm = DB::table('bgsx')->where('bm', $bm)->value('xmmc');//导出文件名为项目名
$grid->header(function ($query) { //表格头部以及相应模型TableModel.php
namespace App\Models;
use Illuminate\Database\Eloquent\Model;
use Encore\Admin\Facades\Admin;
class TableModel extends Model
{
protected $table;//创建属性
public function __construct($bm)//接收控制器变量bm,在函数内部通过func_get_args()函数就可以取得所有传入的参数
{
$this->table = $bm;//赋值给table
}
}TableEditModel.php
namespace App\Models;
use Illuminate\Database\Eloquent\Model;
use Encore\Admin\Facades\Admin;
class TableEditModel extends Model
{
protected $table;//创建属性
const UPDATED_AT = null;//去除编辑保存数据时的updated_at字段
const CREATED_AT = null;//去除新建一行数据时的created_at字段
public function __construct()//接收控制器变量bm,在函数内部通过func_get_args()函数就可以取得所有传入的参数
{
$this->table = session('bm');//赋值给table
}
}后台路由
$router->get('/', 'HomeController@index')->name('admin.home');
$router->resource('/users', UserController::class);//前台用户
$router->resource('/blog', BlogController::class);//博客
$router->resource('/bgsx', BgsxController::class);//excel查询系统
$router->resource('/cxbgsj', TableController::class);//查看表格数据后台创建完成。