我们要实现excel的导入和导出功能,第一步还是导入相关的依赖,这里需要自己指定版本号,注意:一定选好版本号,不然有的方法用了会报错。其次就是我们的hutool的依赖,
<!-- poi --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>${poi}</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>${poi}</version> </dependency><!--hutoll--> <dependency> <groupId>cn.hutool</groupId> <artifactId>hutool-all</artifactId> <version>5.8.16</version> <!-- 版本号可以根据需要进行调整 --> </dependency>
然后编写一个controller类 编写导出方法,很简单 这个username是可以根据用户的名称导出,如果有别的需求可以加其他参数:
/**
*批量导出数据
* @param username
*/
@GetMapping("/export")
@ApiOperation("导出用户数据报表")
public void export(@RequestParam(required = false) String username,
HttpServletResponse response) throws IOException {
// 创建 ExcelWriter 实例
ExcelWriter writer = ExcelUtil.getWriter(true);
// 查询数据
List<SysUser> list;
if (StrUtil.isBlank(username)) {
// 查询所有数据
list = sysUserService.list();
} else {
QueryWrapper<SysUser> queryWrapper = new QueryWrapper<>();
queryWrapper.like("username", username);
// 根据用户名查询数据
list = sysUserService.list(queryWrapper); // 根据用户名查询数据
}
// 写入数据
writer.write(list, true);
// 设置响应格式
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("用户信息表", "UTF-8") + ".xlsx");
// 输出文件
try (ServletOutputStream outputStream = response.getOutputStream()) {
writer.flush(outputStream, true);
} finally {
writer.close();
}
}
然后就是导出功能,
/**
* 批量导入
* @param file
*/
@PostMapping("/import")
@ApiOperation("导入用户数据报表")
public Result importData(MultipartFile file) throws IOException {
// ExcelReader reader = ExcelUtil.getReader(file.getInputStream());
// List<SysUser> sysUsers = reader.readAll(SysUser.class);
//
// //写入数据库
// sysUserService.insert(sysUsers);
// return new Result().success();
// }
try (InputStream inputStream = file.getInputStream()) {
ExcelReader reader = ExcelUtil.getReader(inputStream);
List<SysUser> sysUsers = reader.readAll(SysUser.class);
// 写入数据库
sysUserService.insert(sysUsers);
log.info("Successfully imported {} users.", sysUsers.size());
return new Result().success();
} catch (IOException e) {
log.error("Failed to import data", e);
return new Result().error("数据导入失败,请检查文件格式或内容。");
} catch (Exception e) {
log.error("Unexpected error during import", e);
return new Result().error("数据导入过程中发生未知错误。");
}
}
欢迎各位大佬指正!!!一起加油!!!