PHPExcel php生成excel表格

本文主要是介绍PHPExcel php生成excel表格，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！
vendor("PHPExcel.PHPExcel");       //加载PHPExcel类库

$objPHPExcel = new \PHPExcel();   //实例化PHPExcel类

$objPHPExcel->setActiveSheetIndex(0);       //激活当前的sheet表

$objPHPExcel->getDefaultStyle()->getFont()->setSize(15);   //设置默认字体大小

$objPHPExcel->getActiveSheet()->getRowDimension()->setRowHeight(15);   //设置默认行高

$objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(30);   //设置第一行行高

$objPHPExcel->getActiveSheet()->getStyle( 'B1')->getFont()->setSize(20);   //设置B1单元格字体大小

$objPHPExcel->getActiveSheet()->getStyle( 'B1')->getFont()->setBold(true);   //字体加粗

//设置水平居中
$objPHPExcel->getDefaultStyle()->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

//设置垂直居中
$objPHPExcel->getDefaultStyle()->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);

$objPHPExcel->getActiveSheet()->mergeCells('B1:D1');   //合并单元格

$objPHPExcel->getActiveSheet()->unmergeCells('A28:B28');   //拆分单元格

$objPHPExcel->getActiveSheet()->setCellValue('B1', '日生产异常报表');   //设置指定单元格的值

$styleArray = array(
                'borders' => array(
                    'allborders' => array(
                        'style' => \PHPExcel_Style_Border::BORDER_THIN,//细边框
                    ),
                ),
            );
$objPHPExcel->getActiveSheet()->getStyle( 'B1:C1')->applyFromArray($styleArray);   //设置边框

$objPHPExcel->getActiveSheet()->setTitle($na.'报表');      //设置sheet的名称
$objPHPExcel->setActiveSheetIndex(0);                   //设置sheet的起始位置
$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');   //通过PHPExcel_IOFactory的写函数将上面数据写出来
$PHPWriter = \PHPExcel_IOFactory::createWriter( $objPHPExcel,"Excel2007");

