实现步骤:
一:在http://phpexcel.codeplex.com/下载最新PHPExcel放到Vendor下,注意位置:ThinkPHP\Extend\Vendor\PHPExcel\PHPExcel.php。
二:导出excel代码实现
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
|
/**方法**/
function index(){
$this ->display();
}
public function exportExcel( $expTitle , $expCellName , $expTableData ){
$xlsTitle = iconv( 'utf-8' , 'gb2312' , $expTitle ); //文件名称
$fileName = $_SESSION [ 'account' ]. 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 ;
}
/**
*
* 导出Excel
*/
function expUser(){ //导出Excel
$xlsName = "User" ;
$xlsCell = array (
array ( 'id' , '账号序列' ),
array ( 'truename' , '名字' ),
array ( 'sex' , '性别' ),
array ( 'res_id' , '院系' ),
array ( 'sp_id' , '专业' ),
array ( 'class' , '班级' ),
array ( 'year' , '毕业时间' ),
array ( 'city' , '所在地' ),
array ( 'company' , '单位' ),
array ( 'zhicheng' , '职称' ),
array ( 'zhiwu' , '职务' ),
array ( 'jibie' , '级别' ),
array ( 'tel' , '电话' ),
array ( 'qq' , 'qq' ),
array ( 'email' , '邮箱' ),
array ( 'honor' , '荣誉' ),
array ( 'remark' , '备注' )
);
$xlsModel = M( 'Member' );
$xlsData = $xlsModel ->Field( 'id,truename,sex,res_id,sp_id,class,year,city,company,zhicheng,zhiwu,jibie,tel,qq,email,honor,remark' )->select();
foreach ( $xlsData as $k => $v )
{
$xlsData [ $k ][ 'sex' ]= $v [ 'sex' ]==1? '男' : '女' ;
}
$this ->exportExcel( $xlsName , $xlsCell , $xlsData );
}
|
第三:导入excel数据代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
|
function impUser(){
if (! empty ( $_FILES )) {
import( "@.ORG.UploadFile" );
$config = array (
'allowExts' => array ( 'xlsx' , 'xls' ),
'savePath' => './Public/upload/' ,
'saveRule' => 'time' ,
);
$upload = new UploadFile( $config );
if (! $upload ->upload()) {
$this ->error( $upload ->getErrorMsg());
} else {
$info = $upload ->getUploadFileInfo();
}
vendor( "PHPExcel.PHPExcel" );
$file_name = $info [0][ 'savepath' ]. $info [0][ 'savename' ];
$objReader = PHPExcel_IOFactory::createReader( 'Excel5' );
$objPHPExcel = $objReader ->load( $file_name , $encode = 'utf-8' );
$sheet = $objPHPExcel ->getSheet(0);
$highestRow = $sheet ->getHighestRow(); // 取得总行数
$highestColumn = $sheet ->getHighestColumn(); // 取得总列数
for ( $i =3; $i <= $highestRow ; $i ++)
{
$data [ 'account' ]= $data [ 'truename' ] = $objPHPExcel ->getActiveSheet()->getCell( "B" . $i )->getValue();
$sex = $objPHPExcel ->getActiveSheet()->getCell( "C" . $i )->getValue();
// $data['res_id'] = $objPHPExcel->getActiveSheet()->getCell("D".$i)->getValue();
$data [ 'class' ] = $objPHPExcel ->getActiveSheet()->getCell( "E" . $i )->getValue();
$data [ 'year' ] = $objPHPExcel ->getActiveSheet()->getCell( "F" . $i )->getValue();
$data [ 'city' ]= $objPHPExcel ->getActiveSheet()->getCell( "G" . $i )->getValue();
$data [ 'company' ]= $objPHPExcel ->getActiveSheet()->getCell( "H" . $i )->getValue();
$data [ 'zhicheng' ]= $objPHPExcel ->getActiveSheet()->getCell( "I" . $i )->getValue();
$data [ 'zhiwu' ]= $objPHPExcel ->getActiveSheet()->getCell( "J" . $i )->getValue();
$data [ 'jibie' ]= $objPHPExcel ->getActiveSheet()->getCell( "K" . $i )->getValue();
$data [ 'honor' ]= $objPHPExcel ->getActiveSheet()->getCell( "L" . $i )->getValue();
$data [ 'tel' ]= $objPHPExcel ->getActiveSheet()->getCell( "M" . $i )->getValue();
$data [ 'qq' ]= $objPHPExcel ->getActiveSheet()->getCell( "N" . $i )->getValue();
$data [ 'email' ]= $objPHPExcel ->getActiveSheet()->getCell( "O" . $i )->getValue();
$data [ 'remark' ]= $objPHPExcel ->getActiveSheet()->getCell( "P" . $i )->getValue();
$data [ 'sex' ]= $sex == '男' ?1:0;
$data [ 'res_id' ] =1;
$data [ 'last_login_time' ]=0;
$data [ 'create_time' ]= $data [ 'last_login_ip' ]= $_SERVER [ 'REMOTE_ADDR' ];
$data [ 'login_count' ]=0;
$data [ 'join' ]=0;
$data [ 'avatar' ]= '' ;
$data [ 'password' ]=md5( '123456' );
M( 'Member' )->add( $data );
}
$this ->success( '导入成功!' );
} else
{
$this ->error( "请选择上传的文件" );
}
}
|
四、模板代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
< html >
< head >
</ head >
< body >
< P >< a href = "{:U('Index/expUser')}" >导出数据并生成excel</ a ></ P >< br />
< form action = "{:U('Index/impUser')}" method = "post" enctype = "multipart/form-data" >
< input type = "file" name = "import" />
< input type = "hidden" name = "table" value = "tablename" />
< input type = "submit" value = "导入" />
</ form >
</ body >
</ html >
|
最后下载:demo下载
以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持服务器之家。
原文链接:http://www.thinkphp.cn/topic/14005.html