随笔 - 292  文章 - 3 评论 - 0 阅读 - 73043
< 2025年3月 >
日 一 二 三 四 五 六
23 24 25 26 27 28 1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 29
30 31 1 2 3 4 5

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

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

 

 

 

 下方许多分页

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

遇到的问题是：

 

 前面41行背景色正常显示

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

代码:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
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);
    }

 修改后：

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
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;
}

 

posted on   Beautytoloveme  阅读(150)  评论(0)  编辑  收藏  举报
相关博文：
·  PHPExcel设置打印区域
·  php把汉字转化为拼音插件
·  隔行/列设置背景色
·  poi 导出设置 单元格颜色
·  element ui的分页的问题
阅读排行：
· 震惊！C++程序真的从main开始吗？99%的程序员都答错了
· winform 绘制太阳，地球，月球 运作规律
· 【硬核科普】Trae如何「偷看」你的代码？零基础破解AI编程运行原理
· 上周热点回顾（3.3-3.9）
· 超详细：普通电脑也行Windows部署deepseek R1训练数据并当服务器共享给他人
Copyright © 2025 Beautytoloveme
Powered by .NET 9.0 on Kubernetes Powered by: 博客园
点击右上角即可分享
微信分享提示
AI IDE Trae