用阿里巴巴开源导出接口导出Excel
后端代码
boolean downlaod = "download".equals(vo.getDownload());
if (downlaod){// 下载
List<UserVo> list = userService.findDownload(search);//这是从数据库里面传过来的符合导出条件的结果集
DownloadExcelModel excel = new DownloadExcelModel(
"id", "昵称", "手机号", "性别", "生日", "状态", "总押金额度", "占用的额度",
"用户的可用现金金额", "添加日期" ,"最后修改日期", "公司名称",
"行业", "职业", "用户当前所用app的设备", "注册来源", "身份证号码", "真实姓名",
"用户实名认证ID", "实名认证结果", "企业统一社会信用代码", "企业法人认证ID", "法人认证结果",
"身份证正面照", "身份证背面照", "身份证到期时间", "手持身份证照片", "手持身份证照片(1)", "email", "订单数量"
);
if (list != null && list.size() > 0) {
String dateType = "yyyy-MM-dd HH:mm:ss";
for (UserVo item : list) {
excel.setSheetRow(
item.getId().toString(),
item.getNickname(),
item.getMobile(),
item.getSex() == null ? "" : (item.getSex() == 1 ? "男" : "女"),
DateUtils.format(item.getBirthday(), "yyyy-MM-dd"),
item.getStatus() == null ? "" : item.getStatus().toString(),
item.getAllCashPledge() == null ? "" : item.getAllCashPledge().toString(),
item.getUseCashPledge() == null ? "" : item.getUseCashPledge().toString(),
item.getCash() == null ? "" : item.getCash().toString(),
DateUtils.format(item.getCreateDate(), dateType),
DateUtils.format(item.getModifyDate(), dateType),
item.getCompanyName(),
item.getIndustry(),
item.getProfession(),
item.getAppDevice(),
item.getRegSource(),
item.getIdCardNum(),
item.getTrueName(),
item.getCertifyId(),
(item.getFacePassed() ? "是" : "否"),
item.getCompanyCode(),
item.getCertBizCode(),
(item.getCertPassed() ? "是" : "否"),
item.getIdCardFrontUrl(),
item.getIdCardBackUrl(),
item.getIdCardExpireDate(),
item.getHandCardFrontUrl(),
item.getHandCardFrontHash(),
item.getEmail(),
String.valueOf(item.getCountom())
);
}
}
//用户模块导出记录
try{
this.downloadExcel(excel,"导出用户"+ DateUtils.format(new Date(),"yyyyMMddHHmmss"));
operatorLogService.addLog(headerValue,1,2);
}
catch (Exception e){
operatorLogService.addLog(headerValue,2,2);
}
return null;
}
封装好的方法
protected void downloadExcel(Workbook workbook, String fileName){
HttpServletResponse response = ControllerContent.getResponse();
try (OutputStream out = response.getOutputStream()){
fileName = new String(fileName.getBytes(StandardCharsets.UTF_8),"ISO8859-1");
response.setContentType("application/msexcel");
response.setHeader("Content-disposition", "attachment; filename="+fileName+".xlsx");
workbook.write(out);
} catch (Exception e) {
logger.error("导出exel错误 fileName={}",fileName,e);
}
}
protected void downloadExcel(Workbook workbook, String fileName){
HttpServletResponse response = ControllerContent.getResponse();
try (OutputStream out = response.getOutputStream()){
fileName = new String(fileName.getBytes(StandardCharsets.UTF_8),"ISO8859-1");
response.setContentType("application/msexcel");
response.setHeader("Content-disposition", "attachment; filename="+fileName+".xlsx");
workbook.write(out);
} catch (Exception e) {
logger.error("导出exel错误 fileName={}",fileName,e);
}
}
获取线程的响应的方法
package com.zhiwei.rent.common;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
public class ControllerContent {
private static ThreadLocal<HttpServletRequest> REQ = new ThreadLocal<>();
private static ThreadLocal<HttpServletResponse> RES = new ThreadLocal<>();
private static ThreadLocal<UserAuthorization> USER = new ThreadLocal<>();
private static ThreadLocal<OperatorAuthorization> ADMIN_USER = new ThreadLocal<>();
public static void setRequest(HttpServletRequest req,HttpServletResponse response){
REQ.set(req);
RES.set(response);
}
public static void setUser(UserAuthorization user){
if(user != null) {
USER.set(user);
}
}
public static void setOperator(OperatorAuthorization operator){
if(operator != null){
ADMIN_USER.set(operator);
}
}
public static HttpServletRequest getRequest(){
return REQ.get();
}
public static HttpServletResponse getResponse(){
return RES.get();
}
public static UserAuthorization getUserAuthorization(){
return USER.get();
}
public static OperatorAuthorization getOperatorAuthorization(){
return ADMIN_USER.get();
}
public static void clear(){
REQ.remove();
RES.remove();
USER.remove();
ADMIN_USER.remove();
}
}
前端代码
一开始我后端写好了，然后点击导出按钮没反应，原来是因为前端代码没写好
我的后端代码是写好了的，我用postman试了一下，发现后端请求相应是ok的
那么现在来看看前端代码
导出按钮
<el-form-item>
<el-button @click="handleCurrentChange(1,'download')" :loading = "search.loading" type="primary" >导出Excel</el-button>
</el-form-item>
方法
handleCurrentChange(page,download){
this.search.page = page;
this.loadList(download);
},
loadList(download){
console.log(this.search.endDate);
this.search.download = download;
if(this.search.loading){return;}
if(download == 'download'){
let loading = this.$loading({lock: true,text:'正在导出，请稍等......',spinner: 'el-icon-loading',background:'rgba(0, 0, 0, 0.7)' });
this.$api({method:'post',url: '/admin/user/find.json',data:this.search,responseType:'blob'}).then(response =>{
let url = window.URL.createObjectURL(new Blob([response.data]))
let link = document.createElement('a')
link.style.display = 'none'
link.href = url
link.setAttribute('download',"用户数据"+new Date().format("yyyy年MM月dd日hh时mm分ss秒"+".xlsx"));
document.body.appendChild(link)
link.click()
loading.close()
}).catch((error) =>{loading.close();});
return;
}
else{
console.log("查询到了没");
var that = this;
that.search.loading = true;
that.$api.post('/admin/user/find.json',that.search).then(data=>{
that.search.loading = false;
if(data.data.code != '0'){
this.$message.error(data.data.msg);
return;
}
that.search.list = data.data.data.list;
that.search.totalPage = data.data.data.totalPage;
that.search.totalRow = data.data.data.totalRow;
that.search.totalNum=data.data.data.totalRow;
}).catch(function(){
that.search.loading = false;
});
}
},
在这里当时候踩了一个坑，就是我点击导出，一直都是在正在导出中，请教了别人才知道，在控制台看报错发现 search 没有定义，我说我在持层定义了，原来，持久层定义的得用this. 来调用
Mybatis的坑
映射
mybatis里面Mapper的resultMap映射，column是指的是你数据库的字段名，然后property就是属性名
我在工作的时候遇到了
这里面订单数量不是数据库里有的，而是通过一系列表关联计算出来的，我不知道数据库里对应什么字段名，然后我就尝试用括号里面的countom，发现不对，后面一搜csdn才知道，这个字段名就是你设置的字段名，你可以在SQL里面设置一下字段名，好表示一些，所以我就在SQL片段里面给它的字段设置成了"订单数量"就OK了
模糊查询
在把nickname数值传过来的时候，就把它拼接起来
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
