ThinkPHP+PHPExcel[导入][导出]实现方法-php手册-PHP中文网

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

php中文网

发布： 2016-06-07 11:43:28

原创

3273人浏览过

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

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

首页

PHP培训

视频教程

学习路径

资源下载

技术文章

社区

微信公众号

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