CI框架导入 excel

整合PHP  Excel和PHPexcelReader到

librarys下面

 

 

两个excel整合成一个excel

 

<?php

class Excel extends Controller {

    public function __construct() {
        parent::Controller();
         //初始化读取excel类
         @$this->load->library("reader");
         $reader = @$this->reader;
         $reader->setOutputEncoding('UTF-8');
    }
    /// 将1的内容合并到2的内容当中从而生成3
    public function getExcelContent(){
        header("content-type:text/html;charset=utf-8"); 
        
        $this -> load -> library('Excel/PHPExcel.php');

        $this->load->library('Excel/PHPExcel/Reader/PHPExcel_Reader_Excel5.php'); 
        $objReader=new PHPExcel_Reader_Excel5();
            
        $objPHPExcel = new PHPExcel();

        $data2=array();
           
        $excel2="/home/wwwroot/excel/crs_edu_old/public/admin/system/excel/zsgxry_2.xls";
        $this->reader->read($excel2);
        $numRows1 = $this->reader->sheets [0] [numRows];
    	$numCols1 = $this->reader->sheets [0] [numCols];
        $project1 = $this->reader->sheets [0] [cells];
        
        
        if ($numRows1 > 0 && $numCols1 > 0) {
            for($i=2;$i<=$numRows1;$i++){
                $data2[$i]['id']=$project1[$i][1];
                $data2[$i]['company']=$project1[$i][2];
                $data2[$i]['name']=$project1[$i][3];
                $data2[$i]['sex']=$project1[$i][4];
                $data2[$i]['birthday']=$project1[$i][5];
                $data2[$i]['cankao']=$project1[$i][6];
                
            }
        }

        $excel1="/home/wwwroot/excel/crs_edu_old/public/admin/system/excel/zsgxry_1.xls";
        
        $objPHPExcel = $objReader->load($excel1);
            //获取sheet表数目
            $sheetCount = $objPHPExcel->getSheetCount();

            //默认选中sheet0表
            $sheetSelected = 0;

            $objPHPExcel->setActiveSheetIndex($sheetSelected);

            //获取表格行数
            $rowCount = $objPHPExcel->getActiveSheet()->getHighestRow();

            //获取表格列数
            $columnCount = $objPHPExcel->getActiveSheet()->getHighestColumn();

       
                
            $dataArr = array();


            /** 循环读取每个单元格的数据 */
            //行数循环
            for ($row = 2; $row <= $rowCount; $row++){
                //列数循环 , 列数是以A列开始
                for ($column = 'E'; $column < $columnCount; $column++) {
                    //第一出现的位置不区分大小写 ，
                    if(stripos($objPHPExcel->getActiveSheet()->getCell("E".$row)->getValue(),"，")>0){
                     
                        $data_arr=explode("，",$objPHPExcel->getActiveSheet()->getCell("E".$row)->getValue());
                       
                        foreach($data_arr as $val){
                            
                            $dataArr[$row]["E"].=$val.";";
                        }
                        $dataArr[$row]["E"]=rtrim($dataArr[$row]["E"],";");
                        //第一出现的位置不区分大小写 、
                     }elseif(stripos($objPHPExcel->getActiveSheet()->getCell("E".$row)->getValue(),"、")){
                        $data_arr1=explode("、",$objPHPExcel->getActiveSheet()->getCell("E".$row)->getValue());
                       
                        foreach($data_arr1 as $val){
                            
                            $dataArr[$row]["E"].=$val.";";
                        }
                        $dataArr[$row]["E"]=rtrim($dataArr[$row]["E"],";");
                     }else{
                        $dataArr[$row]["E"] = $objPHPExcel->getActiveSheet()->getCell("E".$row)->getValue();
                     }
                     $dataArr[$row]["F"] = $objPHPExcel->getActiveSheet()->getCell("F".$row)->getValue();
                }
            }

           

            $objPHPExcel -> getDefaultStyle() -> getFont() -> setSize(10);
            $objPHPExcel->getActiveSheet()->getStyle('C')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);

