首先是在HTML页面中加上导出按钮:
<div class="ibox-body">
<div id="exampleToolbar" role="group">
<button type="button" class="btn btn-info" onclick="exportExel()">
<i class="fa fa-download" aria-hidden="true"></i>导出excel
</button>
</div>
<table id="analysisTable" data-mobile-responsive="true">
</table>
</div>
然后在对应的js文件中加上导出:
function exportExel() {
window.location.href= prefix+"/export";
}
这就到了导出层了,需要我们去controller层去实现接口
@Log("导出")
@GetMapping("/export")
void export(@RequestParam Map<String, Object> params,HttpServletResponse response) {
Query query = new Query(params);
List<Analysis> analysisList = analysisService.list(query);
//导出操作
FileUtil.exportExcel(analysisList,"系统数据分析","系统数据",Analysis.class,"定级系统.xls",response);
}
这里的系统数据分析,系统数据,定级分析,分别对应的是excel文件中不同的sheet。
List<Analysis> list(Map<String, Object> map);
然后是service的实现类:
@Override
public List<Analysis> list(Map<String, Object> map) {
return analysisDao.list(map);
}
后边是dao层
List<Analysis> list(Map<String, Object> map);
最后是xml层
<!--list-->
<select id="list" resultType="com.bootdo.nsmp.domain.Analysis">
select
info.`id`,
info.`dept_namee`,
dpt.`principal_name`,
dpt.`unit_type`,
info.`sys_name`,
info.`dept_name`,
info.`begin_use_time`,
gd.`pro_grade`,
gd.`pro_time`,
gd.`jug_res`,
gd.`gov_res`,
sd.name as dptName,
pro.`name` as provinceName,
c.`name` as cityName,
a.`name` as areaName
from depart as dpt
left join info_sys as info on dpt.dept_namee = info.dept_namee
left join grade as gd on gd.info_sys_id = info.id
left join sys_dept as sd on dpt.dept_namee = sd.dept_id
left join province pro on dpt.province = pro.code_p
left join city c on dpt.city = c.code_c
left join area a on dpt.area = a.code_a
<if test="offset != null and limit != null">
limit ${offset}, ${limit}
</if>
</select>
然后是层层将结果返回,最后加载在表中。
总之,导出excel文件的原理就是首先拿着id去调用后端,去库里查找,然后将查找到的结果放在文件中,然后下载下来。
是不是还引用了一个插件,我不知道。有这么个文件可以用来参考。
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title></title>
</head>
<body>
<div class="tools">
<button type="button" class="btn green" id="excell" onclick="method5('dataTable')">导出考勤表格</button>
</div>
<table border="1" id="dataTable">
<tr>
<td>王婷111</td>
<td>一见倾城333 </td>
</tr>
<tr>
<td>祈澈姑娘222</td>
<td>Python开发者交流平台44</td>
</tr>
<tr>
<td>wwwangting888</td>
<td>13661725475</td>
</tr>
</table>
</body>
<script>
//打印表格
var idTmr;
function getExplorer() {
var explorer = window.navigator.userAgent;
//ie
if(explorer.indexOf("MSIE") >= 0) {
return 'ie';
}
//firefox
else if(explorer.indexOf("Firefox") >= 0) {
return 'Firefox';
}
//Chrome
else if(explorer.indexOf("Chrome") >= 0) {
return 'Chrome';
}
//Opera
else if(explorer.indexOf("Opera") >= 0) {
return 'Opera';
}
//Safari
else if(explorer.indexOf("Safari") >= 0) {
return 'Safari';
}
}
function method5(tableid) {
if(getExplorer() == 'ie') {
var curTbl = document.getElementById(tableid);
var oXL = new ActiveXObject("Excel.Application");
var oWB = oXL.Workbooks.Add();
var xlsheet = oWB.Worksheets(1);
var sel = document.body.createTextRange();
sel.moveToElementText(curTbl);
sel.select();
sel.execCommand("Copy");
xlsheet.Paste();
oXL.Visible = true;
try {
var fname = oXL.Application.GetSaveAsFilename("Excel.xls",
"Excel Spreadsheets (*.xls), *.xls");
} catch(e) {
print("Nested catch caught " + e);
} finally {
oWB.SaveAs(fname);
oWB.Close(savechanges = false);
oXL.Quit();
oXL = null;
idTmr = window.setInterval("Cleanup();", 1);
}
} else {
tableToExcel(tableid)
}
}
function Cleanup() {
window.clearInterval(idTmr);
CollectGarbage();
}
var tableToExcel = (function() {
var uri = 'data:application/vnd.ms-excel;base64,',
template = '<html><head><meta charset="UTF-8"></head><body><table border="1">{table}</table></body></html>',
base64 = function(
s) {
return window.btoa(unescape(encodeURIComponent(s)))
},
format = function(s, c) {
return s.replace(/{(\w+)}/g, function(m, p) {
return c[p];
})
}
return function(table, name) {
if(!table.nodeType)
table = document.getElementById(table)
var ctx = {
worksheet: name || 'Worksheet',
table: table.innerHTML
}
window.location.href = uri + base64(format(template, ctx))
}
})()
</script>
</html>
是的,我去验证了,这个插件文件确实使用到了。
//打印表格
var idTmr;
function getExplorer() {
var explorer = window.navigator.userAgent;
//ie
if(explorer.indexOf("MSIE") >= 0) {
return 'ie';
}
//firefox
else if(explorer.indexOf("Firefox") >= 0) {
return 'Firefox';
}
//Chrome
else if(explorer.indexOf("Chrome") >= 0) {
return 'Chrome';
}
//Opera
else if(explorer.indexOf("Opera") >= 0) {
return 'Opera';
}
//Safari
else if(explorer.indexOf("Safari") >= 0) {
return 'Safari';
}
}
function exportFile(tableid) {debugger
if(getExplorer() == 'ie') {
var curTbl = document.getElementById(tableid);
var oXL = new ActiveXObject("Excel.Application");
var oWB = oXL.Workbooks.Add();
var xlsheet = oWB.Worksheets(1);
var sel = document.body.createTextRange();
sel.moveToElementText(curTbl);
sel.select();
sel.execCommand("Copy");
xlsheet.Paste();
oXL.Visible = true;
try {
var fname = oXL.Application.GetSaveAsFilename("Excel.xls",
"Excel Spreadsheets (*.xls), *.xls");
} catch(e) {
print("Nested catch caught " + e);
} finally {
oWB.SaveAs(fname);
oWB.Close(savechanges = false);
oXL.Quit();
oXL = null;
idTmr = window.setInterval("Cleanup();", 1);
}
} else {
tableToExcel(tableid)
}
}
function Cleanup() {
window.clearInterval(idTmr);
CollectGarbage();
}
var tableToExcel = (function() {
var uri = 'data:application/vnd.ms-excel;base64,',
template = '<html><head><meta charset="UTF-8"></head><body><table border="1">{table}</table></body></html>',
base64 = function(
s) {
return window.btoa(unescape(encodeURIComponent(s)))
},
format = function(s, c) {
return s.replace(/{(\w+)}/g, function(m, p) {
return c[p];
})
}
return function(table, name) {
if(!table.nodeType)
table = document.getElementById(table)
var ctx = {
worksheet: name || 'Worksheet',
table: table.innerHTML
}
window.location.href = uri + base64(format(template, ctx))
}
})()