EasyExcel实现Excel文件多sheet导入导出

一、概述

最近公司需要做一个需求，通过excel上传病例信息，并将病例信息进行归档和整理；该需求可以简化为excel模板下载和excel上传并解析归档。既然知道需求了，找excel的操作工具jar包吧，发现以前常用的poi需要写的代码太多，但是时间紧急，没办法只能找新的工具。easypoi 在读写数据的时候，优先是先将数据写入内存，优点是读写性能非常高，但是当数据量很大的时候，会出现oom，当然它也提供了 sax 模式的读写方式，需要调用特定的方法实现。病例的信息量可能会很大，很有可能会造成内存溢出，没办法，再找吧！easyexcel 基于sax模式进行读写数据，不会出现oom情况，程序有过高并发场景的验证，因此程序运行比较稳定，相对于 easypoi 来说，虽然读写性能稍慢，但是更符合我目前的开发场景。

二、easypoi实例

easypoi 的亮点就是基于注解实体类来实现导入、导出excel，使用起来非常简单！

1. jar包引入

<dependency><groupId>cn.afterturn</groupId><artifactId>easypoi-base</artifactId><version>4.1.0</version></dependency><dependency><groupId>cn.afterturn</groupId><artifactId>easypoi-web</artifactId><version>4.1.0</version></dependency><dependency><groupId>cn.afterturn</groupId><artifactId>easypoi-annotation</artifactId><version>4.1.0</version></dependency>

2. 创建实体注解类

@Data
@NoArgsConstructor
@AllArgsConstructor
public class UserEntity {@Excel(name = "姓名")private String name;@Excel(name = "年龄")private int age;@Excel(name = "操作时间",format="yyyy-MM-dd HH:mm:ss", width = 20.0)private Date time;
}

3. 导出业务

public static void main(String[] args) throws Exception {List<UserEntity> dataList = new ArrayList<>();for (int i = 0; i < 10; i++) {UserEntity userEntity = new UserEntity();userEntity.setName("李四" + i);userEntity.setAge(30 + i);userEntity.setTime(new Date(System.currentTimeMillis() + i));dataList.add(userEntity);}//生成excel文档Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("用户","用户信息"),UserEntity.class, dataList);FileOutputStream fos = new FileOutputStream("D:/easypoi-user1.xls");workbook.write(fos);fos.close();
}

4. 导入业务

public static void main(String[] args) {ImportParams params = new ImportParams();params.setTitleRows(1);params.setHeadRows(1);long start = new Date().getTime();List<Map<String, Object>> list = ExcelImportUtil.importExcel(new File("D:/easypoi-user2.xls"),Map.class, params);System.out.println(new Date().getTime() - start);System.out.println(JSONArray.toJSONString(list));
}

更多操作方法，请查阅easypoi指导手册，里面的介绍很详细，操作表格相当灵活多变。

三、easyExcel实例

1. jar包引入

!-- poi操作excel --><dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>2.2.10</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-scratchpad</artifactId><version>4.0.1</version></dependency>

2. 创建实体注解类


/*** 演示表-个人基础信息*/
@Data
@NoArgsConstructor
@AllArgsConstructor
public class FirstSheetVO {@ExcelProperty(value = "序号",index = 0)private Integer orderNum;@ExcelProperty(value = "姓名",index = 1)private String name;@ExcelProperty(value = "年龄",index = 2)private Integer age;@ExcelProperty(value = "性别",index = 3)private String gender;@ExcelProperty(value = "职业",index = 4)private String professional;@ExcelProperty(value = "出生日期")private Date birthDate;
}/*** 演示表-个人详细信息*/
@Data
@NoArgsConstructor
@AllArgsConstructor
public class SecondSheetVO {@ExcelProperty(value = "序号",index = 0)private Integer orderNum;@ExcelProperty(value = "学历",index = 1)private String education;@ExcelProperty(value = "工作地点",index = 2)private String workplace;
}

3. 导出业务(多表写入)

easyExcel导入.png

    @GetMapping("/export")public void exportExcel(HttpServletResponse response){String file_name = null;try {file_name = new String("dicom影像描述匹配模板1.0".getBytes(), "ISO-8859-1");response.setContentType("application/vnd.ms-excel");response.setHeader("Content-Disposition","attachment;filename="+file_name+".xlsx");List<FirstSheetVO> firstSheetVOS = new ArrayList<>();FirstSheetVO firstSheetVO = new FirstSheetVO();firstSheetVO.setOrderNum(1);firstSheetVO.setAge(24);firstSheetVO.setBirthDate(new Date());firstSheetVO.setGender("男");firstSheetVO.setProfessional("教师");firstSheetVO.setName("李华");firstSheetVOS.add(firstSheetVO);List<SecondSheetVO> secondSheetVOS = new ArrayList<>();SecondSheetVO secondSheetVO = new SecondSheetVO();secondSheetVO.setOrderNum(1);secondSheetVO.setEducation("本科");secondSheetVO.setWorkplace("广东");secondSheetVOS.add(secondSheetVO);// 表一写入ExcelWriter writer = EasyExcel.write(response.getOutputStream(), FirstSheetVO.class).build();WriteSheet sheet = EasyExcel.writerSheet(0, "基础信息").build();writer.write(firstSheetVOS,sheet);// 表二写入WriteSheet sheet2 = EasyExcel.writerSheet(1, "详细信息").head(SecondSheetVO.class).build();writer.write(secondSheetVOS,sheet2);// 关闭流writer.finish();} catch (Exception e) {e.printStackTrace();}}

控制台执行结果:

表序号：0
表名：Sheet1
{0=1, 1=小明, 2=26, 3=男, 4=教师, 5=1995/11/23}
{0=2, 1=小花, 2=25, 3=女, 4=幼师, 5=1993/8/26}
表序号：1
表名：Sheet2
{0=1, 1=本科, 2=九江}
{0=2, 1=大专, 2=武汉}

4. 导入业务

    @PostMapping("/upload")public String upload(MultipartFile file){try {ExcelReader reader = EasyExcel.read(file.getInputStream()).build();List<ReadSheet> sheets = reader.excelExecutor().sheetList();for (int i = 0; i < sheets.size(); i++) {ReadSheet readSheet = sheets.get(i);System.out.println("表序号：" + readSheet.getSheetNo());System.out.println("表名：" + readSheet.getSheetName());List<Object> objects = EasyExcel.read(file.getInputStream()).sheet(i).doReadSync();objects.forEach(System.out::println);}} catch (IOException e) {log.info(e.getMessage(),e);return "fail";}return "success";}