<?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;

    }

}

?>