【easypoi 一对多导入解决方案】

1.需求

• 把如图的数据导入，
  （1）校验姓名长度不能大于 100
  （2）校验每一行次数 + 费用之和不能大于等于 10
  （3）提示哪一行报错了
• 首先是一个一对多的导入，其次提示哪一行报错使用 ExcelImportUtil.importExcelMore().getFailWorkbook() 方法生成一个问题 excel 到服务器本地，再写一个下载失败文档的接口，让用户下载即可；
• 但是在在导入的时候报错，Cannot add merged region B5:B7 to sheet because it overlaps with an existing merged region (B3:B5).，且在校验每一行次数 + 费用之和不能大于等于 10时只能获取第一行的数据。

2.复现问题

上代码

package com.example.myeasypoi;import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.annotation.ExcelCollection;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.result.ExcelImportResult;
import cn.afterturn.easypoi.excel.entity.result.ExcelVerifyHandlerResult;
import cn.afterturn.easypoi.handler.inter.IExcelDataModel;
import cn.afterturn.easypoi.handler.inter.IExcelModel;
import cn.afterturn.easypoi.handler.inter.IExcelVerifyHandler;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.apache.commons.collections4.CollectionUtils;
import org.hibernate.validator.constraints.Length;
import org.springframework.web.bind.annotation.RestController;import java.io.BufferedInputStream;
import java.io.FileInputStream;
import java.util.Date;
import java.util.List;@RestController
public class ImportController {public static void main(String[] args) throws Exception {BufferedInputStream bis = new BufferedInputStream(new FileInputStream("D:/study/code_source/my-easypoi/src/main/resources/ExcelExportTemplateMyNestedLoop.xlsx"));ImportParams importParams = new ImportParams();importParams.setHeadRows(2);importParams.setNeedVerify(true);importParams.setVerifyHandler(new MyVerifyHandler());ExcelImportResult<Object> result = ExcelImportUtil.importExcelMore(bis, MyPojo.class, importParams);System.out.println(result);}@Datapublic static class MyPojo extends Traffic implements IExcelDataModel, IExcelModel{/*** 行号*/private int rowNum;/*** 错误消息*/private String errorMsg;@Overridepublic String getErrorMsg() {return errorMsg;}@Overridepublic void setErrorMsg(String s) {this.errorMsg =s;}@Overridepublic Integer getRowNum() {return rowNum;}@Overridepublic void setRowNum(Integer rowNum) {this.rowNum = rowNum;}}public static class MyVerifyHandler implements IExcelVerifyHandler<MyPojo> {@Overridepublic ExcelVerifyHandlerResult verifyHandler(MyPojo myPojo) {StringBuilder sb = new StringBuilder();List<TrafficDetail> shareBikes = myPojo.getShareBikes();List<TrafficDetail> subways = myPojo.getSubways();if (CollectionUtils.isNotEmpty(shareBikes)){shareBikes.forEach(shareBike -> {if(getSum(shareBike.getNumber(),shareBike.getCost())>=10){sb.append("共享单车次数和费用之和大于 10");}});}if(CollectionUtils.isNotEmpty(subways)){subways.forEach(subway -> {if(getSum(subway.getNumber(),subway.getCost()) >=10){sb.append("地铁次数和费用之和大于 10");}});}if(sb.length()!= 0){return  new ExcelVerifyHandlerResult(false,sb.toString());}return new ExcelVerifyHandlerResult(true);}private int getSum(Integer a ,Integer b){return (a == null ? 0 : a) + (b == null ? 0 : b);}}@Data@AllArgsConstructor@NoArgsConstructorpublic static class Traffic{@Excel(name = "序号")private Integer id;@Excel(name = "姓名")@Length(max = 100,message = "姓名长度不能大于 100")private String name;@Excel(name = "日期",format = "yyyy-MM-dd")private Date date;@ExcelCollection(name = "共享单车")private List<TrafficDetail> shareBikes;@ExcelCollection(name = "地铁")private List<TrafficDetail> subways;}@Data@AllArgsConstructor@NoArgsConstructorpublic static class TrafficDetail{@Excel(name = "次数")private Integer number;@Excel(name = "费用")private Integer cost;}
}

2.1校验时获取不到一对多中多的完整数据

2.2控制台报错 Cannot add merged region B5:B7 to sheet because it overlaps with an existing merged region (B3:B5).

easypoi 是先校验，再获取值，所以第二个报错在我放开第一个断点后出现。

3.如何解决

第二个问题处理： Cannot add merged region B5:B7 to sheet because it overlaps with an existing merged region (B3:B5).

首先 ImportExcelMore 实际调用的 new ExcelImportService().importExcelByIs(inputstream, pojoClass, params, true);查看代码逻辑，是因为ExcelImportService.removeSuperfluousRows 错误

如何改呢，上代码，就是继承 ExcelImportService 类，重写这部分代码，然后在调研时候直接用自己写的 类；

