以下是我自己的实现方法,可能存在很多不足,欢迎大家提出改进...
实现步骤:
一:去官网 http://phpexcel.codeplex.com/ 下载最新PHPExcel放到Vendor下,注意位置:ThinkPHP\Extend\Vendor\PHPExcel\PHPExcel.php。
使用方法
导入:
导出:
实现步骤:
一:去官网 http://phpexcel.codeplex.com/ 下载最新PHPExcel放到Vendor下,注意位置:ThinkPHP\Extend\Vendor\PHPExcel\PHPExcel.php。
二:在CommonAction.class.php中添加以下两个函数:
/**
+----------------------------------------------------------
* Export Excel | 2013.08.23
* Author:HongPing <hongping626@qq.com>
+----------------------------------------------------------
* @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 = $xlsTitle.'_'.date('YmdHi');<span style="white-space:pre"> </span>//or $xlsTitle 文件名称可根据自己情况设定
vendor("PHPExcel.PHPExcel");
$objPHPExcel = new \PHPExcel();
//设置表头
$key = 0;
foreach($expCellName as $v){
//注意,不能少了。将列数字转换为字母\
$colum = \PHPExcel_Cell::stringFromColumnIndex($key);
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue($colum.'1', $v[1]);
$key += 1;
}
//插入数据
$column = 2;
$objActSheet = $objPHPExcel->getActiveSheet();
foreach($expTableData as $key => $rows){ //行写入
$span = 0;
foreach($rows as $keyName=>$value){// 列写入
$j = \PHPExcel_Cell::stringFromColumnIndex($span);
$objActSheet->setCellValue($j.$column, $value);
$span++;
}
$column++;
}
header('Content-Type: application/vnd.ms-excel');
<span style="white-space:pre"> </span> header('Content-Disposition: attachment; filename='.$fileName.'.xlsx');//attachment新窗口打印inline本窗口打印
<span style="white-space:pre"> </span> header('Cache-Control: max-age=0');
$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
<span style="white-space:pre"> </span> $objWriter->save('php://output');
exit;
}
/**
+----------------------------------------------------------
* Import Excel | 2013.08.23
* Author:HongPing <hongping626@qq.com>
+----------------------------------------------------------
* @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<=$allRow;$currentRow++){
$row = array();
for($currentColumn=0;$currentColumn<$allColumn;$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);
}
使用方法
导入:
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);
}