tp3.2.3使用phpExcel导入导出

  1. 获取phpexcel类,这里贴个地址,http://www.php.cn/xiazai/leiku/1491

  2. 下载解压之后放到项目中,比如我项目是tp3.2,放在/ThinkPHP/Library/Vendor

    NK}SR9S7}_GL}23QZKJ_G@4.png

  3. 然后写个html页面做测试,因为我不讲究美观性,就做了两个按钮

    <div class="admin-main layui-anim layui-anim-upbit">

        <a href="{:U('Excel/expExcel')}"><button>导出</button></a>

    </div>

    <form method="post" action="{:U('Excel/impExcel')}" enctype="multipart/form-data">

        <input type="file" name="file">

        <input type="submit" value="导入" >

    </form>

  4. 准备一个测试数据表,test表

    T)KKFRH5DL`M_4F`RX3@O~I.png

  5. 在控制器封装导入导出功能

    <?php

    namespace Admin\Controller;

    use Think\Controller;

    class ExcelController extends Controller

    {

        public function index() {

            $this->display();

        }

        /**

         *

         * 导出Excel

         */

        public function expExcel(){//导出Excel

            $xlsName  = "Contacts";

            $xlsCell  = array(

                array('id','编号'),

                array('name','姓名'),

                array('age','年龄'),

                array('create_time','创建时间',50)

            );

            $xlsModel = M('test');

            $xlsData = $xlsModel->select();

            if($xlsData)

            {

                foreach ($xlsData as $key=>$val)

                {

                    if($val['create_time']){

                        $xlsData[$key]['create_time'] = date('Y-m-d H:i:s', $val['create_time']);

                    }

                }

            }

            $this->exportExcel($xlsName,$xlsCell,$xlsData);


        }

        //导出操作

        public function exportExcel($expTitle,$expCellName,$expTableData){

            $xlsTitle = iconv('utf-8', 'gb2312', $expTitle);//文件名称

            $fileName = $expTitle.date('_Ymd_His');//or $xlsTitle 文件名称可根据自己情况设定

            $cellNum = count($expCellName);

            $dataNum = count($expTableData);

            vendor("PHPExcel.PHPExcel");

            $objPHPExcel = new \PHPExcel();

            //循环写入标题

            $key_num = 'A';

            for ($i=0; $i < $cellNum; $i++) {

                $objPHPExcel->setActiveSheetIndex(0)->setCellValue($key_num.'1',$expCellName[$i][1]);

                $key_num++;

            }

            //从第三个单元格开始写入 ,当获取的数据条数数组+1时,则将变量$num+1从而将单元格坐标下移

            foreach($expTableData as $key => $value){

                $num=$key+2;

                $objPHPExcel->setActiveSheetIndex(0)

                    //Excel的第A列,uid是你查出数组的键值,下面以此类推

                    ->setCellValue('A'.$num,$value['id'])

                    ->setCellValue('B'.$num,$value['name'])

                    ->setCellValue('C'.$num,$value['age'])

                    ->setCellValue('D'.$num,$value['create_time']);

            }

            //设置每列单元格的宽度

            $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(8);

            $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(15);

            $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(12);

            $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(30);

            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;

        }

        /**实现导入excel

         **/

        public function impExcel(){

            if (!empty($_FILES)) {

                $upload = new \Think\Upload();// 实例化上传类

                $filepath='./public/upload/excle/';

                //没有路径就创建

                if(!is_file($filepath)){

                    mkdir($filepath);

                }

                $upload->exts = array('xlsx','xls');// 设置附件上传类型

                $upload->rootPath  =  $filepath; // 设置附件上传根目录

                $upload->saveName  =     'time';

                $upload->autoSub   =     false;

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

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

                }

                foreach ($info as $key => $value) {

                    unset($info);

                    $info[0]=$value;

                    $info[0]['savepath']=$filepath;

                }

                vendor("PHPExcel.PHPExcel");

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

                $objReader = \PHPExcel_IOFactory::createReader('Excel5');


                $objPHPExcel = $objReader->load($file_name,$encode='utf-8');

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

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

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

                $j=0;

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

                {

                    $data['name']= $objPHPExcel->getActiveSheet()->getCell("B".$i)->getValue();

                    $data['age']= $objPHPExcel->getActiveSheet()->getCell("C".$i)->getValue();

                    $data['create_time']= strtotime($objPHPExcel->getActiveSheet()->getCell("D".$i)->getValue());

                    $res = M('test')->add($data);

                    if(!$res){

                        echo 'no';die;

                    }

                    $j++;

                }

                unlink($file_name);

                $this->success('导入成功!本次导入测试数据:'.$j.'条');

            }else

            {

                $this->error("请选择上传的文件");

            }

        }

    }

  6. 测试导出功能,点击导出

    (5UYX[]X~41FADF0X)MB89O.png

  7. 测试导入功能,在excel里面创建3条信息,然后选择这个文件,点击导入

    A~2PLSHEV0E_I59{PGX[3KO.png

    ~[6TFTMY_C%Q)58]$4QV9PI.png

发表评论

  1. https://www.ekffo150.com ????????

  2. https://www.ekffo150.com/theking https://www.ekffo150.com/merit https://www.ekffo150.com/yes https://www.ekffo150.com/first https://www.ekffo150.com/sands https://www.ekffo150.com/joy https://www.ekffo150.com/royal https://www.ekffo150.com/starclub https://www.ekffo150.com/asian https://www.ekffo150.com/superman https://www.ekffo150.com/gatsby https://www.ekffo150.com/33casino https://www.ekffo150.com/ondasino https://www.ekffo150.com https://www.ajp4949.com/theking https://www.ajp4949.com/me

  3. <a href="https://www.ekffo150.com/" target="_blank">??????</a> <a href="https://www.ekffo150.com/yes" target="_blank">?????</a> <a href="https://www.ekffo150.com/first" target="_blank">??????</a> <a href="https://www.ekffo150.com/theking" target="_blank">?????</a> <a href="https://www.ekffo150.com/merit" target="_blank">??????</a> <a h

  4. 测试可用,感谢大佬 :smile:

  5. I am a new blogger and I have to say that I started to visit ShoutMeLoud after finding a very interesting post about some info that I was looking for. I have to admit that after receiving some of these great post on my email and reading through them, I have found on this blog a huge source of valuable information that helps me climb to a higher level every day. <a href="https://ase2020.com/">https://ase2020.com/</a>

  6. play casino games online: https://www.btlcasino.com https://www.btlcasino.com/theking https://www.btlcasino.com/sands https://www.btlcasino.com/first https://www.btlcasino.com/yes https://www.btlcasino.com/super https://www.btlcasino.com/gatsby https://www.btlcasino.com/33

  7. https://www.btlcasino.com/first ??????

  8. :cry: 哈哈就看看