引言

在日常開發過程中，Excel 導入是非常常見的場景，而且也有很多開源的項目是針對Excel的讀寫的，如Apache 的poi ，最近用的比較好的還是阿裡的EasyExcel 開源工具。平時我們隻是簡單的讀取檔案并寫入資料庫持久化即可，但是前段時間，産品搞了個需求，需要将導入失敗的資料及原因寫入Excel并下載下傳，那這就有得玩了，廢話不多說，上才藝。

産品需求

導入Excel資料

資料格式校驗

資料合法性校驗（校驗資料庫）

失敗資料提供使用者下載下傳，并支援再次導入

技術選型

https://github.com/alibaba/easyexcel ，Excel 讀取/寫入

https://www.xuxueli.com/xxl-job/ ，做異步處理

需求實作

項目依賴(maven)

<!-- easyexcle -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcle</artifactId>
<version>2.2.6</version>
</dependency>
<!-- xxl job -->
<dependency>
<groupId>com.xuxueli</groupId>
<artifactId>xxl-job-core</artifactId>
<version>${xxl-job.version}</version>
</dependency>

檔案解析

解析導入檔案，擷取檔案資料量，用于判定導入是否走異步導入。

public class EasyExcelUtils {

/**
*
* 解析檔案，擷取最後一行
* @param inputStream 檔案流
* @param sheetNum 讀取excel表格的sheetNum 索引
* @return 總行數
*/
public static Integer lastNum(InputStream inputStream,Integer sheetNum){

Workbook wb = null;
sheetNum = sheetNum == null ? 0 : sheetNum;
try {
wb = WorkbookFactory.create(inputStream);
Sheet sheet = wb.getSheetAt(sheetNum);
CellReference cellReference = new CellReference("A4");
// 處理空行
for (int i = cellReference.getRow();i <= sheet.getLastRowNum();){
// 省略部分代碼
}
return sheet.getLastRowNum();
} catch (Exception e){

}
return 0;
}
}

判定導入資料檔案是否為空，如果為空，将傳回錯誤資訊

@RestController
// 省略其他注解
public class ProjectInfoController {
/**
* 項目資訊導入
*/
@PostMapping("/import")
public R projectInfoImport(MultipartFile file,HttpServletResponse response){
InputStream inputStream = null;
int lastNum = 0;
try {
lastNum = EasyExcelUtils.lastNum(file.getInputStream());
}catch(IOException e){
// 省略部分代碼
}
if (lastNum <= 0 ){
throw CustomExcetpoin(500,"導入檔案資料為空，請重新上傳");
}

}
}

檔案解析拿到導入資料的資料量，與系統配置的檔案導入上限值進行判定，如果大于上限值将走異步處理（異步導入，請檢視異步“異步導入”導入内容）。

@RestController
// 省略其他注解
public class ProjectInfoController {

@Resource
private AsyncExcelService asyncExcelService;
/**
* 項目資訊導入
*/
@PostMapping("/import")
public R projectInfoImport(MultipartFile file,HttpServletResponse response){
InputStream inputStream = null;
int lastNum = 0;
try {
lastNum = EasyExcelUtils.lastNum(file.getInputStream());
}catch(IOException e){
// 省略部分代碼
}
if (lastNum <= 0 ){
throw CustomExcetpoin(500,"導入檔案資料為空，請重新上傳");
}
// 擷取系統配置的導入上限值
Integer importMax = asyncExcelService.asyncProjectImportMax();
if (lastNum > importMax ){
// 達到上限，走異步
asyncExcelService.asyncProjectImport(file,response);
return R.success("資料導入成功，因資料量比較大，已轉為異步導入");
}
// 省略其他代碼
}
}

AsyncExcelService 接口實作

/**
* 異步導出/導入 service 
*/
public interface AsyncExcelService {

/** 預設導入資料上限 **/
Integer DEFAULT_IMPORT_DATA_MAX = 500;

/**
* 擷取最大導入上限值，超過則走異步
*/
Integer getImportMax();

/**
* 異步導入資料
*/
void asyncProjectImport(MultipartFile file,HttpServletResponse response);
}
@Service
// 省略其他注解
public class AsyncExcelServiceImpl implements AsyncExcelService {
@Resource
private IParamtersClient paramtersClient;

@Override
public Integer getImportMax(){
Integer value = getParamVaule("paramName",Integer.class);
return value == null ? DEFAULT_IMPORT_DATA_MAX : value;
}

/**
* 調用架構接口擷取系統參數
*
*/
private <T> T getParamVaule(String name,Class<T> clazz){
CCBHousingUser user = SecureUtil.getUser();
// 省略部分代碼

// 擷取系統配置參數
Parameters parameters = paramtersClient.getParamterByCodeAndOrg(name,user.getOrganizationId());

// 省略部分代碼
}
}

