1.準(zhǔn)備Read.xlsx（具有兩個(gè)sheet）

2.讀取第一個(gè)sheet中的數(shù)據(jù)

1.模板

@NoArgsConstructor
@AllArgsConstructor
@Data
@Builder
public static class DemoData {// 根據(jù)Excel中指定列名或列的索引讀取@ExcelProperty(value = "字符串標(biāo)題")private String name;@ExcelProperty(value = "日期標(biāo)題")private Date hireDate;@ExcelProperty(value = "數(shù)字標(biāo)題")private Double salary;
}

2.方法

/*** 讀取第一個(gè)sheet中的數(shù)據(jù)*/
@Test
public void testRead01() {// 讀取文件路徑String fileName = "/Users/sunxiansheng/IdeaProjects/demo/easyexcel-demo/src/main/resources/Read.xlsx";// 存儲(chǔ)解析的數(shù)據(jù)List<DemoData> demoDataArrayList = new ArrayList<>();// 1.創(chuàng)建readExcelReader excelReader = EasyExcel.read(fileName).build();// 2.創(chuàng)建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("解析完成一個(gè)sheet...");}}).build();// 參數(shù)為可變參數(shù)，可以讀取多個(gè)sheetexcelReader.read(sheetOne);// 必須關(guān)閉流,否則會(huì)出現(xiàn)內(nèi)存泄漏excelReader.finish();// 輸出所有解析的數(shù)據(jù)System.out.println("所有解析的數(shù)據(jù)為:");demoDataArrayList.forEach(System.out::println);
}

3.結(jié)果

3.讀取所有sheet中的數(shù)據(jù)

1.模板

@NoArgsConstructor
@AllArgsConstructor
@Data
@Builder
public static class DemoData {// 根據(jù)Excel中指定列名或列的索引讀取@ExcelProperty(value = "字符串標(biāo)題")private String name;@ExcelProperty(value = "日期標(biāo)題")private Date hireDate;@ExcelProperty(value = "數(shù)字標(biāo)題")private Double salary;
}

2.方法

/*** 讀取所有sheet中的數(shù)據(jù)*/
@Test
public void testRead02() {// 讀取文件路徑String fileName = "/Users/sunxiansheng/IdeaProjects/demo/easyexcel-demo/src/main/resources/Read.xlsx";// 存儲(chǔ)解析的數(shù)據(jù)List<DemoData> demoDataArrayList = new ArrayList<>();// 直接使用EasyExcel讀取excelExcelReader excelReader = EasyExcel.read(fileName, DemoData.class, new AnalysisEventListener<DemoData>() {// 每解析一行數(shù)據(jù),該方法會(huì)被調(diào)用一次@Overridepublic void invoke(DemoData demoData, AnalysisContext analysisContext) {demoDataArrayList.add(demoData);}// 解析完成一個(gè)sheet后被調(diào)用@Overridepublic void doAfterAllAnalysed(AnalysisContext analysisContext) {System.out.println("解析完成一個(gè)sheet...");// 可以將解析的數(shù)據(jù)保存到數(shù)據(jù)庫}}).build();excelReader.readAll(); // 讀所有sheet// 必須關(guān)閉流,否則會(huì)出現(xiàn)內(nèi)存泄漏excelReader.finish();// 輸出所有解析的數(shù)據(jù)System.out.println("所有解析的數(shù)據(jù)為:");demoDataArrayList.forEach(System.out::println);
}

3.結(jié)果

EasyExcel填充

1.簡單填充

1.準(zhǔn)備 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.創(chuàng)建writeExcelWriter excelWriter = EasyExcel.write(filledFileName).withTemplate(templateFileName).build();// 2.創(chuàng)建sheetWriteSheet writeSheet = EasyExcel.writerSheet().sheetNo(0).build();// 3.構(gòu)建數(shù)據(jù)Map<String, Object> data = new HashMap<>();data.put("name", "張三");data.put("age", 20);data.put("birthday", new Date());// 4.填充數(shù)據(jù)excelWriter.fill(data, writeSheet);// 5.關(guān)閉流excelWriter.finish();
}

4.結(jié)果

2.列表填充

1.準(zhǔn)備 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.創(chuàng)建writeExcelWriter excelWriter = EasyExcel.write(filledFileName).withTemplate(templateFileName).build();// 2.創(chuàng)建sheetWriteSheet writeSheet = EasyExcel.writerSheet().sheetNo(0).build();// 3.構(gòu)建數(shù)據(jù)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.填充數(shù)據(jù)excelWriter.fill(data, writeSheet);// 5.關(guān)閉流excelWriter.finish();}

4.結(jié)果

3.組合填充

1.準(zhǔn)備 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.創(chuàng)建writeExcelWriter excelWriter = EasyExcel.write(filledFileName).withTemplate(templateFileName).build();// 2.創(chuàng)建sheetWriteSheet writeSheet = EasyExcel.writerSheet().sheetNo(0).build();// 3.構(gòu)建數(shù)據(jù)// 3.1 構(gòu)建數(shù)據(jù)1Map<String, Object> data1 = new HashMap<>();data1.put("time", new Date());data1.put("num", 2);// 3.2 構(gòu)建數(shù)據(jù)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.填充數(shù)據(jù)excelWriter.fill(data1, fillConfig, writeSheet);excelWriter.fill(data2, fillConfig, writeSheet);// 5.關(guān)閉流excelWriter.finish();
}

4.結(jié)果

4.水平填充

1.準(zhǔn)備 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.創(chuàng)建writeExcelWriter excelWriter = EasyExcel.write(filledFileName).withTemplate(templateFileName).build();// 2.創(chuàng)建sheetWriteSheet writeSheet = EasyExcel.writerSheet().sheetNo(0).build();// 3.構(gòu)建數(shù)據(jù)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.填充數(shù)據(jù)excelWriter.fill(data, fillConfig, writeSheet);// 5.關(guān)閉流excelWriter.finish();}

4.結(jié)果