phpexcel多sheet分页设置背景色问题

项目中需要导出一份班级社团表

 

 

 

 下方许多分页

需求是把每个分页第二行到有数据的最后一行背景色变黑

遇到的问题是：

 

 前面41行背景色正常显示

之后背景色不在变化，与分页无关，如果第一页就超过41行，则第一页背景色都显示不全

代码:

    public function excelClassClubStudent(array $club_list): PHPExcel
    {
        $this->excel = new PHPExcel();

        // $styleThinBlackBorderOutline = [
        //          'borders' => [
        //              'allborders' => [ //设置全部边框
        //                  'style' => \PHPExcel_Style_Border::BORDER_THIN  //粗的是BORDER_THICK
        //              ],
        //          ],
        //      ];


        $count = 0;
        foreach ($club_list as $key => $val) {
            if($key > 0){
               $this->excel->createSheet();
            }
               // $this->excel->createSheet();

            $this->excel->setActiveSheetIndex($count);

            $this->excel->getActiveSheet()->setTitle($val['class_allname']);

            //内容增加一行名称
            $sheettitle = $val['class_allname'] . "社团名册";
            $this->excel->getActiveSheet()->mergeCells('A1'.':'.'E1');
            $pCoordinate = PHPExcel_Cell::stringFromColumnIndex(0) . '' . (1);
            $this->excel->getActiveSheet()->setCellValue($pCoordinate, $sheettitle);
            $this->excel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

            //填充表头外数据
            $sheet = $this->excel->getActiveSheet();
            $sheet->setCellValue('A' . 2, ' '."班级");
            $sheet->setCellValue('B' . 2, ' '."新步伐号");
            $sheet->setCellValue('C' . 2, ' '."姓名");
            $sheet->setCellValue('D' . 2, ' '."社团名称");
            $sheet->setCellValue('E' . 2, ' '."上课地点");

            //设置颜色
            $sheet->getStyle( 'A2:E2')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
            $sheet->getStyle( 'A2:E2')->getFill()->getStartColor()->setARGB('FFAAAAAA');
            // $sheet->getStyle( 'A2:E2')->applyFromArray($styleThinBlackBorderOutline);

            $_k = 3;//开始行
            $line = ['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'];
            $num = 1;
            foreach($val['list'] as  $_v)
            {   
                
                $sheet->setCellValue('A' . $_k, ' '.$val['class_allname']);
                $sheet->setCellValue('B' . $_k, ' '.$_v['scs_account_club']);
                $sheet->setCellValue('C' . $_k, ' '.$_v['last_name'] . $_v['first_name']);
                $sheet->setCellValue('D' . $_k, ' '.$_v['sc_club_name']);
                $sheet->setCellValue('E' . $_k, ' '.$_v['sc_address']);

                $sheet->getStyle( 'A' . $_k . ':E' . $_k)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
                $sheet->getStyle( 'A' . $_k . ':E' . $_k)->getFill()->getStartColor()->setARGB('FFAAAAAA');

                // $sheet->getStyle( 'A' . $_k . ':E' . $_k)->applyFromArray($styleThinBlackBorderOutline);
                $sheet->getColumnDimension($line[0])->setWidth(15);
                $sheet->getColumnDimension($line[1])->setWidth(10);
                $sheet->getColumnDimension($line[2])->setWidth(15);
                $sheet->getColumnDimension($line[3])->setWidth(25);
                $sheet->getColumnDimension($line[4])->setWidth(25);

                ++$_k;
                ++$num;
                
            }
            
                ++$count;
            $sheet->getPageSetup()->setPrintArea('A1:E' . $_k);
        }

 修改后：

    public function excelClassClubStudent(array $club_list): PHPExcel
    {
        $this->excel = new PHPExcel();

        $count = 0;
        foreach ($club_list as $key => $val) {
            if($key > 0){
               $this->excel->createSheet();
            }
               // $this->excel->createSheet();

            $this->excel->setActiveSheetIndex($count);

            $this->excel->getActiveSheet()->setTitle($val['class_allname']);

            //内容增加一行名称
            $sheettitle = $val['class_allname'] . "社团名册";
            $this->excel->getActiveSheet()->mergeCells('A1'.':'.'E1');
            $pCoordinate = PHPExcel_Cell::stringFromColumnIndex(0) . '' . (1);
            $this->excel->getActiveSheet()->setCellValue($pCoordinate, $sheettitle);
            $this->excel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

            //填充表头外数据
            $sheet = $this->excel->getActiveSheet();
            $sheet->setCellValue('A' . 2, ' '."班级");
            $sheet->setCellValue('B' . 2, ' '."新步伐号");
            $sheet->setCellValue('C' . 2, ' '."姓名");
            $sheet->setCellValue('D' . 2, ' '."社团名称");
            $sheet->setCellValue('E' . 2, ' '."上课地点");

            //设置颜色
            $sheet->getStyle( 'A2:E2')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
            $sheet->getStyle( 'A2:E2')->getFill()->getStartColor()->setARGB('FFAAAAAA');
            // $sheet->getStyle( 'A2:E2')->applyFromArray($styleThinBlackBorderOutline);

            $_k = 3;//开始行
            $line = ['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'];
            $num = 1;
            foreach($val['list'] as  $_v)
            {   
                
                $sheet->setCellValue('A' . $_k, ' '.$val['class_allname']);
                $sheet->setCellValue('B' . $_k, ' '.$_v['scs_account_club']);
                $sheet->setCellValue('C' . $_k, ' '.$_v['last_name'] . $_v['first_name']);
                $sheet->setCellValue('D' . $_k, ' '.$_v['sc_club_name']);
                $sheet->setCellValue('E' . $_k, ' '.$_v['sc_address']);

                // $sheet->getStyle( 'A' . $_k . ':E' . $_k)->applyFromArray($styleThinBlackBorderOutline);
                $sheet->getColumnDimension($line[0])->setWidth(15);
                $sheet->getColumnDimension($line[1])->setWidth(10);
                $sheet->getColumnDimension($line[2])->setWidth(15);
                $sheet->getColumnDimension($line[3])->setWidth(25);
                $sheet->getColumnDimension($line[4])->setWidth(25);

                ++$_k;
                ++$num;
                
            }
            
                ++$count;
                $_k--;
            $sheet->getStyle( 'A' . 2 . ':E' . $_k)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
            $sheet->getStyle( 'A' . 2 . ':E' . $_k)->getFill()->getStartColor()->setARGB('FFAAAAAA');
            $sheet->getPageSetup()->setPrintArea('A1:E' . $_k);
        }


        return $this->excel;
    }