thinkphp导入导出excel表单数据

在PHP项目经常要导入导出Excel表单。

下载自己博客园文件中的PHPExcel压缩包解压后，将PHPExcel.class.php文件和PHPExcel文件夹放在“项目\ThinkPHP\Library\Org\Util”目录下。

导入

在页面上传excel文件提交到后台，利用form表单提单即可

<form method="post" enctype="multipart/form-data" action="" >  
<input type="submit" value="导入" />                               
<input  type="file" name="excel" />                    
</form>

提交到后台后进行上传处理，这是一个执行上传的方法

public function data(){
        if(IS_POST){
        //设置编码防止乱码
        header("Content-Type:text/html;charset=utf-8");
            $upload = new \Think\Upload();// 实例化上传类
            $upload->maxSize   =     3145728 ;// 设置附件上传大小
            $upload->exts      =     array('xls','xlsx','csv');// 设置附件上传类
            $upload->rootPath  =     'Uploads/';
            $upload->savePath  =     ''; // 设置附件上传目录
            // 上传文件
            $info   =   $upload->uploadOne($_FILES['excel']);
            $fileRoute = 'Uploads/'.$info['savepath'].$info['savename'];
            $exts = $info['ext'];
           // var_dump($info);
            if(!$info) {// 上传错误提示错误信息
                  $this->error($upload->geterror());
              }else{// 上传成功
                  $this->data_import($fileRoute, $exts);
            }          
        }
    }

上传文件的格式设为excel的格式，xls，xlsx,csv均可支持

然后对上传后的文件内容进行读取导入，下面是一个读取的方法。

protected function data_import($fileRoute, $exts='xls')
    {
        //导入PHPExcel类库，因为PHPExcel没有用命名空间，只能inport导入
        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();
        }else if($exts == 'csv'){
            import("Org.Util.PHPExcel.Reader.CSV");
            $PHPReader=new \PHPExcel_Reader_CSV();
        }
 
        //var_dump($PHPReader);die;
        //载入文件
        $PHPExcel=$PHPReader->load($fileRoute);
        //获取表中的第一个工作表，如果要获取第二个，把0改为1，依次类推
        $currentSheet=$PHPExcel->getSheet(0);
        //获取总列数
        $allColumn=$currentSheet->getHighestColumn();
        //获取总行数
        $allRow=$currentSheet->getHighestRow();
        //循环获取表中的数据，$currentRow表示当前行，从哪行开始读取数据，索引值从0开始
        for($currentRow=1;$currentRow<=$allRow;$currentRow++){
            //从哪列开始，A表示第一列
            for($currentColumn='A';$currentColumn<=$allColumn;$currentColumn++){
                //数据坐标
                $address=$currentColumn.$currentRow;
                //读取到的数据，保存到数组$arr中
                $data[$currentRow][$currentColumn]=$currentSheet->getCell($address)->getValue();
            }
 
        }
        $this->save_import($data);
    }

用上面这个方法获得excel里的数据，然后用save_import把获得的数据存入数据库

//保存导入数据
    public function save_import($data)
    {
        //print_r($data);exit;
 
        $bath = M('data');
         
        //插入新数据时先清空原表数据，没有这个需要可以注释下面步骤
        //M('mobile')->where('1=1')->delete();
         
        foreach ($data as $k=>$v){
                //$mobile=$v['A'];   //注：****** （1）处
                $info['a'] = $v['A'];
                $info['b'] = $v['B'];
                $info['c']=$v['C'];
                $info['d_water']=$v['D'];
                $info['e']=$v['E'];
                $info['f']=$v['F'];
 
                $arr[] = $info;
 
        }
        $result=$bath->addall($arr);
        if($result){
            $this->success('数据导入成功');
        }else{
            $this->error('数据导入失败');
        }
        //print_r($info);
    }

导出方法一:

