当前位置：首页 > news >正文

导入文件允许合并表格

news 2025/8/29 10:22:06

本来呢，已经有几年没咋写博客了，但是好像网上没什么好的合并导入可以抄的，周末加班了一天弄出来了，想一想也不算造轮子，可以露一手出来，最近也挺喜欢写注释的，应该方便大家抄的

public class TrainClassArrangeListener extends AnalysisEventListener<TrainClassArrangeVo> implements ExcelListener<TrainClassArrangeVo> {private final TrainClassArrangeMapper baseMapper;private static final Logger logger = LoggerFactory.getLogger(TrainClassArrangeListener.class);// 存储所有行的原始数据private final Map<Integer, Map<Integer, String>> allRowDataCache = new ConcurrentHashMap<>();// 存储所有数据对象（按行索引）private final Map<Integer, TrainClassArrangeVo> dataMap = new ConcurrentHashMap<>();// 存储合并单元格信息private final List<CellExtra> mergeInfoList = new ArrayList<>();private final StringBuilder successMsg = new StringBuilder();private final StringBuilder failureMsg = new StringBuilder();private int successNum = 0;private int failureNum = 0;//导入总数private int totalRows = 0;private final Long planId;public TrainClassArrangeListener(TrainClassArrangeMapper baseMapper, Long planId) {this.baseMapper = baseMapper;this.planId = planId;}@Overridepublic void invoke(TrainClassArrangeVo data, AnalysisContext context) {int rowIndex = context.readRowHolder().getRowIndex();//把所有导入的list添加计划iddata.setPlanId(planId);Map<Integer, Cell> cellMap = context.readRowHolder().getCellMap();logger.debug("读取第{}行数据", rowIndex);// 缓存原始单元格数据cacheRowData(rowIndex, cellMap);// 缓存数据对象dataMap.put(rowIndex, data);totalRows++;}/*** 将合并的单元格添加mergeInfoList ，进行同意处理* @param extra* @param context*/@Overridepublic void extra(CellExtra extra, AnalysisContext context) {if (extra.getType() == CellExtraTypeEnum.MERGE) {mergeInfoList.add(extra);logger.debug("发现合并单元格: 行{}-{}, 列{}-{}",extra.getFirstRowIndex(), extra.getLastRowIndex(),extra.getFirstColumnIndex(), extra.getLastColumnIndex());}}@Overridepublic void doAfterAllAnalysed(AnalysisContext analysisContext) {try {logger.info("开始处理合并单元格，共{}个合并区域", mergeInfoList.size());// 处理所有合并单元格processAllMergedCells();// 发送所有处理后的数据sendAllData();logger.info("Excel解析完成，共处理{}行数据", totalRows);} finally {// 清理资源allRowDataCache.clear();dataMap.clear();mergeInfoList.clear();}}/*** 缓存行数据*/private void cacheRowData(int rowIndex, Map<Integer, Cell> cellMap) {if (cellMap == null) return;Map<Integer, String> rowData = new HashMap<>();for (Map.Entry<Integer, Cell> entry : cellMap.entrySet()) {int columnIndex = entry.getKey();com.alibaba.excel.metadata.Cell cell = entry.getValue();String cellValue = convertCellToString(cell);rowData.put(columnIndex, cellValue);}allRowDataCache.put(rowIndex, rowData);}/*** 处理所有合并单元格*/private void processAllMergedCells() {for (CellExtra extra : mergeInfoList) {processSingleMerge(extra);}}/*** 处理单个合并区域*/private void processSingleMerge(CellExtra extra) {// 只处理垂直合并（同一列）if (!Objects.equals(extra.getFirstColumnIndex(), extra.getLastColumnIndex())) {return;}int firstRow = extra.getFirstRowIndex();int lastRow = extra.getLastRowIndex();int columnIndex = extra.getFirstColumnIndex();// 获取合并区域第一个单元格的值String firstCellValue = getCellValue(firstRow, columnIndex);if (firstCellValue != null && !firstCellValue.trim().isEmpty()) {logger.debug("处理合并: 列{}, 行{}-{}, 值: {}",columnIndex, firstRow, lastRow, firstCellValue);// 为合并区域内的所有行设置相同的值for (int row = firstRow; row <= lastRow; row++) {TrainClassArrangeVo data = dataMap.get(row);if (data != null) {setFieldValue(data, columnIndex, firstCellValue);dataMap.put(row,data);System.out.println(dataMap.get(row));}}}}/*** 发送所有数据*/private void sendAllData() {for (int i = 1; i <= totalRows; i++) {TrainClassArrangeVo data = dataMap.get(i);if (data != null) {try {TrainClassArrange arrange = MapstructUtils.convert(data, TrainClassArrange.class);baseMapper.insert(arrange);successNum++;} catch (Exception e) {failureNum++;logger.error("发送第{}行数据失败", i, e);}}}}/*** 获取单元格值*/private String getCellValue(int rowIndex, int columnIndex) {Map<Integer, String> rowData = allRowDataCache.get(rowIndex);return rowData != null ? rowData.get(columnIndex) : null;}/*** 转换单元格为字符串*/private String convertCellToString(com.alibaba.excel.metadata.Cell cell) {if (cell == null) return null;ReadCellData<?> readCellData = (ReadCellData<?>) cell;if (readCellData.getType() == CellDataTypeEnum.NUMBER) {Number number = readCellData.getNumberValue();return number != null ? number.toString() : null;}return readCellData.getStringValue();}/*** 设置字段值*/private void setFieldValue(TrainClassArrangeVo data, int columnIndex, String value) {if (value == null) return;try {switch (columnIndex) {case 1:data.setDateArrange(value); break;case 2:data.setTrainClassName(value); break;case 3:data.setTrainee(value);break;case 4:data.setTrainContent(value);break;case 5:data.setGoalRequire(value);break;case 6:data.setOrganizationDept(value);break;case 7:data.setUnitOrPerson(value);break;case 8:data.setCompleteTrainTime(value);break;case 9:data.setTrainHour(value);break;case 10:data.setCheckMethod(value);break;default:logger.debug("忽略未知列索引: {}", columnIndex);break;}} catch (Exception e) {logger.error("设置字段值失败，列{}，值: {}", columnIndex, value, e);}}@Overridepublic ExcelResult<TrainClassArrangeVo> getExcelResult() {return new ExcelResult<TrainClassArrangeVo>() {@Overridepublic List<TrainClassArrangeVo> getList() {return null;}@Overridepublic List<String> getErrorList() {return null;}@Overridepublic String getAnalysis() {if (failureNum > 0) {throw  new TrainBusinessException("很抱歉，导入失败！共 " + failureNum + " 条数据格式不正确");
//                    failureMsg.insert(0,
//                            "很抱歉，导入失败！共 " + failureNum + " 条数据格式不正确");
//                    ValidateBiz.error(failureMsg.toString());} else {successMsg.insert(0,"恭喜您，数据已全部导入成功！共 " + successNum + " 条");}return successMsg.toString();}};}
}

