背景:
在后台系统管理项目中,经常会用到excel的导入导出,而Apache POI又显得比较笨重,因此阿里巴巴封装了POI,发布了开源的轻量级的解析、生成excel的框架。
导入Excel:
前端代码:
html:
<div class=" layui-upload-button" style="border:#FFFFFF ;">
<button type="button" class="layui-btn" id="test1">
<i class="layui-icon"></i>导入成员信息
</button>
<input class="layui-upload" type="file" accept="undefined" id="excelFile" name="excelFile" >
</div>
js:
function uploadExcel() {
var formData = new FormData();
formData.append('excelFile', $('#excelFile')[0].files[0]);
// formData.append('id', id);//将id追加再id中
//console.log(formData);
layer.msg('文件上传中', {icon: 16});
$.ajax({
type: "post",
processData: false,
contentType: false,
url: "/importExcel",
data: formData,
success: function (data) {
if (data.status == 1) {
layer.closeAll('loading');
layer.msg(data.msg, {icon: 1, time: 1000});
window.parent.location.reload();
return false;
} else {
layer.msg(data.msg, {icon: 2, time: 1000});
}
}
});
}
后台代码:
控制层:
@Autowired
private UserService userService;
@PostMapping("importExcel")
public ServerResponse importExcel(@RequestParam("excelFile")MultipartFile excelFile){
try {
List<User> objects = EasyExcel.read(excelFile.getInputStream(), User.class, null).sheet(0).doReadSync();
for (int i=0;i<objects.size();i++){
userService.insertSelective(objects.get(i));//此处调用业务层增加方法将数据添加进数据库,根据自己底层代码调用(本人所用的是mybatis-plus)
}
} catch (IOException e) {
e.printStackTrace();
}
return ServerResponse.buildSuccessMsg("导入成功,即将刷新页面");
}
生成Excel:
前端代码:
<div class="layui-input-inline">
<div class="site-demo-upbar">
<div class=" layui-download-button" style="border:#FFFFFF ;">
<a class="layui-btn" type="button" href="/export">
<i class="layui-icon"></i>导出成员信息
</a>
</div>
</div>
</div>
后台代码:
@RequestMapping("/export")
public String ExporExcel(HttpServletResponse response) throws Exception { //throws IOException {
ExcelWriter writer = null;
OutputStream outputStream = response.getOutputStream();
try {
//添加响应头信息
response.setHeader("Content-disposition", "attachment; filename=" + "小区信息表.xls");
response.setContentType("application/msexcel;charset=UTF-8");//设置类型
response.setHeader("Pragma", "No-cache");//设置头
response.setHeader("Cache-Control", "no-cache");//设置头
response.setDateHeader("Expires", 0);//设置日期头
EasyExcel.write(outputStream,User.class).sheet("成员信息统计").doWrite(userService.writeData());
writer.finish();
outputStream.flush();
} catch (IOException e) {
e.printStackTrace();
} finally {
try {
response.getOutputStream().close();
} catch (IOException e) {
e.printStackTrace();
}
}
return null;
}
踩坑:平时见到的弹窗几乎都是js来触发的,就习惯性的认为浏览器的下载弹窗也是靠js来触发的,殊不知其是靠后台代码,通过response来触发。