//js文件 放置js文件表格绑定事件上方
function Removedu(data){
var ids = [];
$.each(data, function (i, j) {
if(ids.indexOf(j.id)===-1){
ids.push(j.id);
}
});
return ids;
}
$(document).on("click", ".btn-export", function () {
var ids = Table.api.selectedids(table);
var page = Removedu(table.bootstrapTable('getData'));
var all = table.bootstrapTable('getOptions').totalRows;
// console.log(ids, page, all);
Layer.confirm("请选择导出的选项!<form action='" + Fast.api.fixurl("user/user/export") + "' method='get' target='_blank'><input type='hidden' name='ids' value='' /><input type='hidden' name='filter' ><input type='hidden' name='op'><input type='hidden' name='search'><input type='hidden' name='columns'></form>", {
title: '导出数据',
btn: ["选中项(" + ids.length + "条)", "本页(" + page.length + "条)", "全部(" + all + "条)"],
success: function (layero, index) {
$(".layui-layer-btn a", layero).addClass("layui-layer-btn0");
}, yes: function (index, layero) {
submitForm(ids.join(","), layero);
return false;
},
btn2: function (index, layero) {
submitForm(page.join(","), layero);
layer.close(index);
return false;
},
btn3: function (index, layero) {
submitForm("all", layero);
return false;
}
})
});
var submitForm = function (ids, layero) {
var options = table.bootstrapTable('getOptions');
console.log(options);
var columns = [];
$.each(options.columns[0], function (i, j) {
if (j.field && !j.checkbox && j.visible && j.field != 'operate') {
columns.push(j.field);
}
});
var search = options.queryParams({});
$("input[name=search]", layero).val(options.searchText);
$("input[name=ids]", layero).val(ids);
$("input[name=filter]", layero).val(search.filter);
$("input[name=op]", layero).val(search.op);
$("input[name=columns]", layero).val(columns.join(','));
$("form", layero).submit();
};
// 为表格绑定事件
Table.api.bindevent(table);
//index.html 页面添加导出按钮
<a href="javascript:;" class="btn btn-success btn-export {:$auth->check('user/user/export')?'':'hide'}" title="{:__('Export')}" id="btn-export-file"><i class="fa fa-download"></i> {:__('Export')}</a>
//控制器
namespace app\admin\controller\user;
use app\common\controller\Backend;
use app\common\library\Auth;
use PhpOffice\PhpSpreadsheet\Shared\Drawing;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use think\Db;
use PHPExcel_IOFactory;
use PHPExcel;
public function export(){
if ($this->request->isGet()) {
$ids = $this->request->get('ids');
$columns = $this->request->get('columns');
//设置过滤方法
$this->request->filter(['strip_tags']);
//如果发送的来源是Selectpage，则转发到Selectpage
if ($this->request->request('keyField')) {
return $this->selectpage();
}
list($where, $sort, $order, $offset, $limit) = $this->buildparams(null);
if($ids != 'all'){
$wheres = "id in (".$ids.")";
}
$list = $this->model
->with('cars')
->where($where)
->where($wheres)
->order($sort, $order)
->limit($offset, $limit)
->field('id,username,mobile,area,address,carnumber,idcard,licence,idcard_start_time,idcard_end_time,driving_no,driving_start_time,driving_end_time,certificate_no,certificate_start_time,certificate_end_time,bankaddress,bankcard,zhihang,relevance_ltd,relevance_mobile,carry_allow_no,carry_allow_start_time,carry_allow_end_time,carry_no,carry_start_time,carry_end_time,travel_no,travel_start_time,travel_end_time')
->paginate($limit);
$list = $list->toArray();
//根据字段列出表头
$columns = 'ID,姓名,手机号,地址,详细地址,车牌号,车辆类型,车身颜色,核载质量,满载质量,身份证号,身份证有效期起,身份证有效期止,驾驶证号,驾驶证有效期起,驾驶证有效期止,从业证书号,从业证有效期起,从业证有效期止,开户行,银行卡号,开户行支行,挂靠企业名称,托运方电话,运输许可证号,许可证有效期起,许可证有效期止,运输证号,运输证有效期起,运输证有效期止,行驶证号,行驶证有效期起,行驶证有效期止,资料';
$columnsarr = $this->alphabet(explode(',',$columns));
$spreadsheet = new Spreadsheet();
$worksheet = $spreadsheet->getActiveSheet();
//设置标题名称
$worksheet->setTitle('入职列表');
$starkey = key($columnsarr);
end($columnsarr);
$endkey = key($columnsarr);
$worksheet->getStyle($starkey.':'.$endkey)->getAlignment()->setHorizontal('center');
foreach ($columnsarr as $key => $value) {
$worksheet->setCellValue($key.'1', __($value));
$worksheet->getColumnDimension($key)->setWidth(30);//设置列宽
}
$j = 2;
foreach ($list['data'] as $k => &$v){
//查询类型
$type_name = "无";
$color = "未知";
$norm = "0";
$full = "0";
if(!empty($v['cars'])){
$color = $v['cars']['color'];
$norm = $v['cars']['norm'];
$full = $v['cars']['full'];
if(!empty($v['cars']['cartype_id'])){
$type = model('cartype')->where(['id'=>$v['cars']['cartype_id']])->find();
$type_name = $type['type_name'];
}
}
$worksheet->getRowDimension($j)->setRowHeight(100);
$num1 = 10;
if(!empty($v['licence'])){
$pic_array = explode(',',$v['licence']);
foreach ($pic_array as $ki => $vi){
// 获取本地文件夹路径
$str = explode('uploads',$vi);
$str2 = explode('/',$str[1]);
$dir = ROOT_PATH . 'public/uploads/' .$str2[0];
$image = ROOT_PATH . 'public/uploads' .$str[1];
// 引入操作图片类
$drawings[$j] = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing();//此处需下载PHPExcel扩展，并放置于 根目录\vendor\phpoffice\ 目录下 （PHPExcel文件在博客我的博文，文件分类中已上传）
$drawings[$j]->setResizeProportional(false); // TODO 此处顺序不可调，因为导出默认是按原图像缩放的，设置成false才可以设置成可控制的宽度，要注意哦！
$drawings[$j]->setName('图片');
$drawings[$j]->setDescription('图片');
$drawings[$j]->setPath($image);
$drawings[$j]->setWidth(60);
$drawings[$j]->setHeight(60);
$drawings[$j]->setOffsetX($num1);
$drawings[$j]->setOffsetY(10);
$drawings[$j]->setCoordinates('AH' . $j);
$drawings[$j]->setWorksheet($worksheet);
$num1 = $num1 + 70; // 增加每张图之间的间距
}
}else{
$worksheet->setCellValue('AH' . $j, '');
}
//表格内容
$worksheet->setCellValue('A' . $j, $v['id']);
$worksheet->setCellValue('B' . $j, $v['username']);
$worksheet->setCellValue('C' . $j, $v['mobile']);
$worksheet->setCellValue('D' . $j, $v['area']);
$worksheet->setCellValue('E' . $j, $v['address']);
$worksheet->setCellValue('F' . $j, $v['carnumber']);
$worksheet->setCellValue('G' . $j, $type_name);
$worksheet->setCellValue('H' . $j, $color);
$worksheet->setCellValue('I' . $j, $norm);
$worksheet->setCellValue('J' . $j, $full);
$worksheet->setCellValue('K' . $j, $v['idcard']);
$worksheet->setCellValue('L' . $j, $v['idcard_start_time']);
$worksheet->setCellValue('M' . $j, $v['idcard_end_time']);
$worksheet->setCellValue('N' . $j, $v['driving_no']);
$worksheet->setCellValue('O' . $j, $v['driving_start_time']);
$worksheet->setCellValue('P' . $j, $v['driving_end_time']);
$worksheet->setCellValue('Q' . $j, $v['certificate_no']);
$worksheet->setCellValue('R' . $j, $v['certificate_start_time']);
$worksheet->setCellValue('S' . $j, $v['certificate_end_time']);
$worksheet->setCellValue('T' . $j, $v['bankaddress']);
$worksheet->setCellValue('U' . $j, $v['bankcard']);
$worksheet->setCellValue('V' . $j, $v['zhihang']);
$worksheet->setCellValue('W' . $j, $v['relevance_ltd']);
$worksheet->setCellValue('X' . $j, $v['relevance_mobile']);
$worksheet->setCellValue('Y' . $j, $v['carry_allow_no']);
$worksheet->setCellValue('Z' . $j, $v['carry_allow_start_time']);
$worksheet->setCellValue('AA' . $j, $v['carry_allow_end_time']);
$worksheet->setCellValue('AB' . $j, $v['carry_no']);
$worksheet->setCellValue('AC' . $j, $v['carry_start_time']);
$worksheet->setCellValue('AD' . $j, $v['carry_end_time']);
$worksheet->setCellValue('AE' . $j, $v['travel_no']);
$worksheet->setCellValue('AF' . $j, $v['travel_start_time']);
$worksheet->setCellValue('AG' . $j, $v['travel_end_time']);
$j++;
}
$total = count($list);
$file_name = '用户信息列表' .date('YmdHis').$total.'.xlsx';
// 客户端文件下载
header('Content-Type:application/vnd.ms-excel');
header('Content-Disposition:attachment;filename='.$file_name);
header('Cache-Control:max-age=0');
$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
ob_end_clean();
$writer->save('php://output');
exit;
}
}
