【代码段】PHPexcel导出Excel

$filename = '学校信息_' . date("YmdHis") . '.Xlsx';

$table_title = '大学信息表(本科)';

$sub_title = '导出时间: ' . date('Y-m-d H:i:s') . ' 技术支持: 椰露布丁';

$spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();

$spreadsheet->getProperties()->setTitle($table_title);

$spreadsheet->getProperties()->setCreator('椰露布丁');

$spreadsheet->getProperties()->setSubject($table_title);

$spreadsheet->getProperties()->setKeywords($table_title);

$sheet = $spreadsheet->getActiveSheet();

// 声明使用的表格列

$cellName = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ');

// 设置表格列标题

$cell_title = ['ID','全国统一代码','学校名称','英文名称','创办时间','办学性质','院校类型','学科层次','院校特色','主管部门','省','市','地址','地区全名称','独立学院','读研比例','出国比例','硕士点','博士点','男比例','女比例','LOGO','官方网址','官方电话','头部背景图','校园视频','校园环境图','学校简介'];

// 获取数据行数

$dataNum = count($list);

// 获取表格列数量

$cellNum = count($cell_title);



//设置标签名

$sheet->setTitle($table_title);

// 设置缩放级别

$sheet->getSheetView()->setZoomScale(90);

// 设置标签颜色

$sheet->getTabColor()->setRGB('FF0000');

// 设置页面水平居中

$sheet->getPageSetup()->setHorizontalCentered(true);

$sheet->getPageSetup()->setPaperSize(\PhpOffice\PhpSpreadsheet\Worksheet\PageSetup::PAPERSIZE_A4);

// 设置横页

$sheet->getPageSetup()->setOrientation(\PhpOffice\PhpSpreadsheet\Worksheet\PageSetup::ORIENTATION_LANDSCAPE);

// 设置默认列宽

$sheet->getDefaultColumnDimension()->setWidth(10);

//自动计算列宽

// $sheet->getColumnDimension($cellName[$cellNum-1])->setAutoSize(true);

// 设置打印区域

$sheet->getPageSetup()->setPrintArea('A1:'.$cellName[$cellNum-1].($dataNum+3));

// 设置边距

$sheet->getPageMargins()->setTop(1);

$sheet->getPageMargins()->setRight(0.75);

$sheet->getPageMargins()->setLeft(0.75);

$sheet->getPageMargins()->setBottom(1);

// 设置工作表的打印页眉和页脚

$sheet->getHeaderFooter()->setOddHeader('我就是页眉信息啊');

$sheet->getHeaderFooter()->setOddFooter(date('Y-m-d H:i:s'));

// 设置border，和整体单元格格对齐方式

$styleArray = [

'borders' => [

'outline' => [

'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THICK,

'color' => ['argb' => 'FF000000'],

],

'inside' => [

'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,

'color' => ['argb' => 'FF000000'],

],

],

'alignment' => [

'horizontal' => Alignment::HORIZONTAL_CENTER_CONTINUOUS,

'vertical' => Alignment::VERTICAL_CENTER,

]

];

$sheet->getStyle('A1:'.$cellName[$cellNum-1].($dataNum+3))->applyFromArray($styleArray);

$styleArray = [

'alignment' => [

'horizontal' => Alignment::HORIZONTAL_LEFT,

'vertical' => Alignment::VERTICAL_CENTER,

]

];

$sheet->getStyle('A4:'.$cellName[$cellNum-1].($dataNum+3))->applyFromArray($styleArray);

// 设置表格总标题

// 合并A1,A2行

$sheet->mergeCells('A1:'.$cellName[$cellNum-1].'1');

$sheet->mergeCells('A2:'.$cellName[$cellNum-1].'2');

// 填入A1,A2内容

$sheet->setCellValue('A1',$table_title);

$sheet->setCellValue('A2',$sub_title);

// 设置A1:A3加粗,字体

$sheet->getStyle('A1:'.$cellName[$cellNum-1].'3')->getFont()->setBold(true);

// 设置A1:A3行字号

$sheet->getStyle('A1')->getFont()->setSize(45);

$sheet->getStyle('A2:A3')->getFont()->setSize(14);

// 设置A1:A2行文字颜色

$sheet->getStyle('A1:A2')->getFont()->getColor()->setARGB('FF000000');

// 设置表格列标题

$titCol = 'A';

foreach ($cell_title as $key => $value) {

// 单元格内容写入

$sheet->setCellValue($titCol . '3', $value);

$titCol++;

}

$row = 4; // 从第二行开始

foreach ($list as $key=>$item) {

$dataCol = 'A';

$sheet->getColumnDimension('A')->setWidth(3.2);

$sheet->getColumnDimension('B')->setWidth(12);

$sheet->getColumnDimension('AA')->setWidth(12);

$sheet->getColumnDimension('AB')->setWidth(195);



if ($row%2==0) {

$fillColor = 'F8F8FF';

}else{

$fillColor = 'F5F5DC';

}

foreach ($item as $vv) {

// 内容过长自动换行

$sheet->getStyle($dataCol.$row)->getAlignment()->setWrapText(true);

// 设置单元格背景色

$sheet->getStyle($dataCol.$row)->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setRGB($fillColor);

$sheet->setCellValue($dataCol . $row, $vv);

$dataCol++;

}

$row++;

}



//浏览器保存

// Redirect output to a client’s web browser (Xlsx)

header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');

header('Content-Disposition: attachment;filename='.$filename);

header('Cache-Control: max-age=0');

// If you're serving to IE 9, then the following may be needed

header('Cache-Control: max-age=1');

// If you're serving to IE over SSL, then the following may be needed

header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past

header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified

header('Cache-Control: cache, must-revalidate'); // HTTP/1.1

header('Pragma: public'); // HTTP/1.0

// 保存到本地文件



$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');

$writer->save('php://output');

exit;