/*** Excel 导入监听** @author Lion Li*/
public interface ExcelListener<T> extends ReadListener<T> {ExcelResult<T> getExcelResult();
}

@Slf4j
@NoArgsConstructor
public class DefaultExcelListener<T> extends AnalysisEventListener<T> implements ExcelListener<T> {/*** 是否Validator检验，默认为是*/private Boolean isValidate = Boolean.TRUE;/*** excel 表头数据*/private Map<Integer, String> headMap;/*** 导入回执*/private ExcelResult<T> excelResult;public DefaultExcelListener(boolean isValidate) {this.excelResult = new DefaultExcelResult<>();this.isValidate = isValidate;}/*** 处理异常** @param exception ExcelDataConvertException* @param context   Excel 上下文*/@Overridepublic void onException(Exception exception, AnalysisContext context) throws Exception {String errMsg = null;if (exception instanceof ExcelDataConvertException excelDataConvertException) {// 如果是某一个单元格的转换异常 能获取到具体行号Integer rowIndex = excelDataConvertException.getRowIndex();Integer columnIndex = excelDataConvertException.getColumnIndex();errMsg = StrUtil.format("第{}行-第{}列-表头{}: 解析异常<br/>",rowIndex + 1, columnIndex + 1, headMap.get(columnIndex));if (log.isDebugEnabled()) {log.error(errMsg);}}if (exception instanceof ConstraintViolationException constraintViolationException) {Set<ConstraintViolation<?>> constraintViolations = constraintViolationException.getConstraintViolations();String constraintViolationsMsg = StreamUtils.join(constraintViolations,ConstraintViolation::getMessage, ", ");errMsg = StrUtil.format("第{}行数据校验异常: {}",context.readRowHolder().getRowIndex() + 1, constraintViolationsMsg);if (log.isDebugEnabled()) {log.error(errMsg);}}excelResult.getErrorList().add(errMsg);throw new ExcelAnalysisException(errMsg);}@Overridepublic void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {this.headMap = headMap;log.debug("解析到一条表头数据: {}", JsonUtils.toJsonString(headMap));}@Overridepublic void invoke(T data, AnalysisContext context) {if (isValidate) {ValidatorUtils.validate(data);}excelResult.getList().add(data);}@Overridepublic void doAfterAllAnalysed(AnalysisContext context) {log.debug("所有数据解析完成！");}@Overridepublic ExcelResult<T> getExcelResult() {return excelResult;}}

//接口调用地方
TrainClassArrangeListener listener = new TrainClassArrangeListener(classArrangeMapper, planId);// 读取Excel时启用额外信息读取EasyExcel.read(file.getInputStream(), TrainClassArrangeVo.class,listener).extraRead(CellExtraTypeEnum.MERGE) // 关键：启用合并单元格读取.sheet().doRead();