先贴代码
【JS部分】
index：function(){
//*************************** 自定义export开始
$(document).on("click", "#btn-export-all", function () {
//当前窗口
var index;
var options = table.bootstrapTable('getOptions');
var search = options.queryParams({});
//拼接参数
var export_url = "card/orders/export";
var post_url = export_url + '?' + Object.keys(search).map(i => `${i}=${search[i]||''}`).join('&')
//请求导出
layer.confirm('确定导出?',{btn: ['确定', '取消'],title:"提示"}, function(){
//设置等待5s
layer.load(0,{time:5000});
//关闭窗口
layer.close(layer.index);
//执行下载
$.ajax({
type: "post",
url: post_url,
data: null,
dataType: "json",
async:true,
success:function(data) {
if(data.code == 1){
// 创建a标签，设置属性，并触发点击下载
var $a = $("<a>");
$a.attr("href", data.file);
$a.attr("download",data.filename);
$("body").append($a);
$a[0].click();
$a.remove();
layer.msg('导出成功', {icon: 1});
}else{
layer.msg('导出失败', {icon: 2});
}
}
});
});
});
//*************************** 自定义export结束
}
【控制器部分】
/**
* 导出功能
*/
public function export()
{
$this->relationSearch = true;
if ($this->request->isPost()) {
set_time_limit(0);
ini_set("memory_limit", -1); //-1不限制内存
$xlsName =date('YmdHis').'订单数据';
//设置过滤方法
$this->request->filter(['strip_tags', 'trim']);
//如果发送的来源是Selectpage，则转发到Selectpage
if ($this->request->request('keyField')) {
return $this->selectpage();
}
list($where, $sort, $order, $offset, $limit) = $this->buildparams();
$total = $this->model
->with(['merchandise','lists','user'])
->where($where)
->order($sort, $order)
->count();
$list = $this->model
->with(['merchandise','lists','user'])
->where($where)
->order($sort, $order)
->limit($offset, $limit)
->select();
$order_status_lists=['-1'=>'已退货','1'=>'待发货','2'=>'发货中','3'=>'已发货'];
$pay_status_lists=['-1'=>'支付失败','1'=>'待支付','2'=>'已支付'];
$process_status_lists=['-1'=>'处理失败','1'=>'未处理','2'=>'待处理','3'=>'已处理'];
#数据重组
foreach ($list as $row) {
$row['store_name']=$row['lists']['name'];
$row['order_status_txt']=isset($order_status_lists[$row['order_status']])?$order_status_lists[$row['order_status']]:'';
$row['pay_status_txt']=isset($pay_status_lists[$row['pay_status']])?$pay_status_lists[$row['pay_status']]:'';
$row['process_status_txt']=isset($process_status_lists[$row['process_status']])?$process_status_lists[$row['process_status']]:'';
$row['create_time_txt']=!empty($row['create_time'])?date('Y-m-d H:i:s',$row['create_time']):'';
$row['pay_time_txt']=!empty($row['pay_time'])?date('Y-m-d H:i:s',$row['pay_time']):'';
$row['process_time_txt']=!empty($row['process_time'])?date('Y-m-d H:i:s',$row['process_time']):'';
$row['refund_time_txt']=!empty($row['refund_time'])?date('Y-m-d H:i:s',$row['refund_time']):'';
}
$xlsCell = [
['card_order_no', '订单编号'],
['title', '订单标题'],
['store_id', '网点Id'],
['user_id', '用户ID'],
['card_merchandise_id', '卡券商品ID'],
['store_name', '店铺名称'],
['mobile', '手机号'],
['card_type', '卡券类型'],
['coupons_no', '卡券编号'],
['original_price', '原价'],
['shop_price', '售价'],
['subsidy_price', '补贴价'],
['coupon_price', '优惠价格'],
['number', '数量'],
['pay_price', '支付金额'],
['pay_type', '支付类型'],
['pay_status_txt', '支付状态'],
['order_status_txt', '订单状态'],
['process_status_txt', '处理状态'],
['pay_time_txt', '支付日期'],
['process_time_txt', '处理日期'],
['refund_time_txt', '退款日期'],
['refund_reason', '退款原因'],
['refund_remark', '退款信息标注'],
['create_time_txt', '创建日期']
];
return $this->downloadExcel($xlsName,$xlsCell,$list);
}
}
■注意：不要生搬硬套！我的控制使用了联查
【导出的类】可放在controller里面
protected function downloadExcel($expTitle, $expCellName, $expTableData)
{
$xlsTitle = iconv('utf-8', 'gb2312', $expTitle);//文件名称
$fileName = $expTitle;
$cellNum = count($expCellName);// 单元格长度
$dataNum = count($expTableData);
$spreadsheet = new Spreadsheet();
$worksheet = $spreadsheet->getActiveSheet();
$worksheet->setTitle($expTitle);
$cellName = [
'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'
];
$spreadsheet->getActiveSheet(0)
->mergeCells('A1:' . $cellName[$cellNum - 1] . '1');//合并单元格为表头
$spreadsheet->setActiveSheetIndex(0)->setCellValue('A1', $expTitle);// 设置表头单元格
for ($i = 0; $i < $cellNum; $i++) {
$spreadsheet->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++) {
$item_index =$expTableData[$i][$expCellName[$j][0]];
if(is_numeric($item_index)&strlen($item_index)>9){
$spreadsheet->getActiveSheet(0)->setCellValue($cellName[$j] . ($i + 3),' '.$item_index);
}else{
$spreadsheet->getActiveSheet(0)->setCellValue($cellName[$j] . ($i + 3),$item_index);
}
}
}
$end_filename =$fileName.'.xls';
#ob_end_clean();
//这一步非常关键，用来清除缓冲区防止导出的excel乱码
header('pragma:public');
header('Content-type:application/vnd.ms-excel');
//header('Content-type:application/vnd.ms-excel;charset=utf-8;name="' . $xlsTitle . '.xls"');
header("Content-Disposition:attachment;filename=$fileName.xls");
header('Cache-Control: max-age=0');
//"xls"参考下一条备注
//$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$writer = new Xlsx($spreadsheet);
//"Excel2007"生成2007版本的xlsx，"Excel5"生成2003版本的xls 调用工厂类
$writer->save('php://output');
$xlsData = ob_get_contents();
ob_end_clean();
return json(['code'=>1,'filename'=>$end_filename,'file'=>'data:application/vnd.ms-excel;base64,'.base64_encode($xlsData)]);
}
【特别声明】
use PhpOffice\PhpSpreadsheet\Helper\Sample;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Worksheet\PageSetup;
use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\Color;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
【视图部分】
index.html
增加导出按钮
<a href="javascript:;" class="btn btn-warning" title="导出全部" id="btn-export-all"><i class="fa fa-send"></i> 全部导出</a>
