tp框架下PHPExcel实现表格数据的导入导出功能

tp框架下PHPExcel实现表格数据的导入导出功能

前端代码：

<a class="btn btn-success"  href="javascript:void(0)" id="import"  style="float:left;">{pigcms{:L_("导入店员信息")}</a> 

 <a class="btn btn-success"  href="{pigcms{:U('ShopTrain/exportExcel')}" style="float:right;">{pigcms{:L_("导出店员培训信息")}</a> 

导入功能结合layui中upload组件,引入layui的js代码：

<script type="text/javascript" src="__STATIC__/layui/layui.js"></script>

<script>

    layui.use('upload', function(){

        var upload = layui.upload;

        //执行实例

        var uploadInst = upload.render({

            elem: '#import' //绑定元素

            ,url: "{:U('ShopTrain/import')}" //上传接口对应的方法

            ,accept: 'file'

            ,done: function(res){

                if(res.code == 1){

                    layer.msg(res.msg, {icon: 6,time:4000});

                    location.reload(true);

                }

                else

                {

                    layer.msg(res.msg, {icon:5});

                    return false;

                }  

                        

            }

            ,error: function(){

                //请求异常回调

            }

        });

        $(".layui-upload-file").hide();

    });

</script>

数据导入到Excel控制器代码如下：

/**

     * 导入店员信息

     */

    public function import()

    {

         set_time_limit(0);

        ini_set('memory_limit','1000M');       

        if (!empty($_FILES)) {

            import('ORG.Net.UploadFile');

            $config=array(

                'allowExts'=>array('xlsx','xls'),

                'savePath'=>'./upload/',

                'saveRule'=>'time',

            );

            $upload = new UploadFile($config);

            if (!$upload->upload()) {

                $this->error($upload->getErrorMsg());

            } else {

                $info = $upload->getUploadFileInfo();       

            }

            //引入phpExcel类

            require_once dirname(dirname(dirname(__FILE__))). '/ORG/Excel/Classes/PHPExcel.php';

            $file_name = $info[0]['savepath'].$info[0]['savename'];

            $extension = strtolower( pathinfo($file_name, PATHINFO_EXTENSION) );

            if ($extension =='xlsx') {

                $objReader = new PHPExcel_Reader_Excel2007();

                $objExcel = $objReader ->load($file_name);

            } else if ($extension =='xls') {

                $objReader = new PHPExcel_Reader_Excel5();

                $objExcel = $objReader ->load($file_name);

            } else if ($extension=='csv') {

                $PHPReader = new PHPExcel_Reader_CSV();

                $PHPReader->setInputEncoding('GBK'); //默认输入字符集

                $PHPReader->setDelimiter(',');  //默认的分隔符

                $objExcel = $PHPReader->load($file_name); //载入文件

            } else {

                $this->jsonError('文件格式错误');   

            }

            $sheet = $objExcel->getSheet(0);

            $highestRow = $sheet->getHighestRow(); // 取得总行数

            $highestColumn = $sheet->getHighestColumn(); // 取得总列数

            $num = 1;

            $database_merchant_store_info = D('merchant_store_info');

            for ($i = 2; $i <= $highestRow; $i++) {

                $company = array();

                $company['name']    = $objExcel->getActiveSheet()->getCell("A".$i)->getValue();

                if (empty($company['name'])) {

                    break;

                }

                $company['mobile']   = $objExcel->getActiveSheet()->getCell("B".$i)->getValue();

                $company['idcard']  = $objExcel->getActiveSheet()->getCell("C".$i)->getValue();

                $company['job'] = $objExcel->getActiveSheet()->getCell("D".$i)->getValue();

                $condition['name'] = $company['name'];

                $condition['mobile'] = $company['mobile'];

                $ori_company = $database_merchant_store_info->where($condition)->find();

                if (! empty($ori_company)) {

                    // continue;

                } else {

                    $company_id = $database_merchant_store_info->add($company);

                    $num ++;

                } 

            }

            if($company_id)

            {

                $sdata['code'] = 1;

                $sdata['msg'] ='导入成功';

            }else{

                $sdata['code'] = 0;

                $sdata['msg'] = '导入失败，请重新导入！';

            }

            echo json_encode($sdata);

        }   

    }

导出到excel控制器代码：

    public function exportExcel()

    {

        // 引入类库

        require_once dirname(dirname(dirname(__FILE__))). '/ORG/Excel/Classes/PHPExcel.php';

        // 模拟获取数据

        $data = self::getData();

        // 创建Excel文件对象

        $objPHPExcel = new PHPExcel();

        $objPHPExcel->setActiveSheetIndex(0)

            ->setCellValue('A1','编号')

            ->setCellValue('B1','姓名')

            ->setCellValue('C1','年龄)')

            ->setCellValue('D1','身份证号)');

        //设置excel表单根据内容大小变化

        $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true);

        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);

        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);

        $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setAutoSize(true);

        //$i=1是标题行

        $i=2;

        foreach ($data as $k=>$v){

            $objPHPExcel->setActiveSheetIndex(0)

                ->setCellValue('A'.$i,$v['id'])

                ->setCellValue('B'.$i,$v['name'])

                ->setCellValue('C'.$i,'age')

                ->setCellValue('E'.$i,$v['idcard']."\t");

            $i++;

        }

     

        //文件名称

        $filename=urlencode('员工信息').'_'.date('YmdHis');

        //生成xls文件

        ob_end_clean();//清除缓冲区,避免乱码

        ob_start();

        header('Content-Type: application/vnd.ms-excel');

        header('Content-Disposition: attachment;filename="'.$filename.'.xls"');

        header('Cache-Control: max-age=0');

        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

        $objWriter->save('php://output');

        exit;

        

    }

 

    // 准备数据

    protected function getData()

    {

         $studentList = [

             [

                 'id' => '1',

                 'name' => 'student01',

                 'age' => '23'，

                 'idcard'=>'412375199102095589'

             ], [

                 'id' => '2',

                 'name' => 'student02',

                 'age' => '19',

                   'idcard'=>'412375199102095589'

             ], [

                 'id' => '3',

                 'name' => 'student03',

                 'age' => '18',

                  'idcard'=>'412375199102095589'

             ]

        ];

         return $studentList;

}