header('Content-Disposition: attachment;filename="'.$name.'.xlsx"');
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
return json($objWriter->save("php://output")); //表示在$path路径下面生成demo.xlsx文件
        if ($rq && $cj){
            //$su == 1;  日报表
            //$su == 2;  周报表
            //$su == 3;  月报表

            if ($su==1){
                $na = '日';
                //查询warn_record表需要导出的数据
                $rows = db('warn_record')
                    ->field('tags2, duration_time, start_time, finish_time, bz, jtbh')
                    ->where('start_time','like',$rq.'%')
                    ->where('cj',$cj)
                    ->where('is_warn','=','0')
                    ->select();

                //查询warn_day表需要导出的数据
                $rows_warn_day = db('warn_day')
                    ->field('run_time, normal_time, number, bz, cl, jtbh')
                    ->where('name',$rq)
                    ->where('cj',$cj)
                    ->select();
            }elseif ($su==2){
                $na = '周';
                $rq2 = request()->param('rq2');
                $rq3 = substr($rq2,0,9).(substr($rq2,9,1)+1);

                //查询warn_record表需要导出的数据
                $rows = db('warn_record')
                    ->field('tags2, duration_time, start_time, finish_time, bz, jtbh')
                    ->where('start_time','>',$rq)
                    ->where('start_time','<',$rq3)
                    ->where('cj',$cj)
                    ->where('is_warn','=','0')
                    ->select();

                //查询warn_day表需要导出的数据
                $rows_warn_day = db('warn_day')
                    ->field('run_time, normal_time, number, bz, cl, jtbh')
                    ->where('name','>=',$rq)
                    ->where('name','<=',$rq2)
                    ->where('cj',$cj)
                    ->select();
            }elseif ($su==3){
                $na = '月';
                $rq = substr($rq,0,7);
                //查询warn_record表需要导出的数据
                $rows = db('warn_record')
                    ->field('tags2, duration_time, start_time, finish_time, bz, jtbh')
                    ->where('start_time','like',$rq.'%')
                    ->where('cj',$cj)
                    ->where('is_warn','=','0')
                    ->select();

                //查询warn_day表需要导出的数据
                $rows_warn_day = db('warn_day')
                    ->field('run_time, normal_time, number, bz, cl, jtbh')
                    ->where('name','like',$rq.'%')
                    ->where('cj',$cj)
                    ->select();
            }
            //判断是否查到数据
            if (count($rows_warn_day)==0){
                return $this->error('未查到记录');
            }




            //1.加载PHPExcel类库
            vendor("PHPExcel.PHPExcel");
            vendor("PHPExcel.PHPExcel.Writer.Excel5");
            vendor("PHPExcel.PHPExcel.Writer.Excel2007");
            vendor("PHPExcel.PHPExcel.IOFactory");

            //2.实例化PHPExcel类
            $objPHPExcel = new \PHPExcel();

            //3.激活当前的sheet表
            $objPHPExcel->setActiveSheetIndex(0);


            //异常类型汇总
            foreach ($rows as $k=>$v){
                $rows_tags2[$k] = $v['tags2'];
            }
//halt($rows);
            if (count($rows)==0){
                $num = 0;
            }else{
                $num = count(array_unique($rows_tags2));       //异常类型个数
            }

            $objPHPExcel->getDefaultStyle()->getFont()->setSize(14);    //设置默认字体大小
            $objPHPExcel->getDefaultStyle()->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);   //设置水平居中
            $objPHPExcel->getDefaultStyle()->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);   //设置垂直居中
            $styleArray = array(
                'borders' => array(
                    'allborders' => array(
                        'style' => \PHPExcel_Style_Border::BORDER_THIN,//细边框
                    ),
                ),
            );      //边框样式

            $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(5);

            $objPHPExcel->getActiveSheet()->mergeCells('B1:'.chr(65+6+$num*2).'1');         //合并单元格(第一行)

            $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(30);         //设置第一行行高
            $objPHPExcel->getActiveSheet()->getStyle( 'B1')->getFont()->setSize(16);        //设置B1字体大小

            if ($su==1){
                //表名
                $name = $cj.$na.'生产异常报表'.trim(substr($rq,5,2)).'月'.trim(substr($rq,8,2)).'日';
                $objPHPExcel->getActiveSheet()->setCellValue('B1', $cj.$na.'生产异常报表'.trim(substr($rq,5,2)).'月'.trim(substr($rq,8,2)).'日');      //设置指定单元格文字
            }elseif ($su==2){
                $name = $cj.$na.'生产异常报表'.trim(substr($rq,5,2)).'月'.trim(substr($rq,8,2)).'日-'.trim(substr($rq2,5,2)).'月'.trim(substr($rq2,8,2)).'日';
                $objPHPExcel->getActiveSheet()->setCellValue('B1', $cj.$na.'生产异常报表'.trim(substr($rq,5,2)).'月'.trim(substr($rq,8,2)).'日-'.trim(substr($rq2,5,2)).'月'.trim(substr($rq2,8,2)).'日');      //设置指定单元格文字
            }elseif ($su==3){
                $name = $cj.$na.'生产异常报表'.trim((int)substr($rq,5,2)).'月';
                $objPHPExcel->getActiveSheet()->setCellValue('B1', $cj.$na.'生产异常报表'.trim((int)substr($rq,5,2)).'月');      //设置指定单元格文字
            }

            $objPHPExcel->getActiveSheet()->mergeCells('B2:B3');    //合并单元格
            $objPHPExcel->getActiveSheet()->mergeCells('C2:C3');
            $objPHPExcel->getActiveSheet()->mergeCells('D2:D3');
            $objPHPExcel->getActiveSheet()->mergeCells('E2:E3');
            $objPHPExcel->getActiveSheet()->mergeCells('F2:F3');
            $objPHPExcel->getActiveSheet()->setCellValue('B2', '机台');
            $objPHPExcel->getActiveSheet()->setCellValue('C2', '班次');
            $objPHPExcel->getActiveSheet()->setCellValue('D2', '运行时间');
            $objPHPExcel->getActiveSheet()->setCellValue('E2', '产量');
            $objPHPExcel->getActiveSheet()->setCellValue('F2', '正常生产时间');
            $objPHPExcel->getActiveSheet()->getStyle('B2')->getFont()->setSize(11);
            $objPHPExcel->getActiveSheet()->getStyle('C2')->getFont()->setSize(11);
            $objPHPExcel->getActiveSheet()->getStyle('D2')->getFont()->setSize(11);
            $objPHPExcel->getActiveSheet()->getStyle('E2')->getFont()->setSize(11);
            $objPHPExcel->getActiveSheet()->getStyle('F2')->getFont()->setSize(11);
            $objPHPExcel->getActiveSheet()->getStyle( 'B2:B3')->applyFromArray($styleArray);    //设置边框
            $objPHPExcel->getActiveSheet()->getStyle( 'C2:C3')->applyFromArray($styleArray);
            $objPHPExcel->getActiveSheet()->getStyle( 'D2:D3')->applyFromArray($styleArray);
            $objPHPExcel->getActiveSheet()->getStyle( 'E2:E3')->applyFromArray($styleArray);
            $objPHPExcel->getActiveSheet()->getStyle( 'F2:F3')->applyFromArray($styleArray);

            if (count($rows)!=0){
                //异常类型持续时间汇总
                foreach (array_unique($rows_tags2) as $k=>$v){
                    $arr1[$v] = 0;
                    foreach ($rows as $m=>$n) {
                        if ($n['tags2']==$v){
                            $arr1[$v] += $this->stringToSec($n['duration_time']);
                        }
                    }
                }

                arsort($arr1);       //根据持续时间降序排列
                $arr_tags2 = array_keys($arr1);
                foreach ($arr_tags2 as $k=>$v){
                    $objPHPExcel->getActiveSheet()->mergeCells(chr(65+6+2*$k).'2:'.chr(65+6+2*$k+1).'2');    //合并单元格
                    $objPHPExcel->getActiveSheet()->setCellValue(chr(65+6+2*$k).'2', $v);
                    $objPHPExcel->getActiveSheet()->getStyle(chr(65+6+2*$k).'2')->getFont()->setSize(11);
                    $objPHPExcel->getActiveSheet()->getStyle( chr(65+6+2*$k).'2:'.chr(65+6+2*$k+1).'2')->applyFromArray($styleArray);

                    $objPHPExcel->getActiveSheet()->setCellValue(chr(65+6+2*$k).'3', '时间汇总');
                    $objPHPExcel->getActiveSheet()->setCellValue(chr(65+6+2*$k+1).'3', '次数汇总');
                    $objPHPExcel->getActiveSheet()->getStyle(chr(65+6+2*$k).'3')->getFont()->setSize(11);
                    $objPHPExcel->getActiveSheet()->getStyle(chr(65+6+2*$k+1).'3')->getFont()->setSize(11);
                    $objPHPExcel->getActiveSheet()->getStyle( chr(65+6+2*$k).'3:'.chr(65+6+2*$k).'3')->applyFromArray($styleArray);
                    $objPHPExcel->getActiveSheet()->getStyle( chr(65+6+2*$k+1).'3:'.chr(65+6+2*$k+1).'3')->applyFromArray($styleArray);
                }
            }

            //异常类型机台汇总
            foreach ($rows_warn_day as $k=>$v){
                $rows_jtbh[$k] = $v['jtbh'];
            }
            $rows_jtbh = array_values(array_unique($rows_jtbh));
            sort($rows_jtbh);

            foreach ($rows_jtbh as $k=>$v){
                $objPHPExcel->getActiveSheet()->mergeCells('B'.(4+$k*3).':B'.(4+$k*3+2));
                $objPHPExcel->getActiveSheet()->setCellValue('B'.(4+$k*3),$v);
                $objPHPExcel->getActiveSheet()->getStyle('B'.(4+$k*3))->getFont()->setSize(11);
                $objPHPExcel->getActiveSheet()->getStyle( 'B'.(4+$k*3).':B'.(4+$k*3+2))->applyFromArray($styleArray);

                $objPHPExcel->getActiveSheet()->setCellValue('C'.(4+$k*3),'A');
                $objPHPExcel->getActiveSheet()->setCellValue('C'.(4+$k*3+1),'B');
                $objPHPExcel->getActiveSheet()->setCellValue('C'.(4+$k*3+2),'合计');
                $objPHPExcel->getActiveSheet()->getStyle('C'.(4+$k*3))->getFont()->setSize(11);
                $objPHPExcel->getActiveSheet()->getStyle('C'.(4+$k*3+1))->getFont()->setSize(11);
                $objPHPExcel->getActiveSheet()->getStyle('C'.(4+$k*3+2))->getFont()->setSize(11);
                $objPHPExcel->getActiveSheet()->getStyle( 'C'.(4+$k*3).':C'.(4+$k*3))->applyFromArray($styleArray);
                $objPHPExcel->getActiveSheet()->getStyle( 'C'.(4+$k*3+1).':C'.(4+$k*3+1))->applyFromArray($styleArray);
                $objPHPExcel->getActiveSheet()->getStyle( 'C'.(4+$k*3+2).':C'.(4+$k*3+2))->applyFromArray($styleArray);


                //汇总统计数据（运行时间、产量、正常生产时间）
                $arr['run_timeA'] = $arr['clA'] = $arr['normal_timeA'] = 0;
                $arr['run_timeB'] = $arr['clB'] = $arr['normal_timeB'] = 0;
                $arr['run_timeC'] = $arr['clC'] = $arr['normal_timeC'] = 0;
                foreach ($rows_warn_day as $m=>$n){
                    if ($n['jtbh']==$v && $n['bz']=='A班') {
                        $arr['run_timeA'] += $this->stringToSec($n['run_time']);
                        $arr['clA'] += $n['cl'];
                        $arr['normal_timeA'] += $this->stringToSec($n['normal_time']);
                    }
                    if ($n['jtbh']==$v && $n['bz']=='B班') {
                        $arr['run_timeB'] += $this->stringToSec($n['run_time']);
                        $arr['clB'] += $n['cl'];
                        $arr['normal_timeB'] += $this->stringToSec($n['normal_time']);
                    }
                }

                //计算合计
                $arr['run_timeC'] = $arr['run_timeA']+$arr['run_timeB'];
                $arr['clC'] = $arr['clA']+$arr['clB'];
                $arr['normal_timeC'] = $arr['normal_timeA']+$arr['normal_timeB'];

                //int转00:00:00
                $arr['run_timeA'] = $this->secToString($arr['run_timeA']);
                $arr['normal_timeA'] = $this->secToString($arr['normal_timeA']);
                $arr['run_timeB'] = $this->secToString($arr['run_timeB']);
                $arr['normal_timeB'] = $this->secToString($arr['normal_timeB']);
                $arr['run_timeC'] = $this->secToString($arr['run_timeC']);
                $arr['normal_timeC'] = $this->secToString($arr['normal_timeC']);

                //遍历插入
                for($i=0; $i<=3;$i++){
                    $objPHPExcel->getActiveSheet()->setCellValue('D'.(4+$k*3),$arr['run_timeA']);
                    $objPHPExcel->getActiveSheet()->setCellValue('E'.(4+$k*3),$arr['clA']);
                    $objPHPExcel->getActiveSheet()->setCellValue('F'.(4+$k*3),$arr['normal_timeA']);
                    $objPHPExcel->getActiveSheet()->getStyle('D'.(4+$k*3))->getFont()->setSize(11);
                    $objPHPExcel->getActiveSheet()->getStyle('E'.(4+$k*3))->getFont()->setSize(11);
                    $objPHPExcel->getActiveSheet()->getStyle('F'.(4+$k*3))->getFont()->setSize(11);
                    $objPHPExcel->getActiveSheet()->getStyle( 'D'.(4+$k*3).':D'.(4+$k*3))->applyFromArray($styleArray);
                    $objPHPExcel->getActiveSheet()->getStyle( 'E'.(4+$k*3).':E'.(4+$k*3))->applyFromArray($styleArray);
                    $objPHPExcel->getActiveSheet()->getStyle( 'F'.(4+$k*3).':F'.(4+$k*3))->applyFromArray($styleArray);

                    $objPHPExcel->getActiveSheet()->setCellValue('D'.(4+$k*3+1),$arr['run_timeB']);
                    $objPHPExcel->getActiveSheet()->setCellValue('E'.(4+$k*3+1),$arr['clB']);
                    $objPHPExcel->getActiveSheet()->setCellValue('F'.(4+$k*3+1),$arr['normal_timeB']);
                    $objPHPExcel->getActiveSheet()->getStyle('D'.(4+$k*3+1))->getFont()->setSize(11);
                    $objPHPExcel->getActiveSheet()->getStyle('E'.(4+$k*3+1))->getFont()->setSize(11);
                    $objPHPExcel->getActiveSheet()->getStyle('F'.(4+$k*3+1))->getFont()->setSize(11);
                    $objPHPExcel->getActiveSheet()->getStyle( 'D'.(4+$k*3+1).':D'.(4+$k*3+1))->applyFromArray($styleArray);
                    $objPHPExcel->getActiveSheet()->getStyle( 'E'.(4+$k*3+1).':E'.(4+$k*3+1))->applyFromArray($styleArray);
                    $objPHPExcel->getActiveSheet()->getStyle( 'F'.(4+$k*3+1).':F'.(4+$k*3+1))->applyFromArray($styleArray);

                    $objPHPExcel->getActiveSheet()->setCellValue('D'.(4+$k*3+2),$arr['run_timeC']);
                    $objPHPExcel->getActiveSheet()->setCellValue('E'.(4+$k*3+2),$arr['clC']);
                    $objPHPExcel->getActiveSheet()->setCellValue('F'.(4+$k*3+2),$arr['normal_timeC']);
                    $objPHPExcel->getActiveSheet()->getStyle('D'.(4+$k*3+2))->getFont()->setSize(11);
                    $objPHPExcel->getActiveSheet()->getStyle('E'.(4+$k*3+2))->getFont()->setSize(11);
                    $objPHPExcel->getActiveSheet()->getStyle('F'.(4+$k*3+2))->getFont()->setSize(11);
                    $objPHPExcel->getActiveSheet()->getStyle( 'D'.(4+$k*3+2).':D'.(4+$k*3+2))->applyFromArray($styleArray);
                    $objPHPExcel->getActiveSheet()->getStyle( 'E'.(4+$k*3+2).':E'.(4+$k*3+2))->applyFromArray($styleArray);
                    $objPHPExcel->getActiveSheet()->getStyle( 'F'.(4+$k*3+2).':F'.(4+$k*3+2))->applyFromArray($styleArray);

                }

                if (count($rows)!=0){
                    //异常
                    foreach ($arr_tags2 as $m=>$n){
                        //A
                        $rowA = [];
                        foreach ($rows as $row){
                            if ($row['jtbh']==$v && $row['tags2']==$n && $row['bz']=='A组'){
                                array_push($rowA, $row);
                            }
                        }

                        $row_A['time'] = 0;
                        foreach ($rowA as $item=>$value){
                            $row_A['time'] += $this->stringToSec($value['duration_time']);
                        }
                        $row_A['time'] = $this->secToString($row_A['time']);
                        $row_A['num'] = count($rowA);

                        if ($row_A['num']!=0){
                            $objPHPExcel->getActiveSheet()->setCellValue(chr(65+6+2*$m).(4+$k*3),$row_A['time']);
                            $objPHPExcel->getActiveSheet()->setCellValue(chr(65+6+2*$m+1).(4+$k*3),$row_A['num']);
                            $objPHPExcel->getActiveSheet()->getStyle(chr(65+6+2*$m).(4+$k*3))->getFont()->setSize(11);
                            $objPHPExcel->getActiveSheet()->getStyle(chr(65+6+2*$m+1).(4+$k*3))->getFont()->setSize(11);
                            $objPHPExcel->getActiveSheet()->getStyle( chr(65+6+2*$m).(4+$k*3).':'.chr(65+6+2*$m).(4+$k*3))->applyFromArray($styleArray);
                            $objPHPExcel->getActiveSheet()->getStyle( chr(65+6+2*$m+1).(4+$k*3).':'.chr(65+6+2*$m+1).(4+$k*3))->applyFromArray($styleArray);
                        }else{
                            $objPHPExcel->getActiveSheet()->getStyle(chr(65+6+2*$m).(4+$k*3))->getFont()->setSize(11);
                            $objPHPExcel->getActiveSheet()->getStyle(chr(65+6+2*$m+1).(4+$k*3))->getFont()->setSize(11);
                            $objPHPExcel->getActiveSheet()->getStyle( chr(65+6+2*$m).(4+$k*3).':'.chr(65+6+2*$m).(4+$k*3))->applyFromArray($styleArray);
                            $objPHPExcel->getActiveSheet()->getStyle( chr(65+6+2*$m+1).(4+$k*3).':'.chr(65+6+2*$m+1).(4+$k*3))->applyFromArray($styleArray);
                        }

                        //B
                        $rowB = [];
                        foreach ($rows as $row){
                            if ($row['jtbh']==$v && $row['tags2']==$n && $row['bz']=='B组'){
                                array_push($rowB, $row);
                            }
                        }
                        $row_B['time'] = 0;
                        foreach ($rowB as $item=>$value){
                            $row_B['time'] += $this->stringToSec($value['duration_time']);
                        }
                        $row_B['time'] = $this->secToString($row_B['time']);
                        $row_B['num'] = count($rowB);

                        if ($row_B['num']!=0){
                            $objPHPExcel->getActiveSheet()->setCellValue(chr(65+6+2*$m).(4+$k*3+1),$row_B['time']);
                            $objPHPExcel->getActiveSheet()->setCellValue(chr(65+6+2*$m+1).(4+$k*3+1),$row_B['num']);
                            $objPHPExcel->getActiveSheet()->getStyle(chr(65+6+2*$m).(4+$k*3+1))->getFont()->setSize(11);
                            $objPHPExcel->getActiveSheet()->getStyle(chr(65+6+2*$m+1).(4+$k*3+1))->getFont()->setSize(11);
                            $objPHPExcel->getActiveSheet()->getStyle( chr(65+6+2*$m).(4+$k*3+1).':'.chr(65+6+2*$m).(4+$k*3+1))->applyFromArray($styleArray);
                            $objPHPExcel->getActiveSheet()->getStyle( chr(65+6+2*$m+1).(4+$k*3+1).':'.chr(65+6+2*$m+1).(4+$k*3+1))->applyFromArray($styleArray);
                        }else{
                            $objPHPExcel->getActiveSheet()->getStyle(chr(65+6+2*$m).(4+$k*3+1))->getFont()->setSize(11);
                            $objPHPExcel->getActiveSheet()->getStyle(chr(65+6+2*$m+1).(4+$k*3+1))->getFont()->setSize(11);
                            $objPHPExcel->getActiveSheet()->getStyle( chr(65+6+2*$m).(4+$k*3+1).':'.chr(65+6+2*$m).(4+$k*3+1))->applyFromArray($styleArray);
                            $objPHPExcel->getActiveSheet()->getStyle( chr(65+6+2*$m+1).(4+$k*3+1).':'.chr(65+6+2*$m+1).(4+$k*3+1))->applyFromArray($styleArray);
                        }

                        //C合计
                        $row_C['time'] = $this->secToString($this->stringToSec($row_A['time'])+$this->stringToSec($row_B['time']));
                        $row_C['num'] = $row_A['num']+$row_B['num'];
                        if ($row_C['num']!=0){
                            $objPHPExcel->getActiveSheet()->setCellValue(chr(65+6+2*$m).(4+$k*3+2),$row_C['time']);
                            $objPHPExcel->getActiveSheet()->setCellValue(chr(65+6+2*$m+1).(4+$k*3+2),$row_C['num']);
                            $objPHPExcel->getActiveSheet()->getStyle(chr(65+6+2*$m).(4+$k*3+2))->getFont()->setSize(11);
                            $objPHPExcel->getActiveSheet()->getStyle(chr(65+6+2*$m+1).(4+$k*3+2))->getFont()->setSize(11);
                            $objPHPExcel->getActiveSheet()->getStyle( chr(65+6+2*$m).(4+$k*3+2).':'.chr(65+6+2*$m).(4+$k*3+2))->applyFromArray($styleArray);
                            $objPHPExcel->getActiveSheet()->getStyle( chr(65+6+2*$m+1).(4+$k*3+2).':'.chr(65+6+2*$m+1).(4+$k*3+2))->applyFromArray($styleArray);
                        }else{
                            $objPHPExcel->getActiveSheet()->getStyle(chr(65+6+2*$m).(4+$k*3+2))->getFont()->setSize(11);
                            $objPHPExcel->getActiveSheet()->getStyle(chr(65+6+2*$m+1).(4+$k*3+2))->getFont()->setSize(11);
                            $objPHPExcel->getActiveSheet()->getStyle( chr(65+6+2*$m).(4+$k*3+2).':'.chr(65+6+2*$m).(4+$k*3+2))->applyFromArray($styleArray);
                            $objPHPExcel->getActiveSheet()->getStyle( chr(65+6+2*$m+1).(4+$k*3+2).':'.chr(65+6+2*$m+1).(4+$k*3+2))->applyFromArray($styleArray);
                        }
                    }
                }else{
                    $objPHPExcel->getActiveSheet()->mergeCells('G2:G3');
                    $objPHPExcel->getActiveSheet()->setCellValue('G2','无异常');
                    $objPHPExcel->getActiveSheet()->getStyle('G2')->getFont()->setSize(11);
                }


            }