				$objPHPExcel -> setActiveSheetIndex(0)
			-> setCellValue('A1', '干部id(不可改)')
            -> setCellValue('B1', '单位(不可改)')
            -> setCellValue('C1', '姓名(不可改)')
            -> setCellValue('D1', '性别(不可改)')
            -> setCellValue('E1', '生日(不可改)')
			-> setCellValue('F1', '职务参考(任免审批表填写职务)')
			-> setCellValue('G1', '职务统计(可选值:党)')
            -> setCellValue('H1', '排序(填写自然数字)');
            
           
            
            $num1=count($data2);
            
            foreach($data2 as $i=>$val){
                $objPHPExcel -> getActiveSheet()-> setCellValue('A'.$i, $data2[$i]['id']);
				$objPHPExcel -> getActiveSheet()-> setCellValue('B'.$i, $data2[$i]['company']);
				$objPHPExcel -> getActiveSheet()-> setCellValue('C'.$i, $data2[$i]['name']);
				$objPHPExcel -> getActiveSheet()-> setCellValue('D'.$i, $data2[$i]['sex']);
				$objPHPExcel -> getActiveSheet()-> setCellValue('E'.$i, $data2[$i]['birthday']);
			    $objPHPExcel -> getActiveSheet()-> setCellValue('F'.$i, $data2[$i]['cankao']);
				$objPHPExcel -> getActiveSheet()-> setCellValue('G'.$i, ";".$dataArr[$i]['E'].";");
                $objPHPExcel -> getActiveSheet()-> setCellValue('H'.$i, $dataArr[$i]['F']);
            }
               
            
			
            

            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
            header('Content-Disposition: attachment;filename=11.xls');
            header('Cache-Control: max-age=0');

            $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); 
           
         
            
            $objWriter->save('php://output');
            // $objWriter -> save("/home/wwwroot/excel/crs_edu_old/public/admin/system/excel/zsgxry.xls");
      
        
        
    }

  public  function getExcel1(){
         //excel1内容
         $excel1="/home/wwwroot/excel/crs_edu_old/public/admin/system/excel/zsgxry_1.xls";
         $this->reader->read($excel1);
         $numRows =$this->reader->sheets [0] [numRows];
         $numCols = $this->reader->sheets [0] [numCols];
         $project = $this->reader->sheets [0] [cells];
         $msg = '';$msg2='';$count=0;$count2=0;$r=0;
         
         if ($numRows > 0 && $numCols > 0) {
             for($i=2;$i<=$numRows;$i++){
                 
                 if(stripos($project[$i][5],"，")>0){
                     
                    $data_arr=explode("，",$project[$i][5]);
                    foreach($data_arr as $val){
                        $data[$i]['position'].=$val.";";
                    }
                    $data[$i]['position']=rtrim($data[$i]['position'],";");
                 }else{
                    $data[$i]['position']=$project[$i][5];
                 }
                 $data[$i]['sorts']=$project[$i][6];
             }
         }

         return $data;
    }




 public   function getExcel2(){
        //excel2内容
        $excel2="/home/wwwroot/excel/crs_edu_old/public/admin/system/excel/zsgxry_2.xls";
        $this->reader->read($excel2);
        $numRows1 = $this->reader->sheets [0] [numRows];
    	$numCols1 = $this->reader->sheets [0] [numCols];
        $project1 = $this->reader->sheets [0] [cells];
        $msg = '';$msg2='';$count=0;$count2=0;$r=0;
        
        if ($numRows1 > 0 && $numCols1 > 0) {
            for($i=2;$i<=$numRows1;$i++){
                $data2[$i]['id']=$project1[$i][1];
                $data2[$i]['company']=$project1[$i][2];
                $data2[$i]['name']=$project1[$i][3];
                $data2[$i]['sex']=$project1[$i][4];
                $data2[$i]['birthday']=$project1[$i][5];
                $data2[$i]['cankao']=$project1[$i][6];
                
            }
        }
        return $data2;
    }
    
}
?>