think PHP6 excel导出

封装基础库

基础层的作用：负责封装特定功能的库或应用第三方类库

在lib层添加Excel类

在Excel类中定义静态的方法，方便调用

namespace app\admin\lib;use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;class Excel
{/*** 导出* @param array $header* @param bool $type* @param array $data* @param string $fileName* @return \think\response\Json|void* @throws \PhpOffice\PhpSpreadsheet\Exception* @throws \PhpOffice\PhpSpreadsheet\Writer\Exception*/public static function export($header = [], $type = true, $data = [], $fileName = "1910"){// 实例化类$preadsheet = new Spreadsheet();// 创建sheet$sheet = $preadsheet->getActiveSheet();// 循环设置表头数据foreach ($header as $k => $v) {$sheet->setCellValue($k, $v);}// 生成数据$sheet->fromArray($data, null, "A2");// 样式设置$sheet->getDefaultColumnDimension()->setWidth(12);// 设置下载与后缀if ($type) {header("Content-Type:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");$type = "Xlsx";$suffix = "xlsx";} else {header("Content-Type:application/vnd.ms-excel");$type = "Xls";$suffix = "xls";}ob_end_clean();//清楚缓存区// 激活浏览器窗口header("Content-Disposition:attachment;filename=$fileName.$suffix");//缓存控制header("Cache-Control:max-age=0");// 调用方法执行下载$writer = IOFactory::createWriter($preadsheet, $type);// 数据流$writer->save("php://output");exit();}
}

 控制器中调用

/*** excel导出* @throws \think\db\exception\DataNotFoundException* @throws \think\db\exception\DbException* @throws \think\db\exception\ModelNotFoundException* @throws \think\exception\DbException*/public function excel(){
//        设置表格的表头数据 A1 B1 C1 D1……  以此类推$header = ["A1"=>"id","B1"=>"角色编码","C1"=>"角色名"];//调用Role模型从Role表查询数据并转数组$arr = \app\admin\model\Role::sel()->toArray();//定义空数组$data = [];
//        将需要到出的数组方法到$data中foreach ($arr as $v){$data[] = ['id'=>$v['id'],'role_code'=>$v['role_code'],'role_name'=>$v['role_name'],];}//保存文件类型$type = true;//设置下载文件保存的名称$fileName = "用户角色信息统计表".time();//调用方法导出excelExcel::export($header,$type,$data,$fileName);}