thinkphp安装插件phpExcel实现导出导入数据实例教程 |
您所在的位置：网站首页 › 基因决定论知乎 › thinkphp安装插件phpExcel实现导出导入数据实例教程 |
thinkphp安装插件phpExcel实现导出导入数据实例教程
|
插件phpExcel 版本：1.8.0 下载地址：http://www.chtml.cn/topic/show/40 实例使用的thinkphp版本3.2.3 适用个版本的thinkphp框架安装 一、下载完毕后解压文件得到以下几个文件夹选择黑色框里的文件放到thinkphp的框架系统目录下\Thinkphp\Library\Vendor\下 三、在公共控制器中加入导入导出公用代码 /** +---------------------------------------------------------- * Export Excel | 2013.08.23 * Author:HongPing +---------------------------------------------------------- * @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;$isetActiveSheetIndex(0)->setCellValue($cellName[$i].'2', $expCellName[$i][1]); } // Miscellaneous glyphs, UTF-8 for($i=0;$isetCellValue($cellName[$j].($i+3), $expTableData[$i][$expCellName[$j][0]]); } } header('pragma:public'); header('Content-type:application/vnd.ms-excel;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 +---------------------------------------------------------- * @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 ;$currentRowgetCell($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); }四、之后在你作为导出数据使用的控制器写入下列代码 //导入： 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); }五、可能出现的问题 1.找不到某个控制器，或者其他错误，检查一下是不是实例化插件路径不对。可以尝试在new 的后面加“ / ”试试。 2.导出电话号码或者数字类型数据第一个0消失或者末尾0消失的问题解决方式：在公用的控制器中导出部分的代码加上“ ”；就可以解决 $objPHPExcel->getActiveSheet(0)->setCellValue($cellName[$j].($i+3)," ".$expTableData[$i][$expCellName[$j][0]]);3.解决导出数据列表宽度的问题 $objPHPExcel->getActiveSheet(0)->setCellValue($cellName[$j].($i+3)," ".$expTableData[$i][$expCellName[$j][0]])->getDefaultColumnDimension($cellName[$j])->setWidth(25); |
二、把Classes的文件名称改成PHPExcel【本文地址】