前提导入相关依赖
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>5.8.5</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
1.这里用的springboot,先创建相关的表随意填写信息,如下
2.创建相应pojo,和查询的service
3.导入代码
@PostMapping("/import")
@ResponseBody
public void importExcel(MultipartFile file) throws IOException {
InputStream in = file.getInputStream();
ExcelReader excelReader=ExcelUtil.getReader(in);
List<List<Object>> list = excelReader.read(1);
ArrayList<User> listUser = new ArrayList<>();
for (List<Object> row: list) {
User user=new User();
user.setId(Integer.parseInt(row.get(0).toString()));
user.setName(row.get(1).toString());
user.setPwd(row.get(2).toString());
listUser.add(user);
}
System.out.println(listUser.toString());
}
4.导出代码
@GetMapping("/export")
public void export(HttpServletResponse response) throws IOException {
List<User> list= userService.selectAll();;
ExcelWriter writer= ExcelUtil.getWriter(true);
writer.addHeaderAlias("id","ID");
writer.addHeaderAlias("name","用户名");
writer.addHeaderAlias("pwd","密码");
writer.write(list,true);
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset:utf-8");
String fileName= URLEncoder.encode("用户信息","UTF-8");
response.setHeader("Content-Disposition","attachment;filename="+fileName+".xlsx");
ServletOutputStream outputStream= response.getOutputStream();
writer.flush(outputStream,true);
outputStream.close();
writer.close();
}