前言
由於工作原因,有這種需求,就是把資料庫中的資料匯出成 Excel 表格,同時,也得支援人家用 Excel 表格匯入資料到資料庫。當前專案也是在用 EasyExcel,所以我不得不學啦!
以前學習的過程中,有聽過 EasyExcel 這麼一個東西,不過從來沒用過,所以,正好藉此機會學習,看看如何使用它來實現需求。
在學習 EasyExcel 的這段時間裡,也瞭解到工作中這種匯入匯出的需求還是挺常見的,所以決定記錄下來。
官方文件:https://easyexcel.opensource.alibaba.com/docs/current/
需求
使用者點選匯入按鈕,就能夠上傳 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 開啟後如下:
額,翻車,格式有點點問題,問題不大,這時候就需要一個自定義的攔截器幫我們處理單元格。
自定義攔截器
拿來主義,寫法基本是這樣:
EasyExcel匯出自動適應列寬 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 方法,以二進位制流的方式將資料寫到響應體中。
最後的最後
由本人水平所限,難免有錯誤以及不足之處, 螢幕前的靚仔靚女們
如有發現,懇請指出!
最後,謝謝你看到這裡,謝謝你認真對待我的努力,希望這篇部落格對你有所幫助!
你輕輕地點了個贊,那將在我的心裡世界增添一顆明亮而耀眼的星!