参考项目:p5113
一、导出
index.html
<a href="javascript:;" class="btn btn-success btn-export {:$auth->check('kycms/user/export')?'':'hide'}"><i class="fa fa-plus"></i> {:__('全部导出')}</a>
JS
$(document).on("click",".btn-export",function(){
var options = table.bootstrapTable('getOptions');
var search = options.queryParams({});
var filter = search.filter;
var op = search.op;
location.href = Config.moduleurl+'/p5130/bianma/export?hdid='+Config.p5130.ids
})
PHP
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Cell\DataType;
public function export(){
set_time_limit(0);
$filter = $this->request->param("filter");
$arr = json_decode($filter,true);
//print_r($arr);die;
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
// 设置列宽
$spreadsheet->getActiveSheet()->getColumnDimension('B')->setWidth(20);
$spreadsheet->getActiveSheet()->getColumnDimension('C')->setWidth(20);
$spreadsheet->getActiveSheet()->getColumnDimension('G')->setWidth(20);
$spreadsheet->getActiveSheet()->setCellValueExplicit("A1", "ID", DataType::TYPE_STRING);
$spreadsheet->getActiveSheet()->setCellValueExplicit("B1", "用户名", DataType::TYPE_STRING);
$spreadsheet->getActiveSheet()->setCellValueExplicit("C1", "手机号", DataType::TYPE_STRING);
$spreadsheet->getActiveSheet()->setCellValueExplicit("D1", "会员级别", DataType::TYPE_STRING);
$spreadsheet->getActiveSheet()->setCellValueExplicit("E1", "性别", DataType::TYPE_STRING);
$spreadsheet->getActiveSheet()->setCellValueExplicit("F1", "年龄", DataType::TYPE_STRING);
$spreadsheet->getActiveSheet()->setCellValueExplicit("G1", "学历", DataType::TYPE_STRING);
$spreadsheet->getActiveSheet()->setCellValueExplicit("H1", "身高", DataType::TYPE_STRING);
$users = Db::name("kycms_user")->order("id desc")->select();
foreach($users as $k=>$v){
$index=$k+2;
// 设置单元格的值
$sheet->setCellValue('A'.$index, $v['id']);
$sheet->setCellValue('B'.$index, $v['name']);
$sheet->setCellValue('C'.$index, $v['mobile']);
if($v['jibie']==3){
$jibie='钻石';
}else if($v['jibie']==2){
$jibie='高级';
}else{
$jibie='普通';
}
$sheet->setCellValue('D'.$index, $jibie);
$sheet->setCellValue('E'.$index, $v['sex']);
$sheet->setCellValue('F'.$index, $v['age']);
$sheet->setCellValue('G'.$index, $v['xueli']);
$sheet->setCellValue('H'.$index, $v['height']);
}
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="'.date('Y-m-d').'.xlsx"');
header('Cache-Control: max-age=0');
$writer = new Xlsx($spreadsheet);
$writer->save('php://output');
}
二、导入
<a href="javascript:;" class="btn btn-success btn-import {:$auth->check('kct/bianma/import')?'':'hide'}" title="{:__('导入')}" ><i class="fa fa-plus"></i> {:__('导入')}</a>
<a href="/kct/moban.xlsx" class="btn btn-danger btn-more"><i class="fa fa-cog"></i> {:__('模板下载')}</a>
use PhpOffice\PhpSpreadsheet\IOFactory;
public function import()
{
$file = $this->request->request('file');
Db::startTrans();
try{
$piciid = $this->request->param("piciid");
if(!$piciid){
$this->error("批次不存在");
}
$inputFileType = IOFactory::identify('.'.$file);
$reader = IOFactory::createReader($inputFileType);
$spreadsheet = $reader->load(ROOT_PATH . 'public' . DS . $file);
$sheetData = $spreadsheet->getActiveSheet()->toArray(null, true, true, true);
foreach($sheetData as $k=>$v){
if($k>1 && $v['C']){
$temp =Db::name("kct_bianma")->where(['bianma'=>$v['C']])->find();
if($temp){
$this->error("编码".$v['C']."已经存在");
}
$d=[];
$d['createtime']=time();
$d['updatetime']=time();
$d['kct_pici_id']=$piciid;
$d['bianma']=$v['C'];
$d['num']=0;
Db::name("kct_bianma")->insert($d);
}
}
Db::commit();
$this->success("");
} catch (ValidateException $e) {
Db::rollback();
$this->error($e->getMessage());
} catch (PDOException $e) {
Db::rollback();
$this->error($e->getMessage());
} catch (Exception $e) {
Db::rollback();
$this->error($e->getMessage());
}
}