一,Hutool工具
1. pom.xml中加入依赖
<!--hutool-->
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>5.7.20</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
二,后台导出
2. 在控制器里面实现导入导出的功能
/**
* 导出接口
*/
@GetMapping("/export1")
public void export1(HttpServletResponse response) throws Exception {
//从数据库查出所有的数据
List<Emp> list = empService.listAllEmp();
//在内存操作写到浏览器
ExcelWriter writer = ExcelUtil.getWriter(true);
writer.merge(list.size(),"人员信息表");
//自定义标题别名
writer.addHeaderAlias("eid", "员工编号");
writer.addHeaderAlias("ename", "员工姓名");
writer.addHeaderAlias("esex", "员工性别");
writer.addHeaderAlias("edate", "入职日期");
writer.addHeaderAlias("pid", "员工部门");
writer.addHeaderAlias("esalary", "员工工资");
//一次性写出list对象到Excel使用默认样式,强制输出标题
writer.write(list, true);
//设置浏览器响应的格式
response.setContentType("application/vnd.openxmlformats-officeedocument.sheet;charset=utf-8");
String fileName = URLEncoder.encode("ywlchild", "UTF-8");
response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");
ServletOutputStream out = response.getOutputStream();
writer.flush(out, true);
out.close();
writer.close();
}
三,后台导入
1 在实体类添加ToString注解
2 在控制器添加功能(empService.saveBatch(emps)是使用了mybatisplus)
@PostMapping("/import1")
public Boolean import1(MultipartFile file) throws Exception{
InputStream inputStream = file.getInputStream();
ExcelReader reader = ExcelUtil.getReader(inputStream);
List<List<Object>> list = reader.read(2);
List<Emp>emps = CollUtil.newArrayList();
for (List<Object>row:list){
Emp emp=new Emp(0,row.get(1).toString(),row.get(2).hashCode(),null,row.get(4).hashCode(), row.get(5).hashCode());
emps.add(emp);
}
empService.saveBatch(emps);
return true;
}