//3+3*count($rows_jtbh)+2;
//超3小时明细
            /*----------------------------------*/
            $num = 3;
            $objPHPExcel->getActiveSheet()->mergeCells('B'.(3+3*count($rows_jtbh)+2).':F'.(3+3*count($rows_jtbh)+2));         //合并单元格(第一行)

            $objPHPExcel->getActiveSheet()->getRowDimension(3+3*count($rows_jtbh)+2)->setRowHeight(30);         //设置第一行行高
            $objPHPExcel->getActiveSheet()->getStyle( 'B'.(3+3*count($rows_jtbh)+2))->getFont()->setSize(16);        //设置B1字体大小
            $objPHPExcel->getActiveSheet()->setCellValue('B'.(3+3*count($rows_jtbh)+2), '每'.$na.'生产异常超'.$num.'小时明细（单次）');      //设置指定单元格文字

            $objPHPExcel->getActiveSheet()->setCellValue('B'.(3+3*count($rows_jtbh)+3), '机台');
            $objPHPExcel->getActiveSheet()->setCellValue('C'.(3+3*count($rows_jtbh)+3), '班次');
            $objPHPExcel->getActiveSheet()->setCellValue('D'.(3+3*count($rows_jtbh)+3), '异常类型');
            $objPHPExcel->getActiveSheet()->setCellValue('E'.(3+3*count($rows_jtbh)+3), '异常时间汇总');
            $objPHPExcel->getActiveSheet()->setCellValue('F'.(3+3*count($rows_jtbh)+3), '异常次数');
            $objPHPExcel->getActiveSheet()->getStyle('B'.(3+3*count($rows_jtbh)+3))->getFont()->setSize(11);
            $objPHPExcel->getActiveSheet()->getStyle('C'.(3+3*count($rows_jtbh)+3))->getFont()->setSize(11);
            $objPHPExcel->getActiveSheet()->getStyle('D'.(3+3*count($rows_jtbh)+3))->getFont()->setSize(11);
            $objPHPExcel->getActiveSheet()->getStyle('E'.(3+3*count($rows_jtbh)+3))->getFont()->setSize(11);
            $objPHPExcel->getActiveSheet()->getStyle('F'.(3+3*count($rows_jtbh)+3))->getFont()->setSize(11);
            $objPHPExcel->getActiveSheet()->getStyle( 'B'.(3+3*count($rows_jtbh)+3).':B'.(3+3*count($rows_jtbh)+3))->applyFromArray($styleArray);
            $objPHPExcel->getActiveSheet()->getStyle( 'C'.(3+3*count($rows_jtbh)+3).':C'.(3+3*count($rows_jtbh)+3))->applyFromArray($styleArray);
            $objPHPExcel->getActiveSheet()->getStyle( 'D'.(3+3*count($rows_jtbh)+3).':D'.(3+3*count($rows_jtbh)+3))->applyFromArray($styleArray);
            $objPHPExcel->getActiveSheet()->getStyle( 'E'.(3+3*count($rows_jtbh)+3).':E'.(3+3*count($rows_jtbh)+3))->applyFromArray($styleArray);
            $objPHPExcel->getActiveSheet()->getStyle( 'F'.(3+3*count($rows_jtbh)+3).':F'.(3+3*count($rows_jtbh)+3))->applyFromArray($styleArray);


            foreach($rows_jtbh as $k=>$v){
                //left
                static $left = 0;
                if ($num < 10) $num = '0'.$num;
                $rows2 = [];
                foreach ($rows as $row) {
                    if ($row['jtbh']==$v && $row['duration_time']>$num.':00:00'){
                        array_push($rows2,$row);
                    }
                }

                $rows2_tags2 = [];
                foreach ($rows2 as $i=>$j){
                    $rows2_tags2[$i] = $j['tags2'];
                }
                $number = count(array_unique($rows2_tags2));

                if ($number!=0){
                    $objPHPExcel->getActiveSheet()->mergeCells('B'.(3+3*count($rows_jtbh)+4+$left).':B'.(3+3*count($rows_jtbh)+4+$left+$number-1));

                    $objPHPExcel->getActiveSheet()->setCellValue('B'.(3+3*count($rows_jtbh)+4+$left), $v);
                    $objPHPExcel->getActiveSheet()->getStyle('B'.(3+3*count($rows_jtbh)+4+$left))->getFont()->setSize(11);
                    $objPHPExcel->getActiveSheet()->getStyle( 'B'.(3+3*count($rows_jtbh)+4+$left).':B'.(3+3*count($rows_jtbh)+4+$left+$number-1))->applyFromArray($styleArray);

                    $left += $number;

                    //right
                    static $flag = 0;
                    for($o=0;$o<2;$o++){
                        $o==0?$bz='A':$bz='B';
                        //A/B组
                        $rows2 = [];
                        foreach ($rows as $row) {
                            if ($row['jtbh']==$v && $row['duration_time']>$num.':00:00' && $row['bz']==$bz.'组'){
                                array_push($rows2,$row);
                            }
                        }

                        $rows2_tags2 = [];
                        foreach ($rows2 as $i=>$j){
                            $rows2_tags2[$i] = $j['tags2'];
                        }
                        $r = array_count_values($rows2_tags2);     //异常类型=>停机次数

                        $arr2 = [];
                        foreach (array_unique($rows2_tags2) as $j){
                            $arr2[$j] = 0;
                            foreach ($rows2 as $m=>$n) {
                                if ($n['tags2']==$j){
                                    $arr2[$j] += $this->stringToSec($n['duration_time']);
                                }
                            }
                        }

                        arsort($arr2);       //根据持续时间降序排列
                        $arr2_tags2 = array_keys($arr2);

                        foreach($arr2_tags2 as $i=>$j){
                            $flag++;
                            $objPHPExcel->getActiveSheet()->setCellValue('C'.(3+3*count($rows_jtbh)+3+$flag), $bz);
                            $objPHPExcel->getActiveSheet()->setCellValue('D'.(3+3*count($rows_jtbh)+3+$flag), $j);
                            $objPHPExcel->getActiveSheet()->setCellValue('E'.(3+3*count($rows_jtbh)+3+$flag), $this->secToString($arr2[$j]));
                            $objPHPExcel->getActiveSheet()->setCellValue('F'.(3+3*count($rows_jtbh)+3+$flag), $r[$j]);
                            $objPHPExcel->getActiveSheet()->getStyle('C'.(3+3*count($rows_jtbh)+3+$flag))->getFont()->setSize(11);
                            $objPHPExcel->getActiveSheet()->getStyle('D'.(3+3*count($rows_jtbh)+3+$flag))->getFont()->setSize(11);
                            $objPHPExcel->getActiveSheet()->getStyle('E'.(3+3*count($rows_jtbh)+3+$flag))->getFont()->setSize(11);
                            $objPHPExcel->getActiveSheet()->getStyle('F'.(3+3*count($rows_jtbh)+3+$flag))->getFont()->setSize(11);
                            $objPHPExcel->getActiveSheet()->getStyle( 'C'.(3+3*count($rows_jtbh)+3+$flag).':C'.(3+3*count($rows_jtbh)+3+$flag))->applyFromArray($styleArray);
                            $objPHPExcel->getActiveSheet()->getStyle( 'D'.(3+3*count($rows_jtbh)+3+$flag).':D'.(3+3*count($rows_jtbh)+3+$flag))->applyFromArray($styleArray);
                            $objPHPExcel->getActiveSheet()->getStyle( 'E'.(3+3*count($rows_jtbh)+3+$flag).':E'.(3+3*count($rows_jtbh)+3+$flag))->applyFromArray($styleArray);
                            $objPHPExcel->getActiveSheet()->getStyle( 'F'.(3+3*count($rows_jtbh)+3+$flag).':F'.(3+3*count($rows_jtbh)+3+$flag))->applyFromArray($styleArray);
                        }
                    }

                }

            }
            foreach ($rows as $row) {
                if ($row['jtbh']==$v && $row['duration_time']>$num.':00:00'){
                    array_push($rows2,$row);
                }
            }
            if (count($rows2)==0){
                $objPHPExcel->getActiveSheet()->mergeCells('B'.(3+3*count($rows_jtbh)+4).':F'.(3+3*count($rows_jtbh)+4));         //合并单元格(第一行)

                $objPHPExcel->getActiveSheet()->setCellValue('B'.(3+3*count($rows_jtbh)+4), '空');      //设置指定单元格文字
                $objPHPExcel->getActiveSheet()->getStyle('B'.(3+3*count($rows_jtbh)+4))->getFont()->setSize(11);
                $objPHPExcel->getActiveSheet()->getStyle( 'B'.(3+3*count($rows_jtbh)+4).':F'.(3+3*count($rows_jtbh)+4))->applyFromArray($styleArray);

            }
            $objPHPExcel->getActiveSheet()->setTitle($na.'报表');      //设置sheet的名称
            $objPHPExcel->setActiveSheetIndex(0);                   //设置sheet的起始位置
            $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');   //通过PHPExcel_IOFactory的写函数将上面数据写出来
            $PHPWriter = \PHPExcel_IOFactory::createWriter( $objPHPExcel,"Excel2007");

            header('Content-Disposition: attachment;filename="'.$name.'.xlsx"');
            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
            return json($objWriter->save("php://output")); //表示在$path路径下面生成demo.xlsx文件
        }else{
            return $this->error('请重试');
        }
这篇关于PHPExcel php生成excel表格的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！
PHP教程

PHPExcel php生成excel表格

前端开发

后端开发

移动端开发

数据库

服务器运维

人工智能

区块链

游戏开发

网站运营

大数据/云计算

软件工程

软件/开发工具使用

资讯
