Fastadmin导入导出Excel

参考项目：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());
	}


}