EasyExcel（读取操作和填充操作）

1.准备Read.xlsx（具有两个sheet）

2.读取第一个sheet中的数据

1.模板

@NoArgsConstructor
@AllArgsConstructor
@Data
@Builder
public static class DemoData {// 根据Excel中指定列名或列的索引读取@ExcelProperty(value = "字符串标题")private String name;@ExcelProperty(value = "日期标题")private Date hireDate;@ExcelProperty(value = "数字标题")private Double salary;
}

2.方法

/*** 读取第一个sheet中的数据*/
@Test
public void testRead01() {// 读取文件路径String fileName = "/Users/sunxiansheng/IdeaProjects/demo/easyexcel-demo/src/main/resources/Read.xlsx";// 存储解析的数据List<DemoData> demoDataArrayList = new ArrayList<>();// 1.创建readExcelReader excelReader = EasyExcel.read(fileName).build();// 2.创建sheetReadSheet sheetOne = EasyExcel.readSheet(0).head(DemoData.class).registerReadListener(new AnalysisEventListener<DemoData>() {@Overridepublic void invoke(DemoData demoData, AnalysisContext analysisContext) {demoDataArrayList.add(demoData);}@Overridepublic void doAfterAllAnalysed(AnalysisContext analysisContext) {System.out.println("解析完成一个sheet...");}}).build();// 参数为可变参数，可以读取多个sheetexcelReader.read(sheetOne);// 必须关闭流,否则会出现内存泄漏excelReader.finish();// 输出所有解析的数据System.out.println("所有解析的数据为:");demoDataArrayList.forEach(System.out::println);
}

3.结果

3.读取所有sheet中的数据

1.模板

@NoArgsConstructor
@AllArgsConstructor
@Data
@Builder
public static class DemoData {// 根据Excel中指定列名或列的索引读取@ExcelProperty(value = "字符串标题")private String name;@ExcelProperty(value = "日期标题")private Date hireDate;@ExcelProperty(value = "数字标题")private Double salary;
}

2.方法

/*** 读取所有sheet中的数据*/
@Test
public void testRead02() {// 读取文件路径String fileName = "/Users/sunxiansheng/IdeaProjects/demo/easyexcel-demo/src/main/resources/Read.xlsx";// 存储解析的数据List<DemoData> demoDataArrayList = new ArrayList<>();// 直接使用EasyExcel读取excelExcelReader excelReader = EasyExcel.read(fileName, DemoData.class, new AnalysisEventListener<DemoData>() {// 每解析一行数据,该方法会被调用一次@Overridepublic void invoke(DemoData demoData, AnalysisContext analysisContext) {demoDataArrayList.add(demoData);}// 解析完成一个sheet后被调用@Overridepublic void doAfterAllAnalysed(AnalysisContext analysisContext) {System.out.println("解析完成一个sheet...");// 可以将解析的数据保存到数据库}}).build();excelReader.readAll(); // 读所有sheet// 必须关闭流,否则会出现内存泄漏excelReader.finish();// 输出所有解析的数据System.out.println("所有解析的数据为:");demoDataArrayList.forEach(System.out::println);
}

3.结果

EasyExcel填充

1.简单填充

1.准备 Fill01.xlsx

2.无模版

3.方法

/*** 简单填充*/
@Test
public void testFill01() {// 选择要填充的模板String templateFileName = "/Users/sunxiansheng/IdeaProjects/demo/easyexcel-demo/src/main/resources/Fill01.xlsx";// 选择填充后的文件String filledFileName = "/Users/sunxiansheng/IdeaProjects/demo/easyexcel-demo/src/main/resources/Fill01_end.xlsx";// 1.创建writeExcelWriter excelWriter = EasyExcel.write(filledFileName).withTemplate(templateFileName).build();// 2.创建sheetWriteSheet writeSheet = EasyExcel.writerSheet().sheetNo(0).build();// 3.构建数据Map<String, Object> data = new HashMap<>();data.put("name", "张三");data.put("age", 20);data.put("birthday", new Date());// 4.填充数据excelWriter.fill(data, writeSheet);// 5.关闭流excelWriter.finish();
}

