使用PHPExcel实现对excel数据的导入和导出

最新推荐文章于 2021-03-27 19:51:13 发布

青春微凉不悲伤

最新推荐文章于 2021-03-27 19:51:13 发布

阅读量1.7k

点赞数 2

分类专栏： PHP 文章标签： excel PHPExcel

本文链接：https://blog.csdn.net/u010753613/article/details/50160163

版权

PHP 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

因公司业务需求，需要使用excel文件进行大量的数据的操作，如：批量用户注册、批量修改用户名、批量修改手机号等。由于之前没有做过这种对excel文件的操作，后从网上搜了一下，最后确定使用PHPExcel类。现将使用方法分享给大家。

PHPExcel类是用来操作Office Excel 文档的一个PHP类库，它基于微软的OpenXML标准和PHP语言。可以使用它来读取、写入不同格式的电子表格。

1、前台excel文件操作界面

首先，我们需要在前台HTML页面进行文件的上传，如：

代码如下：

<pre name="code" class="html"><form enctype="multipart/form-data" method="post" id="uploadshow" action="{:U('admin/UserManagement/upload')}
">
  <p id="mainp">
    用户注册绑定管理 → 用户批量注册绑定
  </p>
  <ul id="uls">
    <li οnclick="location='{:U('admin/UserManagement/onebatchre')}'">单个用户注册</li>
    <li class="cur" οnclick="location='{:U('admin/UserManagement/batchregistration')}'">批量用户注册</li>
  </ul>
  <p id="mainimg">
    请选择批量注册文件：
    <input class="inimg" id="urlImg" type="text" aria-describedby="basic-addon2" readonly value="" id="logo" name="logo">
    <input type="button" id="basic-addon2" class="input-group-addon pointer" value="选择文件" >
    <input type="file" data-name="" class="file pointer" id="upFile" name="upFile" accept=".xls"/>
    <a style="width:100px; height:20px; display:block; margin: -18px 0px 0px 525px;" href="/oms/Uploads/templates/plzc.xls">下载模板文件</a>      
  </p>
  <p id="mainl">
    <label id="loyu">支持Excel文档，每次操作请不要超过500条数据</label>
    <label id="jingyu">仅限xls格式，文件最大不超过2M</label>
    <label id="kong">请选择文件！</label>
  </p>
  <p>
    <input  type="hidden" value="1" class="" name="style" data-num="0"/>
    <input  type="submit" id="submitImg" value="确定上传" class="" data-num="0"/>
  </p>
</form>

2、控制器Action层，负责文件的上传

首先，需要先将所选择的文件上传到服务器，类似于上传图片的那种功能，代码如下：

public function upload(){
    header("Content-Type:text/html;charset=utf-8");
    import("ORG.Net.UploadFile"); 

        $style = $_POST['style'];
        $upload = new UploadFile();
    $upload->saveRule  = 'uniqid';
    $upload->maxSize   =  3145728 ;               // 设置附件上传大小
    $upload->allowExts =  array('xls', 'xlsx');   // 设置附件上传类
    $upload->savePath  =  WANBU_PATH."/oms/Uploads/zc/";  // 设置附件上传目录
    // 上传文件
    if(!$upload->upload()) {    // 上传错误提示错误信息
      $this->error($upload->getErrorMsg());
      }else{    // 上传成功
      $info =  $upload->getUploadFileInfo();    //二维数组
      $filename = $upload->savePath . $info['0']['savename'];
      $exts = $extension = $info['0']['extension'];
      //文件上传成功后，进入model层处理上传文件
      $data = D("HandleData")->goods_import($filename, $exts,$style);
          $msg = "数据上传成功<b>".$data['success']."</b>条，失败<b>".$data['fail']."</b>条";
          setSystemLog($_SESSION['userInfo']['username'],'','用户管理','用户批量注册绑定管理',$msg);
    }
    $success=$data['success'];
    $fail=$data['fail'];
    $filename=$data['filename'];
    $empty=0;
    $success=$success?$success:$empty;
    $fail=$fail?$fail:$empty;
    $this->assign('success',$success);
    $this->assign('fail',$fail);
    $this->assign('filename',$filename);
    $this->display("batchregistration");
}

3、模型Model层，负责数据的处理

首先，我们需要了解excel文件的内容，例如：批量用户注册

然后，根据具体需求判断上传的文件数据是否符合要求。

