EasyExcel
分别解析的excel表样式(以下模板在代码中都有操作)
- 简单表头模板
- 复杂表头模板
- 填充简单模板
- 填充复杂模板
使用方法:
- 导入依赖:
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.6</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.72</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
- 使用到的实体类
/**
* @ClassName ExcelData
* @author: fangwenjun
* @date: Created in 2021/3/16 16:11
* @description: 简单表格数据封装 和 数据填充
* @version: 1.0
*/
@Data
public class ExcelData {
@ExcelProperty("ID")
private String id;
@ExcelProperty("用户名")
private String userName;
@ExcelProperty("邮箱")
private String mailbox;
@ExcelProperty("性别")
private String gender;
}
/**
* @ClassName WeeklyData
* @author: fangwenjun
* @date: Created in 2021/3/17 14:53
* @description: 周报数据
* @version: 1.0
*/
@Data
public class WeeklyData {
private String weeklyCycle;
private String teamMembers;
private String reporter;
private List<WorkThisWeek> workThisWeeks;
private List<NextWeekPlan> nextWeekPlans;
}
/**
* @ClassName WorkThisWeek
* @author: fangwenjun
* @date: Created in 2021/3/17 15:09
* @description: 本周工作信息
* @version: 1.0
*/
@Data
public class WorkThisWeek {
private String serialNumber;
private String matter;
private String processDescription;
private String flag;
private String otherDescription;
private String relevantPeople;
private String isContinue;
}
/**
* @ClassName NextWeekPlan
* @author: fangwenjun
* @date: Created in 2021/3/17 15:15
* @description: 下周计划
* @version: 1.0
*/
@Data
public class NextWeekPlan {
private String serialNumber;
private String matter;
private String processDescription;
private String relevantPeople;
private String note;
private String helper;
private String completeTime;
}
- 简单使用:
/**
* @ClassName EasyExcelController
* @author: fangwenjun
* @date: Created in 2021/3/16 16:01
* @description:
* @version: 1.0
*/
@RestController
@RequestMapping("/easyExcel")
public class EasyExcelController {
/**
* @Author: FangWenJun
* @Date: 2021/3/17 10:50
* @Description: 解析简单表头模板
* @param: [file]
* @return: org.springframework.http.ResponseEntity<java.lang.String>
*/
@PostMapping("/parseSimple")
public ResponseEntity<String> parseSimpleExcel(MultipartFile file){
try {
EasyExcel.read(file.getInputStream(),new AnalysisEventListener() {
@Override
public void invoke(Object o, AnalysisContext analysisContext) {
System.out.println(o);
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
System.out.println("解析完成");
}
}).sheet().doRead();
} catch (IOException e) {
e.printStackTrace();
}
return ResponseEntity.ok().build();
}
/**
* @Author: FangWenJun
* @Date: 2021/3/17 10:52
* @Description: 下载模板
* @param: [response]
* @return: org.springframework.http.ResponseEntity<java.lang.Void>
*/
@GetMapping("/download")
public ResponseEntity<Void> downLoad(HttpServletResponse response){
// 设置响应类型和编码格式
String fileName = URLEncoder.encode("测试", "utf-8");
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("UTF-8");
response.setHeader("Content-Disposition","attachment;fileName*=UTF-8''"+fileName+".xlsx");
try {
// 根据 实体类字段生成对应模板, 并写入初始数据
EasyExcel.write(response.getOutputStream(),ExcelData.class).sheet("模板").doWrite(initData());
} catch (Exception e) {
e.printStackTrace();
}
return ResponseEntity.ok().build();
}
/**
* @Author: FangWenJun
* @Date: 2021/3/17 14:17
* @Description: 根据简单头模板填充数据并返回客户端 返回的数据字段要跟模板文件中的字段对应
* @param: [response]
* @return: org.springframework.http.ResponseEntity<java.lang.Void>
*/
@GetMapping("/templateSimpleFill")
public ResponseEntity<Void> templateSimpleFill(HttpServletResponse response) {
// 获取模板
InputStream template = this.getClass()
.getClassLoader().getResourceAsStream("excel-template/测试.xlsx");
try {
// 设置 响应参数
String fileName = URLEncoder.encode("测试", "utf-8");
response.setCharacterEncoding("UTF-8");
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");
// 根据initData()方法获取数据 填充指定模板
EasyExcel.write(response.getOutputStream()).withTemplate(template).sheet().doFill(initData());
} catch (IOException e) {
e.printStackTrace();
}
return ResponseEntity.ok().build();
}
/**
* @Author: FangWenJun
* @Date: 2021/3/17 10:58
* @Description: 解析复杂表头模板
* @param: [file]
* @return: org.springframework.http.ResponseEntity<java.lang.Void>
*/
@GetMapping("/parseHard")
public ResponseEntity<Void> parseHardExcel(MultipartFile file) {
try {
// 这里自定义了一个监听器继承 自带的AnalysisEventListener
EasyExcel.read(file.getInputStream(),new EasyExcelListener()).sheet().doRead();
} catch (IOException e) {
e.printStackTrace();
}
return ResponseEntity.ok().build();
}
/**
* @Author: FangWenJun
* @Date: 2021/3/17 13:56
* @Description: 填充简单模板
* 填充功能需要在表格中设置 {字段名}:普通常量 {.字段名}:list变量 多个list要加前缀{前缀.字段名}
* @param: [response]
* @return: org.springframework.http.ResponseEntity<java.lang.Void>
*/
@GetMapping("/simpleTemplateFill")
public ResponseEntity<Void> simpleTemplateFill(HttpServletResponse response) {
// 获取当前项目 resources下模板文件转换成字节输入流
try (InputStream templateInputStream = getClass()
.getClassLoader().getResourceAsStream("excel-template/测试.xlsx");
) {
// 设置响应体相关信息
String fileName = URLEncoder.encode("测试", "utf-8");
response.setCharacterEncoding("UTF-8");
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");
// 设置输出目标和模板
ExcelWriter excelWriter = EasyExcel
.write(response.getOutputStream()).withTemplate(templateInputStream).build();
// 创建 Sheet 开始填充数据
WriteSheet writeSheet = EasyExcel.writerSheet(1).build();
// 注意：forceNewRow 代表在写入list的时候不管list下面有没有空行 都会创建一行，
//然后下面的数据往后移动。默认 是false，会直接使用下一行，
//如果没有则创建。这会把所有数据放到内存，数据量大时会很耗内存
FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
// 填充 list变量 {.字段名}
excelWriter.fill(initWorkTisWeekData(), fillConfig, writeSheet);
Map<String, Object> map = new HashMap<String, Object>(3);
map.put("weeklyCycle", "2021.03.15 - 2021.03.25");
map.put("teamMembers", "fangwenjun");
map.put("reporter", "fangwenjun");
excelWriter.fill(map, writeSheet);
excelWriter.finish();
} catch (IOException e) {
log.error("export excel error", e);
}
return ResponseEntity.ok().build();
}
/**
* @Author: FangWenJun
* @Date: 2021/3/17 13:56
* @Description: 填充复杂模板
* 当模板中有多个集合时, 需要使用前缀标识 {前缀.字段名}
* @param: [response]
* @return: org.springframework.http.ResponseEntity<java.lang.Void>
*/
@GetMapping("/hardTemplateFill")
public ResponseEntity<Void> hardTemplateFill(HttpServletResponse response) {
// 获取当前项目 resources下模板文件转换成字节输入流
try (InputStream templateInputStream = getClass()
.getClassLoader().getResourceAsStream("excel-template/测试.xlsx");
) {
// 设置响应体相关信息
String fileName = URLEncoder.encode("测试", "utf-8");
response.setCharacterEncoding("UTF-8");
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");
// 设置输出目标和模板
ExcelWriter excelWriter = EasyExcel
.write(response.getOutputStream()).withTemplate(templateInputStream).build();
// 创建 Sheet 开始填充数据
WriteSheet writeSheet = EasyExcel.writerSheet(1).build();
// 配置填充方式
FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
// 填充多个集合时, {前缀.字段名} 需要指定集合名
excelWriter.fill(new FillWrapper("WorkThisWeek",initWorkTisWeekData()),fillConfig,writeSheet);
excelWriter.fill(new FillWrapper("NextWeekPlan",initNextWeekPlanData()),fillConfig,writeSheet);
// 填充普通变量 {字段名}
Map<String, Object> map = new HashMap<String, Object>(3);
map.put("weeklyCycle", "2021.03.15 - 2021.03.25");
map.put("teamMembers", "fangwenjun");
map.put("reporter", "fangwenjun");
excelWriter.fill(map, writeSheet);
excelWriter.finish();
} catch (IOException e) {
log.error("export excel error", e);
}
return ResponseEntity.ok().build();
}
// 初始化数据
public static List<ExcelData> initData() {
ArrayList<ExcelData> excelData = new ArrayList<>();
for (int i = 0; i < 5; i++) {
ExcelData data = new ExcelData();
data.setId("1000" + i);
data.setUserName("fangwenjun");
data.setGender("男");
data.setMailbox("fangwenjun1113@163.com");
excelData.add(data);
}
return excelData;
}
public static List<WorkThisWeek> initWorkTisWeekData() {
ArrayList<WorkThisWeek> excelData = new ArrayList<>();
int num = 0;
for (int i = 0; i < 5; i++) {
WorkThisWeek thisWeek = new WorkThisWeek();
thisWeek.setSerialNumber(""+num++);
thisWeek.setIsContinue("是");
thisWeek.setRelevantPeople("fangwenjun");
thisWeek.setFlag("已完成");
thisWeek.setOtherDescription("无");
thisWeek.setProcessDescription("无");
thisWeek.setMatter("无");
excelData.add(thisWeek);
}
return excelData;
}
public static List<NextWeekPlan> initNextWeekPlanData() {
ArrayList<NextWeekPlan> excelData = new ArrayList<>();
for (int i = 0; i < 5; i++) {
NextWeekPlan weekPlan = new NextWeekPlan();
weekPlan.setSerialNumber(""+i);
weekPlan.setCompleteTime("4月1日");
weekPlan.setHelper("/");
weekPlan.setNote("无");
weekPlan.setRelevantPeople("无");
weekPlan.setMatter("无");
weekPlan.setProcessDescription("无");
excelData.add(weekPlan);
}
return excelData;
}
}
- 自定义监听器
/**
* @ClassName EasyExcelListener
* @author: fangwenjun
* @date: Created in 2021/3/17 16:57
* @description: 自定义监听器
* @version: 1.0
*/
public class EasyExcelListener extends AnalysisEventListener<Map<String,String>> {
public boolean flag = true;
// 解析后的数据封装类
WeeklyData weeklyData = new WeeklyData();
ArrayList<WorkThisWeek> weeks = new ArrayList<>();
ArrayList<NextWeekPlan> plans = new ArrayList<>();
public EasyExcelListener() {
}
@Override
public void invoke(Map<String, String> data, AnalysisContext context) {
Integer sheetNo = context.readSheetHolder().getSheetNo();
if (sheetNo == 0) {
// 获取到当前行的索引
Integer rowIndex = context.readRowHolder().getRowIndex();
Map<Integer, Cell> cellMap = context.readRowHolder().getCellMap();
if (rowIndex == 1) {
weeklyData.setWeeklyCycle(cellMap.get(2).toString());
String teamMembers = cellMap.get(4).toString();
String[] split = teamMembers.split(" ");
weeklyData.setTeamMembers(Arrays.asList(split));
weeklyData.setReporter(cellMap.get(6).toString());
}else if (rowIndex >= 4 && flag){
if ("下周计划".equals(cellMap.get(0).toString())){
flag = false;
return;
}
WorkThisWeek workThisWeek = new WorkThisWeek();
workThisWeek.setSerialNumber(cellMap.get(0).toString());
workThisWeek.setMatter(cellMap.get(1).toString());
workThisWeek.setProcessDescription(cellMap.get(2).toString());
workThisWeek.setFlag(cellMap.get(3).toString());
workThisWeek.setOtherDescription(cellMap.get(4).toString());
workThisWeek.setRelevantPeople(cellMap.get(5).toString());
workThisWeek.setIsContinue(cellMap.get(6).toString());
weeks.add(workThisWeek);
}else if (rowIndex > 4 && !"序号".equals(cellMap.get(0).toString())){
NextWeekPlan weekPlan = new NextWeekPlan();
weekPlan.setSerialNumber(cellMap.get(0).toString());
weekPlan.setMatter(cellMap.get(1).toString());
weekPlan.setProcessDescription(cellMap.get(2).toString());
weekPlan.setRelevantPeople(cellMap.get(3).toString());
weekPlan.setNote(cellMap.get(4).toString());
weekPlan.setHelper(cellMap.get(5).toString());
weekPlan.setCompleteTime(cellMap.get(6).toString());
plans.add(weekPlan);
}
}
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
// 解析完成 封装数据
weeklyData.setWorkThisWeeks(weeks);
weeklyData.setNextWeekPlans(plans);
System.out.println("解析完成:"+ weeklyData);
String string = JSON.toJSONString(weeklyData);
System.out.println("解析完成 = " + string);
}
}
总结:
解析简单模板,基本两行代码搞定,复杂模板时就需要具体的进行操作和解析,跟POI操作差不多. 填充时也是一样, 平时使用的一些简单模板很容易, 复杂模板就要下一些功夫. 我现在只是因需求原因简单使用了解. 更深层次的使用还有待研究. 使用时有很多冗余代码需要抽取,我这只是练习一下, 还请路过的大佬多多指点.
2686
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