public function excel(){
        //引入PHPExcel库文件
        Vendor('PHPExcel.Classes.PHPExcel');
        //创建对象
        $excel = new PHPExcel();
        //Excel表格式,这里简略写了8列
        $letter = array('A','B','C','D','E','F','F','G');
        //表头数组
        $tableheader = array('序号','客栈名称','客栈地址','座机','手机','QQ','邮箱');
        //填充表头信息
        for($i = 0;$i < count($tableheader);$i++) {
            $excel->getActiveSheet()->setCellValue("$letter[$i]1","$tableheader[$i]");
        }
        //表格数组
        $data = array(
                array('1','B','C','D','E','F','G'),
                array('2','B','C','D','E','F','G'),
                array('3','B','C','D','E','F','G'),
                array('4','B','C','D','E','F','G'),
                array('5','B','C','D','E','F','G'),
                array('6','B','C','D','E','F','G'),
               array('7','B','C','D','E','F','G'),
        );
        //填充表格信息
        for ($i = 2;$i <= count($data) + 1;$i++) {
            $j = 0;
            foreach ($data[$i - 2] as $key=>$value) {
                $excel->getActiveSheet()->setCellValue("$letter[$j]$i","$value");
                $j++;
            }
        }
        //创建Excel输入对象
        $write = new PHPExcel_Writer_Excel5($excel);
        header("Pragma: public");
        header("Expires: 0");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");;
        header('Content-Disposition:attachment;filename="客栈信息.xls"');
        header("Content-Transfer-Encoding:binary");
        $write->save('php://output');

导出方法二:

.html页面直接调用out方法，导出即可

 1 public function out() {
 2         $Model = M('表名');
 3         $where = $_SESSION['onethink_admin']['where'];
 4         $data = $Model->field("cname,sname,game_name,mname,count_into,count_show,surl")->where($where)->select();
 5 
 6         //导入PHPExcel类库，因为PHPExcel没有用命名空间，只能inport导入
 7         import("Org.Util.PHPExcel");
 8         import("Org.Util.PHPExcel.Writer.Excel5");
 9         import("Org.Util.PHPExcel.IOFactory.php");
10 
11         $filename = "角色数据表";
12         $headArr = array("广告商渠道名称", "子渠道名称", "游戏名称", "素材名称", "落地页展示量", "到达量", "推广链接地址");
13         $this->getExcel($filename, $headArr, $data);
14     }

 1 public function getExcel($fileName, $headArr, $data) {
 2         //对数据进行检验
 3         if (empty($data) || !is_array($data)) {
 4             die("data must be a array");
 5         }
 6         //检查文件名
 7         if (empty($fileName)) {
 8             exit;
 9         }
10 
11         $date = date("Y_m_d", time());
12         $fileName .= "_{$date}.xls";
13 
14         //创建PHPExcel对象，注意，不能少了\
15         $objPHPExcel = new \PHPExcel();
16         $objProps = $objPHPExcel->getProperties();
17 
18         $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(15);
19         $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(15);
20         $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(15);
21         $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(12);
22         $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(15);
23         $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(12);
24         $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(15);
25         $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(12);
26         $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(15);
27         $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(15);
28         $objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(15);
29         $objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(12);
30         $objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(15);
31         $objPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(12);
32         $objPHPExcel->getActiveSheet()->getColumnDimension('O')->setWidth(15);
33         $objPHPExcel->getActiveSheet()->getColumnDimension('P')->setWidth(15);
34         $objPHPExcel->getActiveSheet()->getColumnDimension('Q')->setWidth(15);
35 
36         //设置表头
37         $key = ord("A");
38         foreach ($headArr as $v) {
39             $colum = chr($key);
40             $objPHPExcel->setActiveSheetIndex(0)->setCellValue($colum . '1', $v);
41             $key += 1;
42         }
43 
44         $column = 2;
45         $objActSheet = $objPHPExcel->getActiveSheet();
46         foreach ($data as $key => $rows) { //行写入
47             $span = ord("A");
48             foreach ($rows as $keyName => $value) {// 列写入
49                 $j = chr($span);
50                 $objActSheet->setCellValue($j . $column, $value);
51                 $span++;
52             }
53             $column++;
54         }
55 
56         $fileName = iconv("utf-8", "gb2312", $fileName);
57         //重命名表
58         // $objPHPExcel->getActiveSheet()->setTitle('test');
59         //设置活动单指数到第一个表,所以Excel打开这是第一个表
60         $objPHPExcel->setActiveSheetIndex(0);
61         header('Content-Type: application/vnd.ms-excel');
62         header("Content-Disposition: attachment;filename=\"$fileName\"");
63         header('Cache-Control: max-age=0');
64 
65         $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
66         $objWriter->save('php://output'); //文件通过浏览器下载
67         exit;
68     }

其中

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

......等是设置excel表格单个单元格宽的；不设置不用写。

posted @ 2018-05-25 17:31 哟风 Views(395) Comments(0) 收藏举报

刷新页面返回顶部

我的屋子

thinkphp导入导出excel表单数据