在线报名系统开发 php+mysql程序开发

微信、手机号码:18012098617 QQ:2373311510
当前位置:首页 > 程序详细介绍

PHPExcel循环生成多个Sheet的XLS

发布时间:2020-12-18 浏览次数:

function rs2($dir){
        
            //列出数据
            $sql="select 统筹区,单位编码 from rs_ry  group by 统筹区,单位编码";
            $r=oci_getAll($sql);
            
            $objExcel = new PHPExcel();
            
            $objWriter = new PHPExcel_Writer_Excel5($objExcel);
                
            foreach($r as $i => $vo){
                   
                //$objWriter->setOffice2003Compatibility(true);  

                //*************************************    
                //设置当前的sheet索引,用于后续的内容操作。    
                //一般只有在使用多个sheet的时候才需要显示调用。    
                //缺省情况下,PHPExcel会自动创建第一个sheet被设置SheetIndex=0    
                
                //$objActSheet = $objPHPExcel->getActiveSheet();
                //$objExcel->setActiveSheetIndex(0);    
                   
                   
                //$objActSheet = $objExcel->getActiveSheet(); 
                if ($i==0){
                    $objActSheet = $objExcel->getActiveSheet(); 
                }else{
                    $objActSheet = $objExcel->createSheet();
                }
                
                //设置当前活动sheet的名称    
                //$objActSheet->setTitle('Sheet1' );    
                   
                //*************************************    
                //设置单元格内容    
                //    
                //由PHPExcel根据传入内容自动判断单元格内容类型  

                //第一行
                $baseRow=1;


                $objActSheet->setCellValue('A'.$baseRow,iconv("utf8","utf-8",'序号  '));
                $objActSheet->setCellValue('B'.$baseRow,iconv("utf8","utf-8",'统筹区  '));
                $objActSheet->setCellValue('C'.$baseRow,iconv("utf8","utf-8",'单位编码  '));
                $objActSheet->setCellValue('D'.$baseRow,iconv("utf8","utf-8",'单位名称  '));
                $objActSheet->setCellValue('E'.$baseRow,iconv("utf8","utf-8",'姓名  '));
                $objActSheet->setCellValue('F'.$baseRow,iconv("utf8","utf-8",'证件类型  '));
                $objActSheet->setCellValue('G'.$baseRow,iconv("utf8","utf-8",'身份证号码  '));
                $objActSheet->setCellValue('H'.$baseRow,iconv("utf8","utf-8",'性别  '));
                $objActSheet->setCellValue('I'.$baseRow,iconv("utf8","utf-8",'国籍  '));
                $objActSheet->setCellValue('J'.$baseRow,iconv("utf8","utf-8",'民族  '));
                $objActSheet->setCellValue('K'.$baseRow,iconv("utf8","utf-8",'手机号码  '));
                $objActSheet->setCellValue('L'.$baseRow,iconv("utf8","utf-8",'出生日期  '));

                $baseRow=2;
                
                $sql="select * from rs_ry where 统筹区='".$vo['统筹区']."' and 单位编码='".$vo['单位编码']."'";

                $r2=oci_getAll($sql);

                foreach($r2 as $j => $v2)
                {    
                    $row = $baseRow + $j;
                    $objActSheet->setCellValueExplicit('A'.$row,($j+1),PHPExcel_Cell_DataType::TYPE_STRING);
  
                 
 $objActSheet->setCellValueExplicit('B'.$row,iconv("utf8","utf-8",$v2['统筹区']),PHPExcel_Cell_DataType::TYPE_STRING);
  
                 
 $objActSheet->setCellValueExplicit('C'.$row,iconv("utf8","utf-8",$v2['单位编码']),PHPExcel_Cell_DataType::TYPE_STRING);
  
                 
 $objActSheet->setCellValueExplicit('D'.$row,iconv("utf8","utf-8",$v2['单位名称']),PHPExcel_Cell_DataType::TYPE_STRING);
  
                 
 $objActSheet->setCellValueExplicit('E'.$row,iconv("utf8","utf-8",$v2['姓名']),PHPExcel_Cell_DataType::TYPE_STRING);
  
                 
 $objActSheet->setCellValueExplicit('F'.$row,iconv("utf8","utf-8","身份证"),PHPExcel_Cell_DataType::TYPE_STRING);
  
                 
 $objActSheet->setCellValueExplicit('G'.$row,iconv("utf8","utf-8",$v2['身份证号码']),PHPExcel_Cell_DataType::TYPE_STRING);
  
                 
 $objActSheet->setCellValueExplicit('H'.$row,iconv("utf8","utf-8",$v2['性别']),PHPExcel_Cell_DataType::TYPE_STRING);
  
                 
 $objActSheet->setCellValueExplicit('I'.$row,iconv("utf8","utf-8",$v2['国籍']),PHPExcel_Cell_DataType::TYPE_STRING);
  
                 
 $objActSheet->setCellValueExplicit('J'.$row,iconv("utf8","utf-8","汉族"),PHPExcel_Cell_DataType::TYPE_STRING);
  
                 
 $objActSheet->setCellValueExplicit('K'.$row,iconv("utf8","utf-8",$v2['手机号码']),PHPExcel_Cell_DataType::TYPE_STRING);
  
                 
 $objActSheet->setCellValueExplicit('L'.$row,iconv("utf8","utf-8",$v2['出生日期']),PHPExcel_Cell_DataType::TYPE_STRING);
                }
                
                $objActSheet->setTitle($vo['单位编码']);
                $objExcel->setActiveSheetIndex(0);

            }
            $outputFileName = "多个Sheet.xls" ;    
            //到文件    
            //   
            //or    
            //到浏览器  
            header("Content-Type: application/force-download");    
            header("Content-Type: application/octet-stream");    
            header("Content-Type: application/download");    
            //header('Content-Disposition:inline;filename="'.$outputFileName.'"');
            header("Content-Disposition: attachment;filename=".$outputFileName."");    
            header("Content-Transfer-Encoding: binary");    
            header("Expires: Mon, 26 Jul 1997 05:00:00 GMT");    
            header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT");    
            header("Cache-Control: must-revalidate, post-check=0, pre-check=0");    
            header("Pragma: no-cache");  
            $objWriter->save('php://output');
            
}