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

composer require phpoffice/phpspreadsheet

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

$list = [];  //    界说数据形式，按照实践利用场景来写便可。

 //  表头
$header = [
  ['key' => 'index', 'title' => '序号'],
  ['key' => 'activity_title', 'title' => '列1名称'],
  ['key' => 'room_name', 'title' => '列两名称'],
];
$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\co妹妹on\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(二4);
        }
        $startColumn = $column[0] . $startRow;
        $endColumn = $column[count($header) - 1] . $startRow;
        //  安排字体巨细及添精
        $this->sheet->getActiveSheet()->getStyle($startColumn . ':' . $endColumn)->getFont()->setBold(true)->setSize(1两);
        //  设施单位格程度居外
        $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 = 两): 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(二4);
            }
        }
        $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;
    }
}