前言
之前寫過一篇極其簡單的excel導入導出,是單個檔案的:
Springboot 最簡單的結合MYSQL資料實作EXCEL表格導出及資料導入_小目标青年的部落格
還寫過一篇單個,多個 excel檔案導出,轉成ZIP包的:
Springboot 導出Excel檔案,多個需要壓縮成 zip 包_小目标青年的部落格
然後:
于是該篇就來了, 指定模闆(自定義)導出資料,就像:
正文
開始實戰:
pom.xml依賴:
<!-- 導入和導出-->
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-base</artifactId>
<version>3.0.3</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-web</artifactId>
<version>3.0.3</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-annotation</artifactId>
<version>3.0.3</version>
</dependency>
我們導出資料的實體類 UserExcelDTO.java
import cn.afterturn.easypoi.excel.annotation.Excel;
/**
* @Author: JCccc
* @Date: 2022-7-14 10:58
* @Description:
*/
public class UserExcelDTO {
@Excel(name = "學号", height = 8, width = 13, isImportField = "true")
private Integer id;
@Excel(name = "姓名", height = 8, width = 13, isImportField = "true")
private String userName;
@Excel(name = "年齡", height = 8, width = 13, isImportField = "true")
private String userAge;
@Override
public String toString() {
return "User{" +
"id=" + id +
", userName='" + userName + '\'' +
", userAge='" + userAge + '\'' +
'}';
}
public UserExcelDTO() {
}
public UserExcelDTO(Integer id, String userName, String userAge) {
this.id = id;
this.userName = userName;
this.userAge = userAge;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
public String getUserAge() {
return userAge;
}
public void setUserAge(String userAge) {
this.userAge = userAge;
}
}
裡面使用了 @Excel注解标記一下相關字段
然後自定義模闆, 注意裡面細節:
每一行資料都是一個對象,都在list 裡面,
是以看到 首個字段 和 末尾最後的字段 是有 括号的 {}:
示例格式:
{{$fe: list t.id
t.userName
t.userAge}}
然後把自定義模闆檔案丢到 靜态資源路徑下:
然後是實作使用自定義模闆,填充list資料導出excel檔案:
/**
* excel導出 清單 指定模闆
*
* @return
*/
@GetMapping(value = "/exportAssignTemplateExcel")
public void opportunityExport(HttpServletResponse response) {
List<UserExcelDTO> exportList = new ArrayList<>();
UserExcelDTO userExcel1=new UserExcelDTO(1001,"JCccc","18");
UserExcelDTO userExcel2=new UserExcelDTO(1002,"ACccc","29");
UserExcelDTO userExcel3=new UserExcelDTO(1003,"GCccc","50");
exportList.add(userExcel1);
exportList.add(userExcel2);
exportList.add(userExcel3);
Map map = Maps.newHashMap();
map.put("list", exportList);
//擷取導出模闆位址
ClassPathResource classPathResource = new ClassPathResource("static/export/template/MyUserTemplate.xlsx");
String path = classPathResource.getPath();
TemplateExportParams templateExportParams1 = new TemplateExportParams(path);
Workbook wb = ExcelExportUtil.exportExcel(templateExportParams1, map);
String time = LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyy_MM_dd_HH_mm_ss"));
String fileName = "使用者資料"+time+".xlsx";
try {
response.setContentType("application/octet-stream;charset=utf-8");
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "utf-8"));
response.flushBuffer();
wb.write(response.getOutputStream());
} catch (IOException e) {
e.printStackTrace();
}
}
代碼簡析:
調用一下接口,看看效果:
excel檔案内容: