首页 >php教程 >php手册 >ThinkPHP+PHPExcel[导入][导出]实现方法

ThinkPHP+PHPExcel[导入][导出]实现方法

WBOY
WBOY原创
2016-06-07 11:43:283474浏览

以下是我自己的实现方法,可能存在很多不足,欢迎大家提出改进...
实现步骤:
一:去官网http://phpexcel.codeplex.com/下载最新PHPExcel放到Vendor下,注意位置:ThinkPHP\Extend\Vendor\PHPExcel\PHPExcel.php。
二:在CommonAction.class.php中添加以下两个函数:/**<br>      +----------------------------------------------------------<br>      * Export Excel | 2013.08.23<br>      * Author:HongPing <hongping626><br>      +----------------------------------------------------------<br>      * @param $expTitle     string File name<br>      +----------------------------------------------------------<br>      * @param $expCellName  array  Column name<br>      +----------------------------------------------------------<br>      * @param $expTableData array  Table data<br>      +----------------------------------------------------------<br>      */<br>     public function exportExcel($expTitle,$expCellName,$expTableData){<br>         $xlsTitle = iconv('utf-8', 'gb2312', $expTitle);//文件名称<br>         $fileName = $_SESSION['loginAccount'].date('_YmdHis');//or $xlsTitle 文件名称可根据自己情况设定<br>         $cellNum = count($expCellName);<br>         $dataNum = count($expTableData);<br>         vendor("PHPExcel.PHPExcel");<br>         $objPHPExcel = new PHPExcel();<br>         $cellName = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z','AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM','AN','AO','AP','AQ','AR','AS','AT','AU','AV','AW','AX','AY','AZ');<br>         <br>         $objPHPExcel->getActiveSheet(0)->mergeCells('A1:'.$cellName[$cellNum-1].'1');//合并单元格<br>         $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', $expTitle.'  Export time:'.date('Y-m-d H:i:s'));  <br>         for($i=0;$i             $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$i].'2', $expCellName[$i][1]); <br>         } <br>           // Miscellaneous glyphs, UTF-8   <br>         for($i=0;$i           for($j=0;$j             $objPHPExcel->getActiveSheet(0)->setCellValue($cellName[$j].($i+3), $expTableData[$i][$expCellName[$j][0]]);<br>           }             <br>         }  <br>         <br>         header('pragma:public');<br>         header('Content-type:application/vnd.ms-excel;charset=utf-8;name="'.$xlsTitle.'.xls"');<br>         header("Content-Disposition:attachment;filename=$fileName.xls");//attachment新窗口打印inline本窗口打印<br>         $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');  <br>         $objWriter->save('php://output'); <br>         exit;   <br>     }<br>      <br>     /**<br>      +----------------------------------------------------------<br>      * Import Excel | 2013.08.23<br>      * Author:HongPing <hongping626><br>      +----------------------------------------------------------<br>      * @param  $file   upload file $_FILES<br>      +----------------------------------------------------------<br>      * @return array   array("error","message")<br>      +----------------------------------------------------------     <br>      */   <br>     public function importExecl($file){ <br>         if(!file_exists($file)){ <br>             return array("error"=>0,'message'=>'file not found!');<br>         } <br>         Vendor("PHPExcel.PHPExcel.IOFactory"); <br>         $objReader = PHPExcel_IOFactory::createReader('Excel5'); <br>         try{<br>             $PHPReader = $objReader->load($file);<br>         }catch(Exception $e){}<br>         if(!isset($PHPReader)) return array("error"=>0,'message'=>'read error!');<br>         $allWorksheets = $PHPReader->getAllSheets();<br>         $i = 0;<br>         foreach($allWorksheets as $objWorksheet){<br>             $sheetname=$objWorksheet->getTitle();<br>             $allRow = $objWorksheet->getHighestRow();//how many rows<br>             $highestColumn = $objWorksheet->getHighestColumn();//how many columns<br>             $allColumn = PHPExcel_Cell::columnIndexFromString($highestColumn);<br>             $array[$i]["Title"] = $sheetname; <br>             $array[$i]["Cols"] = $allColumn; <br>             $array[$i]["Rows"] = $allRow; <br>             $arr = array();<br>             $isMergeCell = array();<br>             foreach ($objWorksheet->getMergeCells() as $cells) {//merge cells<br>                 foreach (PHPExcel_Cell::extractAllCellReferencesInRange($cells) as $cellReference) {<br>                     $isMergeCell[$cellReference] = true;<br>                 }<br>             }<br>             for($currentRow = 1 ;$currentRow                 $row = array(); <br>                 for($currentColumn=0;$currentColumn                     $cell =$objWorksheet->getCellByColumnAndRow($currentColumn, $currentRow);<br>                     $afCol = PHPExcel_Cell::stringFromColumnIndex($currentColumn+1);<br>                     $bfCol = PHPExcel_Cell::stringFromColumnIndex($currentColumn-1);<br>                     $col = PHPExcel_Cell::stringFromColumnIndex($currentColumn);<br>                     $address = $col.$currentRow;<br>                     $value = $objWorksheet->getCell($address)->getValue();<br>                     if(substr($value,0,1)=='='){<br>                         return array("error"=>0,'message'=>'can not use the formula!');<br>                         exit;<br>                     }<br>                     if($cell->getDataType()==PHPExcel_Cell_DataType::TYPE_NUMERIC){<br>                         $cellstyleformat=$cell->getParent()->getStyle( $cell->getCoordinate() )->getNumberFormat();<br>                         $formatcode=$cellstyleformat->getFormatCode();<br>                         if (preg_match('/^([$[A-Z]*-[0-9A-F]*])*[hmsdy]/i', $formatcode)) {<br>                             $value=gmdate("Y-m-d", PHPExcel_Shared_Date::ExcelToPHP($value));<br>                         }else{<br>                             $value=PHPExcel_Style_NumberFormat::toFormattedString($value,$formatcode);<br>                         }                <br>                     }<br>                     if($isMergeCell[$col.$currentRow]&&$isMergeCell[$afCol.$currentRow]&&!empty($value)){<br>                         $temp = $value;<br>                     }elseif($isMergeCell[$col.$currentRow]&&$isMergeCell[$col.($currentRow-1)]&&empty($value)){<br>                         $value=$arr[$currentRow-1][$currentColumn];<br>                     }elseif($isMergeCell[$col.$currentRow]&&$isMergeCell[$bfCol.$currentRow]&&empty($value)){<br>                         $value=$temp;<br>                     }<br>                     $row[$currentColumn] = $value; <br>                 } <br>                 $arr[$currentRow] = $row; <br>             } <br>             $array[$i]["Content"] = $arr; <br>             $i++;<br>         } <br>         spl_autoload_register(array('Think','autoload'));//must, resolve ThinkPHP and PHPExcel conflicts<br>         unset($objWorksheet); <br>         unset($PHPReader); <br>         unset($PHPExcel); <br>         unlink($file); <br>         return array("error"=>1,"data"=>$array); <br>     }</hongping626></hongping626>使用方法
导入:function impUser(){<br>      if(isset($_FILES["import"]) && ($_FILES["import"]["error"] == 0)){<br>         $result = $this->importExecl($_FILES["import"]["tmp_name"]);<br>         if($result["error"] == 1){          <br>           $execl_data = $result["data"][0]["Content"];<br>                   foreach($execl_data as $k=>$v){<br>                       ..这里写你的业务代码..<br>                   }<br>          }<br>       }<br> }导出:function expUser(){//导出Excel<br>         $xlsName  = "User";<br>         $xlsCell  = array(<br>             array('id','账号序列'),<br>             array('account','登录账户'),<br>             array('nickname','账户昵称')<br>         );<br>         $xlsModel = M('Post');<br>         $xlsData  = $xlsModel->Field('id,account,nickname')->select();<br>         $this->exportExcel($xlsName,$xlsCell,$xlsData);<br>     }

AD:真正免费,域名+虚机+企业邮箱=0元

声明:
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn