1、简介 EasyExcel是一个阿里开源的基于Java的、快速、简洁、解决大文件内存溢出的Excel处理工具。 他能让你在不用考虑性能、内存的等因素的情况下,快速完成Excel的读、写等功能。 EasyExcel基于POI进行封装优化,降低内存使用,再大的excel也不会出现内存溢出,让使用更加简单方便。
官网: https://easyexcel.opensource.alibaba.com/ github: https://github.com/alibaba/easyexcel
2、读取Excel EasyExcel需引入如下依赖:
1 2 3 4 5 <dependency > <groupId > com.alibaba</groupId > <artifactId > easyexcel</artifactId > <version > 3.1.0</version > </dependency >
示例中还需引入其他依赖:
1 2 3 4 5 6 7 8 9 10 11 <dependency > <groupId > org.projectlombok</groupId > <artifactId > lombok</artifactId > <version > 1.18.8</version > </dependency > <dependency > <groupId > com.alibaba</groupId > <artifactId > fastjson</artifactId > <version > 1.2.60</version > </dependency >
示例Excel表格demo1.xlsx如下:
读取Excel数据的对象:
1 2 3 4 5 6 7 8 9 10 11 12 13 @Data @HeadRowHeight(20) @ColumnWidth(20) public class DemoData { @ExcelProperty("字符串标题") private String string; @ExcelProperty("日期标题") private Date date; @ExcelProperty("数字标题") private Double doubleData; @ExcelIgnore private String ignore; }
注解: ExcelProperty:指定当前字段对应excel中的那一列,可以根据名字或者Index去匹配,不写,默认按顺序匹配,可以根据converter指定字段转换器; ExcelIgnore:默认所有字段都会和excel去匹配,加了这个注解会忽略该字段; DateTimeFormat:日期转换,用String去接收excel日期格式的数据会调用这个注解; NumberFormat:数字转换,用String去接收excel数字格式的数据会调用这个注解; ExcelIgnoreUnannotated:默认不加ExcelProperty的注解的都会参与读写,加了不会参与; ColumnWidth:设置列宽; ContentRowHeight:设置行高; HeadRowHeight:设置头高。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 @Slf4j public class EasyExcelReadTest { @Test public void syncRead () { String fileName = "demo1.xlsx" ; List<DemoData> list = EasyExcel.read(fileName).head(DemoData.class).sheet().doReadSync(); for (DemoData data : list) { log.info("读取到数据:{}" , JSON.toJSONString(data)); } List<Map<Integer, String>> listMap = EasyExcel.read(fileName).sheet().doReadSync(); for (Map<Integer, String> data : listMap) { log.info("读取到数据:{}" , JSON.toJSONString(data)); } } @Test public void simpleRead () { String fileName = "demo1.xlsx" ; EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).sheet().doRead(); try (ExcelReader excelReader = EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).build()) { ReadSheet readSheet = EasyExcel.readSheet(0 ).build(); excelReader.read(readSheet); } } }
读监听器:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 @Slf4j public class DemoDataListener extends AnalysisEventListener <DemoData > { private static final int BATCH_COUNT = 200 ; private List<DemoData> list = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT); private DemoService demoService; public DemoDataListener () { demoService = new DemoService(); } public DemoDataListener (DemoService demoService) { this .demoService = demoService; } @Override public void invoke (DemoData data, AnalysisContext context) { log.info("解析到一条数据:{}" , JSON.toJSONString(data)); list.add(data); if (list.size() >= BATCH_COUNT) { saveData(); list = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT); } } @Override public void doAfterAllAnalysed (AnalysisContext context) { saveData(); log.info("所有数据解析完成!" ); } private void saveData () { log.info("{}条数据,开始存储数据库!" , list.size()); demoService.save(list); log.info("存储数据库成功!" ); } }
业务层数据处理:
1 2 3 4 5 public class DemoService { public void save (List<DemoData> list) { } }
3、写入Excel 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 @Slf4j public class EasyExcelWriteTest { @Test public void writeExcel () { String fileName = "demo" + System.currentTimeMillis() + ".xlsx" ; EasyExcel.write(fileName, DemoData.class) .sheet("模板" ) .doWrite(() -> { return data(); }); fileName = "demo" + System.currentTimeMillis() + ".xlsx" ; EasyExcel.write(fileName, DemoData.class).sheet("模板" ).doWrite(data()); fileName = "demo" + System.currentTimeMillis() + ".xlsx" ; try (ExcelWriter excelWriter = EasyExcel.write(fileName, DemoData.class).build()) { WriteSheet writeSheet = EasyExcel.writerSheet("模板" ).build(); excelWriter.write(data(), writeSheet); } } private List<DemoData> data () { List<DemoData> list = new ArrayList<DemoData>(); for (int i = 0 ; i < 10 ; i++) { DemoData data = new DemoData(); data.setString("字符串" + i); data.setDate(new Date()); data.setDoubleData(0.56 ); list.add(data); } return list; } }
4、Web上传下载 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 @Controller public class UploadDownController { @Autowired private DemoService demoService; @GetMapping("download") public void download (HttpServletResponse response) throws IOException { response.setContentType("application/vnd.ms-excel" ); response.setCharacterEncoding("utf-8" ); String fileName = URLEncoder.encode("测试" , "UTF-8" ).replaceAll("\\+" , "%20" ); response.setHeader("Content-disposition" , "attachment;filename*=utf-8''" + fileName + ".xlsx" ); EasyExcel.write(response.getOutputStream(), DemoData.class).sheet("模板" ).doWrite(data()); } @GetMapping("api/download") public void downloadApi (HttpServletResponse response) throws IOException { try { response.setContentType("application/vnd.ms-excel" ); response.setCharacterEncoding("utf-8" ); String fileName = URLEncoder.encode("测试" , "UTF-8" ).replaceAll("\\+" , "%20" ); response.setHeader("Content-disposition" , "attachment;filename*=utf-8''" + fileName + ".xlsx" ); EasyExcel.write(response.getOutputStream(), DemoData.class).autoCloseStream(Boolean.FALSE).sheet("模板" ) .doWrite(data()); } catch (Exception e) { response.reset(); response.setContentType("application/json" ); response.setCharacterEncoding("utf-8" ); Map<String, String> map = new HashMap<String, String>(); map.put("code" , "500" ); map.put("message" , "下载文件失败" + e.getMessage()); response.getWriter().println(JSON.toJSONString(map)); } } @PostMapping("upload") @ResponseBody public String upload (MultipartFile file) throws IOException { EasyExcel.read(file.getInputStream(), DemoData.class, new DemoDataListener(demoService)).sheet().doRead(); return "success" ; } private List<DemoData> data () { List<DemoData> list = new ArrayList<>(); for (int i = 0 ; i < 10 ; i++) { DemoData data = new DemoData(); data.setString("字符串" + 0 ); data.setDate(new Date()); data.setDoubleData(0.56 ); list.add(data); } return list; } }
Excel上传时,获取Excel数据,我们也可以封装个工具类ExcelUtils:
1 2 3 4 5 6 7 8 9 10 11 12 13 @Slf4j public class ExcelUtils { public static <T> List<T> getExcelModelData (final InputStream inputStream, Class<T> clazz) { if (null == inputStream) { throw new NullPointerException("the inputStream is null!" ); } ExcelReaderBuilder result = EasyExcel.read(inputStream, clazz, null ); ExcelReaderSheetBuilder sheet1 = result.sheet(); List<T> resultData = sheet1.doReadSync(); return resultData; } }
上传的代码也可改为如下:
1 2 3 4 5 6 7 @PostMapping("upload" ) @ResponseBody public String upload(MultipartFile file) throws IOException { List<DemoData> excelModelData = ExcelUtils . getExcelModelData(file .getInputStream () , DemoData .class ); demoService.save(excelModelData); return "success" ; }
其他示例可参考:EasyExcel Demo
API参考:EasyExcel API