使用PhpSpreadsheet 导出excel文件根据数据判断设置文字颜色

最新推荐文章于 2023-08-22 19:33:10 发布

新老农

最新推荐文章于 2023-08-22 19:33:10 发布

阅读量264

点赞数

文章标签： php excel

懒虫

本文链接：https://blog.csdn.net/xcbzsy/article/details/132149814

版权

在这里插入图片描述

public function index(){
        //接收前端参数 查询数据出来 目前演示为测试数据
        $data = [
            [
                "id" => 1,
                "name" => "小黄",
                "age" => "10"
            ],
            [
                "id" => 2,
                'name' => "小红",
                "age" => "11",
            ],
            [
                "id" => 3,
                "name" => "小黑",
                "age" => "12"
            ]
        ];
        
        
        $fileName = '('.date("Y-m-d",time()) .'导出）';
        //实例化spreadsheet对象
        $spreadsheet = new Spreadsheet();
        //获取活动工作簿
        $sheet = $spreadsheet->getActiveSheet();
        //设置单元格表头
        $sheet->setCellValue('A1', 'id');
        $sheet->setCellValue('B1', '姓名');
        $sheet->setCellValue('C1', '年龄');
      
 
        $i=2;
        foreach($data as $key => $val){
           
            $sheet->SetCellValueByColumnAndRow('1',$i,$val['id']);
            $sheet->SetCellValueByColumnAndRow('2',$i,$val['name']);
            $sheet->SetCellValueByColumnAndRow('3',$i,$val['age']);
            
            $color = '000000';
            if($val['name'] == '小黄')
               $color = 'CCFF33';
            if($val['name'] == '小红')
               $color = 'B8002E';
            if($val['name'] == '小黑')
               $color = '000000';
            
            $data[$key]['color'] = $color;
            
            $cell = 'B'.$i;
            $spreadsheet->getActiveSheet()->getStyle($cell)->getFont()->getColor()->setRGB($color);
            
            $i++;
        
        }
        
        //MIME协议，文件的类型，不设置描绘默认html
        header('Content-Type:application/vnd.openxmlformats-officedoument.spreadsheetml.sheet');
        //MIME 协议的扩展
        header("Content-Disposition:attachment;filename={$fileName}.xlsx");
        //缓存控制
        header('Cache-Control:max-age=0');
        
        $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet,'Xlsx');
        $writer->save('php://output');
 
    }