其中，IParamtersClient 屬于架構提供的feign 接口，也可以根據自己的實際場景實作相關邏輯。

資料合法校驗

導入資料檔案解析使用的是alibaba 提供的 EasyExcel 開源工具，我們需要在 EasyExcel 工具的基礎上做一些增強處理，如：導入格式校驗、導入表頭校驗、導入資料格式校驗等，如果發生校驗失敗，将錯誤資訊寫入錯誤報告（excel）輸出到用戶端。

定義easyexcel 導入檔案到列與實體映射關系，将使用到 easyexcel 到@ExcleProperty 注解進行關系綁定

@Data
// 省略其他注解
public class ProjectInfoExcelDTO {
@ExcelProperty(index=0,value="序列号")
private String number;

@ExcelProperty(index=1,value="項目名稱")
private String name;

// 省略其他字段屬性
}

注解 @ExcleProperty 常用屬性

index，與excel檔案中，表頭列的索引位置對應（從0開始）
value，與excel檔案中，表頭列的名稱相對應
converter,指定解析資料時，該列需要使用的資料轉換器，轉換器實作Converter接口

定義校驗錯誤的資料結構類型

@Data
// 省略其他注解
public class ExcelChcekErrDTO<T> {
private T t;

private String errMsg;
}

備注：@Data 屬于 lombok 工具，簡化Bean的封裝，感興趣的同學，可以自行查閱資料。

定義Excel導入校驗傳回的資料VO

@Data
// 省略其他注解
public class ExcelCheckResultVO<T> {

/** 校驗成功的資料 **/
private List<T> successDatas;

/** 校驗失敗的資料 **/
private List<ExcelChcekErrDTO> errData;
}

定義資料解析監聽器EasyExcelListener

@Data
// 省略部分注解
public class EasyExcelListener<T> extends AnalysisEventListener<T> {
// 省略部分代碼
}

定義excel 業務校驗管理器 ExcelCheckManager,需要做業務校驗的（與資料庫比對等）需要實作該接口

public interface ExcelCheckManager<T> {

ExcelCheckResultVO checkImportExcle(List<T> datas);
}

表頭校驗

使用EasyExcelListener 用來監聽資料解析過程，其中，invokHeadMap 方法将在解析完成excel表頭時将被執行

