初次邂逅 EasyExcel
前言
由於工作原因，有這種需求，就是把資料庫中的數據導出成 Excel 表格，同時，也得支援人家用 Excel 表格導入數據到資料庫。當前項目也是在用 EasyExcel，所以我不得不學啦！
以前學習的過程中，有聽過 EasyExcel 這麼一個東西，不過從來沒用過，所以，正好藉此機會學習，看看如何使用它來實現需求。
在學習 EasyExcel 的這段時間裡，也了解到工作中這種導入導出的需求還是挺常見的，所以決定記錄下來。
需求
用戶點擊導入按鈕，就能夠上傳 Excel 文件，將 Excel 文件的數據導入到系統中。
用戶勾選目標數據 id，點擊導出按鈕，就能將系統中的數據以 Excel 文件的格式下載到本地。
分析
導入，從用戶的視角來看，就是導入 Excel 文件；從開發者的視角，或者說系統的視角來看，就是讀取用戶的 Excel 文件的數據到系統中（實際上是讀取到電腦的記憶體中），最後將讀取到的數據存儲到資料庫，EasyExcel 在導入的過程中進行了讀操作。
導出，同理，用戶的視角就是導出，開發者的視角就是把系統的數據寫入到用戶的電腦上，即寫操作。
簡而言之，涉及 IO 操作的，視角不同，說法不同（初學IO時就沒搞清楚，為我後續的學習留下了大坑T_T！）。
當然我們也可以把導入說成寫操作，畢竟數據是最終是存儲在系統的資料庫中的，即寫到了系統的資料庫里了。自己別搞混了就行。
準備
本 Demo 使用 Spring Boot 構建，配合 MyBaits Plus，以遊戲數據的導入和導出作為需求；一些工具依賴如下：
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>5.7.10</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.72</version>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-lang3</artifactId>
<version>3.1</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-devtools</artifactId>
<scope>runtime</scope>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
依賴
今天 EasyExcel 主菜，需要加其依賴才能食用~
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.7</version>
</dependency>
配置
server:
port: 4790
spring:
application:
name: easyexcel-demo
datasource:
driver-class-name: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:3306/easy_excel_demo?useUnicode=true&autoReconnect=true&serverTimezone=GMT%2B8&zeroDateTimeBehavior=convertToNull&characterEncoding=utf8
username: root
password: 123456
遊戲實體類
咱們的遊戲類就這些屬性：id、遊戲名稱、價格、uuid，發售日期、創建時間、修改時間
/**
* @author god23bin
* @version 1.0
* @description 遊戲
* @date 2022/10/21 16:51:02
*/
@Data
@TableName("t_game")
public class Game {
@TableId(type = IdType.AUTO)
private Long id;
private String name;
private Double price;
@DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss" ,timezone = "GMT+8")
private Date releaseDate;
@TableField(fill = FieldFill.INSERT)
private String uuid;
@TableField(fill = FieldFill.INSERT)
@DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss" ,timezone = "GMT+8")
private Date gmtCreate;
@TableField(fill = FieldFill.INSERT_UPDATE)
@DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss" ,timezone = "GMT+8")
private Date gmtModified;
}
模型
啥是模型？別慌，先假設需要導入的 Excel 表格長這樣：
那麼這個就是所謂的模型啦，不管是讀還是寫，都需要相對應的對象，所以一般會編寫一個讀對象的類和寫對象的類，當然，如果讀寫的表頭欄位都是一模一樣，直接一個類就可以了，導入導出都用這個類。
讀對象
讀對象-GameImportExcelModel：
/**
* @author god23bin
* @version 1.0
* @description Game 導入的 Excel 數據模型（讀對象）
* @date 2022/10/21 17:18:50
*/
@Data
public class GameImportExcelModel {
private String name;
private Double price;
private Date releaseDate;
}
寫對象
寫對象-GameExportExcelModel：
/**
* @author god23bin
* @version 1.0
* @description Game 導出的 Excel 數據模型（寫對象）
* @date 2022/10/21 17:18:50
*/
@Data
public class GameExportExcelModel {
@ExcelProperty("遊戲ID")
private Long id;
@ExcelProperty("遊戲名")
private String name;
@ExcelProperty("價格")
private Double price;
@ExcelProperty("發售日期")
private Date releaseDate;
}
實現
導入功能
用戶點擊導入按鈕，就能夠上傳 Excel 文件，將 Excel 文件的數據導入到系統中。
前端實現一個上傳文件的按鈕，後端就接收這個文件，讀取這個文件的數據，存儲到資料庫中。
開胃菜-後端
搭個整體的程式碼框架先！
持久層
GameMapper：
@Mapper
public interface GameMapper extends BaseMapper<Game> {
}
業務層
GameService：
/**
* @author god23bin
* @version 1.0
* @description
* @date 2022/11/8 14:36:43
*/
public interface GameService {
/**
* 導入Excel數據到資料庫
* @date 2022/11/8 14:38
* @param file Excel文件
* @return boolean
**/
boolean importExcel(MultipartFile file);
}
GameServiceImpl：
/**
* @author god23bin
* @version 1.0
* @description
* @date 2022/11/8 14:40:08
*/
@Slf4j
@Service
public class GameServiceImpl extends ServiceImpl<GameMapper, Game> implements GameService {
@Resource
private GameMapper gameMapper;
/**
* 導入Excel數據到資料庫
*
* @param file Excel文件
* @return boolean
* @date 2022/11/8 14:38
**/
@Override
public boolean importExcel(MultipartFile file) {
// 這裡就需要用到「讀監聽器」了，需要我們自己實現
return null;
}
}
控制層
GameController：
/**
* @author god23bin
* @version 1.0
* @description
* @date 2022/11/8 14:31:50
*/
@RestController
public class GameController {
@Resource
private GameService gameService;
@PostMapping("/excel/import/game")
public ResponseEntity<String> importExcel(@RequestPart("file") MultipartFile file) {
gameService.importExcel(file);
return new ResponseEntity<>("OK", HttpStatus.OK);
}
}
正餐-讀數據需要用到的監聽器
對於讀取，有一個監聽器需要我們實現，根據文檔的說明，這個監聽器是不可以讓 Spring 來管理的。
有個很重要的點 DemoDataListener 不能被 spring管理，要每次讀取 excel都要 new，然后里面用到 spring 可以構造方法傳進去
所以我們也不需要加上 @Component 註解把這個類作為組件讓 Spring 掃描。直接一個普通的類就行。
具體程式碼如下，需要知道的是：
- 需要繼承 AnalysisEventListener 類，參數化的類型（泛型）為 GameImportExcelModel（讀對象）
GameImportExcelListener：
/**
* @author god23bin
* @version 1.0
* @description
* @date 2022/10/24 08:45:15
*/
@Slf4j
public class GameImportExcelListener extends AnalysisEventListener<GameImportExcelModel> {
/**
* 每隔100條存儲到資料庫，然後清理list ，方便記憶體回收
*/
private static final int BATCH_COUNT = 100;
/**
* 快取的數據
*/
private List<Game> cachedDataList = new ArrayList<>(BATCH_COUNT);
/**
* 每解析一行數據就會執行這個方法
*
* @param data one row value. Is is same as {@link AnalysisContext#readRowHolder()}
* @param context
*/
@Override
public void invoke(GameImportExcelModel data, AnalysisContext context) {
log.info("解析到一條數據:{}", JSON.toJSONString(data));
Game game = new Game();
BeanUtil.copyProperties(data, game);
cachedDataList.add(game);
if (cachedDataList.size() >= BATCH_COUNT) {
saveData();
// 存儲完成清理 list
cachedDataList.clear();
}
}
private void saveData() {
// 這裡寫存儲到資料庫的邏輯程式碼
}
/**
* 解析完之後會執行這個方法，如果有其他事情需要做，可以在這裡加上程式碼來完成
*
* @param context
*/
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
// 這裡也要保存數據，確保最後遺留的數據也存儲到資料庫
saveData();
log.info("所有數據解析完成！");
}
}
但是！如果我們想要使用 Spring IOC 管理對象，比如 Dao、Mapper 這些對象，現在當前類是用不了 @Autowired 註解將這些對象注入的，那我們怎麼獲取它們？
方法就是：在該類中寫一個構造方法，將這些被 Spring 管理的對象作為參數傳入進來！
比如我這裡需要用到 GameMapper 對象，那麼就將它作構造方法的參數傳進來。
@Slf4j
public class GameImportExcelListener extends AnalysisEventListener<GameImportExcelModel> {
// 省略其他程式碼
private GameMapper gameMapper;
public GameImportExcelListener(GameMapper gameMapper) {
this.gameMapper = gameMapper;
}
@Override
public void invoke(GameImportExcelModel data, AnalysisContext context) {
// ...
}
private void saveData() {
// ...
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
// ...
}
}
完整的監聽器程式碼
GameImportExcelListener：
package cn.god23bin.demo.excel.listener;
import cn.god23bin.demo.entity.Game;
import cn.god23bin.demo.excel.bean.GameImportExcelModel;
import cn.god23bin.demo.mapper.GameMapper;
import cn.hutool.core.bean.BeanUtil;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.fastjson.JSON;
import lombok.extern.slf4j.Slf4j;
import java.util.ArrayList;
import java.util.List;
/**
* @author zwb
* @version 1.0
* @description
* @date 2022/10/24 08:45:15
*/
@Slf4j
public class GameImportExcelListener extends AnalysisEventListener<GameImportExcelModel> {
/**
* 每隔100條存儲到資料庫，然後清理list ，方便記憶體回收
*/
private static final int BATCH_COUNT = 100;
/**
* 快取的數據
*/
private List<Game> cachedDataList = new ArrayList<>(BATCH_COUNT);
private GameMapper gameMapper;
public GameImportExcelListener(GameMapper gameMapper) {
this.gameMapper = gameMapper;
}
/**
* 每解析一行數據就會執行這個方法
*
* @param data one row value. Is is same as {@link AnalysisContext#readRowHolder()}
* @param context
*/
@Override
public void invoke(GameImportExcelModel data, AnalysisContext context) {
log.info("解析到一條數據:{}", JSON.toJSONString(data));
Game game = new Game();
BeanUtil.copyProperties(data, game);
cachedDataList.add(game);
if (cachedDataList.size() >= BATCH_COUNT) {
saveData();
// 存儲完成清理 list
cachedDataList.clear();
}
}
private void saveData() {
// 這裡寫存儲到資料庫的邏輯程式碼
for (Game game : cachedDataList) {
gameMapper.insert(game);
}
}
/**
* 解析完之後會執行這個方法，如果有其他事情需要做，可以在這裡加上程式碼來完成
*
* @param context
*/
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
// 這裡也要保存數據，確保最後遺留的數據也存儲到資料庫
saveData();
log.info("所有數據解析完成！");
}
}
完善業務層
- 使用
EasyExcel.read()方法構建一個 Excel reader builder，第一個參數是文件輸入流，第二個參數是讀對象，指定它這個class去讀，第三個參數就是讀監聽器 - 接著鏈式調用
sheet()方法和doRead()方法，完成整個 Excel 的讀取操作。
@Slf4j
@Service
public class GameServiceImpl extends ServiceImpl<GameMapper, Game> implements GameService {
@Resource
private GameMapper gameMapper;
/**
* 導入Excel數據到資料庫
*
* @param file Excel文件
* @return boolean
* @date 2022/11/8 14:38
**/
@Override
public boolean importExcel(MultipartFile file) {
try {
// 使用 EasyExcel.read() 方法構建一個 Excel reader builder，第一個參數是文件輸入流，第二個參數是讀對象，指定它這個class去讀，第三個參數就是讀監聽器
EasyExcel.read(file.getInputStream(), GameImportExcelModel.class, new GameImportExcelListener(gameMapper))
.sheet()
.doRead();
} catch (IOException e) {
log.error("Error importing: {}", e.getMessage());
return false;
}
return true;
}
}
測試
使用 Postman 測試，請求後端的導入 Excel 的介面，在 Postman 中選好 Post 請求並且輸入請求路徑。
點擊 Headers 設置請求頭：
- Key 中輸入 Content-Type，屬性的值輸入
multipart/form-data
點擊 Body 設置請求體：
- 選擇 form-data 格式，Key 中輸入
file，便可以選擇文件進行上傳了
測試結果：
可以看到Excel中的數據成功存儲到資料庫中了，這就完成了導入的功能！
導出功能
用戶勾選目標數據 id，點擊導出按鈕，就能將系統中的數據以 Excel 文件的格式下載到本地。
細節：需要導出的文件名稱為這種格式：遊戲列表-2022-11-11-12-30-00.xlsx
在這個導出文件的場景下，就需要後端返回前端文件數據，後端有兩種方式可以返回，讓前端進行下載。
- 後端返迴文件所在的 URL，前端直接根據 URL 進行下載。
- 後端以二進位流的形式返迴文件流，前端再接受這個流下載到本地。
由於我們的數據是在資料庫中的，並不是直接存儲 Excel 文件的，所以我們以二進位流的形式返迴文件流給前端，讓前端下載。
看看 EasyExcel 的 write 方法簽名，裡面有好多個重載的 write 方法，我們看看這個就行：
public static ExcelWriterBuilder write(OutputStream outputStream, Class head)
參數說明：
- 第一個參數是文件輸出流
- 第二個參數是指定我們要參照哪個模型類去寫這個 Excel，head 意思就是該類的屬性將作為 Excel 的表頭。
程式碼
主要邏輯
// 假設這裡是從資料庫獲取的集合
List<Game> data = ...;
// 文件格式
String fileName = new String("");
String format = "yyyy-MM-dd-HH-mm-ss";
fileName = fileName + DateUtil.format(new Date(), format);
// 將數據寫到輸出流返回給前端
EasyExcel.write(ExcelUtil.getResponseOutputStream(fileName, response), GameExportExcelModel.class)
.sheet("工作簿")
.doWrite(dataList);
Excel 工具類
/**
* @author god23bin
* @version 1.0
* @description Excel 工具類
* @date 2022/11/18 17:55:48
*/
public class ExcelUtil {
/**
* 獲取響應輸出流
* @date 2022/11/18 18:10
* @param fileName 文件名
* @param response 響應
* @return java.io.OutputStream
**/
public static OutputStream getResponseOutputStream(String fileName, HttpServletResponse response) {
try {
// 給文件名編碼，則前端接收後進行解碼
fileName = URLEncoder.encode(fileName, "UTF-8");
// 指定客戶端接收的響應內容類型為Excel以及字元編碼為UTF-8
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf8");
// 讓瀏覽器提供打開、保存的對話框，以附件的形式下載，同時設置文件名格式
response.setHeader("Content-Disposition", "attachment; filename=" + fileName + ".xlsx");
// 禁止快取
response.setHeader("Cache-Control", "no-store");
response.addHeader("Cache-Control", "max-age=0");
return response.getOutputStream();
} catch (IOException e) {
e.printStackTrace();
}
return null;
}
}
控制層
用 Set 集合去重，防止多次查詢同一數據。
@PostMapping("/excel/export/game")
public ResponseEntity<String> exportExcel(@RequestBody Set<String> uuidList, HttpServletResponse response) {
gameService.exportExcel(new ArrayList<>(uuidList), response);
return new ResponseEntity<>("OK", HttpStatus.OK);
}
業務層
主要邏輯是去資料庫查詢出需要導出的數據，然後轉成對應的導出模型對象，最後使用 EasyExcel 的 write() 方法將數據寫到輸出流。
/**
* 導出資料庫記錄到Excel
*
* @param uuidList uuid集合
* @param response 響應
* @return boolean
* @date 2022/11/11 14:23
**/
@Override
public boolean exportExcel(List<String> uuidList, HttpServletResponse response) {
// 根據uuid找到需要導出的記錄集合
LambdaQueryWrapper<Game> achievementWrapper = new LambdaQueryWrapper<>();
achievementWrapper.in(Game::getUuid, uuidList);
achievementWrapper.orderByDesc(Game::getGmtCreate);
List<Game> games = this.baseMapper.selectList(achievementWrapper);
// 將查詢到的數據轉換成對應Excel的導出模型對象
List<GameExportExcelModel> dataList = games.stream().map(game -> {
GameExportExcelModel gameExportExcelModel = new GameExportExcelModel();
BeanUtil.copyProperties(game, gameExportExcelModel);
return gameExportExcelModel;
}).collect(Collectors.toList());
// 文件格式
String fileName = new String("");
String format = "yyyy-MM-dd-HH-mm-ss";
fileName = fileName + DateUtil.format(new Date(), format);
// 將數據寫到輸出流返回給前端
EasyExcel.write(ExcelUtil.getResponseOutputStream(fileName, response), GameExportExcelModel.class)
.sheet("工作簿")
.doWrite(dataList);
return true;
}
測試
以請求體的方式傳遞一個需要導出的遊戲的 uuid 數組
點擊 Send 按鈕旁邊的三角符號，點擊 Send and Download，這樣就可以下載了，最後下載的 Excel 打開後如下：
額，翻車，格式有點點問題，問題不大，這時候就需要一個自定義的攔截器幫我們處理單元格。
自定義攔截器
拿來主義，寫法基本是這樣：
public class CustomCellWriteHandler extends AbstractColumnWidthStyleStrategy {
private static final int MAX_COLUMN_WIDTH = 255;
private Map<Integer, Map<Integer, Integer>> cache = new HashMap<>(16);
public CustomCellWriteHandler() {
}
/**
* Sets the column width when head create
*
* @param writeSheetHolder
* @param cellDataList
* @param cell
* @param head
* @param relativeRowIndex
* @param isHead
*/
@Override
protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);
if (needSetWidth) {
Map<Integer, Integer> maxColumnWidthMap = cache.get(writeSheetHolder.getSheetNo());
if (maxColumnWidthMap == null) {
maxColumnWidthMap = new HashMap<>(16);
cache.put(writeSheetHolder.getSheetNo(), maxColumnWidthMap);
}
Integer columnWidth = this.dataLength(cellDataList, cell, isHead);
if (columnWidth >= 0) {
if (columnWidth > 255) {
columnWidth = 255;
}
Integer maxColumnWidth = maxColumnWidthMap.get(cell.getColumnIndex());
if (maxColumnWidth == null || columnWidth > maxColumnWidth) {
maxColumnWidthMap.put(cell.getColumnIndex(), columnWidth);
writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), 7250);
}
}
}
}
private Integer dataLength(List<CellData> cellDataList, Cell cell, Boolean isHead) {
if (isHead) {
return cell.getStringCellValue().getBytes().length;
} else {
CellData cellData = (CellData) cellDataList.get(0);
CellDataTypeEnum type = cellData.getType();
if (type == null) {
return -1;
} else {
switch (type) {
case STRING:
return cellData.getStringValue().getBytes().length;
case BOOLEAN:
return cellData.getBooleanValue().toString().getBytes().length;
case NUMBER:
return cellData.getNumberValue().toString().getBytes().length;
default:
return -1;
}
}
}
}
}
但是為什麼這樣寫，好吧，目前不了解T_T，有待研究。
接著註冊這個攔截器，讓它知道該如何處理，修改業務層的程式碼：
EasyExcel.write(ExcelUtil.getResponseOutputStream(fileName, response), GameExportExcelModel.class)
.sheet("工作簿")
.registerWriteHandler(new CustomCellWriteHandler())
.doWrite(dataList);
最後導出效果：
總結
某張 Excel 表需要你導入到系統中，這裡系統指的就是你所做的項目，更準確來說將 Excel 的數據插入到你系統中的資料庫里。那麼就可以使用 EasyExcel。
常見的需求就是對 Excel 數據的導入導出，我們需要做的就是根據 Excel 表進行建模，創建對應的讀對象和寫對象。
對於導入，就需要讀對象配合讀監聽器來實現。
對於導出，就直接通過 write 方法，以二進位流的方式將數據寫到響應體中。
最後的最後
由本人水平所限，難免有錯誤以及不足之處， 螢幕前的靚仔靚女們 如有發現，懇請指出！
最後，謝謝你看到這裡，謝謝你認真對待我的努力，希望這篇部落格對你有所幫助！
你輕輕地點了個贊，那將在我的心裡世界增添一顆明亮而耀眼的星！
