这里演示如何在Thinkphp框架中用PHPExcel类来导入和导出Excel文件,首先下载类文件 点击进入下载,下载后发现有很多目录文件, 很多是说明和演示,最核心的目录是Classes,将这个目录放入到项目内的/ThinkPHP/Library/Vendor下,并改名为PHPExcel,然后在/ThinkPHP/Library/Org/Util下引入文件下载类UploadFile,点击下载. (注:vendor方法默认导入的类库后缀是php的而不是class.php的).到这里,准备工作就完成了.

导入操作

导入操作就是将一个Excel表格的内容导入到程序里,可以插入到数据库,例如我需要往商城添加新的商品,事先就规划好了Excel表格,表格上有新商品的信息,而且是多个,这种情况下,就不需要在后台一条一条插入商品了,直接导入表格就行了.页面如下:

下面是代码

后台代码

//批量添加商品(导入Excel)
    public function goodsAdd()
    {
        if(IS_POST){
            if (!empty($_FILES)) {
                $config=array(
                    'allowExts'=>array('xlsx','xls'),
                    'savePath'=>'./Public/upload/',
                    'saveRule'=>'time',
                );
                $upload = new \Org\Util\UploadFile($config);
                if (!$upload->upload()) {
                    $this->error($upload->getErrorMsg());
                } else {
                    $info = $upload->getUploadFileInfo();
                }

                vendor("PHPExcel.PHPExcel");
                vendor("PHPExcel.PHPExcel.Writer.Excel5");
                vendor("PHPExcel.PHPExcel.IOFactory");
                $file_name=$info[0]['savepath'].$info[0]['savename'];
                $ext = strtolower(pathinfo($file_name,PATHINFO_EXTENSION));//获取扩展名

                //同时兼容两种后缀
                if($ext == 'xlsx'){
                    $objReader = \PHPExcel_IOFactory::createReader('Excel2007');
                    $objPHPExcel = $objReader->load($file_name,'utf-8');
                }elseif($ext == 'xls'){
                    $objReader = \PHPExcel_IOFactory::createReader('Excel5');
                    $objPHPExcel = $objReader->load($file_name,$encode='utf-8');
                }

                $sheet = $objPHPExcel->getSheet(0);
                $highestRow = $sheet->getHighestRow(); // 取得总行数
                $highestColumn = $sheet->getHighestColumn(); // 取得总列数
                for($i=2;$i<=$highestRow;$i++)
                {
                    //data数组里是数据表的字段,等号后面是获取Excel表格内容
                    $data['sid'] = $objPHPExcel->getActiveSheet()->getCell("A".$i)->getValue();
                    $data['title'] = $objPHPExcel->getActiveSheet()->getCell("B".$i)->getValue();
                    $data['thumbnail'] = $objPHPExcel->getActiveSheet()->getCell("C".$i)->getValue();
                    $data['content'] = $objPHPExcel->getActiveSheet()->getCell("D".$i)->getValue();
                    $data['price']= $objPHPExcel->getActiveSheet()->getCell("E".$i)->getValue();
                    $data['t']= time();

                    M('chanpin')->add($data);
                }
                $this->success('导入成功！');
            }else{
                $this->error("请选择上传的文件");
            }
        }else{
            $this -> display();
        }
    }

前台代码

    <form action="{:U('Excel/goodsAdd')}" method="post" enctype="multipart/form-data">
        <input type="file" name="import"/>
        <input type="hidden" name="table" value="tablename"/>
        <input type="submit" value="导入"/>
    </form>

Excel表格格式如下

导入基本上就这样

导出操作

导出功能用的地方就更多了,页面中,只要是表格,都可以实现导出.下面演示一个导出订单列表,如下图所示,当点击导出按钮,直接导出到Excel表格.

后台代码

