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*/
9public 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//如果合并单元格
40if(isset($borderConf['mergeCells']) && $borderConf['mergeCells']){
41foreach($borderConf['mergeCells'] as$mercell){
42// var_dump($mercell);
43$objPHPExcel->getActiveSheet()->mergeCells($mercell['x'].":".$mercell['y']);
44 }
45 }
46//如果分离单元格
47if(isset($borderConf['unmerge']) && $borderConf['unmerge']){
48foreach($borderConf['unmerge'] as$unmercell){
49$objPHPExcel->getActiveSheet()->unmergeCells($unmercell['x'].":".$unmercell['y']);
50 }
51 }
52
53
54//设置列名
55if($data['headtitle']){
56$k=1;//从a1开始
57foreach($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;
68foreach($data['list'] as$row){
69//dump($row);
70$h=1;
71foreach ($data['listfield'] as$fid) {
72// echo $letters_arr[$h].$i.'<br>';
73 // echo $row[$fid].'<br>';
74if(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
81if((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');
109if($outputpath!='')
110 {
111$savefilepath=$outputpath.'/'.$properties['Title'].'.xls';
112$objWriter->save($savefilepath);
113 }else{
114header('Content-Type: application/vnd.ms-excel');
115header('Content-Disposition: attachment;filename='.$properties['Title'].'.xls');
116header('Cache-Control: max-age=0');
117$objWriter->save('php://output');
118 }
119 }
View Code
数据导出前处理
1// 导出
2public 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']=[];
11if($list)
12 {
13foreach ($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_na 46$properties=[
47 'Title'=>$this->_classname,
48 'description'=>$this->_classname,
49 'sheetTitle'=>$this->_classname,
50 ];
51 action('Phpexcel/exportExcelData',[$ExcelD,$properties]);
52 }
View Code
以上代码需要根据具体情况修改，⼤致格式不变就⾏
导⼊getsavefilename
applocation/admin/controller/Phoexcel.php 类名:Phpexcel
1/**
2 * 导⼊excel⽂件
3 * @return [type] [description]
4*/
5public 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⽂件
21try {
22$inputFileType = \PHPExcel_IOFactory::identify($inputFileName);
23$objReader = \PHPExcel_IOFactory::createReader($inputFileType);
24$objPHPExcel = $objReader->load($inputFileName);
25 } catch(Exception$e) {
26die('加载⽂件发⽣错误："'.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 // 获取⼀⾏的数据
40for ($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]);
44if($chulidata)
45 {
46$rowD[$hang]=$chulidata;
47 }
48 }
49return$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*/
7public 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 \ZipArchive();
14$zip->open($filename, \ZipArchive::CREATE); //打开压缩包
15
16 // //向压缩包中添加⽂件
17foreach ($fileNameArr as$file) {
18$zip->addFromString($folder.'/'.basename($file),file_get_contents($file)); //向压缩包中添加⽂件19unlink($file); //删除csv临时⽂件
20 }
21
22$zip->close(); //关闭压缩包
23
24 //输出压缩⽂件提供下载
25header("Cache-Control: max-age=0");
26header("Content-Description: File Transfer");
27header('Content-disposition: attachment; filename='.$filename); // ⽂件名
28header("Content-Type: application/zip"); // zip格式的
29header("Content-Transfer-Encoding: binary"); //
30header('Content-Length: '.filesize($filename)); //
31ob_clean();
32flush();
33readfile($filename);//输出⽂件;
34unlink($filename); //删除压缩包临时⽂件
35 }
View Code
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论