EasyExcel导入导出
https://easyexcel.opensource.alibaba.com/docs/current/
https://github.com/alibaba/easyexcel
源码可以从github下载调试,相对于poi确实好用很多,但是本质上还是使用的poi做的改造,所以3X之后被动依赖还是poi
1、EasyExcel导出数据
1.1、核心元素
- excel模板
- vo
- ExcelWriter
1.2、导入pom
build 是为了 模板能够编译到target下
<dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>3.1.0</version> </dependency> <build> <resources> <resource> <directory>src/main/java</directory> <includes> <include>**/*.*</include> </includes> </resource> <resource> <directory>src/main/resources</directory> <includes> <include>**/*.*</include> </includes> </resource> </resources> <plugins> <plugin> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-maven-plugin</artifactId> </plugin> </plugins> </build>
1.3、模板
src\main\resources\excel\主机记录模板.xlsx
execl内容比较简单不再展示
1.4、两个vo
一个用来接收请求参数,一个用来做excel导出用
import lombok.Data; import java.util.Date; @Data public class NodeVO { private String hostName; private String hostIp; private int status; // 主机状态 0离线 1在线 private Date lastUpdateTime; }
导出的数据模型,注意相关excle注解,详细可参考官网说明,或者github中源码的ReadMe。
这里主要完成字段和excel的对应关系,以及两个核心的format注解。
import com.alibaba.excel.annotation.ExcelIgnore; import com.alibaba.excel.annotation.ExcelProperty; import com.alibaba.excel.annotation.format.DateTimeFormat; import lombok.Data; import java.util.Date; @Data public class NodeExportVO { @ExcelProperty(value = "主机名", index = 0) private String hostName; @ExcelProperty(value = "主机IP", index = 1) private String hostIp; @ExcelIgnore private int status; // 主机状态 0离线 1在线 @ExcelProperty(value = "主机状态", index = 2) private String statusName; @ExcelProperty(value = "上报时间", index = 3) @DateTimeFormat("YYYY-MM-dd") private Date lastUpdateTime; }
1.5、控制层
/** * EasyExcel测试 */ @RestController public class EasyExcelController { @Autowired private IEasyExcelService easyExcelService; /** * 导出 */ @RequestMapping(value = "/export", method = RequestMethod.POST) public void export(@RequestBody List<NodeVO> nodeVOList) { easyExcelService.export(nodeVOList); } }
1.6、服务层实现类
@Service public class EasyExcelService implements IEasyExcelService { @Override public void export(List<NodeVO> nodeVOList) { HttpServletResponse response = ((ServletRequestAttributes) RequestContextHolder.getRequestAttributes()).getResponse(); response.setCharacterEncoding("utf-8"); response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); String fileName = null; try { fileName = URLEncoder.encode("主机记录", "UTF-8"); } catch (UnsupportedEncodingException e) { e.printStackTrace(); } response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx"); List<NodeExportVO> nodeExportVOList = nodeVOList.stream().map(nodeVO -> { NodeExportVO nodeExportVO = new NodeExportVO(); BeanUtils.copyProperties(nodeVO, nodeExportVO); return nodeExportVO; }).collect(Collectors.toList()); if (!CollectionUtils.isEmpty(nodeExportVOList)) { for (NodeExportVO nodeExportVO : nodeExportVOList) { int status = nodeExportVO.getStatus(); String statusName = status == 0 ? "离线" : "在线"; nodeExportVO.setStatusName(statusName); } } ClassPathResource resource = new ClassPathResource("excel/主机记录模板.xlsx"); System.out.println(resource.exists()); ExcelWriter excelWriter = null; try { excelWriter = EasyExcel.write(response.getOutputStream()).withTemplate(resource.getInputStream()).build(); } catch (IOException e) { e.printStackTrace(); } WriteSheet writeSheet = EasyExcel.writerSheet(0).build(); excelWriter.write(nodeExportVOList, writeSheet); excelWriter.finish(); } }
谷歌插件进行接口调试
post
body
[ { "hostName": "jdit-aliyun", "hostIp": "3.182.55.7", "status": 1, "lastUpdateTime": "2022-11-22T15:25:30.334Z" } ]
就可以完成下载了。
2、导入数据
导入数据首先需要下载模板
然后进行导入
2.1、下载Excel模板
2.1.1、准备模板
src\main\resources\excel
linux主机记录模板.xlsx
windows主机记录模板.xlsx
2.1.1、提供下载接口(Get请求即可)
2.1.1.1 控制层
/** * 下载模板 */ @RequestMapping(value = "/downloadTemplate", method = RequestMethod.GET) public void downloadTemplate() { easyExcelService.downloadTemplate(); }
2.1.1.2、业务层
@Override public void downloadTemplate() { HttpServletRequest request = ((ServletRequestAttributes) RequestContextHolder.getRequestAttributes()).getRequest(); String systemType = request.getParameter("systemType"); String fileName = "linux主机记录模板"; if ("windows".equalsIgnoreCase(systemType)) { fileName = "windows主机记录模板"; } ClassPathResource resource = new ClassPathResource("excel/" + fileName+ ".xlsx"); HttpServletResponse response = ((ServletRequestAttributes) RequestContextHolder.getRequestAttributes()).getResponse(); response.setCharacterEncoding("utf-8"); response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); try { fileName = URLEncoder.encode(fileName, "UTF-8"); } catch (UnsupportedEncodingException e) { e.printStackTrace(); } response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx"); try (BufferedInputStream fis = new BufferedInputStream(resource.getInputStream()); OutputStream out = response.getOutputStream(); ) { byte[] bytes = new byte[1024 * 1024 * 10]; int length = 0; while ((length = fis.read(bytes)) != -1) { out.write(bytes, 0, length); } } catch (IOException e) { e.printStackTrace(); } }
2.1.1.3、测试下载
浏览器地址栏输入url地址请求即可
http://localhost:8080/downloadTemplate
2.2、EasyExcel导入数据
遗忘的笔记,突然团队20+人员需要裁员到只保留一个开发,突然感觉又有时间补充下笔记了。
2.2.1、导入入口
-
这里最核心的设计一个技术,IO流,IT系统如何处理文件。
从用户选择文件,要知道浏览器做了什么,网络做了什么,springboot容器又是做了如何转换,才能识别文件,接受解析整个文件的。
/** * 导入 */ @RequestMapping(value = "/import", method = RequestMethod.POST) public ResultDataVO importNode(@RequestParam("file") MultipartFile file, HttpServletRequest request, HttpServletResponse response) { return easyExcelService.importNode(file, request, response); }
2.2.2、具体实现逻辑
@Override public ResultDataVO importNode(MultipartFile file, HttpServletRequest request, HttpServletResponse response) { try (InputStream inputStream = file.getInputStream()) { request.setCharacterEncoding("utf-8"); response.setCharacterEncoding("utf-8"); response.setContentType("text/html"); List<NodeExportVO> importList = excelToList(inputStream); return CommonUtil.result(ResultDataVO.SUCCESS_CODE, "导入成功", importList); } catch (IOException ioException) { log.error("导入失败", ioException); return CommonUtil.result(ResultDataVO.FAILED_CODE, "导入失败", ""); } }
private List<NodeExportVO> excelToList(InputStream inputStream) { ExcelListener<NodeExportVO> listener = new ExcelListener<>(); ExcelReader reader = EasyExcelFactory.read(inputStream, NodeExportVO.class, listener) .headRowNumber(2).build(); reader.readAll(); List<NodeExportVO> importList = listener.getList(); return importList; }
2.2.3、核心组件ExcelListener
/** * desc * * @Author 红尘过客 * @DateTime 2023-08-02 17:08:51 */ @Slf4j public class ExcelListener<T> extends AnalysisEventListener<T> { private List<T> list = new ArrayList<>(); public List<T> getList() { return list; } public void setList(List<T> list) { this.list = list; } /** * 一行一行读取文件内容 * * @param t * @param analysisContext */ @Override public void invoke(T t, AnalysisContext analysisContext) { list.add(t); } @Override public void doAfterAllAnalysed(AnalysisContext analysisContext) { } @Override public void onException(Exception exception, AnalysisContext context) throws Exception { super.onException(exception, context); } @Override public void extra(CellExtra extra, AnalysisContext context) { super.extra(extra, context); } @Override public boolean hasNext(AnalysisContext context) { return super.hasNext(context); } }
2.2.4、验证上传
http://localhost:10005/test/import
标签:
easyExcel
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 没有源码,如何修改代码逻辑?
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战