前提导入相关依赖
<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 { //1.第一种 头必须和实体(英文)一样 //文件处理成io流 InputStream in = file.getInputStream(); // //io流给ExcelReader ExcelReader excelReader=ExcelUtil.getReader(in); // //读取数据且转化为list // List<User> list = excelReader.readAll(User.class); //2.第二种导入方式 //忽略第一行头(第一行是中文的情况),直接读取表的内容 List<List<Object>> list = excelReader.read(1); // List<User> listUser = CollUtil.newArrayList(); 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); //设置content—type response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset:utf-8"); //设置标题 String fileName= URLEncoder.encode("用户信息","UTF-8"); //Content-disposition是MIME协议的扩展，MIME协议指示MIME用户代理如何显示附加的文件。 response.setHeader("Content-Disposition","attachment;filename="+fileName+".xlsx"); ServletOutputStream outputStream= response.getOutputStream(); //将Writer刷新到OutPut writer.flush(outputStream,true); outputStream.close(); writer.close(); }