4.结果

2.列表填充

1.准备 Fill02.xlsx

2.模板

    @Data@Builder@NoArgsConstructor@AllArgsConstructorpublic static class Fill02 {private String name;private Double number;}

3.方法

    /*** 列表填充*/@Testpublic void testFill02() {// 选择要填充的模板String templateFileName = "/Users/sunxiansheng/IdeaProjects/demo/easyexcel-demo/src/main/resources/Fill02.xlsx";// 选择填充后的文件String filledFileName = "/Users/sunxiansheng/IdeaProjects/demo/easyexcel-demo/src/main/resources/Fill02_end.xlsx";// 1.创建writeExcelWriter excelWriter = EasyExcel.write(filledFileName).withTemplate(templateFileName).build();// 2.创建sheetWriteSheet writeSheet = EasyExcel.writerSheet().sheetNo(0).build();// 3.构建数据List<Fill02> data = new ArrayList<>();data.add(Fill02.builder().name("张三").number(100.0).build());data.add(Fill02.builder().name("李四").number(200.0).build());data.add(Fill02.builder().name("王五").number(300.0).build());// 4.填充数据excelWriter.fill(data, writeSheet);// 5.关闭流excelWriter.finish();}

4.结果

3.组合填充

1.准备 Fill03.xlsx

2.模板

    @Data@Builder@NoArgsConstructor@AllArgsConstructorpublic static class Fill03 {private String name;private Integer age;}

3.方法

/*** 组合填充*/
@Test
public void testFill03() {// 选择要填充的模板String templateFileName = "/Users/sunxiansheng/IdeaProjects/demo/easyexcel-demo/src/main/resources/Fill03.xlsx";// 选择填充后的文件String filledFileName = "/Users/sunxiansheng/IdeaProjects/demo/easyexcel-demo/src/main/resources/Fill03_end.xlsx";// 1.创建writeExcelWriter excelWriter = EasyExcel.write(filledFileName).withTemplate(templateFileName).build();// 2.创建sheetWriteSheet writeSheet = EasyExcel.writerSheet().sheetNo(0).build();// 3.构建数据// 3.1 构建数据1Map<String, Object> data1 = new HashMap<>();data1.put("time", new Date());data1.put("num", 2);// 3.2 构建数据2List<Fill03> data2 = new ArrayList<>();data2.add(Fill03.builder().name("张三").age(20).build());data2.add(Fill03.builder().name("李四").age(21).build());// 填充配置，换行填充（混合填充必备配置）FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();// 4.填充数据excelWriter.fill(data1, fillConfig, writeSheet);excelWriter.fill(data2, fillConfig, writeSheet);// 5.关闭流excelWriter.finish();
}

4.结果

4.水平填充

1.准备 Fill04.xlsx

2.模板

@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public static class Fill04 {private String name;private Integer age;
}

3.方法

    /*** 水平填充*/@Testpublic void testFill04() {// 选择要填充的模板String templateFileName = "/Users/sunxiansheng/IdeaProjects/demo/easyexcel-demo/src/main/resources/Fill04.xlsx";// 选择填充后的文件String filledFileName = "/Users/sunxiansheng/IdeaProjects/demo/easyexcel-demo/src/main/resources/Fill04_end.xlsx";// 1.创建writeExcelWriter excelWriter = EasyExcel.write(filledFileName).withTemplate(templateFileName).build();// 2.创建sheetWriteSheet writeSheet = EasyExcel.writerSheet().sheetNo(0).build();// 3.构建数据List<Fill04> data = new ArrayList<>();data.add(Fill04.builder().name("张三").age(20).build());data.add(Fill04.builder().name("李四").age(21).build());// 填充配置，水平填充FillConfig fillConfig = FillConfig.builder().direction(WriteDirectionEnum.HORIZONTAL).build();// 4.填充数据excelWriter.fill(data, fillConfig, writeSheet);// 5.关闭流excelWriter.finish();}

4.结果