tp6 tinkphp6导出通用导出 phpoffice/phpspreadsheet

短命女 2022-09-13 04:16 320阅读 0赞

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;
        }
    }