实现步骤:
一:去官网http://phpexcel.codeplex.com/下载最新PHPExcel放到Vendor下,注意位置:ThinkPHP\Extend\Vendor\PHPExcel\PHPExcel.php。
二:在CommonAction.class.php中添加以下两个函数:
- 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121
/**
+----------------------------------------------------------
* 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
=
$_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
<
$cellNum
;
$i
++){
$objPHPExcel
->setActiveSheetIndex(0)->setCellValue(
$cellName
[
$i
].
'2'
,
$expCellName
[
$i
][1]);
}
// Miscellaneous glyphs, UTF-8
for
(
$i
=0;
$i
<
$dataNum
;
$i
++){
for
(
$j
=0;
$j
<
$cellNum
;
$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@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
);
}
使用方法
导入:
- 1234567891011
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
){
..这里写你的业务代码..
}
}
}
}
导出:
- 1234567891011
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
);
}
版权声明:
此文为本站源创文章[或由本站编辑从网络整理改编],
转载请备注出处:
[ThinkPHP]
http://www.thinkphp.cn/code/403.html
[若此文确切存在侵权,请联系本站管理员进行删除!]
--THE END--