ThinkPHP6.0 导出 Excel 案例

猿掌柜
2021-04-16 / 0 评论 / 42 阅读 / 正在检测是否收录...

第一步:安装,使用composer安装扩展。
composer require phpoffice/phpexcel
第二步:引入 Spread.php 插件

<?php
/*
 * 这个是PhpSpreadsheet的工具
 */
namespace Tools;
// --- 生成Excel
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Alignment as PHPExcel_Style_Alignment;
use PhpOffice\PhpSpreadsheet\Cell\DataType as PHPExcel_Cell_DataType;
use PhpOffice\PhpSpreadsheet\Style\Fill as PHPExcel_Style_Fill;
use PhpOffice\PhpSpreadsheet\Style\Border as Border;
// --- 读取Excel
use PhpOffice\PhpSpreadsheet\IOFactory;
class Spread
{
    /**
     * 样例
     * @access public
     * @return null
     */
    public function putdemo(){
        $fileName = "test";
        $Excel['fileName']=$fileName.date('Y年m月d日-His',time());//or $xlsTitle
        $Excel['cellName']=['A','B','C'];
        $Excel['H'] = ['A'=>22,'B'=>12,'C'=>30];//横向水平宽度
        $Excel['V'] = ['1'=>40,'2'=>26];//纵向垂直高度
        $Excel['sheetTitle']=$fileName;//大标题,自定义
        $Excel['xlsCell']=[['majorid','专业号'],['majorshort','专业简称'],
        ['majorname','专业名称']];
        $expTableData = db("major")->select();
        $this::excelPut($Excel,$expTableData);
    }

