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.結果