解析指定模板的Excel
解析指定模板的Excel,模板如下:
首先定义一个注解
import java.lang.annotation.Documented; import java.lang.annotation.Retention; import java.lang.annotation.Target; import java.lang.annotation.RetentionPolicy; import java.lang.annotation.ElementType; @Target({ElementType.FIELD}) @Retention(RetentionPolicy.RUNTIME) @Documented public @interface ExcelColumn { //名称 String value() default ""; //行数 int col() default 0; }
再新建对应的实体
/** * Excel配置文件,行实体类 */ public class ConfigExcelRow { @ExcelColumn(value = "映射标识", col = 1) private String mappingKey; @ExcelColumn(value = "源标识", col = 2) private String sourceKey; @ExcelColumn(value = "目标标识", col = 3) private String targetkey; @ExcelColumn(value = "源字段", col = 4) private String sourceField; @ExcelColumn(value = "目标字段", col = 5) private String targetField; @ExcelColumn(value = "类型", col = 6) private String type; @ExcelColumn(value = "功能模块", col = 7) private String functionModule; @ExcelColumn(value = "源数据库", col = 8) private String sourceDbInfo; @ExcelColumn(value = "目标数据库", col = 9) private String targetDbInfo; @ExcelColumn(value = "查询条件", col = 10) private String condition; @ExcelColumn(value = "源基础数据标识", col = 11) private String sourceBaseKey; @ExcelColumn(value = "目标基础数据标识", col = 12) private String targetBasekey; public String getMappingKey() { return mappingKey; } public void setMappingKey(String mappingKey) { this.mappingKey = mappingKey; } public String getFunctionModule() { return functionModule; } public void setFunctionModule(String functionModule) { this.functionModule = functionModule; } public String getSourceDbInfo() { return sourceDbInfo; } public void setSourceDbInfo(String sourceDbInfo) { this.sourceDbInfo = sourceDbInfo; } public String getTargetDbInfo() { return targetDbInfo; } public void setTargetDbInfo(String targetDbInfo) { this.targetDbInfo = targetDbInfo; } @Override public String toString() { return "MigrationRow [sourceKey=" + sourceKey + ", targetkey=" + targetkey + ", sourceField=" + sourceField + ", targetField=" + targetField + ", type=" + type + "]"; } public String getSourceBaseKey() { return sourceBaseKey; } public void setSourceBaseKey(String sourceBaseKey) { this.sourceBaseKey = sourceBaseKey; } public String getTargetBasekey() { return targetBasekey; } public void setTargetBasekey(String targetBasekey) { this.targetBasekey = targetBasekey; } public String getSourceKey() { return sourceKey; } public void setSourceKey(String sourceKey) { this.sourceKey = sourceKey; } public String getTargetkey() { return targetkey; } public void setTargetkey(String targetkey) { this.targetkey = targetkey; } public String getSourceField() { return sourceField; } public void setSourceField(String sourceField) { this.sourceField = sourceField; } public String getTargetField() { return targetField; } public void setTargetField(String targetField) { this.targetField = targetField; } public String getType() { return type==null?DEFAULT:type; } public void setType(String type) { this.type = type; } public String getCondition() { return condition; } public void setCondition(String condition) { this.condition = condition; } }
定义接口
public interface ExcelService { /** * 解析Excel数据 */ public <T> List<T> readExcel(String path, Class<T> cls, File file); public <T> List<T> readExcel(String path, Class<T> cls, MultipartFile file); }
实现类
/* * 解析,把文件转化成ConfigExcelRow * */ @Service public class ExcelServiceImpl implements ExcelService { @Autowired FileService fileService; private static final Log logger = LogFactory.getLog(BillDefine.class); public <T> List<T> readExcel(String path, Class<T> cls, File file) { MultipartFile multipartFile = fileService.getMultipartFile(file); return readExcel(path, cls, multipartFile); } public <T> List<T> readExcel(String path, Class<T> cls, MultipartFile file) { try { return _readExcel(path, cls, file); } catch (Exception e) { logger.info(String.format("初始化配置文件失败!")); } return null; } //首行为汉语注解,默认只读取第一个sheet, public <T> List<T> _readExcel(String path, Class<T> cls, MultipartFile file) { long t1 = System.currentTimeMillis(); Workbook workbook = getWorkbook(file); Assert.notNull(workbook, "workbook must not be null"); List<T> dataList = new ArrayList<>(); try { List<Field> fields = Stream.of(cls.getDeclaredFields()).collect(Collectors.toList()); Map<String, List<Field>> classMap = getClassMap(fields); Map<Integer, List<Field>> reflectionMap = null; //索引-->columns Sheet sheet = workbook.getSheetAt(0); //默认读取第一个sheet boolean firstRow = true; for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) { Row row = sheet.getRow(i); if (row == null) continue; //忽略空白行 if (firstRow) { //首行提取注解 reflectionMap = getReflectionMap(row, classMap); firstRow = false; continue; } try { T t = cls.newInstance(); if (handleField(t, row, reflectionMap)) dataList.add(t); else logger.info(String.format("row:%s is blank ignore!", i)); } catch (Exception e) { logger.error(e); } } } catch (Exception e) { logger.error(e); } long t2 = System.currentTimeMillis(); logger.info(String.format(file.getName()+"读取完成! 花费:%sms", (t2 - t1))); return dataList; } //处理具体的变量 <T> boolean handleField(T t, Row row, Map<Integer, List<Field>> reflectionMap) { boolean allBlank = true; for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) { if (reflectionMap.containsKey(j)) { Cell cell = row.getCell(j); String cellValue = getCellValue(cell); if (StringUtils.hasText(cellValue)) allBlank = false; List<Field> fieldList = reflectionMap.get(j); fieldList.forEach(x -> { try { handleField(t, cellValue, x); } catch (Exception e) { logger.error(e); } }); } } return !allBlank; } Map<Integer, List<Field>> getReflectionMap(Row row, Map<String, List<Field>> classMap) { Map<Integer, List<Field>> reflectionMap = new HashMap<>(16); for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) { Cell cell = row.getCell(j); String cellValue = getCellValue(cell); if (classMap.containsKey(cellValue)) { reflectionMap.put(j, classMap.get(cellValue)); } } return reflectionMap; } //根据注解组装classMap Map<String, List<Field>> getClassMap(List<Field> fields) { Map<String, List<Field>> classMap = new HashMap<>(); fields.forEach(field -> { ExcelColumn annotation = field.getAnnotation(ExcelColumn.class); if (annotation != null) { String value = annotation.value(); if (!StringUtils.hasText(value)) return; if (!classMap.containsKey(value)) { classMap.put(value, new ArrayList<>()); } field.setAccessible(true); classMap.get(value).add(field); } }); return classMap; } void fileNameCheck(String fileName) { if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) { throw new IllegalArgumentException("上传文件格式不正确"); } } //获取Workbook,兼容EXCEL2007和EXCEL2003 Workbook getWorkbook(MultipartFile file) { fileNameCheck(file.getOriginalFilename()); String fileName = file.getOriginalFilename(); Workbook workbook = null; InputStream is; try { is = file.getInputStream(); if (fileName.endsWith(EXCEL2007)) {//String EXCEL2007 = "xlsx"; workbook = new XSSFWorkbook(is); } else if (fileName.endsWith(EXCEL2003)) {//String EXCEL2003 = "xlx"; workbook = new HSSFWorkbook(is); } } catch (IOException e) { logger.error(e); } return workbook; } //处理具体属性,根据类型处理 <T> void handleField(T t, String value, Field field) throws Exception { Class<?> type = field.getType(); if (type == null || type == void.class || !StringUtils.hasText(value)) { return; } if (type == Object.class) { field.set(t, value); // 数字类型 } else if (type.getSuperclass() == null || type.getSuperclass() == Number.class) { if (type == int.class || type == Integer.class) { field.set(t, Integer.parseInt(value)); } else if (type == long.class || type == Long.class) { field.set(t, Long.parseLong(value)); } else if (type == byte.class || type == Byte.class) { field.set(t, Byte.parseByte(value)); } else if (type == short.class || type == Short.class) { field.set(t, Short.parseShort(value)); } else if (type == double.class || type == Double.class) { field.set(t, Double.parseDouble(value)); } else if (type == float.class || type == Float.class) { field.set(t, Float.parseFloat(value)); } else if (type == char.class || type == Character.class) { field.set(t, value.charAt(0)); } else if (type == boolean.class) { field.set(t, Boolean.parseBoolean(value)); } else if (type == BigDecimal.class) { field.set(t, new BigDecimal(value)); } } else if (type == Boolean.class) { field.set(t, Boolean.valueOf(value)); } else if (type == Date.class) { field.set(t, value); } else if (type == String.class) { field.set(t, value); } else { Constructor<?> constructor = type.getConstructor(String.class); field.set(t, constructor.newInstance(value)); } } //获取具体cell的值,根据类型获取 String getCellValue(Cell cell) { if (cell == null) { return ""; } if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {//数字和日期均为CELL_TYPE_NUMERIC if (DateUtil.isCellDateFormatted(cell)) { return HSSFDateUtil.getJavaDate(cell.getNumericCellValue()).toString(); } else { return new BigDecimal(cell.getNumericCellValue()).toString(); } } else if (cell.getCellType() == Cell.CELL_TYPE_STRING) { return StringUtils.trimWhitespace(cell.getStringCellValue()); } else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) { return StringUtils.trimWhitespace(cell.getCellFormula()); } else if (cell.getCellType() == Cell.CELL_TYPE_BLANK) { return ""; } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) { return String.valueOf(cell.getBooleanCellValue()); } else if (cell.getCellType() == Cell.CELL_TYPE_ERROR) { return "ERROR"; } else { return cell.toString().trim(); } } }
yian