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文件

//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);$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字体大小

$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);


　　　　　　　　//查询数据库写入数据
            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);
            }
$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文件