ThinkPHP 使用 PhpSpreadsheet 导出 Excel 文件

一、安装 PhpSpreadsheet

composer require phpoffice/phpspreadsheet

二、控制器代码

<?php
namespace app\admin\controller;

use think\Db;
use PhpOffice\PhpSpreadsheet\Spreadsheet;

class Student extends Controller
{
	public function exportExcel()
	{
	    // 查询要导出的数据
	    $data = Db::name('student')->select();
	    // 实例化
	    $spreadsheet = new Spreadsheet();
	    // 获取活动工作薄
	    $sheet = $spreadsheet->getActiveSheet();
	
	    // 获取单元格
	    $cellA = $sheet->getCell('A1');
	    // 设置单元格的值
	    $cellA->setValue('ID');
	    // 设置 A 列 列宽
	    $sheet->getColumnDimension('A')->setWidth(10);
	    // 设置第一行 行高
	    $sheet->getRowDimension(1)->setRowHeight(20);
	
	    $cellB = $sheet->getCell('B1');
	    $cellB->setValue('学生头像');
	    $sheet->getColumnDimension('B')->setWidth(10);
	
	    $cellC = $sheet->getCell('C1');
	    $cellC->setValue('学生姓名');
	    $sheet->getColumnDimension('C')->setWidth(10);
	
	    $cellD = $sheet->getCell('D1');
	    $cellD->setValue('学生年龄');
	    $sheet->getColumnDimension('D')->setWidth(10);
	
	    $cellE = $sheet->getCell('E1');
	    $cellE->setValue('学生性别');
	    $sheet->getColumnDimension('E')->setWidth(10);
	
	    $cellF = $sheet->getCell('F1');
	    $cellF->setValue('所属班级');
	    $sheet->getColumnDimension('F')->setWidth(30);
	
	    $cellG = $sheet->getCell('G1');
	    $cellG->setValue('所属学校');
	    $sheet->getColumnDimension('G')->setWidth(40);
	
	    $cellH = $sheet->getCell('H1');
	    $cellH->setValue('创建时间');
	    $sheet->getColumnDimension('H')->setWidth(30);
	
	    // 设置样式 标题栏
	    $styleArray = [
	        'alignment' => [
	            'horizontal' => 'center', //水平居中
	            'vertical' => 'center', //垂直居中
	        ],
	        'font' => [
	            'name' => '黑体',
	            'bold' => false,
	            'size' => 10
	        ]
	    ];
	    // 设置样式 正文
	    $styleArrayBody = [
	        'alignment' => [
	            'horizontal' => 'center', //水平居中
	            'vertical' => 'center', //垂直居中
	        ],
	        'font' => [
	            'name' => '宋体',
	            'bold' => false,
	            'size' => 10
	        ]
	    ];
	    // 应用样式
	    $sheet->getStyle('A1')->applyFromArray($styleArray);
	    $sheet->getStyle('B1')->applyFromArray($styleArray);
	    $sheet->getStyle('C1')->applyFromArray($styleArray);
	    $sheet->getStyle('D1')->applyFromArray($styleArray);
	    $sheet->getStyle('E1')->applyFromArray($styleArray);
	    $sheet->getStyle('F1')->applyFromArray($styleArray);
	    $sheet->getStyle('G1')->applyFromArray($styleArray);
	    $sheet->getStyle('H1')->applyFromArray($styleArray);
	
	    // 从 A2 开始填充数据
	    foreach ($data as $k => $v) {
	        $n = $k + 2;
	        // 获取单元格
	        $cellA = $sheet->getCell('A'.$n);
	        // 设置单元格的值
	        $cellA->setValue($v['id']);
	
	        $cellB = $sheet->getCell('B'.$n);
	        $cellB->setValue('');
	
	        $cellC = $sheet->getCell('C'.$n);
	        $cellC->setValue($v['name']);
	
	        $cellD = $sheet->getCell('D'.$n);
	        $cellD->setValue($v['age']);
	
	        $cellE = $sheet->getCell('E'.$n);
	        $cellE->setValue($v['sex']);
	
	        $cellF = $sheet->getCell('F'.$n);
	        $cellF->setValue($v['room_name']);
	
	        $cellG = $sheet->getCell('G'.$n);
	        $cellG->setValue($v['school_name']);
	
	        $cellH = $sheet->getCell('H'.$n);
	        $cellH->setValue($v['create_time']);
	
	        // 再给表格体设置样式
	        $sheet->getStyle('A'.$n)->applyFromArray($styleArrayBody);
	        $sheet->getStyle('B'.$n)->applyFromArray($styleArrayBody);
	        $sheet->getStyle('C'.$n)->applyFromArray($styleArrayBody);
	        $sheet->getStyle('D'.$n)->applyFromArray($styleArrayBody);
	        $sheet->getStyle('E'.$n)->applyFromArray($styleArrayBody);
	        $sheet->getStyle('F'.$n)->applyFromArray($styleArrayBody);
	        $sheet->getStyle('G'.$n)->applyFromArray($styleArrayBody);
	        $sheet->getStyle('H'.$n)->applyFromArray($styleArrayBody);
	    }
	
	    // 下载文件名
	    $file_name = '学生列表.xlsx';
	    header('Content-Type:application/vnd.ms-excel');
	    header('Content-Disposition:attachment;filename='.$file_name);
	    header('Cache-Control:max-age=0');
		// 下载
	    $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
	    $writer->save('php://output');
	}
}

三、效果图

四、相关参考

1. 在 PhpStudy 中配置 Composer >>>