thinkphp6: 用phpspreadsheet导出数据到excel (php 8.1.1 / thinkphp v6.0.10LTS / phpspreadsheet 1.21.0 )

一，安装phpshpreadsheet

1,composer安装

liuhongdi@lhdpc:~$ cd /data/php/admapi/
liuhongdi@lhdpc:/data/php/admapi$ composer require phpoffice/phpspreadsheet

2,查看版本:

liuhongdi@lhdpc:/data/php/admapi$ composer show phpoffice/phpspreadsheet
name     : phpoffice/phpspreadsheet
descrip. : PHPSpreadsheet - Read, Create and Write Spreadsheet documents in PHP - Spreadsheet engine
keywords : OpenXML, excel, gnumeric, ods, php, spreadsheet, xls, xlsx
versions : * 1.21.0
type     : library
license  : MIT License (MIT) (OSI approved) https://spdx.org/licenses/MIT.html#licenseText
homepage : https://github.com/PHPOffice/PhpSpreadsheet
source   : [git] https://github.com/PHPOffice/PhpSpreadsheet.git 1a359d2ccbb89c05f5dffb32711a95f4afc67964
dist     : [zip] https://api.github.com/repos/PHPOffice/PhpSpreadsheet/zipball/1a359d2ccbb89c05f5dffb32711a95f4afc67964 1a359d2ccbb89c05f5dffb32711a95f4afc67964
path     : /data/php/admapi/vendor/phpoffice/phpspreadsheet
names    : phpoffice/phpspreadsheet
…

可以看到版本是1.21.0

说明：刘宏缔的架构森林是一个专注架构的博客，

网站：https://blog.imgtouch.com
本文: https://blog.imgtouch.com/index.php/2023/05/29/thinkphp6-yong-phpspreadsheet-dao-chu-shu-ju-dao-excel-php/

         对应的源码可以访问这里获取： https://github.com/liuhongdi/
         或: https://gitee.com/liuhongdi

说明：作者:刘宏缔 邮箱: 371125307@qq.com

二，编写php代码

1,类库

lib/util/excel.php

<?php
namespace app\lib\util;
 
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
 
class excel {
 
    /*
     * 把导出为excel到浏览器并下载
     * $title:标题
     * $headArr: 表头
     * $data: 数据
     * $fileName:文件名
     * */
    function excelExport($title="",$headArr = [], $data = [],$fileName = '') {
        $fileName       .= "_" . date("Y_m_d", time()) . ".xls";
        $spreadsheet    = new Spreadsheet();
 
        $objPHPExcel    = $spreadsheet->getActiveSheet();
        $objPHPExcel->setTitle($title);
        $objPHPExcel->setCellValueByColumnAndRow(1, 1, $title);
        // 设置表头
        $key = ord("A"); 
        foreach ($headArr as $v) {
            $colum = chr($key);
            $objPHPExcel->setCellValue($colum . '1', $v);
            $key += 1;
        }
 
        $column = 2;
        foreach ($data as $key => $rows) { // 行写入
            $span = ord("A");
            foreach ($rows as $keyName => $value) { // 列写入
                $objPHPExcel->setCellValue(chr($span) . $column, $value);
                $span++;
            }
            $column++;
        }
 
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="' . $fileName . '.xlsx"');
        header('Cache-Control: max-age=0');
        $writer = new Xlsx($spreadsheet);
        $writer->save('php://output');
        //删除临时的sheet
        $spreadsheet->disconnectWorksheets();
        unset($spreadsheet);
        exit;
    }
}

2,在controller中调用:

controller/Article.php

use app\lib\util\excel;
class Article extends BaseController
{
    //导出为excel文件
    public function excel() {
        $fileName="2022年员工名单";
        $title="2022年员工名单";
        $headArr = ['id', 'name', 'sex'];
        $data = [
            [1,'老刘','男'],
            [2,'老马','女'],
            [3,'小刘','男'],
            ];
        $excelObj = new excel();
        $excelObj->excelExport($title, $headArr, $data,$fileName);
    }
}

三，测试效果

1,访问:

http://192.168.219.6:8000/article/excel

2,查看导出的文件:

四，查看php和thinkphp的版本: 

php:

liuhongdi@lhdpc:/data/php/admapi$ php --version
PHP 8.1.1 (cli) (built: Dec 20 2021 16:12:16) (NTS)
Copyright (c) The PHP Group
Zend Engine v4.1.1, Copyright (c) Zend Technologies
    with Zend OPcache v8.1.1, Copyright (c), by Zend Technologies 

thinkphp:

liuhongdi@lhdpc:/var/www/html$ cd /data/php/admapi/
liuhongdi@lhdpc:/data/php/admapi$ php think version
v6.0.10LTS