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

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

luck_007

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

阅读量576

点赞数 1

分类专栏： PHP 函数接口文章标签： php PhpSpreadsheet Powered by 金山文档

本文链接：https://blog.csdn.net/qq_30986969/article/details/129671169

版权

PHP 同时被 3 个专栏收录

21 篇文章 0 订阅

订阅专栏

函数

7 篇文章 0 订阅

订阅专栏

接口

5 篇文章 0 订阅

订阅专栏

安装扩展

composer require phpoffice/phpspreadsheet

控制器代码如下(根据姓名判断设置文字不同颜色)

<?php
namespace app\api\controller;
use think\Controller;
use think\Db;

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

class Export extends Controller
{
        
    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');

    }

}