使用EasyExcel處理導入導出 · 碼山有道：Java技術生態

[TOC] 復雜EasyExcel的導入導出。使用方案：Alibaba EasyExcel。 > 官網地址：https://www.yuque.com/easyexcel/doc/easyexcel ## 優點分析 - 可以實現自動按名稱匹配Excel表頭，即使出現了表頭順序變化； - 表頭與實體類中注解不匹配時，不會報錯，較為友好，只是不能匹配的數據會返回null。步驟解釋： 1. 創建接收數據的model或者pojo； 2. 定義一個listener用來監聽解析完成的數據，并完成數據入庫； ## 使用Demo 前提：引用對應的maven。 ``` <easyexcel.version>2.2.6</easyexcel.version> ``` ``` <dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>${easyexcel.version}</version> </dependency> ``` ### 1-定義數據接收對象 ``` import com.alibaba.excel.annotation.ExcelIgnore; import com.alibaba.excel.annotation.ExcelProperty; import com.mrzihan.resc.util.excel.CustomStringIntegerConverter; import lombok.Data; /** * 對應模板：2020年省目錄所有字段導出，共29列 * Description:資源導入映射實體對象 * Author:mrzihan * CreateDate:2018/12/6 * "@ExcelProperty"使用注意事項： * - 屬性名首字母不能大寫，否則取不到值 */ @Data public class ResImportByNamePojo { @ExcelProperty("區域") private String areaName; @ExcelProperty("數源單位") private String supplyDept; @ExcelIgnore private String supplyDeptId; @ExcelProperty("信息資源名稱") private String resTitle; @ExcelProperty("歸集表英文名稱") private String tableName; @ExcelIgnore private String resKeyword; @ExcelProperty("信息資源摘要") private String abstractInfo; @ExcelProperty("所屬系統名稱") private String sysName; @ExcelProperty("信息資源格式") private String resFormat; @ExcelProperty("重點領域分類") private String fieldName; @ExcelProperty("更新頻率") private String updateCycle; @ExcelProperty("修改日期") private String CreateDate; @ExcelProperty("是否引用省級") private String extended; @ExcelProperty("狀態") private String sts; @ExcelProperty("歸集狀態") private String collectSts; @ExcelIgnore private String transType; @ExcelProperty("英文名稱") private String colName; @ExcelProperty("中文名稱") private String colDesc; @ExcelProperty("數據類型") private String colType; @ExcelProperty("字段描述") private String colComment; @ExcelProperty(value = "數據長度",converter = CustomStringIntegerConverter.class) private Integer colLength; @ExcelProperty("默認值") private String colDefaultValue; @ExcelProperty("是否字典項") private String ifDictionary; @ExcelProperty("共享屬性") private String shareType; @ExcelProperty("共享條件") private String shareCondition; @ExcelProperty("開放屬性") private String ifPublic; @ExcelProperty("開放條件") private String publicCondition; @ExcelProperty("是否可為空") private String emptyEnable; @ExcelProperty("是否主鍵") private String ifKey; @ExcelProperty("是否歸集") private String ifAllowGj; } ``` ### 2-定義一個listener用來監聽解析完成的數據定義一個listener用來監聽解析完成的數據，并調用業務service分批次插入數據庫。 ``` import com.alibaba.excel.context.AnalysisContext; import com.alibaba.excel.event.AnalysisEventListener; import com.alibaba.fastjson.JSON; import com.mrzihan.common.exception.ApplicationException; import com.mrzihan.common.exception.DuplicateException; import com.mrzihan.common.model.DeptModel; import com.mrzihan.common.model.UserModel; import com.mrzihan.common.util.ApplicationContext; import com.mrzihan.common.util.DictionaryHelper; import com.mrzihan.resc.constants.BusinessConstans; import com.mrzihan.resc.enums.DictionaryEnum; import com.mrzihan.resc.model.ImportHistory; import com.mrzihan.resc.model.resRegist.RegistFormModel; import com.mrzihan.resc.model.resRegist.ResTransModel; import com.mrzihan.resc.model.resRegist.TransColumnsModel; import com.mrzihan.resc.pojo.ResImportByNamePojo; import com.mrzihan.resc.service.IDdeptService; import com.mrzihan.resc.service.IPersistentObjectService; import com.mrzihan.resc.service.resManager.IResRegistService; import org.apache.commons.lang3.StringUtils; import org.sevenstar.persistent.db.exception.PersistentException; import org.sevenstar.persistent.db.ibatis.ApplicationUtil; import org.sevenstar.persistent.db.ibatis.IbatisDao; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.util.Assert; import java.util.*; /** * 2020年新版省平臺目錄導入功能 * Created by mrzihan on 2020/6/25. * connect to cowboy2014@qq.com */ public class StandardResImportListener extends AnalysisEventListener<ResImportByNamePojo> { private static final Logger logger = LoggerFactory.getLogger(StandardResImportListener.class); public List<ResImportByNamePojo> list = new ArrayList<ResImportByNamePojo>(); //定義一個名字按照資源分條導入 public String importingResName = ""; private static String REGEX_CHINESE = "[\u4e00-\u9fa5]";// 中文正則 ImportHistory history = new ImportHistory(); private final String fileName; private final UserModel user; /** * 假設這個是一個DAO，當然有業務邏輯這個也可以是一個service。當然如果不用存儲這個對象沒用。 */ private final IPersistentObjectService persistent; private final IResRegistService registService; private final IDdeptService deptService; //記錄導入的資源數量 private long resCount = 0; private long validCount = 0; public StandardResImportListener(String fileName) { // 這里是demo，所以隨便new一個。實際使用如果到了spring,請使用下面的有參構造函數 persistent = (IPersistentObjectService) ApplicationUtil.getBean("persistentObjectService"); registService = (IResRegistService) ApplicationUtil.getBean("resRegistService"); deptService = (IDdeptService) ApplicationUtil.getBean("deptService"); user = ApplicationContext.getUserDomain(); this.fileName = fileName; this.init(); } /** * 初始化日志表 */ private void init(){ history.setFile_name(fileName); history.setCreateUser(user); history.setDept_id(user.getDeptDomain().getId()); // history.setData_amount((long) objects.size()); history.setCreate_date(new Date()); history.setSts("N"); history.setValid_amount(0L); history.setResource_amount(0L); history.setType("res"); persistent.insert(history); } /** * 所有數據解析完成了都會來調用 * @param context */ @Override public void doAfterAllAnalysed(AnalysisContext context) { // 這里也要保存數據，確保最后遺留的數據也存儲到數據庫 saveData(); logger.info("所有數據解析完成！"); history.setValid_amount(resCount); history.setResource_amount(validCount); history.setDept_id(user.getDeptDomain().getId()); history.setSts("U"); persistent.update(history); } /** * 加上存儲數據庫 */ private void saveData() { logger.info("{}條數據，開始存儲數據庫！", list.size()); /** 資源導入階段 **/ try { //1- registFormModel轉換 RegistFormModel registFormModel = convertResFormAndSave(); /** 2- 插入資源數據源信息 **/ if (null != registFormModel.getId()){ ResTransModel resTrans = genTransAndSave(registFormModel); //3- 信息項信息存儲 for (ResImportByNamePojo pojo: list){ genColumnsAndSave(pojo, resTrans); } } }catch (Exception e){ String errorMsg = "導入中斷："+e.getMessage(); history.setMessage(errorMsg); persistent.update(history);//此處事務未控制 // 異常增強，中斷執行 throw new ApplicationException(errorMsg); } validCount++; resCount++; //記錄導入日志 logger.info("存儲數據庫成功！"); } /** * 這個每一條數據解析都會來調用 * * @param data * one row value. Is is same as {@link AnalysisContext#readRowHolder()} * @param context */ @Override public void invoke(ResImportByNamePojo data, AnalysisContext context) { logger.info("解析到一條數據:{}", JSON.toJSONString(data)); // 達到BATCH_COUNT了，即完整的識別了一個資源，需要去存儲一次數據庫，防止數據幾萬條數據在內存，容易OOM if (StringUtils.isEmpty(importingResName)){ importingResName = data.getResTitle(); list.add(data); }else if (StringUtils.isNotEmpty(importingResName) && !importingResName.equals(data.getResTitle())){ saveData(); // 存儲完成清理 list list.clear(); importingResName = data.getResTitle(); list.add(data); }else { list.add(data); } } } ``` ### 3-service層業務調用入口 ``` /** * 根據省模板導入 * @param request * @return */ @Override public Map<String, Object> uploadStandardData(HttpServletRequest request) { Map<String, Object> result = new HashMap<>(); CommonsMultipartResolver multipartResolver = new CommonsMultipartResolver(request.getSession().getServletContext()); if (multipartResolver.isMultipart(request)) { MultipartHttpServletRequest multiRequest = (MultipartHttpServletRequest) request; MultipartFile excel = multiRequest.getFile("file"); //版本1：ResImportPojo.class 版本2：ResImportByNamePojo.class StandardResImportListener listener = new StandardResImportListener(excel.getOriginalFilename()); try { EasyExcel.read(excel.getInputStream(), ResImportByNamePojo.class, listener).sheet(0).headRowNumber(1).doRead(); result.put("msg", "格式校驗通過"); } catch (Exception e) { e.printStackTrace(); result.put("msg", e.getMessage()); } } else { result.put("flag", false); result.put("msg", "請上傳正確格式的Excel文件"); return result; } result.put("flag", true); return result; }