本文档记录了一个前端使用 layui 框架实现 Excel 下载功能的过程，包括 thymeleaf 模板映射、AOP 日志处理中的错误及解决方法。在实现过程中，作者发现 layui 官网即将下线，并分享了如何处理前端 AJAX 不支持流接收的问题，使用 fetch 和 XMLHttpRequest 实现。同时，提供了后端接口处理 Excel 下载的代码示例。
说明
最近处理一个excel下载的功能，对于前端这块很长时间没碰了，于是花了时间做了个小练习，前端采用的layui框架。却发现一个让人遗憾的事情， layui官网告知要下线了，致敬贤心大佬，感谢大佬提供的框架一路的的陪伴。
途中遇到的问题，特意记录一下，涨涨记性。
- thymeleaf模板的映射，路径匹配在templates模块下，且无法直接访问该模块下的资源文件，一般通过直接请求controller->templates/xx.html
- 以前做过一个aop日志处理，ProceedingJoinPoint 环绕通知调用忘记返回proceed = proceedingJoinPoint.proceed();这会导致mvc层返回不了数据。
- 前端处理ajax不支持流接收，使用原生fetch和XMLHttpRequest可以处理
前端页面
<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.thymeleaf.org">
<head>
<meta charset="UTF-8">
<title>Title</title>
<script src="/jquery-3.4.1.js" th:href="@{/jquery-3.4.1.js}"></script>
<script src="/layui/layui.js" th:href="@{/layui/layui.js}"></script>
<link href="/layui/css/layui.css" th:href="@{/layui/css/layui.css}" rel="stylesheet">
</head>
<body>
<div class="layui-fluid">
<div class="layui-row">
<ul class="layui-nav layui-bg-red">
<li class="layui-nav-item "><a href="">标题</a></li>
</ul>
</div>
<div class="layui-row ">
<form class="layui-form" action="" lay-filter="example" method="">
<div class="layui-container ">
<div class="layui-row">
<div class="layui-col-xs6 layui-col-sm6 layui-col-md6">
<div class="demoTable">
<label class="layui-form-label">搜索ID：</label>
<div class="layui-inline">
<input id="id" class="layui-input" name="id" autocomplete="off">
</div>
</div>
</div>
<div class="layui-col-xs6 layui-col-sm6 layui-col-md6">
<div class="layui-form">
<div class="layui-form-item">
<div class="layui-inline">
<label class="layui-form-label">日期范围:</label>
<div class="layui-inline" id="test1">
<div class="layui-input-inline">
<input id="date" name="date" type="text" id="test-startDate-1" class="layui-input"
placeholder="开始-结束">
</div>
</div>
</div>
</div>
</div>
</div>
</div>
<div class="layui-row">
<div class="demoTable">
<label class="layui-form-label">类型：</label>
<div class="layui-inline">
<input id="username" class="layui-input" name="username" autocomplete="off">
</div>
</div>
</div>
</div>
<div class="layui-row">
<div class="layui-btn-group">
<button id="selectTable" class="layui-btn" type="button" data-type="reload">搜索</button>
<button id="downexcel" type="button" class="layui-btn">下载</button>
<!-- <button type="button" class="layui-btn ">编辑</button>-->
<!-- <button type="button" class="layui-btn">删除</button>-->
</div>
</div>
<div class="layui-row">
</div>
</form>
</div>
<div class="layui-row">
<div class="layui-tab layui-tab-card">
<ul class="layui-tab-title layui-bg-orange">
<li class="layui-this">网站设置</li>
<li>用户管理</li>
<li>权限分配</li>
<li>商品管理</li>
<li>订单管理</li>
</ul>
<div class="layui-tab-content" style="height: 100%">
<div class="layui-tab-item layui-show" >
<table class="layui-hide" id="LAY_table_user" lay-filter="LAY_table_user"></table>
</div>
<div class="layui-tab-item">2</div>
<div class="layui-tab-item">3</div>
<div class="layui-tab-item">4</div>
<div class="layui-tab-item">5</div>
<div class="layui-tab-item">6</div>
</div>
</div>
</div>
</div>
<script type="text/html" id="toolbarDemo">
<!-- <div class="layui-btn-container">-->
<!-- <button class="layui-btn layui-btn-sm" lay-event="getCheckData">获取选中行数据</button>-->
<!-- <button class="layui-btn layui-btn-sm" lay-event="getCheckLength">获取选中数目</button>-->
<!-- <button class="layui-btn layui-btn-sm" lay-event="isAll">验证是否全选</button>-->
<!-- </div>-->
</script>
<script>
layui.use(['table', 'laydate', 'form', 'element'], function () {
var element = layui.element;
var table = layui.table;
var laydate = layui.laydate;
var form = layui.form;
var $ = layui.$;
var data = form.val('example');
var args=$(".layui-form").serialize();
var tabledata;
var tabletitle=[
{field: 'id', title: 'ID', width: 80, sort: true, fixed: true}
, {field: 'username', title: '用户名', width: 80}
, {field: 'sex', title: '性别', width: 80, sort: true}
, {field: 'city', title: '城市', width: 80}
, {field: 'sign', title: '签名', width: 80}
, {field: 'experience', title: '积分', sort: true, width: 80}
, {field: 'score', title: '评分', sort: true, width: 80}
, {field: 'classify', title: '职业', width: 80}
, {field: 'wealth', title: '财富', sort: true, width: 135}
// ,{checkbox: true, fixed: true}
];
form.on('submit(formDemo)', function(data){
layer.msg(JSON.stringify(data.field));
return false;
});
//日期范围
laydate.render({
elem: '#test-startDate-1'
, range: true
});
//方法级渲染
var ins1 =table.render({
elem: '#LAY_table_user'
, url: '/excel/showPeople/'
, method: "post"
,title: '用户数据表'
, cols: [tabletitle]
,toolbar: '#toolbarDemo' //开启头部工具栏，并为其绑定左侧模板
// ,defaultToolbar: ['filter', 'exports', 'print', { //自定义头部工具栏右侧图标。如无需自定义，去除该参数即可
// title: '提示'
// ,layEvent: 'LAYTABLE_TIPS'
// ,icon: 'layui-icon-tips'
// }]
, id: 'testReload'
, page: true
// ,height:
, where: {
id: data.id
, username: data.username
}
// , contentType: "application/json"
, contentType: "application/x-www-form-urlencoded"
, response: {
statusName: 'code' //规定数据状态的字段名称，默认：code
, statusCode: 200 //规定成功的状态码，默认：0
, msgName: 'message' //规定状态信息的字段名称，默认：msg
, countName: 'total' //规定数据总数的字段名称，默认：count
, dataName: 'data' //规定数据列表的字段名称，默认：data
}
, parseData: function (res) { //res 即为原始返回的数据
return {
"code": res.code, //解析接口状态
"message": res.message, //解析提示文本
"total": res.total, //解析数据长度
"data": res.data //解析数据列表
};
}
, done: function (res, curr, count) {
tabledata=res.data
//如果是异步请求数据方式，res即为你接口返回的信息。
//如果是直接赋值的方式，res即为：{data: [], count: 99} data为当前页数据、count为数据总长度
}
});
//头工具栏事件
table.on('toolbar(LAY_table_user)', function(obj){
var checkStatus = table.checkStatus(obj.config.id);
switch(obj.event){
case 'getCheckData':
var data = checkStatus.data;
layer.alert(JSON.stringify(data));
break;
case 'getCheckLength':
var data = checkStatus.data;
layer.msg('选中了：'+ data.length + ' 个');
break;
case 'isAll':
layer.msg(checkStatus.isAll ? '全选': '未全选');
break;
//自定义头工具栏右侧图标 - 提示
case 'LAYTABLE_TIPS':
layer.alert('这是工具栏右侧自定义的一个图标按钮');
break;
};
});
// active = {
// reload: function () {
// // console.log("data:"+$(".layui-form").serialize())
// data = form.val('example');
// //执行重载
// table.reload('testReload', {
// page: {
// curr: 1 //重新从第 1 页开始
// }
// , where: {
// id: data.id
// , username: data.username
// }
// });
// }
// };
// $('#selectTable').on('click', function () {
// var type = $(this).data('type');
// active[type] ? active[type].call(this) : '';
// });
function request() {
fetch('/excel/downexcel', {
method: 'POST',
headers: {
'Content-Type': 'application/json',
},
body: JSON.stringify({title:tabletitle,data:tabledata}),
})
.then(res => res.blob())
.then(data => {
if (data.size==0){
layui.use('layer', function () {
var layer = layui.layer;
layer.msg("下载失败！");
});
}else {
let blobUrl = window.URL.createObjectURL(data);
download(blobUrl);
}
});
}
function download(blobUrl) {
const a = document.createElement('a');
a.download = 'a.xls';
a.href = blobUrl;
a.click();
}
$('#downexcel').on('click', function () {
request();
});
function fileDownload(stream, name, suffix = '.xls') {
if (stream && name) {
const blob = new Blob([stream])
const fullName = `${name + suffix}`
// IE10+ 浏览器特殊处理
if (window.navigator.msSaveBlob) {
window.navigator.msSaveBlob(blob, fullName)
} else {
const href = window.URL.createObjectURL(blob)
let a = document.createElement('a')
a.href = href
a.download = fullName
document.body.appendChild(a)
a.click()
window.URL.revokeObjectURL(href)
document.body.removeChild(a)
}
}
}
});
</script>
</body>
</html>
后端
package com.it.bank.controller;
import com.alibaba.fastjson.JSON;
import com.it.bank.po.DemoVo;
import com.it.bank.util.CodeStatus;
import com.it.bank.util.Result;
import lombok.extern.slf4j.Slf4j;
import org.apache.http.HttpResponse;
import org.apache.logging.log4j.core.util.UuidUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.beans.BeanUtils;
import org.springframework.boot.configurationprocessor.json.JSONObject;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.context.request.RequestAttributes;
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;
import org.thymeleaf.expression.Lists;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.io.PrintWriter;
import java.lang.reflect.Field;
import java.util.*;
import java.util.concurrent.atomic.AtomicReference;
import java.util.function.Function;
import java.util.stream.Collectors;
@RequestMapping("excel")
@Controller
@CrossOrigin
@Slf4j
public class ExcelController {
/**
* 查询数据
* @param demovo
* @param page
* @param limit
* @return
*/
@RequestMapping(value = "showPeople",method = RequestMethod.POST)
@ResponseBody
public Result showPeple( DemoVo demovo, Integer page, Integer limit){
Result result=getData(demovo,page,limit);
return result;
}
@RequestMapping(value = "showPeople1")
public String showPeple1( DemoVo demovo, Integer page, Integer limit, Model model){
Result result=getData(demovo,page,limit);
model.addAttribute("page",result.getPage());
model.addAttribute("limit",limit);
model.addAttribute("total",result.getTotal());
model.addAttribute("data",result.getData());
return "testjsp.html";
}
//创建数据
private Result getData(DemoVo demovo, Integer page, Integer limit) {
String id="111";
if (Objects.nonNull(page)){
id=page+id;
}
if (Objects.isNull(limit)){
limit=10;
}
DemoVo demoVo = new DemoVo();
demoVo.setId(id);
demoVo.setSex("女");
demoVo.setUsername("王昭君");
demoVo.setCity("宁波");
demoVo.setWealth("10000");
demoVo.setScore("9.0");
demoVo.setSign(UuidUtil.getTimeBasedUuid().toString());
demoVo.setClassify("老板");
demoVo.setExperience("999");
ArrayList<Object> list = new ArrayList<>();
for (int i = 0; i < limit; i++) {
DemoVo demoVo1 = new DemoVo();
BeanUtils.copyProperties(demoVo,demoVo1);
demoVo1.setId((Integer.valueOf(demoVo.getId())+i)+"");
list.add(demoVo1);
}
Result result = new Result();
result.setCode(200);
result.setMessage("显示excel的数据");
result.setData(list);
result.setPage(page);
result.setTotal(30);
return result;
}
/**
* 下载excel
* @param demoVoList
*/
@RequestMapping("downexcel")
@ResponseBody
public void downexcel(@RequestBody Map<String,List<Map<String,String>>> demoVoList){
List<Map<String,String>> title = Optional.ofNullable(demoVoList.get("title")).filter(a->a.toString().contains("field")).orElse(null);
writeExcel(title, demoVoList.get("data"));
}
//生成excel
private void writeExcel(List<Map<String,String>>titleList , List<Map<String,String>> dataList) {
ServletRequestAttributes servletAttributes=(ServletRequestAttributes)RequestContextHolder.getRequestAttributes();
HttpServletResponse responseholder = servletAttributes.getResponse();
try (
OutputStream out = responseholder.getOutputStream();
){
Workbook workBook = new HSSFWorkbook();
Sheet sheet = workBook.createSheet("sheet"+0);
if (Objects.nonNull(titleList)&&Objects.nonNull(dataList)) {
Row[] rows=new Row[dataList.size()];
Row row = sheet.createRow(0);
for (int j = 0; j < titleList.size(); j++) {
Map<String, String> dataMap = titleList.get(j);
String name = dataMap.get("field");
String title = dataMap.get("title");
Cell first = row.createCell(j);
first.setCellValue(title);
for (int k = 0; k <dataList.size(); k++) {
if (Objects.isNull(rows[k])) {
rows[k] = sheet.createRow(k+1);
}
Cell second = rows[k].createCell(j);
second.setCellValue(dataList.get(k).get(name));
}
}
}
workBook.write(out);
} catch (Exception e) {
e.printStackTrace();
}
log.warn("下载失败！");
}
}
加个弹窗用于修改下载的文件名称,不过没必要,下载后浏览器会弹窗出文件路径可供修改
//弹窗
<div id="importProcLayer">
<div class="row"><br>
<div class="col-md-10 col-md-offset-1 input-group">
<span class="input-group-addon">下载文件名：</span>
<input type="text" class="form-control" id="excelname" name="excelname" >
</div>
</div>
</div>
//导出excel前端代码
function request() {
fetch('yebAssetsDetailDownExcel.htm', {
method: 'POST',
headers: {
"Content-Type": "application/x-www-form-urlencoded;charset=utf-8"
},
body: "data="+JSON.stringify({title:tabletitle,methed:excelWay}),
})
.then(res => {
var name=res.headers.get('content-disposition').split('filename=')[1];
let fileName = decodeURIComponent(name);
$("#excelname").val(fileName);
res.blob().then(data => {
if (data.size==0||data.type=='text/html'){
layer.msg("下载失败！");
}else {
layer.open({
type: 1,
area: ['520px', '300px'],
title: '下载',
content: $('#importProcLayer'),
zIndex:1900,
shade:0,
btn: ['确定','取消'],
btn1:function(){
console.log("开始下载："+JSON.stringify(data)+"_"+data.size+"_"+data.toString())
let blobUrl = window.URL.createObjectURL(data);
$("#importProcLayer").show();
var fileName=$("#excelname").val();
const link = document.createElement('a')
download(blobUrl,fileName);
},
btn2:function(){
console.log("取消下载")
}
});
}
});
});
}
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