//订单导出Excel
    public function expMonitor(){
        $uid = session('uid');
        $xlsName  = "本市订单";
        //表头
        $xlsCell  = array(
            array('id','序列号'),
            array('order_num','订单号'),
            array('uid','收件人'),
            array('phone','联系方式'),
            array('address','收货地址'),
            array('addtime','下单时间'),
            array('pay_status','订单状态'),
            array('pay_time','支付时间'),
            array('total','总金额'),
        );

        $prefix = C('DB_PREFIX');
        $where['isdel'] = 1;
        $where['pparent_id'] = $uid;
        $orders_num = isset($_GET['orders_num']) ? I('get.orders_num') : '';
        $pay_status = isset($_GET['pay_status']) ? I('get.pay_status') : '';
        $p = isset($_GET['page']) ? intval($_GET['page']) : '1';
        if($orders_num){
            $where['order_num'] = array('LIKE','%'.$orders_num.'%');
        }
        if($pay_status){
            if($pay_status == 1){
                $where['pay_status'] = 1;
            }else if($pay_status == 2){
                $where['pay_status'] = 2;
            }
        }
        $xlsModel = M('orders');
        $pagesize = 10;#每页数量
        $offset = $pagesize * ($p - 1);//计算记录偏移量
        
        $xlsData  = $xlsModel -> field("{$prefix}orders.*,{$prefix}user_address.name,{$prefix}user_address.address,{$prefix}user_address.detailed_address,{$prefix}user_address.zipcode,{$prefix}user_address.mobile")
            ->order('id desc')
            ->join("{$prefix}user_address ON {$prefix}orders.address_id = {$prefix}user_address.address_id")
            ->where($where)
            ->limit($offset . ',' . $pagesize)
            ->select();
        foreach ($xlsData as $k => $v)
        {
            $userinfo=getUserInfo($v['uid']);
            $userinfo2 = getUserAddress($v['address_id']);
            $xlsData[$k]['uid']=$userinfo['nickname'];
            $xlsData[$k]['phone']=$userinfo2['mobile'];
            $xlsData[$k]['address']=$userinfo2['address'].$userinfo2['detailed_address'];
            $xlsData[$k]['addtime']=date('Y-m-d H:i',$v['addtime']);
            $xlsData[$k]['pay_status']=$v['pay_status']==1 ? '未支付' : '已支付';
            $xlsData[$k]['pay_time']=$v['pay_time'] ? date('Y-m-d H:i',$v['pay_time']) : '无';
            $xlsData[$k]['total']=$v['total'];
        }
        $this->exportExcel($xlsName,$xlsCell,$xlsData);
    }

    //导出
    public function exportExcel($expTitle,$expCellName,$expTableData){
        $xlsTitle = iconv('utf-8', 'gb2312', $expTitle);//文件名称
        $fileName = $_SESSION['account'].date('_YmdHis');//or $xlsTitle 文件名称可根据自己情况设定
        $cellNum = count($expCellName);
        $dataNum = count($expTableData);

        vendor("PHPExcel.PHPExcel");
        $objPHPExcel = new \PHPExcel();

        $cellName = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z','AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM','AN','AO','AP','AQ','AR','AS','AT','AU','AV','AW','AX','AY','AZ');
        $objPHPExcel->getActiveSheet(0)->mergeCells('A1:'.$cellName[$cellNum-1].'1');//合并单元格
        
        for($i=0;$i<$cellNum;$i++){
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$i].'2', $expCellName[$i][1]);
        }
        
        for($i=0;$i<$dataNum;$i++){
            for($j=0;$j<$cellNum;$j++){
                $objPHPExcel->getActiveSheet(0)->setCellValue($cellName[$j].($i+3), $expTableData[$i][$expCellName[$j][0]]);
            }
        }

        header('pragma:public');
        header('Content-type:application/vnd.ms-excel;charset=utf-8;name="'.$xlsTitle.'.xls"');
        header("Content-Disposition:attachment;filename=$fileName.xls");//attachment新窗口打印inline本窗口打印

        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
        exit;
    }

打开导出的表格内容如下:

查询的SQL语句和订单列表的基本一样,因为导出的条目是当前页面上罗列出的条目,前台传过来的参数主要有搜索条件和当前页码,数据的判断主要在expMonitor方法完成.

以上就是PHPExcel类的基本用法,它还有很多个性化操作,欢迎探索.