tp5的导入与导出excel表格

请注意下面说的

PHPExcel/Classes/PHPExcel

需要引入 phpexcel 插件文件 网上搜一大堆这里就不添加下载链接了哈

导出
导出前数据处理
导入
文件夹压缩并下载

数据导出

applocation/admin/controller/Phoexcel.php 类名:Phpexcel

  1 /**
  2      * 导出 excel数据
  3      * @param  [type] $data       [数据整合]
  4      * @param  [type] $properties [补充信息]
  5      * @param  string $outputpath [文件临时保存所在的文件夹]
  6      * @param  [type] $borderConf [合并与分离单元格]
  7      * @return [type]             [description]
  8      */
  9     public function exportExcelData($data,$properties,$outputpath='',$borderConf=[])
 10     {
 11         //补充文档信息
 12         $properties['Title']=isset($properties['Title'])?$properties['Title']:'Office 2007 xlsx Document';
 13         $properties['description']=isset($properties['description'])?$properties['description']:'The test export data of file';
 14 
 15         $properties['sheetTitle']=isset($properties['sheetTitle'])?$properties['sheetTitle']:'sheet1';
 16 
 17         $properties['keyword']=isset($properties['keyword'])?$properties['keyword']:'office 2007 openxml php';
 18         $properties['Category']=isset($properties['Category'])?$properties['Category']:'Test result file';
 19         $properties['Creator']=isset($properties['Creator'])?$properties['Creator']:'zfkj';
 20         $properties['LastModified']=isset($properties['LastModified'])?$properties['LastModified']:'zfkj';
 21 
 22 
 23         import("PHPExcel/Classes/PHPExcel", EXTEND_PATH);
 24         $objPHPExcel=new PHPExcel();
 25         // $objPHPExcel=new PExcel();
 26        
 27         $objPHPExcel->getProperties()->setCreator($properties['Creator'])
 28                                      ->setLastModifiedBy($properties['LastModified'])
 29                                      ->setTitle($properties['Title'])
 30                                      ->setSubject($properties['Title'])
 31                                      ->setDescription($properties['description'])
 32                                      ->setKeywords($properties['keyword'])
 33                                      ->setCategory($properties['Category']);
 34         //设置当前的sheet   
 35         $objPHPExcel->setActiveSheetIndex(0);
 36         //设置列数组
 37         $letters_arr = array(1=>'A',2=>'B',3=>'C',4=>'D',5=>'E',6=>'F',7=>'G',8=>'H',9=>'I',10=>'J',11=>'K',12=>'L',13=>'M', 14=>'N',15=>'O',16=>'P',17=>'Q',18=>'R',19=>'S',20=>'T',21=>'U',22=>'V',23=>'W',24=>'X',25=>'Y',26=>'Z');
 38 
 39         //如果合并单元格
 40         if(isset($borderConf['mergeCells']) && $borderConf['mergeCells']){
 41             foreach($borderConf['mergeCells'] as $mercell){
 42                 // var_dump($mercell);
 43                 $objPHPExcel->getActiveSheet()->mergeCells($mercell['x'].":".$mercell['y']);
 44             }
 45         }
 46         //如果分离单元格
 47         if(isset($borderConf['unmerge']) && $borderConf['unmerge']){
 48             foreach($borderConf['unmerge'] as $unmercell){
 49                 $objPHPExcel->getActiveSheet()->unmergeCells($unmercell['x'].":".$unmercell['y']);
 50             }
 51         }
 52 
 53 
 54         //设置列名
 55         if($data['headtitle']){
 56             $k=1;//从a1开始
 57             foreach($data['headtitle'] as $tit){
 58                 $tit=getutf8($tit);
 59                 $objPHPExcel->getActiveSheet()->setCellValue($letters_arr[$k]."1",$tit);
 60                 // $objPHPExcel->getActiveSheet()->getColumnDimension($letters_arr[$k])->setAutoSize(true);
 61                 // ->setWidth(25)
 62                 $k++;
 63             }
 64         }
 65         //添加数据
 66         $i=2;
 67         //print_r($list);exit;
 68         foreach($data['list'] as $row){
 69         //dump($row);
 70             $h=1;
 71             foreach ($data['listfield'] as $fid) {              
 72                 // echo $letters_arr[$h].$i.'<br>';
 73                 // echo $row[$fid].'<br>';
 74                 if(isset($row[$fid]))
 75                 {
 76                     $value=getutf8($row[$fid]);
 77                     $objPHPExcel->getActiveSheet()->setCellValue($letters_arr[$h].$i, $value);  
 78 
 79                     $ti=getutf8($data['headtitle'][$h-1]);
 80                     
 81                     if((strlen($value)+7 < strlen($ti)) || (strlen($value)+2 < strlen($ti)))
 82                     {
 83                         $wid=strlen($ti);
 84                         $objPHPExcel->getActiveSheet()->getColumnDimension($letters_arr[$h])->setWidth($wid);
 85                     }else if((strlen($value)-6 == strlen($ti))){
 86                         $wid=strlen($value);
 87                         $objPHPExcel->getActiveSheet()->getColumnDimension($letters_arr[$h])->setWidth($wid);
 88                     }else if(strlen($value)/5 > strlen($ti)){
 89                         $objPHPExcel->getActiveSheet()->getColumnDimension($letters_arr[$h])->setWidth(40);
 90                     }else{
 91                         $objPHPExcel->getActiveSheet()->getColumnDimension($letters_arr[$h])->setAutoSize(true);
 92                     }
 93                     // var_dump($ti,$value);
 94                     // var_dump(strlen($value),strlen($ti));
 95                     // echo '<hr>';
 96                     $objPHPExcel->getActiveSheet()->getStyle($letters_arr[$h].$i)->getAlignment()->setWrapText(true)->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);//自动换行
 97 
 98                 }
 99                 $h++;
100             }           
101             $i++;       
102         }
103         // die;
104         // exit;
105         //设置sheet的name   
106         $objPHPExcel->getActiveSheet()->setTitle($properties['sheetTitle']);  
107         
108         $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
109         if($outputpath!='')
110         {
111             $savefilepath=$outputpath.'/'.$properties['Title'].'.xls';
112             $objWriter->save($savefilepath);
113         }else{
114             header('Content-Type: application/vnd.ms-excel');
115             header('Content-Disposition: attachment;filename='.$properties['Title'].'.xls');
116             header('Cache-Control: max-age=0');
117             $objWriter->save('php://output');
118         }
119     }

