mydal

怕光阴流逝 让一切都作废

0%

tp5使用PhpSpreadsheet扩展导出数据到excel

发表于 更新于 分类于
本文字数： 2.9k 阅读时长 ≈ 7 分钟

​ PhpSpreadsheet是一个纯PHP类库，它提供了一组类，允许您从不同的电子表格文件格式（如Excel和LibreOffice Calc）读取和写入。

​ 刚上班没几天,遇到一个需求,后台导出数据到excel表中,这个操作之前听过,没有用过,在网上找了半天,找了个轮子,拿过来用,有时间研究一下文档.

composer安装PhpSpreadsheet组件

1
λ composer require phpoffice/phpspreadsheet

安装完成之后,vendor文件夹下生成一个phpoffice文件

12.png

tp5导出

首先在控制器中引入类库

1
2
3
4
5
6
7
8
9
10
11
12
13
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Reader\Xls;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Worksheet\PageSetup;
use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\Color;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
//实际项目中根据情况引入相应的类库

在视图中定义

1
<li><a href="{:url('admin/daochu')}" class="layui-btn layui-btn-normal layui-btn-x">导出数据</a></li>

在控制器中,定义导出数据的方法

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
/**
 * 导出数据到excel
 */
public function daochu()
{
    $spreadsheet = new Spreadsheet(); 
    $worksheet = $spreadsheet->getActiveSheet();
    //设置工作表标题名称
    $worksheet->setTitle('总分数表');
    //表头
    //设置单元格内容
    $worksheet->setCellValueByColumnAndRow(1, 1, '测试');
    $worksheet->setCellValueByColumnAndRow(1, 2, '序号');
    $worksheet->setCellValueByColumnAndRow(2, 2, '姓名');
    $worksheet->setCellValueByColumnAndRow(3, 2, '年级');
    $worksheet->setCellValueByColumnAndRow(4, 2, '总分数');
    //合并单元格
    $worksheet->mergeCells('A1:E1');
    
    $styleArray = [
        'font' => [
            'bold' => true
        ],
        'alignment' => [
            'horizontal' => Alignment::HORIZONTAL_CENTER,
        ],
    ];
    //设置单元格样式
    $worksheet->getStyle('A1')->applyFromArray($styleArray)->getFont()->setSize(28);

    $worksheet->getStyle('A2:E2')->applyFromArray($styleArray)->getFont()->setSize(14);
    ;
    //查询数据
    $jzInfo=Db::query("select a.id,sum(score) sumscore,b.name,b.class from score  a inner join student b on a.stu_id=b.id group by stu_id order by sumscore asc");

    $len = count($jzInfo);
    $j = 0;
    for ($i=0; $i < $len; $i++) {
        $j = $i + 3; //从表格第3行开始
        $worksheet->setCellValueByColumnAndRow(1, $j, $jzInfo[$i]['id']);
        $worksheet->setCellValueByColumnAndRow(2, $j, $jzInfo[$i]['name']);
        $worksheet->setCellValueByColumnAndRow(3, $j, $jzInfo[$i]['class']);
        $worksheet->setCellValueByColumnAndRow(4, $j, $jzInfo[$i]['sumscore']);
    }

    $styleArrayBody = [
        'borders' => [
            'allBorders' => [
                'borderStyle' => Border::BORDER_THIN,
                'color' => ['argb' => '666666'],
            ],
        ],
        'alignment' => [
            'horizontal' => Alignment::HORIZONTAL_CENTER,
        ],
    ];
    $total_jzInfo = $len + 2;
    //添加所有边框/居中
    $worksheet->getStyle('A1:C'.$total_jzInfo)->applyFromArray($styleArrayBody);
    $filename = '测试表.xlsx';
    header('Content-Type: application/vnd.ms-excel');
    header('Content-Disposition: attachment;filename="'.$filename.'"');
    header('Cache-Control: max-age=0');//禁止缓存
    $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
    $writer->save('php://output');
}

导出数据成功