1.基本介绍
EasyExcel是一个基于Java的简单、省内存的读写Excel的开源项目。在尽可能节约内存的情况下支持读写百M的Excel。
依赖
<dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>2.2.3</version></dependency>
2.简单的导出一个excel
对象
importcom.alibaba.excel.annotation.ExcelProperty; importcom.alibaba.excel.annotation.format.DateTimeFormat; importcom.alibaba.excel.annotation.write.style.ColumnWidth; publicclassDemoExcel { (value= {"数字"}, index=0) (20) privateDoubledoubleData; (value= {"字符"}, index=1) (20) privateStringstring; /*** 自定义的时间格式*/("yyyy年MM月dd日 HH:mm:ss") (value= {"时间"}, index=2) (30) privateDatedate; }
Controller
importcom.alibaba.excel.EasyExcel; ("/download") publicvoiddownload(HttpServletResponseresponse) { // 模拟从数据库查询数据，不一定要转成demoExcel对象，只要字段能对应上就行List<DemoExcel>list=getList(); try { // 导出数据response.setContentType("application/vnd.ms-excel"); response.setCharacterEncoding("utf-8"); StringfileName=URLEncoder.encode(StringUtils.join(System.currentTimeMillis(), "_", "downloadExcel"), "UTF-8"); response.setHeader("Content-disposition", "attachment;filename="+fileName+".xlsx"); EasyExcel.write(response.getOutputStream(), DemoExcel.class).sheet("sheet1") .doWrite(list); } catch (Exceptione) { log.debug("导出文件失败:{}", e.getMessage()); thrownewRuntimeException("导出文件失败"); } }
使用postman调试
点击send and download就能下载到excel
如果excel打不开，报错，多半是抛异常了，可以右键用记事本打开看到返回的json，也可以直接点一下send再调一次接口直接看到报错信息
3.简单的导入一个excel
对象
importcom.alibaba.excel.annotation.ExcelProperty; importcom.alibaba.excel.annotation.format.DateTimeFormat; importcom.alibaba.excel.annotation.write.style.ColumnWidth; publicclassDemoExcel { (value= {"数字"}, index=0) (20) privateDoubledoubleData; (value= {"字符"}, index=1) (20) privateStringstring; /*** 自定义的时间格式*/("yyyy年MM月dd日 HH:mm:ss") (value= {"时间"}, index=2) (30) privateDatedate; }
监听
importcom.alibaba.excel.context.AnalysisContext; importcom.alibaba.excel.event.AnalysisEventListener; publicclassDemoDataListenerextendsAnalysisEventListener<DemoExcel> { privateList<DemoExcel>list=newArrayList(); publicvoidinvoke(DemoExceldata, AnalysisContextcontext) { // 这里取到的data就是单独的一行数据，也可以在这个方法里对数据进行一些简单的处理list.add(data); } publicvoiddoAfterAllAnalysed(AnalysisContextcontext) { // 这个方法是在excel解析完成后能对数据进行操作，也能在这里对数据进行各种处理log.info("获取数据量："+list.size()); } }
Controller
importcom.alibaba.excel.EasyExcel; ("/upload") publicvoidupload(("file") MultipartFilefile) throwsIOException { DemoDataListenerlistener=newDemoDataListener(); //headRowNumber(1)从第二行读数据//sheet()默认读第一个sheet页,当然想读第二页就往里填个1EasyExcel.read(file.getInputStream(), DemoExcel.class, listener).sheet().headRowNumber(1).doRead(); List<DemoExcel>list=listener.getList(); // 我们取到了excel中的数据后就能用来进行想要的操作了list.forEach(s-> { log.info("读取到数据------"+s); }); }
使用postman调试
我还是用刚才导出来的那个文件进行一次导入
postman向上面这样设置就能进行导入文件了
控制台的日志告诉我取到了这个数组
4.导出的excel要自带下拉框,以及多个sheet页的导出
Controller
importcom.alibaba.excel.EasyExcel; importcom.alibaba.excel.ExcelWriter; importcom.alibaba.excel.write.metadata.WriteSheet; importcom.alibaba.excel.write.metadata.WriteTable; ("/download/default") publicvoiddownloadDefault(HttpServletResponseresponse) { try { // 导出数据response.setContentType("application/vnd.ms-excel"); response.setCharacterEncoding("utf-8"); // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系StringfileName=URLEncoder.encode(StringUtils.join(System.currentTimeMillis(), "_", "downloadExcel"), "UTF-8"); response.setHeader("Content-disposition", "attachment;filename="+fileName+".xlsx"); ExcelWriterwriter=EasyExcel.write(response.getOutputStream()).build(); // 预设值List<DefaultData>defaultData=getDefault(); // 设置第1个sheet为我们的模版 同时设置一个下拉框// 把sheet设置为不需要头 不然会输出sheet的头 这样看起来第一个table 就有2个头了WriteSheetwriteSheet=EasyExcel.writerSheet(0,"模板").needHead(false) .registerWriteHandler(newCustomSheetWriteHandler(defaultData)) .build(); // 填写表单的表头WriteTablewriteTable0=EasyExcel.writerTable(0).needHead(true).build(); writeTable0.setClazz(DemoExcel.class); writer.write(Lists.newArrayList(), writeSheet, writeTable0); // 根据预设值的pid整理一下List<DemoDefaultExcel>defaultExcelList=getFormDataList(defaultData); // 设置第2个sheet为字段可选范围 ,把可以选择的范围列出来，没有也没事WriteSheetwriteDefaultDataSheet=EasyExcel.writerSheet(1,"字段选填范围") .head(DemoDefaultExcel.class).build(); writer.write(defaultExcelList, writeDefaultDataSheet); writer.finish(); } catch (Exceptione) { log.debug("导出文件失败:{}", e.getMessage()); thrownewRuntimeException("导出文件失败"); } }
Handler
importcom.alibaba.excel.write.handler.SheetWriteHandler; importcom.alibaba.excel.write.metadata.holder.WriteSheetHolder; importcom.alibaba.excel.write.metadata.holder.WriteWorkbookHolder; importorg.apache.poi.ss.usermodel.DataValidation; importorg.apache.poi.ss.usermodel.DataValidationConstraint; importorg.apache.poi.ss.usermodel.DataValidationHelper; importorg.apache.poi.ss.util.CellRangeAddressList; publicclassCustomSheetWriteHandlerimplementsSheetWriteHandler { privateList<DefaultData>defaultDataList; publicCustomSheetWriteHandler(List<DefaultData>defaultDataList){ this.defaultDataList=defaultDataList; } publicvoidbeforeSheetCreate(WriteWorkbookHolderwriteWorkbookHolder, WriteSheetHolderwriteSheetHolder) { } publicvoidafterSheetCreate(WriteWorkbookHolderwriteWorkbookHolder, WriteSheetHolderwriteSheetHolder) { // 筛选出pid为1的值，添加到第2列的下拉框中writeSheetHolder.getSheet().addValidationData(getValidationDataByPid(1L, 1, writeSheetHolder)); writeSheetHolder.getSheet().addValidationData(getValidationDataByPid(2L, 2, writeSheetHolder)); } privateDataValidationgetValidationDataByPid(Longpid, intcol, WriteSheetHolderwriteSheetHolder){ List<String>dataValues=defaultDataList.stream().filter(item->null!=item.getPid() &&item.getPid().equals(pid)) .map(DefaultData::getNodeName).collect(Collectors.toList()); returngetValidationDataBySelects(writeSheetHolder, col, dataValues.toArray(newString[dataValues.size()])); } privateDataValidationgetValidationDataBySelects(WriteSheetHolderwriteSheetHolder, intcol, String[] valus){ // 这里4个参数代表从第二行到第65536行，从第几列到第几列。可以根据需求增加CellRangeAddressListcellRangeAddressList=newCellRangeAddressList(1, 65535, col, col); DataValidationHelperhelper=writeSheetHolder.getSheet().getDataValidationHelper(); DataValidationConstraintconstraint=helper.createExplicitListConstraint(valus); DataValidationdataValidation=helper.createValidation(constraint, cellRangeAddressList); returndataValidation; } }
DefaultData.java
publicclassDefaultData { privateLongpid; privateStringnodeName; }
预设值的结构是这样的
导出的文件第一页，第二列和第三列有了下拉框选项
第二页有可选范围的示范
5.导出的excel要有示例
DemoExampleExcel.java
importcom.alibaba.excel.annotation.ExcelProperty; importcom.alibaba.excel.annotation.format.DateTimeFormat; importcom.alibaba.excel.annotation.write.style.ColumnWidth; importcom.alibaba.excel.annotation.write.style.ContentStyle; importcom.alibaba.excel.annotation.write.style.HeadStyle; importorg.apache.poi.ss.usermodel.FillPatternType; // 以注解形式设置样式// 头背景设置成黄色 IndexedColors.YELLOW.getIndex()(fillPatternType=FillPatternType.SOLID_FOREGROUND, fillForegroundColor=13) // 内容的背景设置成黄色 IndexedColors.YELLOW.getIndex()(fillPatternType=FillPatternType.SOLID_FOREGROUND, fillForegroundColor=13) publicclassDemoExampleExcel { // 相同的表头会自动合并(value= {"示例", "示例", "数字"}, index=0) (20) privateDoubledoubleData; (value= {"示例", "示例", "字符"}, index=1) (20) privateStringstring; (value= {"示例", "示例", "字符2"}, index=2) (20) privateStringstring2; ("yyyy年MM月dd日 HH:mm:ss") (value= {"示例", "示例", "时间"}, index=3) (30) privateDatedate; }
Controller
importcom.alibaba.excel.EasyExcel; importcom.alibaba.excel.ExcelWriter; importcom.alibaba.excel.write.metadata.WriteSheet; importcom.alibaba.excel.write.metadata.WriteTable; ("/download/example") publicvoiddownloadExample(HttpServletResponseresponse) { try { // 导出数据response.setContentType("application/vnd.ms-excel"); response.setCharacterEncoding("utf-8"); // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系StringfileName=URLEncoder.encode(StringUtils.join(System.currentTimeMillis(), "_", "downloadExcel"), "UTF-8"); response.setHeader("Content-disposition", "attachment;filename="+fileName+".xlsx"); ExcelWriterwriter=EasyExcel.write(response.getOutputStream()).build(); // 把sheet设置为不需要头 不然会输出sheet的头 这样看起来第一个table 就有2个头了WriteSheetwriteSheet=EasyExcel.writerSheet(0,"模板").needHead(false).build(); // 获取示例list 填写示例List<DemoExampleExcel>exampleExcels=getExampleList(); WriteTablewriteTable0=EasyExcel.writerTable(0).needHead(true).build(); writeTable0.setClazz(DemoExampleExcel.class); writer.write(exampleExcels, writeSheet, writeTable0); // 示例和表头间增加一栏空行writer.write(Lists.newArrayList(""), writeSheet); // 填写表单的表头WriteTablewriteTable1=EasyExcel.writerTable(1).needHead(true).build(); writeTable1.setClazz(DemoExcel.class); writer.write(Lists.newArrayList(), writeSheet, writeTable1); writer.finish(); } catch (Exceptione) { log.debug("导出文件失败:{}", e.getMessage()); thrownewRuntimeException("导出文件失败"); } }
这里只列举了几种实用并且可能用的上的小功能。
更多的进阶技巧可以查看官方语雀。也欢迎大家继续补充这篇文章。
6.参考资料
github地址:https://github.com/alibaba/easyexcel
