tp6 tinkphp6导出 通用导出 phpoffice/phpspreadsheet

tp 和laravel通用导出

1.执行：

composer require phpoffice/phpspreadsheet  ^1.18

 

2完整的导出

 

<?php

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\IOFactory;


class Spread
{
    //composer require phpoffice/phpspreadsheet ^1.18
    
    /**
     * 导出示例
     * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
     */
    public static function testExport(){

        $data = [
            ['title'=>'张三', 'content' => '张三的内容', 'prices' => '100', 'create_time' => '2017.10.12', 'end_time' =>'2020.12.12'],
            ['title'=>'李四', 'content' => '李四的内容', 'prices' => '222', 'create_time' => '2017.10.12', 'end_time' =>'2020.12.12'],
            ['title'=>'王莽', 'content' => '王莽的内容', 'prices' => '343', 'create_time' => '2017.10.12', 'end_time' =>'2022.03.12'],
        ];
        $header = ['名称', '内容', '金额', '开始时间', '结束时间'];

        self::export($data, $header, $title = 'test', $filename = 'test', $file_type = 'Csv');
    }

    /**
     * 下载导出文件
     * @param array $data
     * @param array $header 表头
     * @param string $title excel标题
     * @param string $filename 文件名称
     * @param string $format
     * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
     */
    public static function export(array $data, array $header, $title = 'test', $filename = 'test', $format = 'Csv')
    {
        set_time_limit(0);
        $title_key = [];
        foreach ($data[0] as $key=>$val){
            $title_key[] = $key;
        }
        $unit = array(
            'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U',
            'V', 'W', 'X', 'Y', 'Z', 'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN',
            'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ'
        );
        $line_count = count($header);

        $newExcel = new Spreadsheet();  //创建一个新的excel文档
        $objSheet = $newExcel->getActiveSheet();  //获取当前操作sheet的对象
        $objSheet->setTitle($title);  //设置当前sheet的标题

        $n = 1;
        for ($i = 0; $i < $line_count; ++$i){
            $newExcel->getActiveSheet()->getColumnDimension($unit[$i])->setAutoSize(true);
            $objSheet->setCellValue($unit[$i].$n, $header[$i]);

            foreach ($data as $key=>$val){
                $m = $key + 2;
                $objSheet->setCellValue($unit[$i] . $m, $val[$title_key[$i]]);
            }
        }

        ob_end_clean() ;
        // $format只能为 Xlsx 或 Xls
        if ($format == 'Xlsx') {
            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        } elseif ($format == 'Xls') {
            header('Content-Type: application/vnd.ms-excel');
        }else{
            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        }

        header("Content-Disposition: attachment;filename=" . $filename .'-'. date('YmdHis') . '.' . strtolower($format));
        header('Cache-Control: max-age=0');
        $objWriter = IOFactory::createWriter($newExcel, $format);

        $objWriter->save('php://output');

        //通过php保存在本地的时候需要用到
        //$objWriter->save($dir.'/demo.xlsx');

        //以下为需要用到IE时候设置
        // If you're serving to IE 9, then the following may be needed
        header('Cache-Control: max-age=1');
        //If you're serving to IE over SSL, then the following may be needed
        header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
        header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
        header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
        header('Pragma: public'); // HTTP/1.0
        exit;
    }
}

封装代码，仅供参考