View Code

数据导出前处理

 1 // 导出
 2     public function export_do()
 3     {
 4         $ws=$this->_search();
 5         $w=$ws['w'];
 6         $list=$this->_mod->getlist($w);                                                
 7         
 8         $ExcelD=[];        
 9         $ExcelD['headtitle']=['ID','名称','地区','属性','规模(面积或千米)','造价(万元)','业主单位','计划报名时间','公司审核','参标状态','项目状态','状态','备注','报备人','报备人电话','已加入合并','专属客服','专属客服电话','客服团队','客服团队电话','添加时间','修改时间'];
10         $ExcelD['list']=[];
11         if($list)
12         {
13             foreach ($list as $k => $v) {
14                 $v['status']=$this->_mod->status($v);
15                 // $v=$v->getdata();
16                 $info=[
17                     'id'=>$v['id'],
18                     'name'=>$v['name'],
19                     'region'=>$v['region']['province'].$v['region']['city'].$v['region']['area'].$v['daddress'],
20                     'attr_name'=>isset($this->attr_list[$v['attr_id']])?$this->attr_list[$v['attr_id']]:'',
21                     'scale'=>$v['scale'],
22                     'p_value'=>$v['p_value'],
23                     'company'=>$v['company'],
24                     'sign_up_time'=>$v['sign_up_time'],
25                     'examine_status'=>$this->examine_status_list[$v['examine_status']],
26                     'standard_status'=>$this->standard_status_list[$v['standard_status']],
27                     'project_status'=>$this->project_status_list[$v['project_status']],
28                     'status'=>$v['status'],
29                     'remarks'=>$v['remarks'],
30                     'user_name'=>$v['user']['name'],
31                     'user_mobile'=>$v['user']['mobile'],
32                     'cart_true'=>$v['cart_true']==1?'是':'否',
33                     'servicer_name'=>empty($v['servicer']['name'])?'无':$v['servicer']['name'],
34                     'servicer_mobile'=>empty($v['servicer']['mobile'])?'无':$v['servicer']['mobile'],
35                     'user_r_name'=>isset($v['user']['r_name'])?$v['user']['r_name']:'无',
36                     'user_r_mobile'=>isset($v['user']['r_mobile'])?$v['user']['r_mobile']:'无',
37                     'addtime'=>date('Y-m-d H:i:s',$v['addtime']),
38                     'updatetime'=>date('Y-m-d H:i:s',$v['updatetime']),
39                     
40                 ];
41                 $ExcelD['list'][]=$info;
42             }
43         }
44 
45         $ExcelD['listfield']=['id','name','region','attr_name','scale','p_value','company','sign_up_time','examine_status','standard_status','project_status','status','remarks','user_name','user_mobile','cart_true','servicer_name','servicer_mobile','user_r_name','user_r_mobile','addtime','updatetime'];
46         $properties=[
47             'Title'=>$this->_classname,
48             'description'=>$this->_classname,
49             'sheetTitle'=>$this->_classname,
50         ];
51         action('Phpexcel/exportExcelData',[$ExcelD,$properties]);
52     }

