java-自定义excel导入导出-3.0

package com.epartner.module.epm.excel; import cn.hutool.core.bean.BeanUtil; import cn.hutool.core.bean.copier.CopyOptions; import cn.hutool.core.collection.IterUtil; import cn.hutool.core.collection.ListUtil; import cn.hutool.core.io.FileUtil; import cn.hutool.core.io.IoUtil; import cn.hutool.core.util.IdUtil; import cn.hutool.core.util.NumberUtil; import cn.hutool.core.util.ReflectUtil; import cn.hutool.core.util.StrUtil; import cn.hutool.extra.spring.SpringUtil; import cn.hutool.json.JSONUtil; import cn.hutool.poi.excel.BigExcelWriter; import cn.hutool.poi.excel.ExcelReader; import com.epartner.framework.common.core.FileReqVO; import com.epartner.framework.common.exception.ServiceException; import com.epartner.framework.common.exception.util.ServiceExceptionUtil; import com.epartner.framework.common.pojo.CommonResult; import com.epartner.framework.common.pojo.FileImport; import com.epartner.framework.common.util.CollUtil; import com.epartner.framework.common.util.ValidUtil; import com.epartner.framework.security.core.util.SecurityFrameworkUtils; import com.epartner.module.epm.ErrorCodeConstants; import com.epartner.module.epm.util.ThreadUtil; import com.epartner.module.infra.api.file.FileApi; import com.epartner.module.infra.api.file.dto.FileCreateReqDTO; import com.epartner.module.system.api.comm.SystemImportApi; import com.epartner.module.system.api.comm.dto.ImportFailDTO; import com.epartner.module.system.api.comm.dto.ImportingLogDTO; import com.epartner.module.system.api.comm.dto.SystemImportErrDto; import com.epartner.module.system.api.dict.DictDataApi; import com.epartner.module.system.api.dict.dto.DictDataRespDTO; import lombok.extern.slf4j.Slf4j; import org.apache.poi.ss.usermodel.*; import org.apache.poi.ss.util.CellRangeAddressList; import org.jetbrains.annotations.NotNull; import org.redisson.api.RLock; import org.redisson.api.RedissonClient; import org.springframework.beans.factory.annotation.Value; import org.springframework.data.redis.core.RedisTemplate; import org.springframework.stereotype.Component; import org.springframework.web.multipart.MultipartFile; import javax.annotation.Resource; import javax.servlet.ServletOutputStream; import javax.servlet.http.HttpServletResponse; import java.io.File; import java.io.IOException; import java.lang.reflect.Field; import java.net.URLEncoder; import java.time.LocalDate; import java.time.format.DateTimeFormatter; import java.util.*; import java.util.concurrent.TimeUnit; import java.util.stream.Collectors; /** * 导入 * * @author miaxia */ @Slf4j @Component public class ExcelImport { @Resource private SystemImportApi systemImportApi; @Resource private DictDataApi dictDataApi; /** * 填写导入数据sheet名称 */ private static final String dataSheetName = "导入数据"; /** * 必填标识 */ private static final String requireChar = "*"; private static final String redis_lock = "redis_lock:import_file:"; private static final String dictSheetName = "数据字典"; @Value("${custom.file.path}") private String tempFilePath; @Resource private RedissonClient redissonClient; @Resource private RedisTemplate redisTemplate; @Resource private FileApi fileApi; /** * 路径+时间+用户 * * @return */ private String getTempFilePath() { //时间 String format = LocalDate.now().format(DateTimeFormatter.BASIC_ISO_DATE); //用户 Long loginUserId = SecurityFrameworkUtils.getLoginUserId(); return StrUtil.concat(true, tempFilePath, String.format("/%s/%s/%s", format, loginUserId, IdUtil.fastSimpleUUID())); } /** * 模板下载 * * @param response * @param tClass * @param fileName * @param <T> */ public <T> void downloadTemplate(HttpServletResponse response, Class<T> tClass, String fileName) { BigExcelWriter writer = ExcelUtil.getBigWriter(); ServletOutputStream out = null; try { response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8"); response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "utf-8")); //1.读取类加载表头 List<Field> fieldArrayList = getHeadFieldList(tClass); //3.获取表头信息 writer.renameSheet(dataSheetName); //表头 List<String> headerList = new ArrayList<>(); for (Field field : fieldArrayList) { ImportHeader header = field.getAnnotation(ImportHeader.class); String value = header.value(); if (header.required()) { headerList.add(value + requireChar); } else { headerList.add(value); } } writer.writeHeadRow(headerList); //4.输出字典下拉列表 Map<String, List<DictDataRespDTO>> dictListMap = getDictListMap(fieldArrayList); int dictIndex = 0; //固定字典行列处理，输入提示处理 List<List<String>> dictColList = new ArrayList<>(); for (int i = 0; i < fieldArrayList.size(); i++) { Field field = fieldArrayList.get(i); ImportHeader header = field.getAnnotation(ImportHeader.class); //字典 if (!StrUtil.isBlank(header.dictType())) { List<DictDataRespDTO> dictDataList = dictListMap.get(header.dictType()); Sheet sheet = writer.getSheet(); DataValidationHelper dataValidationHelper = sheet.getDataValidationHelper(); // 引用其他工作表或工作簿的单元格范围 //列名 String colName = ExcelUtil.indexToColName(dictIndex); DataValidationConstraint constraint = dataValidationHelper.createFormulaListConstraint(String.format("'%s'!$%s$2:$%s$%s", dictSheetName, colName, colName, dictDataList.size() + 1)); DataValidation validation = dataValidationHelper.createValidation(constraint, new CellRangeAddressList(1, 20, i, i)); // 显示下拉箭头 validation.setSuppressDropDownArrow(true); sheet.addValidationData(validation); dictIndex++; List<String> list = new ArrayList<>(); list.add(header.value()); list.addAll(CollUtil.map(dictDataList, DictDataRespDTO::getLabel, false)); dictColList.add(list); } //输入提示 if (StrUtil.isNotBlank(header.tips())) { Sheet sheet = writer.getSheet(); Row row = sheet.getRow(0); Drawing<?> drawingPatriarch = sheet.getDrawingPatriarch(); if (null == drawingPatriarch) { drawingPatriarch = sheet.createDrawingPatriarch(); } Cell cell = row.getCell(i); CreationHelper creationHelper = writer.getWorkbook().getCreationHelper(); ClientAnchor clientAnchor = creationHelper.createClientAnchor(); clientAnchor.setCol1(i); clientAnchor.setRow1(0); clientAnchor.setCol2(i + 5); clientAnchor.setRow2(2); Comment cellComment = drawingPatriarch.createCellComment(clientAnchor); cellComment.setString(creationHelper.createRichTextString(header.tips())); cell.setCellComment(cellComment); } } ExcelUtil.autoSizeColumnAllForChinese(writer); //5.输出选项sheet //选项查询结果缓存 Map<Class, List<Link