一.导入poi依赖

<!-- poi实现excel导入导出--><dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>3.9</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml-schemas</artifactId><version>3.9</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>3.9</version></dependency>

主要用到该依赖下的两个类：XSSFWorkbook和HSSFWorkbook类。

XSSFWorkbook是Excel中的xlsx版本,HSSFWorkbook是xls版本,我使用的是xlsx版本的。

二.详细代码

业务层：

@Servicepublic class UserServiceImpl implements UserService {@Autowiredprivate UserMapper userMapper;@Overridepublic XSSFWorkbook downloadAssociatedUser() {List<User> list = userMapper.selectByExample(null);//查出数据库数据XSSFWorkbook wb = new XSSFWorkbook();Sheet sheet = wb.createSheet("UserList");//创建一张表Row titleRow = sheet.createRow(0);//创建第一行，起始为0titleRow.createCell(0).setCellValue("账号");//第一列titleRow.createCell(1).setCellValue("名称");titleRow.createCell(2).setCellValue("权限来源");titleRow.createCell(3).setCellValue("账号状态");int cell = 1;for (User user : list) {Row row = sheet.createRow(cell);//从第二行开始保存数据row.createCell(0).setCellValue(user.getUserAccount());row.createCell(1).setCellValue(user.getUserName());//将数据库的数据遍历出来row.createCell(2).setCellValue(user.getFromAuth());row.createCell(3).setCellValue(user.getStatus());cell++;}return wb;}

控制层:

@Controllerpublic class UserController {@Autowiredprivate UserService userService;@RequestMapping(value = "/user/download",method = RequestMethod.GET)public void userExcel(HttpServletResponse response) {XSSFWorkbook wb = userService.downloadAssociatedUser();String fileName = "用户清单" + new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date()) + ".xlsx";OutputStream outputStream = null;try {fileName = URLEncoder.encode(fileName,"UTF-8");//设置ContentType请求信息格式response.setContentType("application/vnd.ms-excel");response.setHeader("Content-disposition", "attachment;filename=" + fileName);outputStream = response.getOutputStream();wb.write(outputStream);outputStream.flush();outputStream.close();} catch (UnsupportedEncodingException e) {e.printStackTrace();} catch (IOException e) {e.printStackTrace();}}}

参考文章：springboot实现数据库数据导出生成Excel报表 - changlinlo - 博客园