/*** Copyright 2013-2015 JueYue ([e-mail])* <p>* Licensed under the Apache License, Version 2.0 (the "License"); you may not use this file except* in compliance with the License. You may obtain a copy of the License at* <p>* http://www.apache.org/licenses/LICENSE-2.0* <p>* Unless required by applicable law or agreed to in writing, software distributed under the License* is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express* or implied. See the License for the specific language governing permissions and limitations under* the License.*/
package com.example.myeasypoi;import cn.afterturn.easypoi.excel.annotation.ExcelTarget;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.params.ExcelCollectionParams;
import cn.afterturn.easypoi.excel.entity.params.ExcelImportEntity;
import cn.afterturn.easypoi.excel.entity.result.ExcelImportResult;
import cn.afterturn.easypoi.excel.entity.result.ExcelVerifyHandlerResult;
import cn.afterturn.easypoi.entity.BaseTypeConstants;
import cn.afterturn.easypoi.excel.imports.CellValueService;
import cn.afterturn.easypoi.excel.imports.ExcelImportService;
import cn.afterturn.easypoi.excel.imports.base.ImportBaseService;
import cn.afterturn.easypoi.excel.imports.recursive.ExcelImportForkJoinWork;
import cn.afterturn.easypoi.exception.excel.ExcelImportException;
import cn.afterturn.easypoi.exception.excel.enums.ExcelImportEnum;
import cn.afterturn.easypoi.handler.inter.IExcelDataModel;
import cn.afterturn.easypoi.handler.inter.IExcelModel;
import cn.afterturn.easypoi.util.PoiCellUtil;
import cn.afterturn.easypoi.util.PoiPublicUtil;
import cn.afterturn.easypoi.util.PoiReflectorUtil;
import cn.afterturn.easypoi.util.PoiValidationUtil;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.builder.ReflectionToStringBuilder;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.formula.functions.T;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.util.IOUtils;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;import java.io.*;
import java.lang.reflect.Field;
import java.util.*;
import java.util.concurrent.ForkJoinPool;/*** Excel 导入服务** @author JueYue 2014年6月26日 下午9:20:51*/
@SuppressWarnings({"rawtypes", "unchecked", "hiding"})
public class MyExcelImportService extends ExcelImportService {private final static Logger LOGGER = LoggerFactory.getLogger(MyExcelImportService.class);private CellValueService cellValueServer;private boolean   verifyFail = false;/*** 异常数据styler*/private CellStyle errorCellStyle;private List<Row> successRow;private List<Row> failRow;private List      failCollection;public MyExcelImportService() {successRow = new ArrayList<Row>();failRow = new ArrayList<Row>();failCollection = new ArrayList();this.cellValueServer = new CellValueService();}/**** 向List里面继续添加元素** @param object* @param param* @param row* @param titlemap* @param targetId* @param pictures* @param params*/public void addListContinue(Object object, ExcelCollectionParams param, Row row,Map<Integer, String> titlemap, String targetId,Map<String, PictureData> pictures,ImportParams params, StringBuilder errorMsg) throws Exception {Collection collection = (Collection) PoiReflectorUtil.fromCache(object.getClass()).getValue(object, param.getName());Object entity = PoiPublicUtil.createObject(param.getType(), targetId);if (entity instanceof IExcelDataModel) {((IExcelDataModel) entity).setRowNum(row.getRowNum());}String picId;// 是否需要加上这个对象boolean isUsed = false;for (int i = row.getFirstCellNum(); i < titlemap.size(); i++) {Cell   cell        = row.getCell(i);String titleString = (String) titlemap.get(i);if (param.getExcelParams().containsKey(titleString)) {if (param.getExcelParams().get(titleString).getType() == BaseTypeConstants.IMAGE_TYPE) {picId = row.getRowNum() + "_" + i;saveImage(entity, picId, param.getExcelParams(), titleString, pictures, params);} else {try {saveFieldValue(params, entity, cell, param.getExcelParams(), titleString, row);} catch (ExcelImportException e) {// 如果需要去校验就忽略,这个错误,继续执行if (params.isNeedVerify() && ExcelImportEnum.GET_VALUE_ERROR.equals(e.getType())) {errorMsg.append(" ").append(titleString).append(ExcelImportEnum.GET_VALUE_ERROR.getMsg());}}}isUsed = true;}}if (isUsed) {collection.add(entity);}}/*** 获取key的值,针对不同类型获取不同的值** @author JueYue 2013-11-21*/private String getKeyValue(Cell cell) {Object obj = PoiCellUtil.getCellValue(cell);return obj == null ? null : obj.toString().trim();}/*** 获取保存的真实路径*/private String getSaveUrl(ExcelImportEntity excelImportEntity, Object object) throws Exception {String url = "";if (ExcelImportEntity.IMG_SAVE_PATH.equals(excelImportEntity.getSaveUrl())) {if (excelImportEntity.getMethods() != null&& excelImportEntity.getMethods().size() > 0) {object = getFieldBySomeMethod(excelImportEntity.getMethods(), object);}url = object.getClass().getName().split("\\.")[object.getClass().getName().split("\\.").length - 1];return excelImportEntity.getSaveUrl() + File.separator + url;}return excelImportEntity.getSaveUrl();}private <T> List<T> importExcel(Collection<T> result, Sheet sheet, Class<?> pojoClass,ImportParams params,Map<String, PictureData> pictures) throws Exception {List                           collection      = new ArrayList();Map<String, ExcelImportEntity> excelParams     = new HashMap<>();List<ExcelCollectionParams>    excelCollection = new ArrayList<>();String                         targetId        = null;i18nHandler = params.getI18nHandler();boolean isMap = Map.class.equals(pojoClass);if (!isMap) {Field[]     fileds  = PoiPublicUtil.getClassFields(pojoClass);ExcelTarget etarget = pojoClass.getAnnotation(ExcelTarget.class);if (etarget != null) {targetId = etarget.value();}getAllExcelField(targetId, fileds, excelParams, excelCollection, pojoClass, null, null);}Iterator<Row> rows = sheet.rowIterator();for (int j = 0; j < params.getTitleRows(); j++) {rows.next();}Map<Integer, String> titlemap = getTitleMap(rows, params, excelCollection, excelParams);checkIsValidTemplate(titlemap, excelParams, params, excelCollection);Row    row     = null;Object object  = null;String picId;int    readRow = 1;//跳过无效行for (int i = 0; i < params.getStartRows(); i++) {rows.next();}//判断index 和集合,集合情况默认为第一列if (excelCollection.size() > 0 && params.getKeyIndex() == null) {params.setKeyIndex(0);}int          endRow       = sheet.getLastRowNum() - params.getLastOfInvalidRow();if (params.getReadRows() > 0) {endRow = Math.min(params.getReadRows(), endRow);}if (params.isConcurrentTask()) {ForkJoinPool forkJoinPool = new ForkJoinPool();ExcelImportForkJoinWork task           = new ExcelImportForkJoinWork(params.getStartRows() + params.getHeadRows() + params.getTitleRows(), endRow, sheet, params, pojoClass, this, targetId, titlemap, excelParams);ExcelImportResult       forkJoinResult = forkJoinPool.invoke(task);collection = forkJoinResult.getList();failCollection = forkJoinResult.getFailList();} else {StringBuilder errorMsg;while (rows.hasNext()) {row = rows.next();// Fix 如果row为无效行时候跳出if (row.getRowNum() > endRow) {break;}/* 如果当前行的单元格都是无效的，那就继续下一行 */if (row.getLastCellNum()<0) {continue;}if(isMap && object != null) {((Map) object).put("excelRowNum", row.getRowNum());}errorMsg = new StringBuilder();// 判断是集合元素还是不是集合元素,如果是就继续加入这个集合,不是就创建新的对象// keyIndex 如果为空就不处理,仍然处理这一行if (params.getKeyIndex() != null&& (row.getCell(params.getKeyIndex()) == null|| StringUtils.isEmpty(getKeyValue(row.getCell(params.getKeyIndex()))))&& object != null) {for (ExcelCollectionParams param : excelCollection) {addListContinue(object, param, row, titlemap, targetId, pictures, params, errorMsg);}} else {object = PoiPublicUtil.createObject(pojoClass, targetId);try {Set<Integer> keys = titlemap.keySet();for (Integer cn : keys) {Cell   cell        = row.getCell(cn);String titleString = (String) titlemap.get(cn);if (excelParams.containsKey(titleString) || isMap) {if (excelParams.get(titleString) != null&& excelParams.get(titleString).getType() == BaseTypeConstants.IMAGE_TYPE) {picId = row.getRowNum() + "_" + cn;saveImage(object, picId, excelParams, titleString, pictures,params);} else {try {saveFieldValue(params, object, cell, excelParams, titleString, row);} catch (ExcelImportException e) {// 如果需要去校验就忽略,这个错误,继续执行if (params.isNeedVerify() && ExcelImportEnum.GET_VALUE_ERROR.equals(e.getType())) {errorMsg.append(" ").append(titleString).append(ExcelImportEnum.GET_VALUE_ERROR.getMsg());}}}}}//for (int i = row.getFirstCellNum(), le = titlemap.size(); i < le; i++) {//}if (object instanceof IExcelDataModel) {((IExcelDataModel) object).setRowNum(row.getRowNum());}for (ExcelCollectionParams param : excelCollection) {addListContinue(object, param, row, titlemap, targetId, pictures, params, errorMsg);}if (verifyingDataValidity(object, row, params, isMap, errorMsg)) {collection.add(object);} else {failCollection.add(object);}} catch (ExcelImportException e) {LOGGER.error("excel import error , row num:{},obj:{}", readRow, ReflectionToStringBuilder.toString(object));if (!e.getType().equals(ExcelImportEnum.VERIFY_ERROR)) {throw new ExcelImportException(e.getType(), e);}} catch (Exception e) {LOGGER.error("excel import error , row num:{},obj:{}", readRow, ReflectionToStringBuilder.toString(object));throw new RuntimeException(e);}}readRow++;}}return collection;}/*** 校验数据合法性*/public boolean verifyingDataValidity(Object object, Row row, ImportParams params,boolean isMap, StringBuilder fieldErrorMsg) {boolean isAdd = true;Cell    cell  = null;if (params.isNeedVerify()) {String errorMsg = PoiValidationUtil.validation(object, params.getVerifyGroup());if (StringUtils.isNotEmpty(errorMsg)) {cell = row.createCell(row.getLastCellNum());cell.setCellValue(errorMsg);if (object instanceof IExcelModel) {IExcelModel model = (IExcelModel) object;model.setErrorMsg(errorMsg);}isAdd = false;verifyFail = true;}}if (params.getVerifyHandler() != null) {ExcelVerifyHandlerResult result = params.getVerifyHandler().verifyHandler(object);if (!result.isSuccess()) {if (cell == null) {cell = row.createCell(row.getLastCellNum());}cell.setCellValue((StringUtils.isNoneBlank(cell.getStringCellValue())? cell.getStringCellValue() + "," : "") + result.getMsg());if (object instanceof IExcelModel) {IExcelModel model = (IExcelModel) object;model.setErrorMsg((StringUtils.isNoneBlank(model.getErrorMsg())? model.getErrorMsg() + "," : "") + result.getMsg());}isAdd = false;verifyFail = true;}}if ((params.isNeedVerify() || params.getVerifyHandler() != null) && fieldErrorMsg.length() > 0) {if (object instanceof IExcelModel) {IExcelModel model = (IExcelModel) object;model.setErrorMsg((StringUtils.isNoneBlank(model.getErrorMsg())? model.getErrorMsg() + "," : "") + fieldErrorMsg.toString());}if (cell == null) {cell = row.createCell(row.getLastCellNum());}cell.setCellValue((StringUtils.isNoneBlank(cell.getStringCellValue())? cell.getStringCellValue() + "," : "") + fieldErrorMsg.toString());isAdd = false;verifyFail = true;}if (cell != null) {cell.setCellStyle(errorCellStyle);failRow.add(row);if(isMap) {((Map) object).put("excelErrorMsg", cell.getStringCellValue());}} else {successRow.add(row);}return isAdd;}/*** 获取表格字段列名对应信息*/private Map<Integer, String> getTitleMap(Iterator<Row> rows, ImportParams params,List<ExcelCollectionParams> excelCollection,Map<String, ExcelImportEntity> excelParams) {Map<Integer, String>  titlemap         = new LinkedHashMap<Integer, String>();Iterator<Cell>        cellTitle;String                collectionName   = null;ExcelCollectionParams collectionParams = null;Row                   row              = null;for (int j = 0; j < params.getHeadRows(); j++) {row = rows.next();if (row == null) {continue;}cellTitle = row.cellIterator();while (cellTitle.hasNext()) {Cell   cell  = cellTitle.next();String value = getKeyValue(cell);value = value.replace("\n", "");int i = cell.getColumnIndex();//用以支持重名导入if (StringUtils.isNotEmpty(value)) {if (titlemap.containsKey(i)) {collectionName = titlemap.get(i);collectionParams = getCollectionParams(excelCollection, collectionName);titlemap.put(i, collectionName + "_" + value);} else if (StringUtils.isNotEmpty(collectionName) && collectionParams != null&& collectionParams.getExcelParams().containsKey(collectionName + "_" + value)) {titlemap.put(i, collectionName + "_" + value);} else {collectionName = null;collectionParams = null;}if (StringUtils.isEmpty(collectionName)) {titlemap.put(i, value);}}}}// 处理指定列的情况Set<String> keys = excelParams.keySet();for (String key : keys) {if (key.startsWith("FIXED_")) {String[] arr = key.split("_");titlemap.put(Integer.parseInt(arr[1]), key);}}return titlemap;}/*** 获取这个名称对应的集合信息*/private ExcelCollectionParams getCollectionParams(List<ExcelCollectionParams> excelCollection,String collectionName) {for (ExcelCollectionParams excelCollectionParams : excelCollection) {if (collectionName.equals(excelCollectionParams.getExcelName())) {return excelCollectionParams;}}return null;}/*** Excel 导入 field 字段类型 Integer,Long,Double,Date,String,Boolean*/public ExcelImportResult importExcelByIs(InputStream inputstream, Class<?> pojoClass,ImportParams params, boolean needMore) throws Exception {if (LOGGER.isDebugEnabled()) {LOGGER.debug("Excel import start ,class is {}", pojoClass);}List<T>               result = new ArrayList<T>();ByteArrayOutputStream baos   = new ByteArrayOutputStream();ExcelImportResult     importResult;try {byte[] buffer = new byte[1024];int    len;while ((len = inputstream.read(buffer)) > -1) {baos.write(buffer, 0, len);}baos.flush();InputStream userIs = new ByteArrayInputStream(baos.toByteArray());if (LOGGER.isDebugEnabled()) {LOGGER.debug("Excel clone success");}Workbook book = WorkbookFactory.create(userIs);boolean isXSSFWorkbook = !(book instanceof HSSFWorkbook);if (LOGGER.isDebugEnabled()) {LOGGER.debug("Workbook create success");}importResult = new ExcelImportResult();createErrorCellStyle(book);Map<String, PictureData> pictures;for (int i = params.getStartSheetIndex(); i < params.getStartSheetIndex()+ params.getSheetNum(); i++) {if (LOGGER.isDebugEnabled()) {LOGGER.debug(" start to read excel by is ,startTime is {}", new Date());}if (isXSSFWorkbook) {pictures = PoiPublicUtil.getSheetPictrues07((XSSFSheet) book.getSheetAt(i),(XSSFWorkbook) book);} else {pictures = PoiPublicUtil.getSheetPictrues03((HSSFSheet) book.getSheetAt(i),(HSSFWorkbook) book);}if (LOGGER.isDebugEnabled()) {LOGGER.debug(" end to read excel by is ,endTime is {}", new Date());}result.addAll(importExcel(result, book.getSheetAt(i), pojoClass, params, pictures));if (LOGGER.isDebugEnabled()) {LOGGER.debug(" end to read excel list by sheet ,endTime is {}", new Date());}if (params.isReadSingleCell()) {readSingleCell(importResult, book.getSheetAt(i), params);if (LOGGER.isDebugEnabled()) {LOGGER.debug(" read Key-Value ,endTime is {}", System.currentTimeMillis());}}}if (params.isNeedSave()) {saveThisExcel(params, pojoClass, isXSSFWorkbook, book);}importResult.setList(result);if (needMore) {InputStream successIs = new ByteArrayInputStream(baos.toByteArray());try {Workbook successBook = WorkbookFactory.create(successIs);if (params.isVerifyFileSplit()){importResult.setWorkbook(removeSuperfluousRows(successBook, failRow, params));importResult.setFailWorkbook(removeSuperfluousRows(book, successRow, params));} else {importResult.setWorkbook(book);}importResult.setFailList(failCollection);importResult.setVerifyFail(verifyFail);} finally {successIs.close();}}} finally {IOUtils.closeQuietly(baos);}return importResult;}private Workbook removeSuperfluousRows(Workbook book, List<Row> rowList, ImportParams params) {for (int i = params.getStartSheetIndex(); i < params.getStartSheetIndex()+ params.getSheetNum(); i++) {for (int j = rowList.size() - 1; j >= 0; j--) {if (rowList.get(j).getRowNum() < rowList.get(j).getSheet().getLastRowNum()) {book.getSheetAt(i).shiftRows(rowList.get(j).getRowNum() + 1, rowList.get(j).getSheet().getLastRowNum(), 1);} else if (rowList.get(j).getRowNum() == rowList.get(j).getSheet().getLastRowNum()) {book.getSheetAt(i).createRow(rowList.get(j).getRowNum() + 1);book.getSheetAt(i).shiftRows(rowList.get(j).getRowNum() + 1, rowList.get(j).getSheet().getLastRowNum() + 1, 1);}}}return book;}/*** 按照键值对的方式取得Excel里面的数据*/private void readSingleCell(ExcelImportResult result, Sheet sheet, ImportParams params) {if (result.getMap() == null) {result.setMap(new HashMap<String, Object>());}for (int i = 0; i < params.getTitleRows() + params.getHeadRows() + params.getStartRows(); i++) {getSingleCellValueForRow(result, sheet.getRow(i), params);}for (int i = sheet.getLastRowNum() - params.getLastOfInvalidRow(); i < sheet.getLastRowNum(); i++) {getSingleCellValueForRow(result, sheet.getRow(i), params);}}private void getSingleCellValueForRow(ExcelImportResult result, Row row, ImportParams params) {for (int j = row.getFirstCellNum(), le = row.getLastCellNum(); j < le; j++) {String text = PoiCellUtil.getCellValue(row.getCell(j));if (StringUtils.isNoneBlank(text) && text.endsWith(params.getKeyMark())) {if (result.getMap().containsKey(text)) {if (result.getMap().get(text) instanceof String) {List<String> list = new ArrayList<String>();list.add((String) result.getMap().get(text));result.getMap().put(text, list);}((List) result.getMap().get(text)).add(PoiCellUtil.getCellValue(row.getCell(++j)));} else {result.getMap().put(text, PoiCellUtil.getCellValue(row.getCell(++j)));}}}}/*** 检查是不是合法的模板*/private void checkIsValidTemplate(Map<Integer, String> titlemap,Map<String, ExcelImportEntity> excelParams,ImportParams params,List<ExcelCollectionParams> excelCollection) {if (params.getImportFields() != null) {// 同时校验列顺序if (params.isNeedCheckOrder()) {if (params.getImportFields().length != titlemap.size()) {LOGGER.error("excel列顺序不一致");throw new ExcelImportException(ExcelImportEnum.IS_NOT_A_VALID_TEMPLATE);}int i = 0;for (String title : titlemap.values()) {if (!StringUtils.equals(title, params.getImportFields()[i++])) {LOGGER.error("excel列顺序不一致");throw new ExcelImportException(ExcelImportEnum.IS_NOT_A_VALID_TEMPLATE);}}} else {for (int i = 0, le = params.getImportFields().length; i < le; i++) {if (!titlemap.containsValue(params.getImportFields()[i])) {throw new ExcelImportException(ExcelImportEnum.IS_NOT_A_VALID_TEMPLATE);}}}} else {Collection<ExcelImportEntity> collection = excelParams.values();for (ExcelImportEntity excelImportEntity : collection) {if (excelImportEntity.isImportField()&& !titlemap.containsValue(excelImportEntity.getName())) {LOGGER.error(excelImportEntity.getName() + "必须有,但是没找到");throw new ExcelImportException(ExcelImportEnum.IS_NOT_A_VALID_TEMPLATE);}}for (int i = 0, le = excelCollection.size(); i < le; i++) {ExcelCollectionParams collectionparams = excelCollection.get(i);collection = collectionparams.getExcelParams().values();for (ExcelImportEntity excelImportEntity : collection) {if (excelImportEntity.isImportField() && !titlemap.containsValue(collectionparams.getExcelName() + "_" + excelImportEntity.getName())) {throw new ExcelImportException(ExcelImportEnum.IS_NOT_A_VALID_TEMPLATE);}}}}}/*** 保存字段值(获取值,校验值,追加错误信息)*/public void saveFieldValue(ImportParams params, Object object, Cell cell,Map<String, ExcelImportEntity> excelParams, String titleString,Row row) throws Exception {Object value = cellValueServer.getValue(params.getDataHandler(), object, cell, excelParams,titleString, params.getDictHandler());if (object instanceof Map) {if (params.getDataHandler() != null) {params.getDataHandler().setMapValue((Map) object, titleString, value);} else {((Map) object).put(titleString, value);}} else {setValues(excelParams.get(titleString), object, value);}}/*** @param object* @param picId* @param excelParams* @param titleString* @param pictures* @param params* @throws Exception*/private void saveImage(Object object, String picId, Map<String, ExcelImportEntity> excelParams,String titleString, Map<String, PictureData> pictures,ImportParams params) throws Exception {if (pictures == null) {return;}PictureData image = pictures.get(picId);if (image == null) {return;}byte[] data     = image.getData();String fileName = "pic" + Math.round(Math.random() * 100000000000L);fileName += "." + PoiPublicUtil.getFileExtendName(data);if (excelParams.get(titleString).getSaveType() == 1) {String path     = getSaveUrl(excelParams.get(titleString), object);File   savefile = new File(path);if (!savefile.exists()) {savefile.mkdirs();}savefile = new File(path + File.separator + fileName);FileOutputStream fos = new FileOutputStream(savefile);try {fos.write(data);} finally {IOUtils.closeQuietly(fos);}setValues(excelParams.get(titleString), object,getSaveUrl(excelParams.get(titleString), object) + File.separator + fileName);} else {setValues(excelParams.get(titleString), object, data);}}private void createErrorCellStyle(Workbook workbook) {errorCellStyle = workbook.createCellStyle();Font font = workbook.createFont();font.setColor(Font.COLOR_RED);errorCellStyle.setFont(font);}}

第一个问题处理，校验时获取不到一对多中多的完整数据

• 当我把 第二个问题处理后，第一个问题在校验的时候还是只能获取一对多的第一个，实在没找到源码，也不想找了，就用一个笨方法处理了，就是把读入的流分成两份，一份用 ExcelImportUtil.importExcel() 不校验获取所有数据，一份用 ExcelImportUtil.importExcelMore() 处理，在设置校验类时，用构造方法的方式放入 ExcelImportUtil.importExcel() 的返回值到校验类中。
• 这样技能够校验了，也能过获取完整的数据了。
  

3 完整环境

3.1 ImportController

package com.example.myeasypoi;import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.annotation.ExcelCollection;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.result.ExcelImportResult;
import cn.afterturn.easypoi.excel.entity.result.ExcelVerifyHandlerResult;
import cn.afterturn.easypoi.handler.inter.IExcelDataModel;
import cn.afterturn.easypoi.handler.inter.IExcelModel;
import cn.afterturn.easypoi.handler.inter.IExcelVerifyHandler;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.hibernate.validator.constraints.Length;
import org.springframework.web.bind.annotation.RestController;import java.io.BufferedInputStream;
import java.io.FileInputStream;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.stream.Collectors;@RestController
public class ImportController {public static void main(String[] args) throws Exception {BufferedInputStream bis1 = new BufferedInputStream(new FileInputStream("D:/study/code_source/my-easypoi/src/main/resources/ExcelExportTemplateMyNestedLoop.xlsx"));BufferedInputStream bis2 = new BufferedInputStream(new FileInputStream("D:/study/code_source/my-easypoi/src/main/resources/ExcelExportTemplateMyNestedLoop.xlsx"));//第一份文件,不校验ImportParams importParams1 = new ImportParams();importParams1.setHeadRows(2);List<MyPojo> firstMyPojoList = ExcelImportUtil.importExcel(bis1, MyPojo.class, importParams1);Map<Integer, MyPojo> idMyPojoMap = firstMyPojoList.stream().collect(Collectors.toMap(MyPojo::getId, e -> e));ImportParams importParams2 = new ImportParams();importParams2.setHeadRows(2);importParams2.setNeedVerify(true);importParams2.setVerifyHandler(new MyVerifyHandler(idMyPojoMap));
//        ExcelImportResult<Object> result = ExcelImportUtil.importExcelMore(bis, MyPojo.class, importParams);ExcelImportResult<MyPojo> result = new MyExcelImportService().importExcelByIs(bis2, MyPojo.class, importParams2,true);List<MyPojo> failList = result.getFailList();System.out.println(failList);}@Datapublic static class MyPojo extends Traffic implements IExcelDataModel, IExcelModel{/*** 行号*/private int rowNum;/*** 错误消息*/private String errorMsg;@Overridepublic String getErrorMsg() {return errorMsg;}@Overridepublic void setErrorMsg(String s) {this.errorMsg =s;}@Overridepublic Integer getRowNum() {return rowNum;}@Overridepublic void setRowNum(Integer rowNum) {this.rowNum = rowNum;}}public static class MyVerifyHandler implements IExcelVerifyHandler<MyPojo> {Map<Integer,MyPojo> idMyPojoMap;public MyVerifyHandler(Map<Integer, MyPojo> idMyPojoMap) {this.idMyPojoMap = idMyPojoMap;}@Overridepublic ExcelVerifyHandlerResult verifyHandler(MyPojo myPojo) {myPojo = idMyPojoMap.get(myPojo.getId());StringBuilder sb = new StringBuilder();//校验String name = myPojo.getName();if(StringUtils.isNotEmpty(name) && name.length() > 100){sb.append("姓名长度不能超过 100");}List<TrafficDetail> shareBikes = myPojo.getShareBikes();List<TrafficDetail> subways = myPojo.getSubways();if (CollectionUtils.isNotEmpty(shareBikes)){shareBikes.forEach(shareBike -> {if(getSum(shareBike.getNumber(),shareBike.getCost())>=10){sb.append("共享单车次数和费用之和大于 10");}});}if(CollectionUtils.isNotEmpty(subways)){subways.forEach(subway -> {if(getSum(subway.getNumber(),subway.getCost()) >=10){sb.append("地铁次数和费用之和大于 10");}});}ExcelVerifyHandlerResult excelVerifyHandlerResult;if(sb.length()!= 0){excelVerifyHandlerResult= new ExcelVerifyHandlerResult(false,sb.toString());}else {excelVerifyHandlerResult= new ExcelVerifyHandlerResult(true);}return excelVerifyHandlerResult;}private int getSum(Integer a ,Integer b){return (a == null ? 0 : a) + (b == null ? 0 : b);}}@Data@AllArgsConstructor@NoArgsConstructorpublic static class Traffic{@Excel(name = "序号")private Integer id;@Excel(name = "姓名")@Length(max = 100,message = "姓名长度不能大于 100")private String name;@Excel(name = "日期",format = "yyyy-MM-dd")private Date date;@ExcelCollection(name = "共享单车")private List<TrafficDetail> shareBikes;@ExcelCollection(name = "地铁")private List<TrafficDetail> subways;}@Data@AllArgsConstructor@NoArgsConstructorpublic static class TrafficDetail{@Excel(name = "次数")private Integer number;@Excel(name = "费用")private Integer cost;}
}

3.2 MyExcelImportService

/*** Copyright 2013-2015 JueYue ([e-mail])* <p>* Licensed under the Apache License, Version 2.0 (the "License"); you may not use this file except* in compliance with the License. You may obtain a copy of the License at* <p>* http://www.apache.org/licenses/LICENSE-2.0* <p>* Unless required by applicable law or agreed to in writing, software distributed under the License* is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express* or implied. See the License for the specific language governing permissions and limitations under* the License.*/
package com.example.myeasypoi;import cn.afterturn.easypoi.excel.annotation.ExcelTarget;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.params.ExcelCollectionParams;
import cn.afterturn.easypoi.excel.entity.params.ExcelImportEntity;
import cn.afterturn.easypoi.excel.entity.result.ExcelImportResult;
import cn.afterturn.easypoi.excel.entity.result.ExcelVerifyHandlerResult;
import cn.afterturn.easypoi.entity.BaseTypeConstants;
import cn.afterturn.easypoi.excel.imports.CellValueService;
import cn.afterturn.easypoi.excel.imports.ExcelImportService;
import cn.afterturn.easypoi.excel.imports.base.ImportBaseService;
import cn.afterturn.easypoi.excel.imports.recursive.ExcelImportForkJoinWork;
import cn.afterturn.easypoi.exception.excel.ExcelImportException;
import cn.afterturn.easypoi.exception.excel.enums.ExcelImportEnum;
import cn.afterturn.easypoi.handler.inter.IExcelDataModel;
import cn.afterturn.easypoi.handler.inter.IExcelModel;
import cn.afterturn.easypoi.util.PoiCellUtil;
import cn.afterturn.easypoi.util.PoiPublicUtil;
import cn.afterturn.easypoi.util.PoiReflectorUtil;
import cn.afterturn.easypoi.util.PoiValidationUtil;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.builder.ReflectionToStringBuilder;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.formula.functions.T;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.util.IOUtils;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;import java.io.*;
import java.lang.reflect.Field;
import java.util.*;
import java.util.concurrent.ForkJoinPool;/*** Excel 导入服务** @author JueYue 2014年6月26日 下午9:20:51*/
@SuppressWarnings({"rawtypes", "unchecked", "hiding"})
public class MyExcelImportService extends ExcelImportService {private final static Logger LOGGER = LoggerFactory.getLogger(MyExcelImportService.class);private CellValueService cellValueServer;private boolean   verifyFail = false;/*** 异常数据styler*/private CellStyle errorCellStyle;private List<Row> successRow;private List<Row> failRow;private List      failCollection;public MyExcelImportService() {successRow = new ArrayList<Row>();failRow = new ArrayList<Row>();failCollection = new ArrayList();this.cellValueServer = new CellValueService();}/**** 向List里面继续添加元素** @param object* @param param* @param row* @param titlemap* @param targetId* @param pictures* @param params*/public void addListContinue(Object object, ExcelCollectionParams param, Row row,Map<Integer, String> titlemap, String targetId,Map<String, PictureData> pictures,ImportParams params, StringBuilder errorMsg) throws Exception {Collection collection = (Collection) PoiReflectorUtil.fromCache(object.getClass()).getValue(object, param.getName());Object entity = PoiPublicUtil.createObject(param.getType(), targetId);if (entity instanceof IExcelDataModel) {((IExcelDataModel) entity).setRowNum(row.getRowNum());}String picId;// 是否需要加上这个对象boolean isUsed = false;for (int i = row.getFirstCellNum(); i < titlemap.size(); i++) {Cell   cell        = row.getCell(i);String titleString = (String) titlemap.get(i);if (param.getExcelParams().containsKey(titleString)) {if (param.getExcelParams().get(titleString).getType() == BaseTypeConstants.IMAGE_TYPE) {picId = row.getRowNum() + "_" + i;saveImage(entity, picId, param.getExcelParams(), titleString, pictures, params);} else {try {saveFieldValue(params, entity, cell, param.getExcelParams(), titleString, row);} catch (ExcelImportException e) {// 如果需要去校验就忽略,这个错误,继续执行if (params.isNeedVerify() && ExcelImportEnum.GET_VALUE_ERROR.equals(e.getType())) {errorMsg.append(" ").append(titleString).append(ExcelImportEnum.GET_VALUE_ERROR.getMsg());}}}isUsed = true;}}if (isUsed) {collection.add(entity);}}/*** 获取key的值,针对不同类型获取不同的值** @author JueYue 2013-11-21*/private String getKeyValue(Cell cell) {Object obj = PoiCellUtil.getCellValue(cell);return obj == null ? null : obj.toString().trim();}/*** 获取保存的真实路径*/private String getSaveUrl(ExcelImportEntity excelImportEntity, Object object) throws Exception {String url = "";if (ExcelImportEntity.IMG_SAVE_PATH.equals(excelImportEntity.getSaveUrl())) {if (excelImportEntity.getMethods() != null&& excelImportEntity.getMethods().size() > 0) {object = getFieldBySomeMethod(excelImportEntity.getMethods(), object);}url = object.getClass().getName().split("\\.")[object.getClass().getName().split("\\.").length - 1];return excelImportEntity.getSaveUrl() + File.separator + url;}return excelImportEntity.getSaveUrl();}private <T> List<T> importExcel(Collection<T> result, Sheet sheet, Class<?> pojoClass,ImportParams params,Map<String, PictureData> pictures) throws Exception {List                           collection      = new ArrayList();Map<String, ExcelImportEntity> excelParams     = new HashMap<>();List<ExcelCollectionParams>    excelCollection = new ArrayList<>();String                         targetId        = null;i18nHandler = params.getI18nHandler();boolean isMap = Map.class.equals(pojoClass);if (!isMap) {Field[]     fileds  = PoiPublicUtil.getClassFields(pojoClass);ExcelTarget etarget = pojoClass.getAnnotation(ExcelTarget.class);if (etarget != null) {targetId = etarget.value();}getAllExcelField(targetId, fileds, excelParams, excelCollection, pojoClass, null, null);}Iterator<Row> rows = sheet.rowIterator();for (int j = 0; j < params.getTitleRows(); j++) {rows.next();}Map<Integer, String> titlemap = getTitleMap(rows, params, excelCollection, excelParams);checkIsValidTemplate(titlemap, excelParams, params, excelCollection);Row    row     = null;Object object  = null;String picId;int    readRow = 1;//跳过无效行for (int i = 0; i < params.getStartRows(); i++) {rows.next();}//判断index 和集合,集合情况默认为第一列if (excelCollection.size() > 0 && params.getKeyIndex() == null) {params.setKeyIndex(0);}int          endRow       = sheet.getLastRowNum() - params.getLastOfInvalidRow();if (params.getReadRows() > 0) {endRow = Math.min(params.getReadRows(), endRow);}if (params.isConcurrentTask()) {ForkJoinPool forkJoinPool = new ForkJoinPool();ExcelImportForkJoinWork task           = new ExcelImportForkJoinWork(params.getStartRows() + params.getHeadRows() + params.getTitleRows(), endRow, sheet, params, pojoClass, this, targetId, titlemap, excelParams);ExcelImportResult       forkJoinResult = forkJoinPool.invoke(task);collection = forkJoinResult.getList();failCollection = forkJoinResult.getFailList();} else {StringBuilder errorMsg;while (rows.hasNext()) {row = rows.next();// Fix 如果row为无效行时候跳出if (row.getRowNum() > endRow) {break;}/* 如果当前行的单元格都是无效的，那就继续下一行 */if (row.getLastCellNum()<0) {continue;}if(isMap && object != null) {((Map) object).put("excelRowNum", row.getRowNum());}errorMsg = new StringBuilder();// 判断是集合元素还是不是集合元素,如果是就继续加入这个集合,不是就创建新的对象// keyIndex 如果为空就不处理,仍然处理这一行if (params.getKeyIndex() != null&& (row.getCell(params.getKeyIndex()) == null|| StringUtils.isEmpty(getKeyValue(row.getCell(params.getKeyIndex()))))&& object != null) {for (ExcelCollectionParams param : excelCollection) {addListContinue(object, param, row, titlemap, targetId, pictures, params, errorMsg);}} else {object = PoiPublicUtil.createObject(pojoClass, targetId);try {Set<Integer> keys = titlemap.keySet();for (Integer cn : keys) {Cell   cell        = row.getCell(cn);String titleString = (String) titlemap.get(cn);if (excelParams.containsKey(titleString) || isMap) {if (excelParams.get(titleString) != null&& excelParams.get(titleString).getType() == BaseTypeConstants.IMAGE_TYPE) {picId = row.getRowNum() + "_" + cn;saveImage(object, picId, excelParams, titleString, pictures,params);} else {try {saveFieldValue(params, object, cell, excelParams, titleString, row);} catch (ExcelImportException e) {// 如果需要去校验就忽略,这个错误,继续执行if (params.isNeedVerify() && ExcelImportEnum.GET_VALUE_ERROR.equals(e.getType())) {errorMsg.append(" ").append(titleString).append(ExcelImportEnum.GET_VALUE_ERROR.getMsg());}}}}}//for (int i = row.getFirstCellNum(), le = titlemap.size(); i < le; i++) {//}if (object instanceof IExcelDataModel) {((IExcelDataModel) object).setRowNum(row.getRowNum());}for (ExcelCollectionParams param : excelCollection) {addListContinue(object, param, row, titlemap, targetId, pictures, params, errorMsg);}if (verifyingDataValidity(object, row, params, isMap, errorMsg)) {collection.add(object);} else {failCollection.add(object);}} catch (ExcelImportException e) {LOGGER.error("excel import error , row num:{},obj:{}", readRow, ReflectionToStringBuilder.toString(object));if (!e.getType().equals(ExcelImportEnum.VERIFY_ERROR)) {throw new ExcelImportException(e.getType(), e);}} catch (Exception e) {LOGGER.error("excel import error , row num:{},obj:{}", readRow, ReflectionToStringBuilder.toString(object));throw new RuntimeException(e);}}readRow++;}}return collection;}/*** 校验数据合法性*/public boolean verifyingDataValidity(Object object, Row row, ImportParams params,boolean isMap, StringBuilder fieldErrorMsg) {boolean isAdd = true;Cell    cell  = null;if (params.isNeedVerify()) {String errorMsg = PoiValidationUtil.validation(object, params.getVerifyGroup());if (StringUtils.isNotEmpty(errorMsg)) {cell = row.createCell(row.getLastCellNum());cell.setCellValue(errorMsg);if (object instanceof IExcelModel) {IExcelModel model = (IExcelModel) object;model.setErrorMsg(errorMsg);}isAdd = false;verifyFail = true;}}if (params.getVerifyHandler() != null) {ExcelVerifyHandlerResult result = params.getVerifyHandler().verifyHandler(object);if (!result.isSuccess()) {if (cell == null) {cell = row.createCell(row.getLastCellNum());}cell.setCellValue((StringUtils.isNoneBlank(cell.getStringCellValue())? cell.getStringCellValue() + "," : "") + result.getMsg());if (object instanceof IExcelModel) {IExcelModel model = (IExcelModel) object;model.setErrorMsg((StringUtils.isNoneBlank(model.getErrorMsg())? model.getErrorMsg() + "," : "") + result.getMsg());}isAdd = false;verifyFail = true;}}if ((params.isNeedVerify() || params.getVerifyHandler() != null) && fieldErrorMsg.length() > 0) {if (object instanceof IExcelModel) {IExcelModel model = (IExcelModel) object;model.setErrorMsg((StringUtils.isNoneBlank(model.getErrorMsg())? model.getErrorMsg() + "," : "") + fieldErrorMsg.toString());}if (cell == null) {cell = row.createCell(row.getLastCellNum());}cell.setCellValue((StringUtils.isNoneBlank(cell.getStringCellValue())? cell.getStringCellValue() + "," : "") + fieldErrorMsg.toString());isAdd = false;verifyFail = true;}if (cell != null) {cell.setCellStyle(errorCellStyle);failRow.add(row);if(isMap) {((Map) object).put("excelErrorMsg", cell.getStringCellValue());}} else {successRow.add(row);}return isAdd;}/*** 获取表格字段列名对应信息*/private Map<Integer, String> getTitleMap(Iterator<Row> rows, ImportParams params,List<ExcelCollectionParams> excelCollection,Map<String, ExcelImportEntity> excelParams) {Map<Integer, String>  titlemap         = new LinkedHashMap<Integer, String>();Iterator<Cell>        cellTitle;String                collectionName   = null;ExcelCollectionParams collectionParams = null;Row                   row              = null;for (int j = 0; j < params.getHeadRows(); j++) {row = rows.next();if (row == null) {continue;}cellTitle = row.cellIterator();while (cellTitle.hasNext()) {Cell   cell  = cellTitle.next();String value = getKeyValue(cell);value = value.replace("\n", "");int i = cell.getColumnIndex();//用以支持重名导入if (StringUtils.isNotEmpty(value)) {if (titlemap.containsKey(i)) {collectionName = titlemap.get(i);collectionParams = getCollectionParams(excelCollection, collectionName);titlemap.put(i, collectionName + "_" + value);} else if (StringUtils.isNotEmpty(collectionName) && collectionParams != null&& collectionParams.getExcelParams().containsKey(collectionName + "_" + value)) {titlemap.put(i, collectionName + "_" + value);} else {collectionName = null;collectionParams = null;}if (StringUtils.isEmpty(collectionName)) {titlemap.put(i, value);}}}}// 处理指定列的情况Set<String> keys = excelParams.keySet();for (String key : keys) {if (key.startsWith("FIXED_")) {String[] arr = key.split("_");titlemap.put(Integer.parseInt(arr[1]), key);}}return titlemap;}/*** 获取这个名称对应的集合信息*/private ExcelCollectionParams getCollectionParams(List<ExcelCollectionParams> excelCollection,String collectionName) {for (ExcelCollectionParams excelCollectionParams : excelCollection) {if (collectionName.equals(excelCollectionParams.getExcelName())) {return excelCollectionParams;}}return null;}/*** Excel 导入 field 字段类型 Integer,Long,Double,Date,String,Boolean*/public ExcelImportResult importExcelByIs(InputStream inputstream, Class<?> pojoClass,ImportParams params, boolean needMore) throws Exception {if (LOGGER.isDebugEnabled()) {LOGGER.debug("Excel import start ,class is {}", pojoClass);}List<T>               result = new ArrayList<T>();ByteArrayOutputStream baos   = new ByteArrayOutputStream();ExcelImportResult     importResult;try {byte[] buffer = new byte[1024];int    len;while ((len = inputstream.read(buffer)) > -1) {baos.write(buffer, 0, len);}baos.flush();InputStream userIs = new ByteArrayInputStream(baos.toByteArray());if (LOGGER.isDebugEnabled()) {LOGGER.debug("Excel clone success");}Workbook book = WorkbookFactory.create(userIs);boolean isXSSFWorkbook = !(book instanceof HSSFWorkbook);if (LOGGER.isDebugEnabled()) {LOGGER.debug("Workbook create success");}importResult = new ExcelImportResult();createErrorCellStyle(book);Map<String, PictureData> pictures;for (int i = params.getStartSheetIndex(); i < params.getStartSheetIndex()+ params.getSheetNum(); i++) {if (LOGGER.isDebugEnabled()) {LOGGER.debug(" start to read excel by is ,startTime is {}", new Date());}if (isXSSFWorkbook) {pictures = PoiPublicUtil.getSheetPictrues07((XSSFSheet) book.getSheetAt(i),(XSSFWorkbook) book);} else {pictures = PoiPublicUtil.getSheetPictrues03((HSSFSheet) book.getSheetAt(i),(HSSFWorkbook) book);}if (LOGGER.isDebugEnabled()) {LOGGER.debug(" end to read excel by is ,endTime is {}", new Date());}result.addAll(importExcel(result, book.getSheetAt(i), pojoClass, params, pictures));if (LOGGER.isDebugEnabled()) {LOGGER.debug(" end to read excel list by sheet ,endTime is {}", new Date());}if (params.isReadSingleCell()) {readSingleCell(importResult, book.getSheetAt(i), params);if (LOGGER.isDebugEnabled()) {LOGGER.debug(" read Key-Value ,endTime is {}", System.currentTimeMillis());}}}if (params.isNeedSave()) {saveThisExcel(params, pojoClass, isXSSFWorkbook, book);}importResult.setList(result);if (needMore) {InputStream successIs = new ByteArrayInputStream(baos.toByteArray());try {Workbook successBook = WorkbookFactory.create(successIs);if (params.isVerifyFileSplit()){importResult.setWorkbook(removeSuperfluousRows(successBook, failRow, params));importResult.setFailWorkbook(removeSuperfluousRows(book, successRow, params));} else {importResult.setWorkbook(book);}importResult.setFailList(failCollection);importResult.setVerifyFail(verifyFail);} finally {successIs.close();}}} finally {IOUtils.closeQuietly(baos);}return importResult;}private Workbook removeSuperfluousRows(Workbook book, List<Row> rowList, ImportParams params) {for (int i = params.getStartSheetIndex(); i < params.getStartSheetIndex()+ params.getSheetNum(); i++) {for (int j = rowList.size() - 1; j >= 0; j--) {if (rowList.get(j).getRowNum() < rowList.get(j).getSheet().getLastRowNum()) {book.getSheetAt(i).shiftRows(rowList.get(j).getRowNum() + 1, rowList.get(j).getSheet().getLastRowNum(), 1);} else if (rowList.get(j).getRowNum() == rowList.get(j).getSheet().getLastRowNum()) {book.getSheetAt(i).createRow(rowList.get(j).getRowNum() + 1);book.getSheetAt(i).shiftRows(rowList.get(j).getRowNum() + 1, rowList.get(j).getSheet().getLastRowNum() + 1, 1);}}}return book;}/*** 按照键值对的方式取得Excel里面的数据*/private void readSingleCell(ExcelImportResult result, Sheet sheet, ImportParams params) {if (result.getMap() == null) {result.setMap(new HashMap<String, Object>());}for (int i = 0; i < params.getTitleRows() + params.getHeadRows() + params.getStartRows(); i++) {getSingleCellValueForRow(result, sheet.getRow(i), params);}for (int i = sheet.getLastRowNum() - params.getLastOfInvalidRow(); i < sheet.getLastRowNum(); i++) {getSingleCellValueForRow(result, sheet.getRow(i), params);}}private void getSingleCellValueForRow(ExcelImportResult result, Row row, ImportParams params) {for (int j = row.getFirstCellNum(), le = row.getLastCellNum(); j < le; j++) {String text = PoiCellUtil.getCellValue(row.getCell(j));if (StringUtils.isNoneBlank(text) && text.endsWith(params.getKeyMark())) {if (result.getMap().containsKey(text)) {if (result.getMap().get(text) instanceof String) {List<String> list = new ArrayList<String>();list.add((String) result.getMap().get(text));result.getMap().put(text, list);}((List) result.getMap().get(text)).add(PoiCellUtil.getCellValue(row.getCell(++j)));} else {result.getMap().put(text, PoiCellUtil.getCellValue(row.getCell(++j)));}}}}/*** 检查是不是合法的模板*/private void checkIsValidTemplate(Map<Integer, String> titlemap,Map<String, ExcelImportEntity> excelParams,ImportParams params,List<ExcelCollectionParams> excelCollection) {if (params.getImportFields() != null) {// 同时校验列顺序if (params.isNeedCheckOrder()) {if (params.getImportFields().length != titlemap.size()) {LOGGER.error("excel列顺序不一致");throw new ExcelImportException(ExcelImportEnum.IS_NOT_A_VALID_TEMPLATE);}int i = 0;for (String title : titlemap.values()) {if (!StringUtils.equals(title, params.getImportFields()[i++])) {LOGGER.error("excel列顺序不一致");throw new ExcelImportException(ExcelImportEnum.IS_NOT_A_VALID_TEMPLATE);}}} else {for (int i = 0, le = params.getImportFields().length; i < le; i++) {if (!titlemap.containsValue(params.getImportFields()[i])) {throw new ExcelImportException(ExcelImportEnum.IS_NOT_A_VALID_TEMPLATE);}}}} else {Collection<ExcelImportEntity> collection = excelParams.values();for (ExcelImportEntity excelImportEntity : collection) {if (excelImportEntity.isImportField()&& !titlemap.containsValue(excelImportEntity.getName())) {LOGGER.error(excelImportEntity.getName() + "必须有,但是没找到");throw new ExcelImportException(ExcelImportEnum.IS_NOT_A_VALID_TEMPLATE);}}for (int i = 0, le = excelCollection.size(); i < le; i++) {ExcelCollectionParams collectionparams = excelCollection.get(i);collection = collectionparams.getExcelParams().values();for (ExcelImportEntity excelImportEntity : collection) {if (excelImportEntity.isImportField() && !titlemap.containsValue(collectionparams.getExcelName() + "_" + excelImportEntity.getName())) {throw new ExcelImportException(ExcelImportEnum.IS_NOT_A_VALID_TEMPLATE);}}}}}/*** 保存字段值(获取值,校验值,追加错误信息)*/public void saveFieldValue(ImportParams params, Object object, Cell cell,Map<String, ExcelImportEntity> excelParams, String titleString,Row row) throws Exception {Object value = cellValueServer.getValue(params.getDataHandler(), object, cell, excelParams,titleString, params.getDictHandler());if (object instanceof Map) {if (params.getDataHandler() != null) {params.getDataHandler().setMapValue((Map) object, titleString, value);} else {((Map) object).put(titleString, value);}} else {setValues(excelParams.get(titleString), object, value);}}/*** @param object* @param picId* @param excelParams* @param titleString* @param pictures* @param params* @throws Exception*/private void saveImage(Object object, String picId, Map<String, ExcelImportEntity> excelParams,String titleString, Map<String, PictureData> pictures,ImportParams params) throws Exception {if (pictures == null) {return;}PictureData image = pictures.get(picId);if (image == null) {return;}byte[] data     = image.getData();String fileName = "pic" + Math.round(Math.random() * 100000000000L);fileName += "." + PoiPublicUtil.getFileExtendName(data);if (excelParams.get(titleString).getSaveType() == 1) {String path     = getSaveUrl(excelParams.get(titleString), object);File   savefile = new File(path);if (!savefile.exists()) {savefile.mkdirs();}savefile = new File(path + File.separator + fileName);FileOutputStream fos = new FileOutputStream(savefile);try {fos.write(data);} finally {IOUtils.closeQuietly(fos);}setValues(excelParams.get(titleString), object,getSaveUrl(excelParams.get(titleString), object) + File.separator + fileName);} else {setValues(excelParams.get(titleString), object, data);}}private void createErrorCellStyle(Workbook workbook) {errorCellStyle = workbook.createCellStyle();Font font = workbook.createFont();font.setColor(Font.COLOR_RED);errorCellStyle.setFont(font);}}

3.3 Maven 依赖

<dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-web</artifactId></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-tomcat</artifactId><scope>provided</scope></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-test</artifactId><scope>test</scope></dependency><dependency><groupId>cn.afterturn</groupId><artifactId>easypoi-spring-boot-starter</artifactId><version>4.4.0</version></dependency><!-- 建议只用start --><dependency><groupId>cn.afterturn</groupId><artifactId>easypoi-base</artifactId><version>4.4.0</version></dependency><dependency><groupId>cn.afterturn</groupId><artifactId>easypoi-web</artifactId><version>4.4.0</version></dependency><dependency><groupId>cn.afterturn</groupId><artifactId>easypoi-annotation</artifactId><version>4.4.0</version></dependency><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-test</artifactId></dependency><dependency><groupId>com.alibaba</groupId><artifactId>fastjson</artifactId><version>1.2.76</version></dependency><dependency><groupId>junit</groupId><artifactId>junit</artifactId></dependency><dependency><groupId>org.hibernate</groupId><artifactId>hibernate-validator</artifactId><version>5.2.1.Final</version></dependency><dependency><groupId>org.hibernate</groupId><artifactId>hibernate-validator-annotation-processor</artifactId><version>5.2.1.Final</version></dependency><dependency><groupId>org.glassfish.web</groupId><artifactId>javax.el</artifactId><version>2.2.4</version></dependency>

4.git 完整代码

防止复现不了我说的情况，git