导出类
package com.tcl.ub.core.util; import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.IOException; import java.io.OutputStream; import java.lang.reflect.Field; import java.lang.reflect.Method; import java.net.URLEncoder; import java.util.Collections; import java.util.Comparator; import java.util.Date; import java.util.HashMap; import java.util.List; import java.util.Map; import javax.servlet.http.HttpServletResponse; import org.apache.commons.lang3.StringUtils; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.Comment; import org.apache.poi.ss.usermodel.Font; import org.apache.poi.ss.usermodel.IndexedColors; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.xssf.streaming.SXSSFSheet; import org.apache.poi.xssf.streaming.SXSSFWorkbook; import org.apache.poi.xssf.usermodel.XSSFClientAnchor; import org.apache.poi.xssf.usermodel.XSSFRichTextString; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import com.google.common.collect.Lists; import com.tcl.ub.core.base.annotation.ExcelField; /** * 导出Excel文件(导出“XLSX”格式,支持大数据量导出 @see org.apache.poi.ss.SpreadsheetVersion) * @param <T> * @author l */ public class ExportExcel { private static Logger log = LoggerFactory.getLogger(ExportExcel.class); /** * 工作薄对象 */ private SXSSFWorkbook wb; /** * 工作表对象 */ private SXSSFSheet sheet; /** * 样式列表 */ private Map<String, CellStyle> styles; /** * 当前行号 */ private int rownum; /** * 注解列表(Object[]{ ExcelField, Field/Method }) */ List<Object[]> annotationList = Lists.newArrayList(); public List<Object[]> getAnnotationList() { return annotationList; } /** * 构造函数 * * @param title * 表格标题,传“空值”,表示无标题 * @param cls * 实体对象,通过annotation.ExportField获取标题 */ public ExportExcel(String title, Class<?> cls) { this(title, cls, 1); } /** * 构造函数 * * @param title * 表格标题,传“空值”,表示无标题 * @param cls * 实体对象,通过annotation.ExportField获取标题 * @param type * 导出类型(1:导出数据;2:导出模板) * @param groups * 导入分组 */ public ExportExcel(String title, Class<?> cls, int type, int... groups) { // Get annotation field Field[] fs = cls.getDeclaredFields(); for (Field f : fs) { ExcelField ef = f.getAnnotation(ExcelField.class); if (ef != null && (ef.type() == 0 || ef.type() == type)) { if (groups != null && groups.length > 0) { boolean inGroup = false; for (int g : groups) { if (inGroup) { break; } for (int efg : ef.groups()) { if (g == efg) { inGroup = true; annotationList.add(new Object[] { ef, f }); break; } } } } else { annotationList.add(new Object[] { ef, f }); } } } // Get annotation method Method[] ms = cls.getDeclaredMethods(); for (Method m : ms) { ExcelField ef = m.getAnnotation(ExcelField.class); if (ef != null && (ef.type() == 0 || ef.type() == type)) { if (groups != null && groups.length > 0) { boolean inGroup = false; for (int g : groups) { if (inGroup) { break; } for (int efg : ef.groups()) { if (g == efg) { inGroup = true; annotationList.add(new Object[] { ef, m }); break; } } } } else { annotationList.add(new Object[] { ef, m }); } } } // Field sorting Collections.sort(annotationList, new Comparator<Object[]>() { @Override public int compare(Object[] o1, Object[] o2) { return new Integer(((ExcelField) o1[0]).sort()).compareTo(new Integer(((ExcelField) o2[0]).sort())); }; }); // Initialize List<String> headerList = Lists.newArrayList(); for (Object[] os : annotationList) { String t = ((ExcelField) os[0]).title(); // 如果是导出,则去掉注释 if (type == 1) { String[] ss = StringUtils.split(t, "**", 2); if (ss.length == 2) { t = ss[0]; } } headerList.add(t); } initialize(title, headerList); } /** * 构造函数 * * @param title * 表格标题,传“空值”,表示无标题 * @param headers * 表头数组 */ public ExportExcel(String title, String[] headers) { initialize(title, Lists.newArrayList(headers)); } /** * 构造函数 * * @param title * 表格标题,传“空值”,表示无标题 * @param headerList * 表头列表 */ public ExportExcel(String title, List<String> headerList) { initialize(title, headerList); } /** * 初始化函数 * * @param title * 表格标题,传“空值”,表示无标题 * @param headerList * 表头列表 */ private void initialize(String title, List<String> headerList) { this.wb = new SXSSFWorkbook(500); this.sheet = wb.createSheet("Export"); this.styles = createStyles(wb); // Create title if (StringUtils.isNotBlank(title)) { Row titleRow = sheet.createRow(rownum++); titleRow.setHeightInPoints(30); Cell titleCell = titleRow.createCell(0); titleCell.setCellStyle(styles.get("title")); titleCell.setCellValue(title); sheet.addMergedRegion(new CellRangeAddress(titleRow.getRowNum(), titleRow.getRowNum(), titleRow.getRowNum(), headerList.size() - 1)); } // Create header if (headerList == null) { throw new RuntimeException("headerList not null!"); } Row headerRow = sheet.createRow(rownum++); headerRow.setHeightInPoints(16); sheet.trackAllColumnsForAutoSizing(); //自动调整列宽 for (int i = 0; i < headerList.size(); i++) { Cell cell = headerRow.createCell(i); cell.setCellStyle(styles.get("header")); String[] ss = StringUtils.split(headerList.get(i), "**", 2); if (ss.length == 2) { cell.setCellValue(ss[0]); Comment comment = this.sheet.createDrawingPatriarch() .createCellComment(new XSSFClientAnchor(0, 0, 0, 0, (short) 3, 3, (short) 5, 6)); comment.setString(new XSSFRichTextString(ss[1])); cell.setCellComment(comment); } else { cell.setCellValue(headerList.get(i)); } sheet.autoSizeColumn(i); } for (int i = 0; i < headerList.size(); i++) { int colWidth = sheet.getColumnWidth(i) * 2; sheet.setColumnWidth(i, colWidth < 3000 ? 3000 : colWidth); } log.debug("Initialize success."); } /** * 创建表格内容行 * @param clsList 数据集合 */ public void writeContent(List<?> clsList) { for (Object t : clsList) { Row row = sheet.createRow(rownum++); for (Object[] os : annotationList) { Field f = ((Field) os[1]); try { f.setAccessible(true); Field objField = t.getClass().getDeclaredField(f.getName()); objField.setAccessible(true); Object objVal = objField.get(t); if(objVal instanceof Date) { row.createCell(annotationList.indexOf(os)).setCellValue(new Date(((Date)objVal).getTime())); }else { row.createCell(annotationList.indexOf(os)).setCellValue(String.valueOf(objVal)); } } catch (IllegalArgumentException | IllegalAccessException | NoSuchFieldException | SecurityException e) { e.printStackTrace(); } } } } /** * 创建表格样式 * * @param wb * 工作薄对象 * @return 样式列表 */ private Map<String, CellStyle> createStyles(Workbook wb) { Map<String, CellStyle> styles = new HashMap<String, CellStyle>(); CellStyle style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); Font titleFont = wb.createFont(); titleFont.setFontName("Arial"); titleFont.setFontHeightInPoints((short) 16); titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD); style.setFont(titleFont); styles.put("title", style); style = wb.createCellStyle(); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setBorderRight(CellStyle.BORDER_THIN); style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex()); style.setBorderLeft(CellStyle.BORDER_THIN); style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex()); style.setBorderTop(CellStyle.BORDER_THIN); style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex()); style.setBorderBottom(CellStyle.BORDER_THIN); style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex()); Font dataFont = wb.createFont(); dataFont.setFontName("Arial"); dataFont.setFontHeightInPoints((short) 10); style.setFont(dataFont); styles.put("data", style); style = wb.createCellStyle(); style.cloneStyleFrom(styles.get("data")); style.setAlignment(CellStyle.ALIGN_LEFT); styles.put("data1", style); style = wb.createCellStyle(); style.cloneStyleFrom(styles.get("data")); style.setAlignment(CellStyle.ALIGN_CENTER); styles.put("data2", style); style = wb.createCellStyle(); style.cloneStyleFrom(styles.get("data")); style.setAlignment(CellStyle.ALIGN_RIGHT); styles.put("data3", style); style = wb.createCellStyle(); style.cloneStyleFrom(styles.get("data")); // style.setWrapText(true); style.setAlignment(CellStyle.ALIGN_CENTER); style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); Font headerFont = wb.createFont(); headerFont.setFontName("Arial"); headerFont.setFontHeightInPoints((short) 10); headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); headerFont.setColor(IndexedColors.WHITE.getIndex()); style.setFont(headerFont); styles.put("header", style); return styles; } /** * 添加一行 * * @return 行对象 */ public Row addRow() { return sheet.createRow(rownum++); } /** * 输出数据流 * * @param os * 输出数据流 */ public ExportExcel write(OutputStream os) throws IOException { wb.write(os); return this; } /** * 输出到客户端 * * @param fileName * 输出文件名 */ public ExportExcel write(HttpServletResponse response, String fileName) throws IOException { response.reset(); response.setContentType("application/octet-stream; charset=utf-8"); response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(fileName, "UTF-8")); write(response.getOutputStream()); return this; } /** * 输出到文件 * * @param fileName * 输出文件名 */ public ExportExcel writeFile(String name) throws FileNotFoundException, IOException { FileOutputStream os = new FileOutputStream(name); this.write(os); return this; } /** * 清理临时文件 */ public ExportExcel dispose() { wb.dispose(); return this; } public SXSSFSheet getSheet() { return sheet; } }
导入类:
package com.tcl.ub.core.util; import java.io.File; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.IOException; import java.io.InputStream; import java.lang.reflect.Field; import java.lang.reflect.Type; import java.math.BigDecimal; import java.text.ParseException; import java.util.Date; import java.util.List; import java.util.Map; import java.util.Objects; import org.apache.commons.lang3.StringUtils; import org.apache.poi.hssf.usermodel.HSSFDateUtil; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.openxml4j.exceptions.InvalidFormatException; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.web.multipart.MultipartFile; import com.google.common.collect.Lists; import com.tcl.ub.core.base.annotation.ExcelField; import com.tcl.ub.core.base.validator.ValidatorUtil; import com.tcl.ub.webapp.common.exception.BusinessException; import com.tcl.ub.webapp.common.exception.ParamErrorException; import static com.tcl.ub.webapp.common.util.GlobalKey.XLS; import static com.tcl.ub.webapp.common.util.GlobalKey.XLSX; /** * 导入Excel文件(支持“XLS”和“XLSX”格式) * * @author ThinkGem * @version 2013-03-10 */ public class ImportExcel { private static Logger log = LoggerFactory.getLogger(ImportExcel.class); /** * 工作薄对象 */ private Workbook wb; /** * 工作表对象 */ private Sheet sheet; /** * 标题行号 */ private int headerNum; /** * 注解列表(Object[]{ ExcelField, Field/Method }) */ List<Object[]> annotationList = Lists.newArrayList(); /** * 构造函数 * * @param path * 导入文件,读取第一个工作表 * @param headerNum * 标题行号,数据行号=标题行号+1 * @throws InvalidFormatException * @throws IOException * @throws BusinessErrorException */ public ImportExcel(String fileName, int headerNum) throws IOException, BusinessException { this(new File(fileName), headerNum); } /** * 构造函数 * * @param path * 导入文件对象,读取第一个工作表 * @param headerNum * 标题行号,数据行号=标题行号+1 * @throws InvalidFormatException * @throws IOException * @throws BusinessErrorException */ public ImportExcel(File file, int headerNum) throws IOException, BusinessException { this(file, headerNum, 0); } /** * 构造函数 * * @param path * 导入文件 * @param headerNum * 标题行号,数据行号=标题行号+1 * @param sheetIndex * 工作表编号 * @throws InvalidFormatException * @throws IOException * @throws BusinessErrorException */ public ImportExcel(String fileName, int headerNum, int sheetIndex) throws IOException, BusinessException { this(new File(fileName), headerNum, sheetIndex); } /** * 构造函数 * * @param path * 导入文件对象 * @param headerNum * 标题行号,数据行号=标题行号+1 * @param sheetIndex * 工作表编号 * @throws FileNotFoundException * @throws InvalidFormatException * @throws IOException * @throws BusinessErrorException */ public ImportExcel(File file, int headerNum, int sheetIndex) throws BusinessException, IOException { this(file.getName(), new FileInputStream(file), headerNum, sheetIndex); } /** * 构造函数 * * @param file * 导入文件对象 * @param headerNum * 标题行号,数据行号=标题行号+1 * @param sheetIndex * 工作表编号 * @throws InvalidFormatException * @throws IOException * @throws BusinessErrorException */ public ImportExcel(MultipartFile multipartFile, int headerNum, int sheetIndex) throws IOException, BusinessException { this(multipartFile.getOriginalFilename(), multipartFile.getInputStream(), headerNum, sheetIndex); } /** * 构造函数 * * @param path * 导入文件对象 * @param headerNum * 标题行号,数据行号=标题行号+1 * @param sheetIndex * 工作表编号 * @throws InvalidFormatException * @throws IOException * @throws BusinessErrorException */ public ImportExcel(String fileName, InputStream is, int headerNum, int sheetIndex) throws BusinessException, IOException { if (StringUtils.isBlank(fileName)) { throw new BusinessException("导入文档为空!", ""); } else if (fileName.toLowerCase().endsWith(XLS)) { this.wb = new HSSFWorkbook(is); } else if (fileName.toLowerCase().endsWith(XLSX)) { this.wb = new XSSFWorkbook(is); } else { throw new BusinessException("文档格式不正确!", ""); } if (this.wb.getNumberOfSheets() < sheetIndex) { throw new BusinessException("文档中没有工作表!", ""); } this.sheet = this.wb.getSheetAt(sheetIndex); this.headerNum = headerNum; log.debug("Initialize success."); } /** * 初始化加载出标题对应的字段(类型,名称) * @param cls * @param type * @throws ParamErrorException * @throws BusinessException */ public void initialize(Class<?> cls, int type) throws BusinessException { /** 读取标题行*/ Row headRow = getRow(headerNum); for(int c = 0; c < getLastCellNum(); c++) { Object cellValue = valueOfType(getCellValue(headRow, c), String.class); /** 标记: 如果循环属性的注解title和单元格cellValue相同的时,+1。如果这一波属性遍历完之后没有a=0表示某标题有误,不符合*/ int a=0; Field[] fields = cls.getDeclaredFields(); for (Field f : fields) { ExcelField ef = f.getAnnotation(ExcelField.class); if(ef != null && (ef.type() == 0 || ef.type() == type)) { if(Objects.equals(ef.title(), cellValue)) { annotationList.add(new Object[] { ef, f }); a++; break; } } } //当遍历完之后判断标题是否和相应类的注解中title有相同 if(a==0) { log.error("客户订单数据导入时,initialize()方法error,导入的excel中单元格式内的标题不相符合"); throw new BusinessException("标题行中单元格式内的标题不相符合,请按照下载模板中的格式导入", ""); } } } /** * 读取Excel给封装的泛型类属性赋值 * @param cls * @param type * @return * @throws InstantiationException * @throws IllegalAccessException * @throws NoSuchFieldException * @throws SecurityException */ public List<?> readExcel(Class<?> cls, int type) { Object objBean = null; List<Object> resultList = Lists.newArrayList(); /** 读取内容行*/ for (int i = getDataRowNum(); i < getLastDataRowNum(); i++) { Row row = getRow(i); if (row == null) { continue; } objBean = writeObj(row, cls); /** 校验对象的字段值*/ if(check(objBean)) { resultList.add(objBean); } } return resultList; } /** * 读取表格行根据Class的类型生成实例并赋值 * @param row * @param cls * @return */ public Object writeObj(Row row, Class<?> cls) { Object objBean = null; try { /** 生成cls类型的实例对象*/ objBean = cls.newInstance(); } catch (InstantiationException | IllegalAccessException e1) { e1.printStackTrace(); } for(int c = 0; c < getLastCellNum(); c++) { Object[] obj = annotationList.get(c); Field field = (Field)obj[1]; field.setAccessible(true); Field objField = null; try { /** 根据字段名称获取在对象中声明的属性,给属性赋值*/ objField = objBean.getClass().getDeclaredField(field.getName()); objField.setAccessible(true); objField.set(objBean, valueOfType(getCellValue(row, c), objField.getGenericType())); objField.setAccessible(false); } catch (NoSuchFieldException | SecurityException | IllegalArgumentException | IllegalAccessException e) { e.printStackTrace(); } field.setAccessible(false); } return objBean; } /** * 获取行对象 * * @param rownum * @return */ public Row getRow(int rownum) { return this.sheet.getRow(rownum); } /** * 获取数据行号 * * @return */ public int getDataRowNum() { return headerNum + 1; } /** * 获取最后一个数据行号 * * @return */ public int getLastDataRowNum() { return this.sheet.getPhysicalNumberOfRows() + headerNum; } /** * 获取最后一个列号 * * @return */ public int getLastCellNum() { return this.getRow(headerNum).getLastCellNum(); } /** * 获取单元格值 * * @param row * 获取的行 * @param column * 获取单元格列号 * @return 单元格值 */ public Object getCellValue(Row row, int column) { Object val = ""; try { Cell cell = row.getCell(column); if (cell != null) { switch(cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: if(HSSFDateUtil.isCellDateFormatted(cell)) { val = DateUtil.convertDate2String(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()), DateUtil.LONG_DATE_FORMAT); } else { BigDecimal big = new BigDecimal(cell.getNumericCellValue()); String value = big.toString(); //解决1234.0 去掉后面的.0 if(null != value && !"".equals(value.trim())){ String[] item = value.split("[.]"); if(1 < item.length && "0".equals(item[1])){ value = item[0]; } else if(1 < item.length && !"0".equals(item[1])) { value = String.valueOf(new Double(cell.getNumericCellValue())); } } val = value; } break; case Cell.CELL_TYPE_STRING: val = cell.getStringCellValue(); break; case Cell.CELL_TYPE_FORMULA: val = cell.getCellFormula(); break; case Cell.CELL_TYPE_BOOLEAN: val = cell.getBooleanCellValue(); break; case Cell.CELL_TYPE_BLANK: val = ""; break; case Cell.CELL_TYPE_ERROR: val = cell.getErrorCellValue(); break; default: val = cell.getStringCellValue().toString(); break; } val = val.equals("null") || val.equals("NULL") || val.equals("NaN") ? "" : val; } } catch (Exception e) { return val; } return val; } /** * * @param obj * @param type * @return */ public static Object valueOfType(Object obj, Type type) { Object value = ""; // 以下是判断数据的类型 if(type instanceof Class<?>){ Class<?> cls = (Class<?>)type; if(String.class.isAssignableFrom(cls)) { value = String.valueOf(obj); }else if(Integer.class.isAssignableFrom(cls)) { value = Integer.valueOf(obj.toString()); }else if(Long.class.isAssignableFrom(cls)) { value = Long.valueOf(obj.toString()); }else if(Double.class.isAssignableFrom(cls)){ value = Double.valueOf(obj.toString()); }else if(Date.class.isAssignableFrom(cls)) { try { value = DateUtil.formatDateSecond(obj.toString()); } catch (ParseException e) { e.printStackTrace(); log.error("ImportExcel类中的valueOfType()出现错误"); } }else if(Boolean.class.isAssignableFrom(cls)) { value = Boolean.valueOf(obj.toString()); } return value; } return null; } /** * 判断行是否为空 * @param row * @return */ public Boolean checkRowNull(Row row) { int num = 0; for(int c = 0; c < getLastCellNum(); c++) { Cell cell = row.getCell(c); if (cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK) { num++; } } return num == getLastCellNum(); } /** * 校验数据 * @param objBean * @return */ private boolean check(Object objBean) { ValidatorUtil validatorUtil = new ValidatorUtil(); try { validatorUtil.valid(objBean); for (Map.Entry<String, Object> entry : validatorUtil.validateResult.entrySet()) { if("message".equals(entry.getKey())) { log.error("message:{}", entry.getValue()); } } return validatorUtil.validateResult.size() > 0; } catch (Exception e) { e.printStackTrace(); } return true; } }
校验类:
package com.tcl.ub.core.base.validator; import java.lang.reflect.Field; import java.util.HashMap; import java.util.Map; import org.springframework.util.CollectionUtils; import org.springframework.util.ObjectUtils; import com.tcl.ub.core.base.annotation.DV; import com.tcl.ub.core.base.enums.RegexType; import com.tcl.ub.core.util.RegexUtils; /** * * @author liuqihao * */ public class ValidatorUtil { private DV dv; public Map<String, Object> validateResult; public ValidatorUtil(){ validateResult = new HashMap<>(); } /** * 校验对象 * @param obj * @throws Exception */ public void valid(Object obj) throws Exception { if(CollectionUtils.isEmpty(validateResult)) { validateResult = new HashMap<>(); } else { validateResult.clear(); } /** 获取obj的类型*/ Class<? extends Object> cls = obj.getClass(); /** 获取cls类型的所有声明字段*/ Field [] fields = cls.getDeclaredFields(); for (Field field : fields) { /** 设置字段可访问*/ field.setAccessible(true); valid(field, obj); /** 设置访问后关闭*/ field.setAccessible(false); } } /** * 根据字段声明的注解校验字段值 * @param field * @param obj * @throws IllegalArgumentException * @throws IllegalAccessException */ public void valid(Field field, Object obj) throws IllegalArgumentException, IllegalAccessException { /** 获取对象的成员的注解信息*/ dv = field.getAnnotation(DV.class); if(dv == null) { return; } Object value = field.get(obj); String description = ("").equals(dv.description()) ? field.getName() : dv.description(); /** 是否允许为空*/ if(!dv.nullable()) { if(ObjectUtils.isEmpty(value)) { validateResult.put("message", description + "不能为空"); validateResult.put("result", false); } } /** 校验字段长度*/ if(dv.maxLength() > 0 || dv.minLength() > 0) { if(value instanceof String) { if(((String) value).length() > dv.maxLength() || ((String) value).length() < dv.minLength()) { StringBuffer sbf = new StringBuffer(description); sbf.append("长度不符合要求,最大长度").append(dv.maxLength()).append("最小长度").append(dv.minLength()); validateResult.put("message", sbf.toString()); validateResult.put("result", false); } } } /** 是否启用正则规则*/ if(!RegexType.NONE.equals(dv.regexType()) && dv.regexType() != null) { switch (dv.regexType()) { case SPECIALCHAR: if(RegexUtils.hasSpecialChar(value.toString())){ validateResult.put("message", description+"不能含有特殊字符"); validateResult.put("result", false); } break; case CHINESE: if(RegexUtils.isChinese2(value.toString())){ validateResult.put("message", description+"不能含有中文字符"); validateResult.put("result", false); } break; case EMAIL: if(!RegexUtils.isEmail(value.toString())){ validateResult.put("message", description+"地址格式不正确"); validateResult.put("result", false); } break; case IP: if(!RegexUtils.isIp(value.toString())){ validateResult.put("message", description+"地址格式不正确"); validateResult.put("result", false); } break; case NUMBER: if(!RegexUtils.isNumber(value.toString())){ validateResult.put("message", description+"不是数字"); validateResult.put("result", false); } break; case PHONENUMBER: if(!RegexUtils.isPhoneNumber(value.toString())){ validateResult.put("message", description+"["+value.toString()+"]"+"不是数字"); validateResult.put("result", false); } break; default: break; } } if(!dv.regexExpression().equals("")){ if(value.toString().matches(dv.regexExpression())){ validateResult.put("message", description+"数据格式不正确"); validateResult.put("result", false); } } } }
元数据类型:
package com.tcl.ub.core.base.annotation; import java.lang.annotation.ElementType; import java.lang.annotation.Retention; import java.lang.annotation.RetentionPolicy; import java.lang.annotation.Target; /** * Excel注解定义 */ @Target({ElementType.METHOD, ElementType.FIELD, ElementType.TYPE}) @Retention(RetentionPolicy.RUNTIME) public @interface ExcelField { /** * 导出字段名(默认调用当前字段的“get”方法,如指定导出字段为对象,请填写“对象名.对象属性”,例:“area.name”、“office.name”) */ String value() default ""; /** * 导出字段标题(需要添加批注请用“**”分隔,标题**批注,仅对导出模板有效) */ String title(); /** * 字段类型(0:导出导入;1:仅导出;2:仅导入) */ int type() default 0; /** * 导出字段对齐方式(0:自动;1:靠左;2:居中;3:靠右) */ int align() default 0; /** * 导出字段字段排序(升序) */ int sort() default 0; /** * 如果是字典类型,请设置字典的type值 */ String dictType() default ""; /** * 反射类型 */ Class<?> fieldType() default Class.class; /** * 字段归属组(根据分组导出导入) */ int[] groups() default {}; }
元数据类型2(作导入失败时的类型注解用):
package com.tcl.ub.core.base.annotation;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
import com.tcl.ub.core.base.enums.RegexType;
/**
*
* @Description: 数据校验注解
* @author liuqihao
* @date 2017年12月4日
*/
@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.FIELD, ElementType.PARAMETER})
public @interface DV {
//是否可以为空
boolean nullable() default false;
//最大长度
int maxLength() default 0;
//最小长度
int minLength() default 0;
//提供几种常用的正则验证
RegexType regexType() default RegexType.NONE;
//自定义正则验证
String regexExpression() default "";
//参数或者字段描述,这样能够显示友好的异常信息
String description() default "";
}
实体类dto:
package com.tcl.ub.core.base.entity; import java.io.Serializable; import com.tcl.ub.core.base.annotation.DV; import com.tcl.ub.core.base.annotation.ExcelField; import com.tcl.ub.core.base.enums.RegexType; /** * 会员交易记录表对应实体类 * @Description: * @author sun * @date 2017年11月20日 */ public class CustomerFansOrder implements Serializable{ /** * */ private static final long serialVersionUID = -2003955867815772647L; /** * 主键id */ private Integer id; /** * 订单Id */ @DV(nullable=false, description="订单号") @ExcelField(title="订单号", type=0) private String orderId; /** * 订单平台 */ @DV(nullable=false, description="下单平台") @ExcelField(title="下单平台", type=0) private String orderPlatform; /** * 下单时间 */ @DV(nullable=false, description="下单时间") @ExcelField(title="下单时间", type=0) private String orderTime; /** * 商品名称 */ @DV(nullable=false, description="商品名称") @ExcelField(title="商品名称", type=0) private String productName; /** * 商品数量 */ @DV(nullable=false, description="商品数量") @ExcelField(title="商品数量", type=0) private Long productCount; /** * 订单总额 */ @DV(nullable=false, description="订单总额") @ExcelField(title="订单总额", type=0) private Double orderAmount; /** * 商品SN编码 */ @DV(nullable=true, description="商品SN码") @ExcelField(title="商品SN码", type=0) private String productSNCode; /** * 收件人 */ @DV(nullable=false, description="收件人") @ExcelField(title="收件人", type=0) private String receiver; /** * 联系电话 */ @DV(nullable=false, regexType=RegexType.PHONENUMBER, description="联系电话") @ExcelField(title="联系电话", type=0) private String mobile; /** * 客户ID */ private String ssoid; public CustomerFansOrder() { super(); } public CustomerFansOrder(Integer id, String orderId, String orderPlatform, String orderTime, String productName, Long productCount, Double orderAmount, String productSNCode, String receiver, String mobile) { super(); this.id = id; this.orderId = orderId; this.orderPlatform = orderPlatform; this.orderTime = orderTime; this.productName = productName; this.productCount = productCount; this.orderAmount = orderAmount; this.productSNCode = productSNCode; this.receiver = receiver; this.mobile = mobile; } public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getOrderId() { return orderId; } public void setOrderId(String orderId) { this.orderId = orderId; } public String getOrderPlatform() { return orderPlatform; } public void setOrderPlatform(String orderPlatform) { this.orderPlatform = orderPlatform; } public String getOrderTime() { return orderTime; } public void setOrderTime(String orderTime) { this.orderTime = orderTime; } public String getProductName() { return productName; } public void setProductName(String productName) { this.productName = productName; } public Long getProductCount() { return productCount; } public void setProductCount(Long productCount) { this.productCount = productCount; } public Double getOrderAmount() { return orderAmount; } public void setOrderAmount(Double orderAmount) { this.orderAmount = orderAmount; } public String getProductSNCode() { return productSNCode; } public void setProductSNCode(String productSNCode) { this.productSNCode = productSNCode; } public String getReceiver() { return receiver; } public void setReceiver(String receiver) { this.receiver = receiver; } public String getMobile() { return mobile; } public void setMobile(String mobile) { this.mobile = mobile; } public String getSsoid() { return ssoid; } public void setSsoid(String ssoid) { this.ssoid = ssoid; } }
作导入失败时存储失败数据的dto:
package com.tcl.ub.webapp.customer.dto; import java.io.Serializable; import com.tcl.ub.core.base.annotation.ExcelField; /** * 会员交易记录表对应实体类 * @Description: * @author sun * @date 2017年11月20日 */ public class CustomerFansOrderDto implements Serializable{ /** * */ private static final long serialVersionUID = -2003955867815772647L; /** * 主键id */ private Integer id; /** * 订单Id */ @ExcelField(title="订单号", type=0) private String orderId; /** * 订单平台 */ @ExcelField(title="下单平台", type=0) private String orderPlatform; /** * 下单时间 */ @ExcelField(title="下单时间", type=0) private String orderTime; /** * 商品名称 */ @ExcelField(title="商品名称", type=0) private String productName; /** * 商品数量 */ @ExcelField(title="商品数量", type=0) private String productCount; /** * 订单总额 */ @ExcelField(title="订单总额", type=0) private String orderAmount; /** * 商品SN编码 */ @ExcelField(title="商品SN码", type=0) private String productSNCode; /** * 收件人 */ @ExcelField(title="收件人", type=0) private String receiver; /** * 联系电话 */ @ExcelField(title="联系电话", type=0) private String mobile; /** * 客户ID */ private String ssoid; public CustomerFansOrderDto() { super(); } public CustomerFansOrderDto(Integer id, String orderId, String orderPlatform, String orderTime, String productName, String productCount, String orderAmount, String productSNCode, String receiver, String mobile) { super(); this.id = id; this.orderId = orderId; this.orderPlatform = orderPlatform; this.orderTime = orderTime; this.productName = productName; this.productCount = productCount; this.orderAmount = orderAmount; this.productSNCode = productSNCode; this.receiver = receiver; this.mobile = mobile; } public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getOrderId() { return orderId; } public void setOrderId(String orderId) { this.orderId = orderId; } public String getOrderPlatform() { return orderPlatform; } public void setOrderPlatform(String orderPlatform) { this.orderPlatform = orderPlatform; } public String getOrderTime() { return orderTime; } public void setOrderTime(String orderTime) { this.orderTime = orderTime; } public String getProductName() { return productName; } public void setProductName(String productName) { this.productName = productName; } public String getProductCount() { return productCount; } public void setProductCount(String productCount) { this.productCount = productCount; } public String getOrderAmount() { return orderAmount; } public void setOrderAmount(String orderAmount) { this.orderAmount = orderAmount; } public String getProductSNCode() { return productSNCode; } public void setProductSNCode(String productSNCode) { this.productSNCode = productSNCode; } public String getReceiver() { return receiver; } public void setReceiver(String receiver) { this.receiver = receiver; } public String getMobile() { return mobile; } public void setMobile(String mobile) { this.mobile = mobile; } public String getSsoid() { return ssoid; } public void setSsoid(String ssoid) { this.ssoid = ssoid; } }