php 之 excel导出导入合并

2023-11-09 03:12•php•阅读 4014
<?php

class Excel extends Controller {

        //直属高校
    public function __construct() {
        parent::Controller();
         //初始化读取excel类
         @$this->load->library("reader");
         $reader = @$this->reader;
         $reader->setOutputEncoding('UTF-8');
         $this->load->model('LogModel', '', TRUE);
         $this->cismarty->assign('base_url', base_url());
         
    }


    public function index(){

        
        $result=$this->LogModel->listlog(0);
        $this->cismarty->assign('results', $result);
        $this->cismarty->display('excel/excel.html');
    }

  

    //上传文件1
    public function upload1()
    {
        ///home/wwwroot/excel/crs_edu_old/public/admin/system/excel
        ///home/wwwroot/excel/crs_edu_old/public/admin/system/application/excel/
        $config['upload_path']      = APPPATH.'excel/';

        if(!file_exists($config['upload_path'] ))
        {
            mkdir(APPPATH.'excel/',0755);
        }

        $config['allowed_types'] = 'xls|jpg|png';
       
        $config['encrypt_name']=true;

        $config['max_size']     = 31948800;

        $this->load->library('upload', $config);

        
    
       
       

        if (!$this->upload->do_upload('file'))
        {
            
            echo $this->upload->display_errors();
           
        }
        else
        {
           

            $file_name=$this->upload->data('file_name'); 
            echo json_encode($file_name);
        }
    }

     //上传文件2
    public function upload2()
    {
        $config['upload_path']      = APPPATH.'excel/';
        $config['allowed_types'] = 'xls|jpg|png';
       
        $config['encrypt_name']=true;

        $config['max_size']     = 31948800;

        $this->load->library('upload', $config);

        
    
       
       

        if (!$this->upload->do_upload('file'))
        {
            
            echo $this->upload->display_errors();
           
        }
        else
        {
           

            $file_name=$this->upload->data('file_name'); 
            echo json_encode($file_name);
        }
    }

    public function getStrpos($str)
    {
        $strs="";
        if(stripos($str,"，")>0){
                    
            $data_arr=explode("，",$str);
           
            foreach($data_arr as $val){
                
                $strs.=";".$val.";";
            }
            $strs=trim($strs,";");

            return $strs;
         }elseif(stripos($str,"、")>0){
                    
            $data_arr=explode("、",$str);
           
            foreach($data_arr as $val){
                
                $strs.=";".$val.";";
            }
            $strs=trim($strs,";");

            return $strs;
         }elseif(stripos($str,",")>0){
                    
            $data_arr=explode(",",$str);
           
            foreach($data_arr as $val){
                
                $strs.=";".$val.";";
            }
            $strs=trim($strs,";");

            return $strs;
         }else{

            return $str;
         }


    }

    /// 将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=APPPATH.'excel/'.$this->input->post('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];
        
        
        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=APPPATH.'excel/'.$this->input->post('excel1');
        //$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 = 'A'; $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"],";");

                        $str=$objPHPExcel->getActiveSheet()->getCell("E".$row)->getValue();
                        $dataArr[$row]["E"]=str_replace("，",'、',$str);
                       

