php导出电子表,php电子表格 导入/导出excel实例

【实例简介】  本地访问地址：http://localhost/demo32/index.php/Home/Excel/index

【实例截图】

【核心代码】

namespace Home\Controller;

use Think\Controller;

class ExcelController extends Controller {

//列表页面

public function index(){

$map['status']=1;

if(!empty($_POST['nickname'])){

$map['nickname'] = array('like',"%$_POST[nickname]%");

$Page->parameter = "nickname=".$_POST['nickname'].'&';

$this->assign('nickname',$_POST['nickname']);

}

if(!empty($_POST['email'])){

$map['email'] = array('like',"%$_POST[email]%");

$Page->parameter .= "email=".$_POST['email'];

$this->assign('email',$_POST['email']);

}

$User=M("user"); // 实例化User对象// 进行分页数据查询 注意page方法的参数的前面部分是当前的页数使用 $_GET[p]获取

$user_list = $User->where($map)->page($_GET['p'].',3')->select();

dump($User->getLastSql());

$this->assign('user_list',$user_list);// 赋值数据集

$count = $User->where($map)->count();// 查询满足要求的总记录数

$Page = new \Think\Page($count,3);// 实例化分页类 传入总记录数和每页显示的记录数

// 设置分页显示

$Page->setConfig('header', '

共 %TOTAL_ROW%条记录 第 %NOW_PAGE%页/共 %TOTAL_PAGE%页');

$Page->setConfig('prev', '上一页');

$Page->setConfig('next', '下一页');

$Page->setConfig('last', '末页');

$Page->setConfig('first', '首页');

$Page->setConfig('theme', '%FIRST%%UP_PAGE%%LINK_PAGE%%DOWN_PAGE%%END%%HEADER%');

$show = $Page->show();// 分页显示输出

$this->assign('page',$show);// 赋值分页输出

$this->display(); // 输出模板

}

//导入弹窗

public function import(){

$this->display();

}

//导入执行方法

public function import_do(){

上传文件

// $upload = new \Think\Upload();// 实例化上传类

// $upload->maxSize = 3145728 ;// 设置附件上传大小

// $upload->exts = array('jpg', 'gif', 'png', 'jpeg');// 设置附件上传类型

// $upload->savePath = 'Uploads/'; // 设置附件上传目录 // 上传文件

// $info = $upload->uploadOne($_FILES['file']);

// if(!$info) {// 上传错误提示错误信息

// $this->error($upload->getError());

// }else{// 上传成功

// $this->success('上传成功！');

// }

//

//验证文件格式

$file_array = explode ( ".", $_FILES['file']['name']);

$file_extension = strtolower ( array_pop ( $file_array ) );

if($file_extension!='xls'&&$file_extension!='xlsx'){

$json['status']=0;

$json['mess']='只可以上传电子表格，请重新上传文件';

$this->ajaxReturn($json);

}

//读取文件数据

vendor("PHPExcel.PHPExcel.IOFactory");

$objReader=\PHPExcel_IOFactory::createReader('Excel5');

$PHPReader=\PHPExcel_IOFactory::load($_FILES['file']['tmp_name']);

if( empty($PHPReader) ){

$this->_json_echo( 0, 'read error!' );

}

$currentSheet = $PHPReader->getSheet(0);

$allRow = $currentSheet->getHighestRow();

//最大条数限制

if( $allRow > 100 ){

$this->ajaxReturn(array(),'一次最多录入100条',0);

}

$user=M('user');

/**从第二行开始输出，因为excel表中第一行为列名*/

for($currentRow = 2;$currentRow <= $allRow;$currentRow ){

$data[account] = $currentSheet->getCellByColumnAndRow(0,$currentRow)->getValue();

$data[nickname] = $currentSheet->getCellByColumnAndRow(1,$currentRow)->getValue();

$data[email] = $currentSheet->getCellByColumnAndRow(2,$currentRow)->getValue();

$data[remark] = $currentSheet->getCellByColumnAndRow(3,$currentRow)->getValue();

$data[status]=1;

$add_res=$user->add($data);

}

if($add_res){

$this->success('导入成功');

}else{

$this->error('导入失败');

}

}

//电子表格导出

public function export_do(){

$map['status']=1;

if(!empty($_POST['nickname'])){

$map['nickname'] = array('like',"%$_POST[nickname]%");

}

if(!empty($_POST['email'])){

$map['email'] = array('like',"%$_POST[email]%");

}

$User=M("user");

$user_list = $User->where($map)->select();

foreach ($user_list as &$user){

$user['create_time']=date('Y-m-d H:i:s',$user['create_time']);

}

$xlsCell = array('account'=>'账户','nickname'=>'昵称','email'=>'邮箱','create_time'=>'注册时间'

// array('account','账户'),

// array('nickname','昵称'),

// array('email','邮箱'),

// array('create_time','注册时间')

);

//exportExcel('user', $xlsCell, $user_list);

daochuExcel('user', $xlsCell, $user_list);

}

public function verify(){

$Verify = new \Think\Verify();

$Verify->fontSize = 14;//验证码字体大小(像素) 默认为25

$Verify->length = 4;//验证码位数

$Verify->useNoise = false;//是否添加杂点 默认为true

$Verify->codeSet = '0123456789';// 设置验证码字符为纯数字

// $Verify->fontttf = '5.ttf';

// $Verify->useZh = true;//中文验证码 如果无法正常显示，请确认你的 ThinkPHP/Library/Think/Verify/zhttfs/ 目录下面存在中文字体文件。

$Verify->entry();

}

public function check_vcode(){

$code=I("get.code");

$this->ajaxReturn(check_verify($code,''));

}

}