PHPExcel单独设置（高亮）单元格部分文字字体颜色导出文件（两种方法）

在这里插入图片描述

 public function exportData(){ 
   $objPHPExcel = new \PHPExcel();
     //创建一个富文本对象
     $objRichText = new \PHPExcel_RichText();
     $objRichText->createText('铁扇公主');
     //需要改变大小或颜色的文字内容
     $objPayable = $objRichText->createTextRun('芭蕉妹妹');
     //设置加粗
     $objPayable->getFont()->setBold(true);
     //设置斜体
     $objPayable->getFont()->setItalic(true);
     //设置文字大小
     $objPayable->getFont()->setSize(15);
     //设置颜色
     $objPayable->getFont()->setColor(
         new \PHPExcel_Style_Color(\PHPExcel_Style_Color::COLOR_DARKGREEN)
     );
     $objRichText->createText('牛魔王');
     $objPHPExcel->getActiveSheet()->getCell('A1')->setValue($objRichText);
     $PHPWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, "Excel2007");
     $PHPWriter->save($filename.'.xlsx');
 }

定义关键字，寻找关键字，对关键字设置颜色
在这里插入图片描述

 public function exportData(){
       ob_end_clean();
       ob_start();

       $file_path = "c:\\";
       $filename = time().'周出货表';
       //定义需要高亮的关键词
       $keywords = ["你好", "CN", "啊", "亮晶晶", "梳妆匣", "说道", "春风", "一层楼", "之"];

       //标题
       $header = [
           ['name' => "标题", 'content' => '匹配结果']
       ];
       //内容
       $list = [
           ['name' => "第一行", 'content' => "你好艾希你你好好啊，你好好啊，CN你真的好好好啊，眼睛亮晶晶的，nihao"],
           ['name' => "第二行", 'content' => "Hello，请别走走停停，休息一下说道CN休息一下说道，漂亮的梳妆匣漂亮的梳妆匣漂亮的梳妆匣送给你，如沐春风如沐春风"],
           ['name' => "第三行", 'content' => "Hello，欲穷千里目，更上一层楼"],
           ['name' => "第四行", 'content' => "合抱之木，生于毫末；CN九层之台，起于累土；千里之行，始于足下"],
           ['name' => "第四行", 'content' => "内容"],
       ];
       //合并
       $contents = array_merge($header, $list);
       $objPHPExcel = new \PHPExcel();
       
       //数据写进Excel，并对关键字高亮处理
       foreach ($contents as $key => $value) {
           $excelRow = ($key + 1); //excel第几行
           $name = $value['name'];
           $content = $value['content'];
           //A列
           $objPHPExcel->getActiveSheet()->getCell("A{$excelRow}")->setValue($name);
           if ($key != 0) { //不是第一行，内容才需要高亮
               $richTextObj = new \PHPExcel_RichText(); //每一行都需要重新New一个richText
               list($contentArray, $keywordsArray) = self::findKeywords($keywords, $value);
               foreach ($contentArray as $item) {
                   if ($item['high'] == 1) { //需要高亮
                       $setStyleObj = $richTextObj->createTextRun($item['content']);
                       $setStyleObj->getFont()->setColor(new \PHPExcel_Style_Color(\PHPExcel_Style_Color::COLOR_RED)); //设置颜色
                   } else { //不需要高亮
                       $richTextObj->createText($item['content']);
                   }
               }
               $objPHPExcel->getActiveSheet()->getCell("B{$excelRow}")->setValue($content);

               //$objPHPExcel->getActiveSheet()->getCell("C{$excelRow}")->setValue($richTextObj);
               $objPHPExcel->getActiveSheet()->getCell("C{$excelRow}")->setValue($richTextObj);

               //整个单元格文字变色
               //$objPHPExcel->getActiveSheet()->getStyle("C{$excelRow}")->getFont()->getColor()->setARGB(\PHPExcel_Style_Color::COLOR_RED);
               //整个单元格文字变大
               //$objPHPExcel->getActiveSheet()->getStyle("C{$excelRow}")->getFont()->setSize(20);
               //整个单元格文字变粗
               //$objPHPExcel->getActiveSheet()->getStyle("C{$excelRow}")->getFont()->setBold(true);

               $objPHPExcel->getActiveSheet()->getCell("D{$excelRow}")->setValue(implode("，", $keywordsArray));
           } else { //第一行
               $objPHPExcel->getActiveSheet()->getCell("B{$excelRow}")->setValue('原文');
               $objPHPExcel->getActiveSheet()->getCell("C{$excelRow}")->setValue($content);
               $objPHPExcel->getActiveSheet()->getCell("D{$excelRow}")->setValue('关键字');
           }

       }
 
// // 或者方法一
   //  $PHPWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, "Excel2007");
   //  $PHPWriter->save($filename.'.xlsx');

// // 或者方法二
        ob_end_clean();
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="' . $filename . '.xls');
        header('Cache-Control: max-age=0');
        header('Content-Type: text/html; charset=utf-8');
//        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
       $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, "Excel2007");
       $objWriter->save('php://output');
       exit;
   }

    //php根据关键字把内容处理成数组，关键字高亮
    public function findKeywords($keywords, $value)
    {
        $temp = []; //关键字内容记录
        $content = $value['content'];
        $contentLen = mb_strlen($content); //内容的长度

        foreach ($keywords as $key) {
            $keyLen = mb_strlen($key); //关键字的长度
//            $contentLen = mb_strlen($content); //内容的长度
            $count = mb_substr_count($content, $key); //统计字符出现的次数

            $startSearchIndex = 0; //开始搜索的位置，重置
            for ($i = 0; $i < $count; $i++ ) { //记录关键字出现的位置
                $index = mb_stripos($content, $key, $startSearchIndex); //用来查找字符串中某部分字符串首次出现的位置（不区分大小写）。
                $temp[] = [
                    'key' => $key, //关键字内容
                    'index' => $index, //关键字索引位置
                    'len' => $keyLen, //关键字长度
                ];
                $startSearchIndex = ($index + $keyLen); //重置开始搜索的位置

                //关键字被提取，重置content内容
                /*if ($index == 0) { //第一个就是，出现在句子开头，直接截取后面的即可
                    $content = mb_substr($content, $keyLen);
                } else if (($index + $keyLen) == $contentLen) { //出现在句子结尾，直接截取前面内容
                    $content = mb_substr($content, 0, $index);
                } else { //出现在中间，左右两边都要截取
                    $content = mb_substr($content, 0, $keyLen) . mb_substr($content, ($keyLen + $index));
                }*/
            }
        }

        $tempResult = []; //最终结果数据（包括没高亮的数据）

        //二维数组根据关键字的索引index升序
        $sort = array_column($temp, 'index');
        array_multisort($sort, SORT_ASC, $temp);

        if ($temp) { //关键字存在，做数据截取操作
            $lastIndex = 0; //下一个需要匹配的数据
            $tempCount = count($temp); //关键字的长度
            foreach ($temp as $k => $v) {
                $key = $v['key'];
                $index = $v['index'];
                $len = $v['len'];

                if ($index != $lastIndex) { //关键字不是在内容最前面，
                    $tempResult[] = [
                        'content' => mb_substr($content, $lastIndex, ($index - $lastIndex)), //追加内容开头数据
                        'high' => 0 //是否高亮：1-是；0-否
                    ];
                }

                $tempResult[] = [
                    'content' => $key,
                    'high' => 1 //是否高亮：1-是；0-否
                ];

                $lastIndex = ($index + $len);

                //如果是最后一个：判断一下，如果：最后一个关键字不是在结尾，需要拼接一下结尾数据
                if ((($k + 1) == $tempCount) && ($lastIndex != $contentLen)) {
                    $tempResult[] = [
                        'content' => mb_substr($content, $lastIndex), //追加内容开头数据
                        'high' => 0 //是否高亮：1-是；0-否
                    ];
                }
            }

        } else { //关键字不存在，直接赋值
            $tempResult[] = [
                'content' => $content,
                'high' => 0 //是否高亮：1-是；0-否
            ];
        }

        $result = [
            $tempResult, //处理好了的数组
            array_column($temp, 'key') //匹配到的关键字一维数组
        ];
        return $result; //返回结果数据
    }