    /**
     * Excel输出
     * @access public
     * @param  array     $Excel Excel相关配置信息
     * @param  array     $expTableData 表格里面的数据
     * @return file
     */
    public static function excelPut($Excel,$expTableData){
        //  ------------- 文件参数 -------------
        $cellName = $Excel['cellName'];
        $xlsCell = $Excel['xlsCell'];
        $cellNum = count($xlsCell);//计算总列数
        $dataNum = count($expTableData);//计算数据总行数
        $spreadsheet = new Spreadsheet();
        $sheet0 = $spreadsheet->getActiveSheet();
        $sheet0->setTitle("Sheet1");
        //设置表格标题A1
        $sheet0->mergeCells('A1:'.$cellName[$cellNum-1].'1');//表头合并单元格

        // ------------- 表头 -------------
        $sheet0->setCellValue('A1',$Excel['sheetTitle']);

        $sheet0->getStyle('A1')->getFont()->setSize(20);
        $sheet0->getStyle('A1')->getFont()->setName('微软雅黑');
        //设置行高和列宽
        // ------------- 横向水平宽度 -------------
        if(isset($Excel['H'])){
            foreach ($Excel['H'] as $key => $value) {
                $sheet0->getColumnDimension($key)->setWidth($value);
            }
        }

        // ------------- 纵向垂直高度 -------------
        if(isset($Excel['V'])){
            foreach ($Excel['V'] as $key => $value) {
                $sheet0->getRowDimension($key)->setRowHeight($value);
            }
        }

        // ------------- 第二行:表头要加粗和居中,加入颜色 -------------
        $sheet0->getStyle('A1')
        ->applyFromArray(['font' => ['bold' => false],'alignment' => ['horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,'vertical'=>PHPExcel_Style_Alignment::VERTICAL_CENTER]]);
        $setcolor = $sheet0->getStyle("A2:".$cellName[$cellNum-1]."2")->getFill();
        $setcolor->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
        $colors=['00a000','53a500','3385FF','00a0d0','0C8080','EFE4B0','8db4e2','00b0f0','0fb746'];//设置总颜色
        $selectcolor=$colors[mt_rand(0,count($colors)-1)];//获取随机颜色
        $setcolor->getStartColor()->setRGB($selectcolor);

        // ------------- 根据表格数据设置列名称 -------------

        // for($i=0;$i<$cellNum;$i++){
        //     $sheet0->setCellValue($cellName[$i].'2', $xlsCell[$i][1])
        //     ->getStyle($cellName[$i].'2')
        //     ->applyFromArray(['font' => ['bold' => true],'alignment' => ['horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,'vertical'=>PHPExcel_Style_Alignment::VERTICAL_CENTER]]);
        // }

        foreach ($xlsCell as $key => $value) {
            $sheet0->setCellValue($cellName[$key].'2', $value[1])
            ->getStyle($cellName[$key].'2')
            ->applyFromArray(['font' => ['bold' => true],'alignment' => ['horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,'vertical'=>PHPExcel_Style_Alignment::VERTICAL_CENTER]]);
        }

        // ------------- 渲染表中数据内容部分 -------------

        // for($i=0;$i<$dataNum;$i++){
        //     for($j=0;$j<$cellNum;$j++){
        //         $sheet0->getStyle($cellName[$j].($i+3))->applyFromArray(['alignment' => ['horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,'vertical'=>PHPExcel_Style_Alignment::VERTICAL_CENTER]]);
        //         $sheet0->setCellValueExplicit($cellName[$j].($i+3),$expTableData[$i][$xlsCell[$j][0]],PHPExcel_Cell_DataType::TYPE_STRING);
        //         $sheet0->getStyle($cellName[$j].($i+3))->getNumberFormat()->setFormatCode("@");
        //     }
        // }

        foreach ($expTableData as $keyi => $valuei) {
            foreach ($xlsCell as $keyj => $valuej) {
                $sheet0->getStyle($cellName[$keyj].($keyi+3))->applyFromArray(['alignment' => ['horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,'vertical'=>PHPExcel_Style_Alignment::VERTICAL_CENTER]]);
                $sheet0->setCellValueExplicit($cellName[$keyj].($keyi+3),$valuei[$valuej[0]],PHPExcel_Cell_DataType::TYPE_STRING);
                $sheet0->getStyle($cellName[$keyj].($keyi+3))->getNumberFormat()->setFormatCode("@");
            }
        }

        // ------------- 设置边框 -------------
        // $sheet0->getStyle('A2:'.$cellName[$cellNum-1].($i+2))->applyFromArray(['borders' => ['allborders' => ['style' => PHPExcel_Style_Border::BORDER_THIN]]]);

        $styleArray = [
            'borders' => [
                'allBorders' => [
                    'borderStyle' => Border::BORDER_THIN,
                    'color' => ['argb' => 'FF505050'],
                ],
            ],
        ];

        $sheet0->getStyle('A2:'.$cellName[$cellNum-1].($keyi+3))->applyFromArray($styleArray);

        //$sheet0->setCellValue("A".($dataNum+10)," ");//多设置一些行
        // 冻结表格
        if(isset($Excel['freeze'])){
            $sheet0->freezePane($Excel['freeze']);
        }

        // ------------- 输出 -------------
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');//告诉浏览器输出07Excel文件
        //header('Content-Type:application/vnd.ms-excel');//告诉浏览器将要输出Excel03版本文件
        header("Content-Disposition: attachment;filename=".$Excel['fileName'].".xlsx");//告诉浏览器输出浏览器名称
        header('Cache-Control: max-age=0');//禁止缓存
        $writer = new Xlsx($spreadsheet);
        $writer->save('php://output');
        exit;
    }

 
    /**
     * Excel读取
     * @access public
     * @param  string  $filePath Excel文件存放的路径
     * @return array
     */
    public static function excelReader($uploadfile){
        $inputFileType = IOFactory::identify($uploadfile);
        $excelReader   = IOFactory::createReader($inputFileType); //Xlsx
        $PHPExcel      = $excelReader->load($uploadfile); // 载入excel文件
        $sheet         = $PHPExcel->getSheet(0); // 读取第一個工作表
        $sheetdata = $sheet->toArray();
        return $sheetdata; // --- 直接返回数组数据
    }
}

引用插件
use Tools\Spread;
导出excel

public function export_info(){
        $expTableData = Db::name('list')->alias('a')
                ->join('department b','a.department_id = b.department_id')
                ->field('a.*,b.department_name')
                ->select();
        $manager_id = Session::get('user_id'); 
        $manager_name = Db::name('list')->where('userid',$manager_id)->value('username');      
        $Excel['fileName']=$manager_name.date('Y年m月d日-His',time());//or $xlsTitle
        $Excel['cellName']=['A','B','C','D','E','F','G'];
        $Excel['H'] = ['A'=>22,'B'=>22,'C'=>28,'D'=>22,'E'=>22,'F'=>22,'G'=>22];//横向水平宽度
        $Excel['V'] = ['1'=>40,'2'=>26];//纵向垂直高度
        $Excel['sheetTitle']="员工积分信息";//大标题,自定义
        $Excel['xlsCell']=[
            ['username','姓名'],
            ['mobile','手机号'],
            ['points','当前积分'],
            ['finance_points','高级积分'],
            ['total_points','总积分'],
            ['department_name','部门'],
            ['position','岗位'],
            ];
        insert_logs("下载员工积分数据");    
        Spread::excelPut($Excel,$expTableData);        
    }

OK完成!

0

评论

博主关闭了当前页面的评论