View Code

以上代码需要根据具体情况修改，大致格式不变就行

导入

applocation/admin/controller/Phoexcel.php 类名:Phpexcel

 1 /**
 2      * 导入excel文件
 3      * @return [type] [description]
 4      */
 5     public function entryExcelData($file,$extend,$controller,$action,$folder='phpexcel')
 6     {
 7         $time=date('Y-m-d H:i:s');
 8         // 将文件移动到指定文件夹
 9         $path=$folder.DS.'file_'.$extend.DS;
10         // $path=ROOT_PATH.'public/'.$folder.'/file_'.$extend.'/';
11         $info = $file->move($path);
12         $getpath='\'.$path.$info->getSaveName();
13         $getpath       = strtr($getpath,'\','/');
14 
15         $inputFileName='.'.$getpath;
16         // echo $inputFileName;
17 
18         import("PHPExcel/Classes/PHPExcel", EXTEND_PATH);
19 
20         // 读取excel文件
21         try {
22             $inputFileType = PHPExcel_IOFactory::identify($inputFileName);
23             $objReader = PHPExcel_IOFactory::createReader($inputFileType);
24             $objPHPExcel = $objReader->load($inputFileName);
25         } catch(Exception $e) {
26             die('加载文件发生错误："'.pathinfo($inputFileName,PATHINFO_BASENAME).'": '.$e->getMessage());
27         }
28 
29 
30         // 确定要读取的sheet，什么是sheet，看excel的右下角，真的不懂去百度吧
31         $sheet = $objPHPExcel->getSheet(0);
32         $highestRow = $sheet->getHighestRow();
33         $highestColumn = $sheet->getHighestColumn();
34 
35         // echo '<pre>';
36         $rowD=[];
37         // $titleD=[];
38 
39         // 获取一行的数据
40         for ($row = 1; $row <= $highestRow; $row++){
41             $rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, NULL, TRUE, FALSE);
42             $hang=$row;
43             $chulidata=action($controller.'/'.$action,[$rowData[0],$hang]);
44             if($chulidata)
45             {
46                 $rowD[$hang]=$chulidata;
47             }
48         }
49         return $rowD;
50     }

View Code

文件夹压缩并下载

applocation/admin/controller/Phoexcel.php 类名:Phpexcel

 1 /**
 2      * 文件夹压缩并下载文件
 3      * @param  [type] $filename [zip文件命名]
 4      * @param  string $folder   [description]
 5      * @return [type]           [description]
 6      */
 7     public function folderZipdown($filename,$folder='phpexcel')
 8     {
 9         $filename.='.zip';
10         $filename = iconv('utf-8','gbk//ignore',$filename);
11         $fileNameArr=glob(ROOT_PATH.'public/'.$folder.'/*.xls');
12 
13         $zip = new ipArchive(); 
14         $zip->open($filename, ipArchive::CREATE);   //打开压缩包
15      
16         // //向压缩包中添加文件
17         foreach ($fileNameArr as $file) {
18             $zip->addFromString($folder.'/'.basename($file),file_get_contents($file)); //向压缩包中添加文件
19             unlink($file); //删除csv临时文件
20         }
21      
22         $zip->close();  //关闭压缩包
23      
24         //输出压缩文件提供下载
25         header("Cache-Control: max-age=0");
26         header("Content-Description: File Transfer");
27         header('Content-disposition: attachment; filename='.$filename); // 文件名
28         header("Content-Type: application/zip"); // zip格式的
29         header("Content-Transfer-Encoding: binary"); //
30         header('Content-Length: '.filesize($filename)); //
31         ob_clean();
32         flush();
33         readfile($filename);//输出文件;
34         unlink($filename); //删除压缩包临时文件
35     }

View Code