excel导入导出工具类

package org.jeecg.common.util.excel; import lombok.Data; import org.apache.commons.lang3.StringUtils; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellType; import org.apache.poi.ss.usermodel.Workbook; import org.jeecgframework.core.util.ApplicationContextUtil; import org.jeecgframework.dict.service.AutoPoiDictServiceI; import org.jeecgframework.poi.excel.annotation.Excel; import org.jeecgframework.poi.excel.annotation.ExcelCollection; import org.jeecgframework.poi.excel.annotation.ExcelEntity; import org.jeecgframework.poi.excel.annotation.ExcelVerify; import org.jeecgframework.poi.excel.entity.ImportParams; import org.jeecgframework.poi.excel.entity.params.ExcelCollectionParams; import org.jeecgframework.poi.excel.entity.params.ExcelImportEntity; import org.jeecgframework.poi.excel.entity.params.ExcelVerifyEntity; import org.jeecgframework.poi.exception.excel.ExcelImportException; import org.jeecgframework.poi.exception.excel.enums.ExcelImportEnum; import org.jeecgframework.poi.handler.inter.IExcelDataHandler; import org.jeecgframework.poi.util.ExcelUtil; import org.jeecgframework.poi.util.PoiPublicUtil; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import java.io.File; import java.io.FileOutputStream; import java.lang.reflect.Field; import java.lang.reflect.Method; import java.lang.reflect.ParameterizedType; import java.lang.reflect.Type; import java.math.BigDecimal; import java.text.ParseException; import java.text.SimpleDateFormat; import java.util.*; /** * @Description：Excel数据导入处理 * @param： * @Author: huguoyan * @Date: 2023/11/24 15:07 */ public class ExcelImportBaseServiceUtil { private static final Logger LOGGER = LoggerFactory.getLogger(ExcelImportBaseServiceUtil.class); private List<String> hanlderList = null; public Object getValue(IExcelDataHandler dataHanlder, Object object, Cell cell, Map<String, ExcelImportEntityUtil> excelParams, String titleString) throws Exception { ExcelImportEntityUtil entity = (ExcelImportEntityUtil)excelParams.get(titleString); String xclass = "class java.lang.Object"; if (!(object instanceof Map)) { Method setMethod = entity.getMethods() != null && entity.getMethods().size() > 0 ? (Method)entity.getMethods().get(entity.getMethods().size() - 1) : entity.getMethod(); Type[] ts = setMethod.getGenericParameterTypes(); xclass = ts[0].toString(); } Object result = this.getCellValue(xclass, cell, entity); if (entity != null) { result = this.hanlderSuffix(entity.getSuffix(), result); result = this.replaceValue(entity.getReplace(), result, entity.isMultiReplace()); } result = this.hanlderValue(dataHanlder, object, result, titleString); return this.getValueByType(xclass, result, entity); } private Object hanlderValue(IExcelDataHandler dataHanlder, Object object, Object result, String titleString) { if (dataHanlder != null && dataHanlder.getNeedHandlerFields() != null && dataHanlder.getNeedHandlerFields().length != 0) { if (this.hanlderList == null) { this.hanlderList = Arrays.asList(dataHanlder.getNeedHandlerFields()); } return this.hanlderList.contains(titleString) ? dataHanlder.importHandler(object, titleString, result) : result; } else { return result; } } private Object hanlderSuffix(String suffix, Object result) { if (StringUtils.isNotEmpty(suffix) && result != null && result.toString().endsWith(suffix)) { String temp = result.toString(); return temp.substring(0, temp.length() - suffix.length()); } else { return result; } } /*** * 获取单元格内容 * @param xclass * @param cell 列 * @param entity 实体 * @return */ private Object getCellValue(String xclass, Cell cell, ExcelImportEntityUtil entity) { if (cell == null) { return ""; } else { Object result = null; if (!"class java.util.Date".equals(xclass) && !"class java.sql.Time".equals(xclass)) { if (CellType.NUMERIC == cell.getCellTypeEnum()) { result = cell.getNumericCellValue(); } else if (CellType.BOOLEAN == cell.getCellTypeEnum()) { result = cell.getBooleanCellValue(); } else if (CellType.FORMULA == cell.getCellTypeEnum() && PoiPublicUtil.isNumber(xclass)) { result = cell.getNumericCellValue(); } else { result = cell.getStringCellValue(); } } else { if (CellType.NUMERIC == cell.getCellTypeEnum()) { result = cell.getDateCellValue(); } else { cell.setCellType(CellType.STRING); result = this.getDateData(entity, cell.getStringCellValue()); } } return result; } } private Date getDateData(ExcelImportEntityUtil entity, String value) { if (StringUtils.isNotEmpty(entity.getFormat()) && StringUtils.isNotEmpty(value)) { SimpleDateFormat format = new SimpleDateFormat(entity.getFormat()); try { return format.parse(value); } catch (ParseException var5) { LOGGER.error("时间格式化失败,格式化:{},值:{}", entity.getFormat(), value); throw new ExcelImportException(ExcelImportEnum.GET_VALUE_ERROR); } } else { return null; } } private Object replaceValue(String[] replace, Object result, boolean multiReplace) { if (result == null) { return ""; } else if (replace != null && replace.length > 0) { String temp = String.valueOf(result); String backValue = ""; if (temp.indexOf(",") > 0 && multiReplace) { String[] multiReplaces = temp.split(","); String[] var7 = multiReplaces; int var8 = multiReplaces.length; for(int var9 = 0; var9 < var8; ++var9) { String str = var7[var9]; backValue = backValue.concat(this.replaceSingleValue(replace, str) + ","); } if (backValue.equals("")) { backValue = temp; } else { backValue = backValue.substring(0, backValue.length() - 1); } } else { backValue = this.replaceSingleValue(replace, temp); } if (replace.length > 0 && backValue.equals(temp)) { LOGGER.warn("====================字典替换失败,字典值:{},要转换的导入值:{}====================", replace, temp); } return backValue; } else { return result; } } private String replaceSingleValue(String[] replace, String temp) { for(int i = 0; i < replace.length; ++i) { String[] tempArr = replace[i].split("_"); if (temp.equals(tempArr[0])) { return tempArr[1]; } } return temp; } private Object getValueByType(String xclass, Object result, ExcelImportEntityUtil entity) { try { if (result != null && !"".equals(String.valueOf(result))) { if ("class java.util.Date".equals(xclass)) { return result; } else if (!"class java.lang.Boolean".equals(xclass) && !"boolean".equals(xclass)) { if (!"class java.lang.Double".equals(xclass