PhpSpreadsheet导出EXCEL通用方法

namespace app\admin\controller; use PhpOffice\PhpSpreadsheet\Writer\Xlsx; use PhpOffice\PhpSpreadsheet\Spreadsheet; use app\common\model\Advances; use wycto\helper\HelperArray; class ExportController extends DefaultController { function indexAction(){ } function advancesAction(){ $param = $this->_request->param('param'); $where = json_decode($param,true); HelperArray::removeEmpty($where); $rows = Advances::all($where)->toArray(); if(isset($where['company'])){ $filename = $where['company'] . "借支名单_" . date('Ymd'); }else{ $filename = "借支名单_" . date('Ymd'); } $head = array('工厂名称','员工姓名','身份证','工号','借支金额','借支日期'); $keys = array( 0=>'company', 1=>'name', 2=>'idcard', 3=>'number', 4=>'money', 5=>'date_time' ); $this->outdata($filename,$rows,$head,$keys); } /** * 通用导出方法。传入参数即可 * @param unknown $filename 导出的excel文件名称，不包括后缀 * @param unknown $rows 要导出的数据，数组 * @param unknown $head 要导出数据的表头，数组 * @param unknown $keys 要导出数据的键值对对应 */ function outdata($filename, $rows=[], $head=[], $keys=[]) { $count = count($head); //计算表头数量 $spreadsheet = new Spreadsheet(); $sheet = $spreadsheet->getActiveSheet(); //设置样式,设置剧中，加边框，设置行高 $styleArray = [ 'alignment' => [ 'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER, ], 'borders' => [ 'allBorders' => [ 'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN, 'color' => ['argb' => '6184542'], ], ], ]; $rows_count = count($rows); $sheet->getDefaultRowDimension()->setRowHeight(18);//设置默认行高。 $sheet->getStyle('A1:' . strtoupper(chr($count+65-1)) . strval($rows_count+1))->applyFromArray($styleArray); $sheet->getStyle('A1:' . strtoupper(chr($count+65-1)) . '1')->getFont()->setBold(true)->setName('Arial')->setSize(10)->applyFromArray($styleArray); //设置样式结束 //写入表头信息 for ($i = 65; $i < $count + 65; $i++) { //数字转字母从65开始，循环设置表头： $sheet->setCellValue(strtoupper(chr($i)) . '1', $head[$i - 65]); } //写入数据信息 foreach ($rows as $key => $item) { //循环设置单元格： //$key+2,因为第一行是表头，所以写到表格时从第二行开始写 for ($i = 65; $i < $count + 65; $i++) { //数字转字母从65开始： $sheet->setCellValue(strtoupper(chr($i)) . ($key + 2), $item[$keys[$i - 65]]); $spreadsheet->getActiveSheet()->getColumnDimension(strtoupper(chr($i)))->setWidth(20); //固定列宽 } } //header('Content-Type: application/vnd.ms-excel');xls header('Content-Type:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');//xlsx header('Content-Disposition: attachment;filename="' . $filename . '"'); header('Cache-Control: max-age=0'); $writer = new Xlsx($spreadsheet); $writer->save('php://output'); //删除清空： $spreadsheet->disconnectWorksheets(); unset($spreadsheet); exit; } }

$(".export").click(function(){ var param = $("#finder-controller();?>-search").serializeJson(); var options = $('#finder-controller();?>').{$_finder}("options"); param = $.extend(true,options.queryParams,param); var p = JSON.stringify(param); console.log(JSON.stringify(param)); location.href = "{:url('admin/export/advances')}" + "/param/" + p; });