说明
最近处理一个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 = hrefa.download = fullNamedocument.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)@ResponseBodypublic 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")@ResponseBodypublic 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"));}//生成excelprivate 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("取消下载")}});}});});
}