//导入数据方法
    function goods_import($filename, $exts,$style){
        //导入PHPExcel类库，因为PHPExcel没有用命名空间，只能import导入
        import("ORG.Util.PHPExcel");
        //创建PHPExcel对象，注意，不能少了\
        $PHPExcel=new \PHPExcel();
        //如果excel文件后缀名为.xls，导入这个类
        if($exts == 'xls'){
            import("ORG.Util.PHPExcel.Reader.Excel5");
            $PHPReader=new PHPExcel_Reader_Excel5();
        }else if($exts == 'xlsx'){
            import("ORG.Util.PHPExcel.Reader.Excel2007");
            $PHPReader=new PHPExcel_Reader_Excel2007();
        }
        //载入文件
        $PHPExcel=$PHPReader->load($filename);
        //获取表中的第一个工作表，如果要获取第二个，把0改为1，依次类推
        $currentSheet=$PHPExcel->getSheet(0);
        //获取总列数
        $allColumn=$currentSheet->getHighestColumn();
        //获取总行数
        $allRow=$currentSheet->getHighestRow();
        //循环获取表中的数据，$currentRow表示当前行，从哪行开始读取数据，索引值从0开始
        for($currentRow=2;$currentRow<=$allRow;$currentRow++){
            //从哪列开始，A表示第一列
            for($currentColumn='A';$currentColumn<=$allColumn;$currentColumn++){
                //数据坐标
                $address=$currentColumn.$currentRow;
                //读取到的数据，保存到数组$arr中
                $data[$currentRow][$currentColumn]=$currentSheet->getCell($address)->getValue();
            }
        }
        $res=$this->save_import($data,$style); 
        return $res;
    }

    //保存导入数据
    function save_import($data,$style){      
        //如果上传空文件
        if(count($data)==0){
            $num['success']=0; 
            $num['fail']=0; 
            $num['filename']="none"; 
            return $num;
        }

        //批量注册
        $datalist=array();
        $inittime = date('Y-m-d H:i:s', time());
        foreach ($data as $k=>$v){  
            $datalist[$k]=$v;
            if($k >= 2){
                if($k < 502){
                    //用户名校验
                    $username=trim($v['A']);
                    if($username){   
                        $nameLen = (strlen($username) + mb_strlen($username,'UTF8')) / 2;
                        if($nameLen > 15){
                            $datalist[$k]['C']='用户名仅限3-15个字符';continue;
                        }else{  
                            $specialChar = D("UserInfo")->testspechars($username);    
                            if($specialChar == 0){              
                                $datalist[$k]['C']='用户名存在特殊字符';continue;
                            }else{
                                $result=D("DataUser")->checkUsername($username);
                                if(!$result){
                                    $info[$k-2]['username'] = $username;
                                }else{
                                    $datalist[$k]['C']='用户名已存在';continue;
                                }
                            }                   
                        }                   
                    }else{
                        $datalist[$k]['C']='请输入注册用户名';continue;
                    }

                    //手机号校验
                    $mobile=trim($v['B']);
                    if($mobile){
                        $length = strlen($mobile);
                        if($length!=11){
                            $datalist[$k]['C']='手机号码须为11位数字';continue;
                        }else{
                            $info[$k-2]['mobile'] = $mobile;
                        }                
                    }else{
                        $datalist[$k]['C']='请输入注册手机号码';continue;
                    }                     
                }else{
                    $datalist[$k]['C'] = "数据超过500条，请分多次操作";continue;
                }
            }

            //入库操作
            static $num_success=0;
            if(!$datalist[$k]['C']){
                $userid=D("DataUser")->addData($mobile,$username); 
                
                $data['userid'] = $userid;
                $data['mobile'] = $mobile;
                $uploadUserinfo = D("DataUser")->uploadData($data,2); 

                $num_success++;
            } 
            $num['success']=$num_success;
            $datalist[$k]=array();                         
        }

        if($datalist){
            $k=2;
            foreach($datalist as $key=>$val){
                if(empty($val)){
                    continue;
                }
                $data_list[$k++]= $val; 
            }
            $num['fail']=count($data_list); 
            $re=$this->goods_export($datalist,$style);
        }      
        $num['filename']=$re; 
        return $num;
    }

    //导出数据方法
    protected function goods_export($goods_list=array(),$style)
    {       
        $goods_list = $goods_list;
        $k=2;
        foreach($goods_list as $key=>$val){
            if(empty($val)){
                continue;
            }
            $datalist[$k++]= $val; 
        }
        $data = array();

        foreach ($datalist as $k=>$goods_info){    
            $data[$k][A] = $goods_info['A'];
            $data[$k][B] = $goods_info['B'];
            $data[$k][C] = $goods_info['C'];
        }
        $filename="plzc_fail";  
        
        $re=$this->getExcel($filename,$data,$style);
        return $re;
    }

    //导出数据
    private  function getExcel($fileName,$data,$style){   
        //导入PHPExcel类库，因为PHPExcel没有用命名空间，只能inport导入
        import("ORG.Util.PHPExcel");
        import("ORG.Util.PHPExcel.Writer.Excel5");
        import("ORG.Util.PHPExcel.IOFactory.php");

        $date = date("Y_m_d_H_i_s",time());
        $fileName .= "_{$date}.xls";

        //创建PHPExcel对象，注意，不能少了\
        $objPHPExcel = new \PHPExcel();
        $objProps = $objPHPExcel->getProperties();

        //设置表头
        $objPHPExcel->getActiveSheet()->setCellValue('A1', '用户名（必填）');
        $objPHPExcel->getActiveSheet()->setCellValue('B1', '手机号（必填）');
        $objPHPExcel->getActiveSheet()->setCellValue('C1', '失败原因');

        //设置宽度
        $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(15);
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(22);
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(15);

        //设置自动换行
        $objPHPExcel->getActiveSheet()->getStyle('C1:AC'.($k+2))->getAlignment()->setWrapText(true);
 
        //设置行高
        $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(28);
        $objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(15);
        //设置字体
        $objPHPExcel->getActiveSheet()->getStyle()->getFont()->setName('宋体');
        //所有垂直居中
        $objPHPExcel->getActiveSheet()->getStyle('A1:AC'.($k+2))->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

        $column = 2;
        $objActSheet = $objPHPExcel->getActiveSheet();

        foreach($data as $key => $rows){ //行写入
            $span = ord("A");      
            foreach($rows as $keyName=>$value){// 列写入
                $j = chr($span);
                $objActSheet->setCellValueExplicit($j.$column, $value,PHPExcel_Cell_DataType::TYPE_STRING);      
                $span++;          
            }
            $column++;
        }
        $fileName = iconv("utf-8", "gb2312", $fileName);   
        //设置活动单指数到第一个表,所以Excel打开这是第一个表
        $objPHPExcel->setActiveSheetIndex(0);   
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        //保存至某一位置  
        $objWriter->save(WANBU_PATH."/oms/Uploads/error/" . $fileName);   
        if($fileName){   
            return $fileName;
        } 
    }

其中，如果有不符合需求的数据，会保存在失败文件中，并指出失败原因，如：