@Data
// 省略部分注解
public class EasyExcelListener<T> extends AnalysisEventListener<T> {
/** excel 對象的反射類 **/
private Class<T> clazz;

private ExcelCheckManager<T> excelCheckManager;

public EasyExcelListener(ExcelCheckManager<T> excelCheckManager,Class<T> clazz){
this.clazz = clazz;
this.excelCheckManager = excelCheckManager;
}

@Override
public void invokHeadMap(Map<Integer,String> headMap,AnalysisContext context){

super.invokHeadMap(headMap,context);
// 反射擷取實體到屬性值
Map<Integer,String> indexNameMap = getIndexNameMap(clazz);
// 将 headMap 與 indexNameMap 進行對比，是否完全比對
Set<Integer> keySet = indexNameMap.keySet();
for (Integer key : keySet ){
if (StringUtils.isEmpty(headMap.get(key)){
throw ExcelAnalysisExcetpion("資料解析錯誤，請傳入正确的excel格式");
}
if (!headMap.get(key).equals(indexNameMap.get(key)){
throw ExcelAnalysisExcetpion("資料解析錯誤，請傳入正确的excel格式");
}
}

}

/**
* 反射擷取解析資料實體的@ExcleProperty 的value
*/
public Map<Integer,String> getIndexNameMap(Class clazz){

Map<Integer,String> result = new HashMap<>();
Field field;
Field[] fields = clazz.getDeclaredFields();

for (int i = 0; i < fields.length; i++){
field = clazz.getDeclaredField(fields[i].getName());
field.setAccessible(true);
ExcelProperty excleProperty = field.getAnnotation(ExcelProperty.class);
if (excelProperty != null){
int index = excleProperty.index();
String[] values = excleProperty.value();
StringBuilder value = new StringBuilder();
for (String v : values ){
value.append(v);
}
result.put(index,value.toString());
}
}

return result;
}

}

資料非空、格式校驗

資料非空校驗、格式校驗，我們将使用hibernate-validator 校驗器進行校驗格式。

定義validator 工具類

@component
public class EasyExcelValidatorHelper {
private static Validtor validtor;

@Autowired
public EasyExcelValidatorHelper(Validtor validtor){
this.EasyExcelValidatroHelper.validtor = validtor;
}
public static <T> String validateEntity(T obj) throws NoSuchFieldException{
StringBuilder result = new StringBuilder();
// 執行校驗
Set<ConstraionViolation<T>> set = validtor.validate(obj,Default.class);
// 組裝結果
if(set != null && !set.isEmpty()){
for (ConstraionViolation<T> cv : set ){
Field declaredField = obj.getClass.getDeclaredField(cv.getPropertiyPath().toString());
ExcelProperty annotation = declaredField.getAnnotation(ExcelProperty.class);
result.append(annotation.value[0]+":"+cv.getMessage()).append(";");
}
}
return result;
}
}

資料格式校驗，使用EasyExcelListener 用來監聽資料解析過程，其中，invok 方法将逐行解析excel資料的時候将被調用

@Data
// 省略部分注解
public class EasyExcelListener<T> extends AnalysisEventListener<T> {

/** 标記是否執行資料解析 **/
private boolean baseMatching = false;

/** 解析成功的資料 **/
private List<T> successList = new ArrayList<>();

/** 解析失敗的資料 **/
private List<ExcelCheckErrDTO<T>> errList = new ArrayList<>();
/** excel 對象的反射類 **/
private Class<T> clazz;

private List<T> list;

private ExcelCheckManager<T> excelCheckManager;

public EasyExcelListener(ExcelCheckManager<T> excelCheckManager,Class<T> clazz){
this.clazz = clazz;
this.excelCheckManager = excelCheckManager;
}

@Override
public void invok(T t,AnalysisContext context){
// 資料解析/轉換完成，标記進入到解析起
baseMatching = true;
String errMsg;
try {
// 調用驗證器驗證資料格式
errMsg = EasyExcelValidatorHelper.validateEntity(t);
}catch(Exception e){
errMsg = "解析資料出錯";
// 省略部分代碼
}
// 校驗不通過
if (!StringUtils.isEmpty(errMsg){
// 将錯誤資料放入錯誤清單中
ExcelChcekErrDTO errDTO = new ExcelChcekErrDTO(t,errMsg);
errList.add(errDTO);
} else{
// 校驗成功
list.add(t);
}
if (list.size() > 1000){
// 業務校驗
ExcelCheckResultVO excelCheckResultVO = excelCheckManager.checkImportExcel(list);
successList.addAll(excelCheckResultVO.getSuccessDatas());
errList.addAll(excelCheckResultVO.getErrDatas());
list.clear();
}
}

/**
* 所有資料解析完成後調用此方法
*/
@Override
public void doAfterAllAnalysed(AnalysisContext context){
ExcelCheckResultVO excelCheckResultVO = excelCheckManager.checkImportExcel(list);
successList.addAll(excelCheckResultVO.getSuccessDatas());
errList.addAll(excelCheckResultVO.getErrDatas());
list.clear();
}

@Override
public void invokHeadMap(Map<Integer,String> headMap,AnalysisContext context){

super.invokHeadMap(headMap,context);
// 反射擷取實體到屬性值
Map<Integer,String> indexNameMap = getIndexNameMap(clazz);
// 将 headMap 與 indexNameMap 進行對比，是否完全比對
Set<Integer> keySet = indexNameMap.keySet();
for (Integer key : keySet ){
if (StringUtils.isEmpty(headMap.get(key)){
throw ExcelAnalysisExcetpion("資料解析錯誤，請傳入正确的excel格式");
}
if（!headMap.get(key).equals(indexNameMap.get(key)）{
throw ExcelAnalysisExcetpion("資料解析錯誤，請傳入正确的excel格式");
}
}

}

/**
* 反射擷取解析資料實體的@ExcleProperty 的value
*/
public Map<Integer,String> getIndexNameMap(Class clazz){

Map<Integer,String> result = new HashMap<>();
Field field;
Field[] fields = clazz.getDeclaredFields();

for (int i = 0; i < fields.length; i++){
field = clazz.getDeclaredField(fields[i].getName());
field.setAccessible(true);
ExcelProperty excleProperty = field.getAnnotation(ExcelProperty.class);
if (excelProperty != null){
int index = excleProperty.index();
String[] values = excleProperty.value();
StringBuilder value = new StringBuilder();
for (String v : values ){
value.append(v);
}
result.put(index,value.toString());
}
}

return result;
}



}

對需要進行校驗對字段添加注解

@Data
// 省略其他注解
public class ProjectInfoExcelDTO {
@ExcelProperty(index=0,value="序列号")
private String number;

@ExcelProperty(index=1,value="項目名稱")
@NotBlank(message = "請填寫項目名稱")
private String name;

// 省略其他字段屬性
}

validator 常用注解傳送門（validator 常用注解）。

EasyExcel 讀取資料，并調用格式校驗

@RestController
// 省略其他注解
public class ProjectInfoController {

@Resource
private AsyncExcelService asyncExcelService;

@Resource
private ProjectInfoService projectInfoService;
/**
* 項目資訊導入
*/
@PostMapping("/import")
public R projectInfoImport(MultipartFile file,HttpServletResponse response){
InputStream inputStream = null;
int lastNum = 0;
try {
lastNum = EasyExcelUtils.lastNum(file.getInputStream());
}catch(IOException e){
// 省略部分代碼
}
if (lastNum <= 0 ){
throw CustomExcetpoin(500,"導入檔案資料為空，請重新上傳");
}
// 擷取系統配置的導入上限值
Integer importMax = asyncExcelService.asyncProjectImportMax();
if (lastNum > importMax ){
// 達到上限，走異步
asyncExcelService.asyncProjectImport(file,response);
return R.success("資料導入成功，因資料量比較大，已轉為異步導入");
}
// 省略部分代碼

// 執行個體資料解析監聽器
EasyExcelListener<ProjectInfoDTO> easyExcleListener = new EasyExcelListener(projectInfoService,ProjectInfoDTO.class);
// 檔案讀取/解析，并注冊監聽器
EasyExcle.read(file.getInputStream(),ProjectInfoDTO.class,easyExcleListener).sheet(1).doRead();
// 擷取錯誤資料
List<ExcelCheckErrDTO<ProjectInfoExcelDTO>> errList = easyExcleListener.getErrList();
// 擷取解析成功到資料
List<ProjectinfoExcelDTO> successList = easyExcleListener.getSuccessList();
// 如果錯誤資料不為空，将錯誤資料寫入到excel檔案，并輸出到浏覽器
// 省略代碼

// 将成功到資料，批量寫入到資料庫中
// 省略代碼


// 省略其他代碼
}
}

ProjectInfoService 聲明與實作，因為需要做業務資料到校驗，是以ProjectInfoService 需要繼承 ExcelCheckManager 驗證管理器

public interface ProjectInfoService extends ExcelCheckManager{

}
@Service
// 省略其他注解
public class ProjectInfoServiceImpl implements ProjectInfoService {
// 省略部分代碼

@Override
public ExcelCheckResultVO checkImportExcel(List<ProjectInfoExcelDTO> datas){
// 省略代碼
}
}

輸出錯誤報告

檔案校驗完成之後，如果沒有完全通過，需要将錯誤對資料以及錯誤資訊通過easyExcel 輸出到用戶端。

@RestController
// 省略其他注解
public class ProjectInfoController {

@Resource
private AsyncExcelService asyncExcelService;

@Resource
private ProjectInfoService projectInfoService;
/**
* 項目資訊導入
*/
@PostMapping("/import")
public R projectInfoImport(MultipartFile file,HttpServletResponse response){
InputStream inputStream = null;
int lastNum = 0;
try {
lastNum = EasyExcelUtils.lastNum(file.getInputStream());
}catch(IOException e){
// 省略部分代碼
}
if (lastNum <= 0 ){
throw CustomExcetpoin(500,"導入檔案資料為空，請重新上傳");
}
// 擷取系統配置的導入上限值
Integer importMax = asyncExcelService.asyncProjectImportMax();
if (lastNum > importMax ){
// 達到上限，走異步
asyncExcelService.asyncProjectImport(file,response);
return R.success("資料導入成功，因資料量比較大，已轉為異步導入");
}
// 省略部分代碼

// 執行個體資料解析監聽器
EasyExcelListener<ProjectInfoDTO> easyExcleListener = new EasyExcelListener(projectInfoService,ProjectInfoDTO.class);
// 檔案讀取/解析，并注冊監聽器
EasyExcle.read(file.getInputStream(),ProjectInfoDTO.class,easyExcleListener).sheet(1).doRead();
// 擷取錯誤資料
List<ExcelCheckErrDTO<ProjectInfoExcelDTO>> errList = easyExcleListener.getErrList();
// 擷取解析成功到資料
List<ProjectinfoExcelDTO> successList = easyExcleListener.getSuccessList();
// 如果錯誤資料不為空，将錯誤資料寫入到excel檔案，并輸出到浏覽器
if (errList.size() > 0 ){
// 省略部分代碼
} 
// 将成功到資料，批量寫入到資料庫中
// 省略代碼


// 省略其他代碼
}
}

異步導入

異步導入操作，将思考幾個問題：

導入檔案存到什麼地方？當一個同步請求結束之後，後續我們想再次拿到該請求到資料，我們應該考慮将檔案放到某一個單獨到地方，提供我們二次使用，比如：自己到檔案伺服器、oss 存儲等，這裡我們使用自己的檔案伺服器。

怎麼異步執行？我們可以使用新啟用一個本地線程去執行我們的操作，不影響目前請求主線程的操作，也是可以的，但是考慮到執行重試問題，我們将使用(#xxl-job)分布式排程系統，進行排程執行任務。

客戶如何檢視任務執行狀态？我們需要提供一個任務執行日志清單，讓使用者可以清晰的看到本次導出的任務是否執行完成/是否存在導入錯誤。

怎麼将錯誤報告輸出給到客戶？我們需要将導入到錯誤報告檔案（excel）上傳至檔案伺服器，提供使用者二次或多次下載下傳使用；同時，需要将檔案資訊儲存至任務執行日志資訊中，為使用者提供下載下傳入口。

定義通用的job handler 父類 AsyncTaskHandler ，所有需要使用xxl-job 發起異步任務和給xxl-job 發起回調，都需要繼承AsyncTaskHandler ，并實作execute 抽象方法。

public abstract class AsyncTaskHandler <T extends AsyncTaskPramsDTO> {

/** xxl-job server 端提供的建立任務接口 uri **/
private final static String JOB_ADMIN_URI = "/outapi/asyn/";

/** 與xxl-job server 通訊的加密密鑰對 **/
@Setter
protected String publicKey;

/**
* xxl-job server 回調對方法 
*/
public abstract ReturnT<String> execute(String params);

/**
* 向xxl-job 發起排程任務 
*/
public JobResponseDTO sendTask(T prams){
prams.setUser(null);

// 省略部分代碼，相關内容，請查詢xxl-job server 端所提供的接口文檔

// 将 params 中的 user 對象儲存至redis 中，xxl-job 接口有長度限制
}

public abstract RedisUtil getRedisUtil();

public abstract JobProperties getJobProperties();

/** 回調方法名稱 **/
public abstract String getHandlerName();
}
定義 AsyncTaskPramsDTO 異步參數實體
@Data
// 省略其他注解
public class AsyncTaskPramsDTO {

private String requestId;

}

資料導出

資料導出功能常指，客戶想将系統中的相關（按照查詢條件篩選）資料通過excel形式儲存到自己本地。在資料導出過程中，需要通過資料篩選條件将資料從系統資料庫中篩選出來，然後通過一定格式（excel導出模版格式）寫入到excel中，最後輸出到用戶端（浏覽器）提供客戶下載下傳儲存到本地。

使用EasyExcel 導入資料，失敗原因資料導出

引言

産品需求

技術選型

需求實作

項目依賴(maven)

檔案解析

資料合法校驗

異步導入

資料導出

繼續閱讀

java leetcode之[數學問題 Easy]168. Excel表列名稱題目大意一、示意圖二、解題思路

qtp-Doexcel函數寫入失敗或提示缺少對象，經驗分享

SQL表中資料按條件批量導出多個Excel檔案

GlobalMapper20如何導入文本類（txt，csv，excel）的坐标資料序：一、如何導入離散的坐标點二、離散的坐标點如何生産互相連接配接的線三、離散的坐标點如何生成範圍面

如何增值你的文檔資産?_

線上轉換pdf和虛拟列印機生成pdf檔案操作攻略

如何在Word中輸入英語音标

VBA實用代碼拾零

vba excel程式設計三日談(1)

EXCEL VBA筆記

使用ffmpeg進行視訊檔案轉換成FLV整理

檔案資料總管右鍵開檔案夾_如何在Windows的檔案資料總管中設定自定義啟動檔案夾...

利用javascript和jxl實作自定義報表的輸出

Excel多行轉多列

Excel如何将小寫轉大寫金額

個項目的開發過程及成員組成