ThinkPHP+PHPExcel[导入][导出]实现方法
- WBOYOriginal
- 2016-06-07 11:43:283474browse
以下是我自己的实现方法，可能存在很多不足，欢迎大家提出改进...
实现步骤：
一：去官网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元