导出的文件预览如下：
对应的导入操作，也很简单，源码如下：
public static void main(String[] args) { ImportParams params = new ImportParams(); params.setTitleRows(1); params.setHeadRows(1); long start = new Date().getTime(); List<StudentEntity> list = ExcelImportUtil.importExcel(new File("/Users/hello/Documents/easypoi-user1.xls"), UserEntity.class, params); System.out.println(new Date().getTime() - start); System.out.println(JSONArray.toJSONString(list)); }
运行程序，输出结果如下：
[{"age":20,"name":"张三0","time":1616919493000},{"age":21,"name":"张三1","time":1616919493000},{"age":22,"name":"张三2","time":1616919493000},{"age":23,"name":"张三3","time":1616919493000},{"age":24,"name":"张三4","time":1616919493000},{"age":25,"name":"张三5","time":1616919493000},{"age":26,"name":"张三6","time":1616919493000},{"age":27,"name":"张三7","time":1616919493000},{"age":28,"name":"张三8","time":1616919493000},{"age":29,"name":"张三9","time":1616919493000}]3.3、自定义数据结构导出导入
easypoi 同样也支持自定义数据结构导出导入excel。
- 自定义数据导出 excel
public static void main(String[] args) throws Exception { //封装表头 List<ExcelExportEntity> entityList = new ArrayList<ExcelExportEntity>(); entityList.add(new ExcelExportEntity("姓名", "name")); entityList.add(new ExcelExportEntity("年龄", "age")); ExcelExportEntity entityTime = new ExcelExportEntity("操作时间", "time"); entityTime.setFormat("yyyy-MM-dd HH:mm:ss"); entityTime.setWidth(20.0); entityList.add(entityTime); //封装数据体 List<Map<String, Object>> dataList = new ArrayList<>(); for (int i = 0; i < 10; i++) { Map<String, Object> userEntityMap = new HashMap<>(); userEntityMap.put("name", "张三" + i); userEntityMap.put("age", 20 + i); userEntityMap.put("time", new Date(System.currentTimeMillis() + i)); dataList.add(userEntityMap); } //生成excel文档 Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("学生","用户信息"), entityList, dataList); FileOutputStream fos = new FileOutputStream("/Users/panzhi/Documents/easypoi-user2.xls"); workbook.write(fos); fos.close(); }
- 导入 excel
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("/Users/panzhi/Documents/easypoi-user2.xls"), Map.class, params); System.out.println(new Date().getTime() - start); System.out.println(JSONArray.toJSONString(list)); }
更多的 api 操作可以访问 Easypoi - 接口文档
四、easyexcel
easyexcel 是阿里巴巴开源的一款 excel 解析工具，底层逻辑也是基于 apache poi 进行二次开发的。不同的是，再读写数据的时候，采用 sax 模式一行一行解析，在并发量很大的情况下，依然能稳定运行！
下面，我们就一起来了解一下这款新起之秀！
4.1、首先添加依赖包
<dependencies> <dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>2.2.6</version> </dependency> <!--常用工具库--> <dependency> <groupId>com.google.guava</groupId> <artifactId>guava</artifactId> <version>29.0-jre</version> </dependency> </dependencies>
4.2、采用注解导出导入
easyexcel 同样也支持采用注解方式进行导出、导入！
首先，我们创建一个实体类UserEntity，其中@ExcelProperty注解表示导出文件的头部信息。
public class UserEntity { @ExcelProperty(value = "姓名") private String name; @ExcelProperty(value = "年龄") private int age; @DateTimeFormat("yyyy-MM-dd HH:mm:ss") @ExcelProperty(value = "操作时间") private Date time; //set、get省略 }
接着，我们来编写导出服务！
public static void main(String[] args) { List<UserEntity> dataList = new ArrayList<>(); for (int i = 0; i < 10; i++) { UserEntity userEntity = new UserEntity(); userEntity.setName("张三" + i); userEntity.setAge(20 + i); userEntity.setTime(new Date(System.currentTimeMillis() + i)); dataList.add(userEntity); } EasyExcel.write("/Users/hello/Documents/easyexcel-user1.xls", UserEntity.class).sheet("用户信息").doWrite(dataList); }
导出的文件预览如下：
对应的导入操作，也很简单，源码如下：
public static void main(String[] args) { String filePath = "/Users/hello/Documents/easyexcel-user1.xls"; List<DemoData> list = EasyExcel.read(filePath).head(UserEntity.class).sheet().doReadSync(); System.out.println(JSONArray.toJSONString(list)); }
运行程序，输出结果如下：
[{"age":20,"name":"张三0","time":1616920360000},{"age":21,"name":"张三1","time":1616920360000},{"age":22,"name":"张三2","time":1616920360000},{"age":23,"name":"张三3","time":1616920360000},{"age":24,"name":"张三4","time":1616920360000},{"age":25,"name":"张三5","time":1616920360000},{"age":26,"name":"张三6","time":1616920360000},{"age":27,"name":"张三7","time":1616920360000},{"age":28,"name":"张三8","time":1616920360000},{"age":29,"name":"张三9","time":1616920360000}]4.3、自定义数据结构导出导入
easyexcel 同样也支持自定义数据结构导出导入excel。
- 自定义数据导出 excel
public static void main(String[] args) { //表头 List<List<String>> headList = new ArrayList<>(); headList.add(Lists.newArrayList("姓名")); headList.add(Lists.newArrayList("年龄")); headList.add(Lists.newArrayList("操作时间")); //数据体 List<List<Object>> dataList = new ArrayList<>(); for (int i = 0; i < 10; i++) { List<Object> data = new ArrayList<>(); data.add("张三" + i); data.add(20 + i); data.add(new Date(System.currentTimeMillis() + i)); dataList.add(data); } EasyExcel.write("/Users/hello/Documents/easyexcel-user2.xls").head(headList).sheet("用户信息").doWrite(dataList); }
- 导入 excel
public static void main(String[] args) { String filePath = "/Users/panzhi/Documents/easyexcel-user2.xls"; UserDataListener userDataListener = new UserDataListener(); EasyExcel.read(filePath, userDataListener).sheet().doRead(); System.out.println("表头：" + JSONArray.toJSONString(userDataListener.getHeadList())); System.out.println("数据体：" + JSONArray.toJSONString(userDataListener.getDataList())); }
运行程序，输出结果如下：
表头：[{0:"姓名",1:"年龄",2:"操作时间"}] 数据体：[{0:"张三0",1:"20",2:"2021-03-28 16:31:39"},{0:"张三1",1:"21",2:"2021-03-28 16:31:39"},{0:"张三2",1:"22",2:"2021-03-28 16:31:39"},{0:"张三3",1:"23",2:"2021-03-28 16:31:39"},{0:"张三4",1:"24",2:"2021-03-28 16:31:39"},{0:"张三5",1:"25",2:"2021-03-28 16:31:39"},{0:"张三6",1:"26",2:"2021-03-28 16:31:39"},{0:"张三7",1:"27",2:"2021-03-28 16:31:39"},{0:"张三8",1:"28",2:"2021-03-28 16:31:39"},{0:"张三9",1:"29",2:"2021-03-28 16:31:39"}]
更多的 api 操作可以访问 easyexcel - 接口文档
五、小结
总体来说，easypoi和easyexcel都是基于apache poi进行二次开发的。
不同点在于：
1、easypoi 在读写数据的时候，优先是先将数据写入内存，优点是读写性能非常高，但是当数据量很大的时候，会出现oom，当然它也提供了 sax 模式的读写方式，需要调用特定的方法实现。
2、easyexcel 基于sax模式进行读写数据，不会出现oom情况，程序有过高并发场景的验证，因此程序运行比较稳定，相对于 easypoi 来说，读写性能稍慢！
easypoi 与 easyexcel 还有一点区别在于，easypoi 对定制化的导出支持非常的丰富，如果当前的项目需求，并发量不大、数据量也不大，但是需要导出 excel 的文件样式千差万别，那么我推荐你用 easypoi；反之，使用 easyexcel ！