使用ThinkPHP8实现导出Excel数据表格功能

composer require phpoffice/phpspreadsheet

$header = [
  ['key' => 'index', 'title' => '序号'],
  ['key' => 'activity_title', 'title' => '列1名称'],
  ['key' => 'room_name', 'title' => '列2名称'],
];

$list = [];  //    定义数据内容，根据实际应用场景来写即可。

 //  表头
$header = [
  ['key' => 'index', 'title' => '序号'],
  ['key' => 'activity_title', 'title' => '列1名称'],
  ['key' => 'room_name', 'title' => '列2名称'],
];
$list = [];
//  实例化excel
$sheet = new spreadsheet();
//  实例化导出类
$export = new excel($sheet, 0);
//  设置单元格表头
$export->setheader($header);
//  设置单元格数据
$export->setcontent($list, $header);
//  导出：文件名称、sheet名称，返回结果为本地文件存储路径
$res = $export->export($filename, $sheetname);

<?php
 
namespace app\common\controller;
 
/**
 * @note excel操作
 */
class excel
{
 
    //  定义表格对象
    protected object $sheet;
 
    public function __construct(object $sheet, $sheetindex = 0)
    {
        $this->sheet = $sheet;
        if (!is_object($this->sheet)) $this->sheet = new \phpoffice\phpspreadsheet\spreadsheet();
        $this->sheet->getactivesheet($sheetindex);
    }
 
    /**
     * @notes 设置表头
     * @param array $header 表头数据
     * @param string|int $startrow 默认第一行
     */
    public function setheader(array $header, string|int $startrow = 1): object
    {
        $header = array_values($header);
        //  计算总列数
        $column = $this->getcolumn(count($header));
        foreach ($header as $key => $value) {
            $columnname = $column[$key] . $startrow;
            //  设置单元格值
            $this->sheet->getactivesheet()->setcellvalue($columnname, $value['title']);
            //  设置单元格自适应宽度
            $this->sheet->getactivesheet()->getcolumndimension($column[$key])->setautosize(true);
            //  设置单元格自适应高度
            $this->sheet->getactivesheet()->getrowdimension($startrow)->setrowheight(24);
        }
        $startcolumn = $column[0] . $startrow;
        $endcolumn = $column[count($header) - 1] . $startrow;
        //  设置字体大小及加粗
        $this->sheet->getactivesheet()->getstyle($startcolumn . ':' . $endcolumn)->getfont()->setbold(true)->setsize(12);
        //  设置单元格水平居中
        $this->sheet->getactivesheet()->getstyle($startcolumn . ':' . $endcolumn)->getalignment()->sethorizontal(\phpoffice\phpspreadsheet\style\alignment::horizontal_center);
        //  设置单元格垂直居中
        $this->sheet->getactivesheet()->getstyle($startcolumn . ':' . $endcolumn)->getalignment()->setvertical(\phpoffice\phpspreadsheet\style\alignment::vertical_center);
        //  设置单元格边框
        $this->sheet->getactivesheet()->getstyle($startcolumn . ':' . $endcolumn)->getborders()->getallborders()->setborderstyle(\phpoffice\phpspreadsheet\style\border::border_thin);
        return $this->sheet;
    }
 
    /**
     * @notes 设置单元格值
     * @param array $data 数据
     * @param array $header 表头数据
     * @param string|int $startrow 默认第二行开始
     */
    public function setcontent(array $data, array $header, string|int $startrow = 2): object
    {
        //  获取总列数
        $column = $this->getcolumn(count($header));
        //  遍历数据
        foreach ($data as $key => $value) {
            //  遍历表头
            for ($i = 0; $i < count($header); $i++) {
                //  获取单元格名称
                $columnname = $column[$i] . ($key + $startrow);
                //  设置单元格值
                $this->sheet->getactivesheet()->setcellvalue($columnname, $value[$header[$i]['key']] ?? '');
                //  设置单元格自适应宽度
                $this->sheet->getactivesheet()->getcolumndimension($column[$i])->setautosize(true);
                //  设置单元格自适应高度
                $this->sheet->getactivesheet()->getrowdimension($key + $startrow)->setrowheight(24);
            }
        }
        $startcolumn = $column[0] . $startrow;
        $endcolumn = $column[count($column) - 1] . count($data) + $startrow - 1;
        //  设置字体大小及加粗
        $this->sheet->getactivesheet()->getstyle($startcolumn . ':' . $endcolumn)->getfont()->setbold(false)->setsize(11);
        //  设置单元格水平居中
        $this->sheet->getactivesheet()->getstyle($startcolumn . ':' . $endcolumn)->getalignment()->sethorizontal(\phpoffice\phpspreadsheet\style\alignment::horizontal_center);
        //  设置单元格垂直居中
        $this->sheet->getactivesheet()->getstyle($startcolumn . ':' . $endcolumn)->getalignment()->setvertical(\phpoffice\phpspreadsheet\style\alignment::vertical_center);
        //  设置单元格边框
        $this->sheet->getactivesheet()->getstyle($startcolumn . ':' . $endcolumn)->getborders()->getallborders()->setborderstyle(\phpoffice\phpspreadsheet\style\border::border_thin);
        return $this->sheet;
    }
 
    /**
     * @notes 导出数据
     * @param string $filename 文件名
     * @param string $sheetname 表名
     * @return string
     */
    public function export(string $filename, string $sheetname = 'sheet1'): string
    {
        //  设置表格标题
        $this->sheet->getactivesheet()->settitle($sheetname);
        //  设置表格格式
        $writer = new \phpoffice\phpspreadsheet\writer\xlsx($this->sheet);
        //  设置存储路径
        $basepath = public_path();
        $path = 'activity_sequence_template/';
        $fullpath = $basepath . $path . $filename . '.xlsx';
        if (!is_dir($basepath . $path)) mkdir($basepath . $path, 0777, true);
        $writer->save($fullpath);
        return $path . $filename . '.xlsx';
//        //  设置响应头
//        header('content-type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
//        header('content-disposition: attachment;filename="' . $filename . '.xlsx"');
//        header('cache-control: max-age=0');
//        //  导出数据
//        $writer->save('php://output');
    }
 
    /**
     * @notes 自动计算列数
     * @param int|string $colnumber
     * @return array
     */
    protected function getcolumn(int|string $colnumber = 1): array
    {
        //  生成a-z的数组
        $arr = range('a', 'z');
        //  计算循环次数
        $no = ceil($colnumber / count($arr));
        //  定义数组
        $data = [];
        if ($no <= 1) {
            for ($i = 0; $i < $colnumber; $i++) {
                $data[] = $arr[$i];
            }
        } else {
            for ($i = 0; $i < count($arr); $i++) {
                $data[] = $arr[$i];
            }
            for ($i = 0; $i < $colnumber - count($arr); $i++) {
                $list = (($i + count($arr)) % count($arr));
                $data[] = $arr[ceil(($i + 1) / count($arr)) - 1] . $arr[$list];
            }
        }
        return $data;
    }
}