PHPExcel导出功能详解
从github下载PHPExcel：
将下载好的PHPExcel⽂件夹跟PHPExcel放置在框架中的Vendor⽬录下，注意⽬录的结构，按照我的代码来基本上直接引⽤就能使⽤，附
上结构，如下图：
1、如果使⽤Ajax请求：
Html代码部分：
<form class="layui-form " method="post">
<div class="layui-input-inline w150">
<select name="status" id="sel">
<option value="">选择状态</option>
<option value="0" {if condition="$param['status'] eq '0'"}selected {/if}>未审核</option>
<option value="1" {if condition="$param['status'] eq '1'"}selected {/if}>已审核</option>
</select>
</div>
<div class="layui-input-inline w150">
<select name="group" id="group">
<option value="">选择会员组</option>
{volist name="group_list" id="vo"}
<option value="{$vo.group_id}" {if condition="$param['group'] eq $vo.group_id"}selected {/if}>{$vo.group_name}</option>
{/volist}
</select>
</div>
<div class="layui-input-inline w320">
<input size="16" type="text" value="{$start_time}" id="datetimeStart" readonly class="form_datetime" name="start_time" placeholder="请选择开始时间" style= -
<input size="16" type="text" value="{$end_time}" id="datetimeEnd" readonly class="form_datetime"
name="end_time" placeholder="请选择结束时间" layui-input-inline">
<input type="text" autocomplete="off" placeholder="请输⼊搜索条件" class="layui-input" name="wd" value="{$param['wd']}">
</div>
<button class="layui-btn mgl-20 j-search" >查询</button>
<button class="layui-btn mgl-20" type="button" id="btnExport" onclick="excel()">导出</button>
</form>
Js部分代码：
//Excel导出事件
function excel()
{
var status = $("#sel option:selected").val();
var group = $("#group option:selected").val();
var start_time = $('#datetimeStart').val();
var end_time = $('#datetimeEnd').val();
$.ajax({
url: "{:url('admin/user/export')}",
data: {status:status,group:group,start_time:start_time,end_time:end_time}, type: 'post',
dataType: 'json',
success: function (ret) {
var $a = $("<a>");
$a.attr("href",ret.file);
$("body").append($a);
$a.attr("download","file.xls");
$a[0].click();
$a.remove();
if (de == -1) {
alert(ret.msg);
} else if (de == 1) {
window.location.href = "{:url('admin/user/data')}";
}
},
error: function () {
alert('请求失败');
}
})
}
封装Excel导出(我封装到common.php)，代码如下：
function exportExcel($title=array(), $data=array(), $fileName='',$sheet='', $savePath='./', $isDown=false)
{
// Loader::import('PHPExcel.PHPExcel');
// Loader::import('PHPExcel.PHPExcel.IOFactory');
vendor("PHPExcel.PHPExcel");
vendor("PHPExcel.PHPExcel.IOFactory");
$obj = 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', 'A
$obj->getActiveSheet(0)->setTitle($sheet); //设置sheet名称
$_row = 1; //设置纵向单元格标识
if($title){
$_cnt = count($title);
// $obj->getActiveSheet(0)->mergeCells('A'.$_row.':'.$cellName[$_cnt-1].$_row); //合并单元格
// $obj->setActiveSheetIndex(0)->setCellValue('A'.$_row, '数据导出：'.date('Y-m-d H:i:s')); //设置合并后的单元格内容
// $_row++;
$i = 0;
foreach($title AS $v){ //设置列标题
$obj->setActiveSheetIndex(0)->setCellValue($cellName[$i].$_row, $v);
$i++;
}
$_row++;
}
//填写数据
if($data){
$i = 0;
foreach($data AS $_v){
$j = 0;
foreach($_v AS $_cell){
$obj->getActiveSheet(0)->setCellValue($cellName[$j] . ($i+$_row), $_cell);
$j++;
}
$i++;
}
}
//⽂件名处理
getsavefilenameif(!$fileName){
$fileName = uniqid(time(),true);
}
header('Content-Type: application/vnd.ms-excel');
header("Content-Disposition:attachment;filename=$fileName.xls");
$objWrite = \PHPExcel_IOFactory::createWriter($obj, 'Excel2007');
ob_start();
$objWrite->save('php://output');
$xlsData = ob_get_contents();
ob_end_clean();
$response = array(
'code' => '1',
'file' => "data:application/vnd.ms-excel;base64,".base64_encode($xlsData),
'msg'=>'导出成功'
);
return json_encode($response);
}
PHP代码部分：
* Excel导出
* @throws \PHPExcel_Exception
* @throws \PHPExcel_Reader_Exception
* @throws \PHPExcel_Writer_Exception
*/
public function export()
{
if(Request()->isAjax()){
$param = input('post.');
$res = $this->call($param);
// Excel导出部分
$title = ['⽤户名称','状态','积分','上次登录时间','上次登录IP','登录次数'];
$data = [];
foreach($res['list'] as $k=>$v){
$arr = [
'user_name' => $v['user_name'],
'user_status' => $v['user_status'],
'user_points' => $v['user_points'],
'user_last_login_time' => $v['user_last_login_time'],
'user_last_login_ip' => $v['user_last_login_ip'],
'user_login_num' => $v['user_login_num']
];
$data[] = $arr;
}
$result = exportExcel($title,$data,'⽤户名单'.date('Y-m-d'),'⽤户名单','./',true); die($result);
}else{
die(json_encode(array('code'=>-1,'msg'=>'导出失败，请刷新重试')));
}
}
* data()、export()⽅法公共条件
* @param $param
*/
public function call($param)
{
if(!empty($param['start_time']) && !empty($param['end_time'])){
if(strtotime($param['start_time']) > strtotime($param['end_time'])){
return $this->error('开始时间必须⼩于结束时间');
}
}
$param['page'] = intval($param['page']) <1 ? 1 : $param['page'];
$param['limit'] = intval($param['limit']) <1 ? $this->_pagesize : $param['limit'];
$where=[];
if(in_array($param['status'],['0','1'],true)){
$where['user_status'] = $param['status'];
}
if(!empty($param['group'])){
$where['group_id'] = $param['group'];
}
if(!empty($param['wd'])){
$param['wd'] = htmlspecialchars(urldecode($param['wd']));
$where['user_name'] = ['like','%'.$param['wd'].'%'];
}
//时间判断条件
if(!empty($param['start_time'])){
$where['user_last_login_time'] = ['EGT',strtotime($param['start_time'])];
}
if(!empty($param['end_time'])){
$where['user_last_login_time'] = ['ELT',strtotime($param['end_time'])];
}
if(!empty($param['start_time']) && !empty($param['end_time'])){
$where['user_last_login_time'] = [
['EGT', strtotime($param['start_time'])],
['ELT', strtotime($param['end_time'])],
];
}
$order='user_id desc';
$res = model('User')->listData($where,$order,$param['page'],$param['limit']);
return $res;
}
2、如果不使⽤Ajax请求：
封装Excel导出(我封装到common.php)，代码如下：
/**
* 数据导出
* @param array $title 标题⾏名称
* @param array $data 导出数据
* @param string $fileName ⽂件名
* @param string $savePath 保存路径
* @param $type 是否下载 false--保存 true--下载
* @return string 返回⽂件全路径
*/
function exportExcel($title=array(), $data=array(), $fileName='',$sheet='', $savePath='./', $isDown=false){ // Loader::import('PHPExcel.PHPExcel');
// Loader::import('PHPExcel.PHPExcel.IOFactory');
vendor("PHPExcel.PHPExcel");
vendor("PHPExcel.PHPExcel.IOFactory");
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论