                        //第一出现的位置不区分大小写 、
                     }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"],";");

                        $str=$objPHPExcel->getActiveSheet()->getCell("E".$row)->getValue();
                        $dataArr[$row]["E"]=str_replace("、",'、',$str);

                     }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"],";");

                        $str=$objPHPExcel->getActiveSheet()->getCell("E".$row)->getValue();
                        $dataArr[$row]["E"]=str_replace(",",'、',$str);


                     }else{
                         
                        $dataArr[$row]["E"] = $objPHPExcel->getActiveSheet()->getCell("E".$row)->getValue();

                     }

                    
                  



                    switch ($dataArr[$row]["E"])
                    {
                      case '党委副书记、纪委书记':
                        $dataArr[$row]["E"] ='党委副书记';
                        break; 
                      case '党委副书记、副校长':
                        $dataArr[$row]["E"] ='党委副书记';
                        break; 
                      case '校长、党委副书记':
                        $dataArr[$row]["E"] ='校长';
                        break; 
                      case '院长、党委书记':
                        $dataArr[$row]["E"] ='党委书记';
                       
                        break;
                      case '党委书记、院长':
                        $dataArr[$row]["E"] ='党委书记';
                        
                        break;
                      case '副院长':
                        $dataArr[$row]["E"] ='副校长';
                        break; 
                      case '党委书记、校长':
                        $dataArr[$row]["E"] ='党委书记';
                        
                        break;
                      case '校长、党委书记':
                        $dataArr[$row]["E"] ='党委书记';
                       
                        break;
                      case '院长、党委副书记':
                        $dataArr[$row]["E"] ='校长';
                        break; 
                      case '常务副校长（正局级）':
                        $dataArr[$row]["E"] ='常务副校长';
                        break; 
                      case '常务副校长（正厅级）':
                        $dataArr[$row]["E"] ='常务副校';
                        break; 
                      case '党委常务副书记（正局级）':
                        $dataArr[$row]["E"] ='党委常务副书记';
                        break; 
                      case '党委常务副书记（正厅级）':
                        $dataArr[$row]["E"] ='党委常务副书记';
                        break; 
                      case '党委常务副书记（正局级）、副校长':
                        $dataArr[$row]["E"] ='党委常务副书记';
                        break; 
                      case '党委常务副书记、纪委书记':
                        $dataArr[$row]["E"] ='党委常务副书记';
                        break; 
                      case '党委副书记、副校长':
                        $dataArr[$row]["E"] ='党委副书记';
                        break;
                      case '党委副书记、纪委书记':
                        $dataArr[$row]["E"] ='党委副书记';
                        break;
                      case '校长、党委副书记':
                        $dataArr[$row]["E"] ='校长';
                        break;
                      default:
                        $dataArr[$row]["E"] = $this->getStrpos($objPHPExcel->getActiveSheet()->getCell("E".$row)->getValue());
                    }
                    
                   
                  
                     $dataArr[$row]["company"] = $objPHPExcel->getActiveSheet()->getCell("A".$row)->getValue();
                     $dataArr[$row]["name"] = $objPHPExcel->getActiveSheet()->getCell("B".$row)->getValue();
                     $dataArr[$row]["sex"] = $objPHPExcel->getActiveSheet()->getCell("C".$row)->getValue();
                     $dataArr[$row]["birthday"] = $objPHPExcel->getActiveSheet()->getCell("D".$row)->getValue();
                     $dataArr[$row]["F"] = $objPHPExcel->getActiveSheet()->getCell("F".$row)->getValue();
                }
            }
           
           
            $data_arr=$dataArr;
            //var_dump($dataArr);

             /**
     * 两个数组进行对比查询
     * 将1的内容 放到2中
     * excel1为数组 arr1
     * excel2为数组 arr2
     *
     * (复杂度太高 需进行 闭包改进) n*n 
     * 
     * 将数组进行比较 因为多维数组 key不同不可进行key对比
     * 
     */
   
           if(count($data_arr)==count($data2)){

                foreach($data_arr as $key=>$val){
                    unset($data_arr[$key]['E']);
                    unset($data_arr[$key]['F']);
                    foreach($val as $kev1=>$val1){
                       

                        
                        if($data_arr[$key][$kev1]!==$data2[$key][$kev1]){
                            echo "excel1文件：你获取的数据不一样在".$data_arr[$key][$kev1]."行数在{$key},{$kev1}";
                            echo "excel2文件：你获取的数据不一样在".$data2[$key][$kev1]."行数在{$key},{$kev1}";
                            
                            exit();
                            
                        }
                    }
                }
            }else{
                echo "数据不一";
                exit;
            }
            
           

            $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', '排序(填写自然数字)');
            
           
            
          
            
            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=直属高校.xls');
            header('Cache-Control: max-age=0');
    
            $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); 
            $objWriter->save('php://output');

            $data['excel1']=basename($excel1);
            $data['excel2']=basename($excel2);
            $data['type']=0;
            $data['excel_time']=date('Y-m-d H:i:s',$this->input->server('REQUEST_TIME'));
            $data['client_ip']=$this->input->ip_address();

            $this->LogModel->addLog($data);
        
        
    }

   
  


    
    
}
?>
代码冗余比较严重测试版本
上一篇 »Javascript调用 ActiveXObject导出excel文档。
下一篇 »C# EXCEL(.xls和.xlsx)导入到数据库 C# EXCEL(.xls和.xlsx)导入到数据库
php 之 excel导出导入合并

相关推荐

java的，SXSSFEasyExcel阿里开源excel导出和XSSF导出简单示例

Oracle查询的语句导出为html/excel

php导入Excel进mysql数据库

PHP导出CVS格式文件

PHP Excel表格导出图片方法

解决java POI导入Excel超时问题

一个大而全的Java通用开发框架

linux下mysqldump导出数据、表结构，导入数据
