Annotation Excel

最近在开发一些通用的excel数据导入的功能，由于涉及到导入的模块很多，所以开发了一个比较通用的excel导入模板类文件。并且使用annotation作为验证数据的配置。

01 package com.hp.dylan.jv;

02

03 import java.lang.annotation.ElementType;

04 import java.lang.annotation.Retention;

05 import java.lang.annotation.RetentionPolicy;

06 import java.lang.annotation.Target;

07

08 @Retention(RetentionPolicy.RUNTIME)

09 @Target(ElementType.FIELD)

10 public @interface ExcelAnnotation {

11

12 //excel column name

13 public String importCellName();

14

15 //excel cell data weather is required

16 public boolean isRequired();

17

18 //excel cell data datalength.

19 public int importDataLen();

20

21 //excel cell data type.

22 public String importDataType();

23

24 //excel cell mapping index.

25 public int importCellIndex();

26

27 //excel data weather eixts in db.

28 public boolean isEixtance();

29 }
1 看看需要导入excel的模型类的定义：(AdditionJvImportModel)
01 package com.hp.dylan.jv;

02 public class AdditionJvImportModel {

03 @ExcelAnnotation( importCellName = "EN1",isRequired=true ,importDataLen=100,importDataType="String",importCellIndex = 1,isEixtance=true)

04 private String sellEntity;

05 @ExcelAnnotation(importCellName = "SE2",isRequired=false ,importDataLen=4,importDataType="String",importCellIndex =2, isEixtance=false)

06 private String sellSe;

07 @ExcelAnnotation(importCellName = "DI3",isRequired=false ,importDataLen=10,importDataType="String",importCellIndex = 3, isEixtance=false)

08 private String sellDi;

09 @ExcelAnnotation(importCellName = "DEPT4",isRequired=false ,importDataLen=8,importDataType="String",importCellIndex = 4, isEixtance=false)

10 private String sellDept;

11 @ExcelAnnotation(importCellName = "SF5",isRequired=false ,importDataLen=10,importDataType="String",importCellIndex = 5, isEixtance=false)

12 private String sellSf;

13 @ExcelAnnotation(importCellName = "PT6",isRequired=false ,importDataLen=4,importDataType="String",importCellIndex = 6, isEixtance=false)

14 private String sellPt;

15 @ExcelAnnotation(importCellName = "PL7",isRequired=false ,importDataLen=10,importDataType="String",importCellIndex = 7, isEixtance=false)

16 private String sellPl;

17 @ExcelAnnotation(importCellName = "SL8",isRequired=false ,importDataLen=4,importDataType="String",importCellIndex = 8, isEixtance=false)

18 private String sellSl;

19 @ExcelAnnotation(importCellName = "Amount9",isRequired=false ,importDataLen=25,importDataType="number(25,10)",importCellIndex = 9, isEixtance=false)

20 private String sellAmout;

21 @ExcelAnnotation(importCellName = "EN10",isRequired=true ,importDataLen=100,importDataType="String",importCellIndex = 10, isEixtance=true)

22 private String buyEntity;

23 @ExcelAnnotation(importCellName = "SE11",isRequired=false ,importDataLen=4,importDataType="String",importCellIndex = 11, isEixtance=false)

24 private String buySe;

25 @ExcelAnnotation(importCellName = "DI12",isRequired=false ,importDataLen=10,importDataType="String",importCellIndex = 12, isEixtance=false)

26 private String buyDi;

27 @ExcelAnnotation(importCellName = "DEPT13",isRequired=false ,importDataLen=8,importDataType="String",importCellIndex = 13, isEixtance=false)

28 private String buyDept;

29 @ExcelAnnotation(importCellName = "SF14",isRequired=false ,importDataLen=10,importDataType="String",importCellIndex = 14, isEixtance=false)

30 private String buySf;

31 @ExcelAnnotation(importCellName = "PT15",isRequired=false ,importDataLen=4,importDataType="String",importCellIndex = 15, isEixtance=false)

32 private String buyPt;

33 @ExcelAnnotation(importCellName = "PL16",isRequired=false ,importDataLen=10,importDataType="String",importCellIndex = 16, isEixtance=false)

34 private String buyPl;

35 @ExcelAnnotation(importCellName = "SL17",isRequired=false ,importDataLen=4,importDataType="String",importCellIndex = 17, isEixtance=false)

36 private String buySl;

37 @ExcelAnnotation(importCellName = "LSA Factor19",isRequired=false ,importDataLen=25,importDataType="number(25,10)",importCellIndex = 19, isEixtance=false)

38 private String tranFactor;

39 @ExcelAnnotation(importCellName = "GrossICPurchase20",isRequired=false ,importDataLen=25,importDataType="number(25,10)",importCellIndex = 20, isEixtance=false)

40 private String tranGross;

41 @ExcelAnnotation(importCellName = "EX Code21",isRequired=false ,importDataLen=5,importDataType="String",importCellIndex = 21, isEixtance=false)

42 private String tranExCode;

43 @ExcelAnnotation(importCellName = "Transaction Description24",isRequired=false ,importDataLen=200,importDataType="String",importCellIndex = 24, isEixtance=false)

44 private String tranDesr;

45 @ExcelAnnotation(importCellName = "Line Comments25",isRequired=false ,importDataLen=200,importDataType="String",importCellIndex = 25, isEixtance=false)

46 private String tranLineComm;

47 @ExcelAnnotation(importCellName = "Additional Line Comments30",isRequired=false ,importDataLen=200,importDataType="String",importCellIndex = 30, isEixtance=false)

48 private String tranAddLinComm;

49 @ExcelAnnotation(importCellName = "FROM VATID31",isRequired=false ,importDataLen=6,importDataType="String",importCellIndex = 31, isEixtance=false)

50 private String tranFVatId;

51 @ExcelAnnotation(importCellName = "TO VATID32",isRequired=false ,importDataLen=6,importDataType="String",importCellIndex = 32, isEixtance=false)

52 private String tranTVatId;

53 }
1 excel模型的解析中如何获取到此模型的描述配置。也就是获取他的annotation。
1 Map<Integer,ExcelAnnotation> mapping=new HashMap<Integer,ExcelAnnotation>();

2 for (int i = 0; i < filed.length; i++) {

3 Field f = filed[i];

4 ExcelAnnotation excel = f.getAnnotation(ExcelAnnotation.class);

5 if (excel != null) {

6 mapping.put(excel.importCellIndex(), excel);

7 }

8 }
1 如何根据annotation的描述来验证。
1 if(anno.isRequired()){

2 if(cellValue==null||"".equals(cellValue.trim())){

3 sb.append(anno.importCellName()+" can not be empty!"); 。。。}

4 }
1 如果需要跟db交互来进行检验的话，只需要定义一个abstract校验方法，具体的校验规则交给其实现者来完成
1 public abstract String validateExist(String value);
Noted:

你可以annotation完全的看做一种配置，你只需要获取你的model种的annotation标识，然后根据标识来进行一个校验规则的解析

你也可以结合 class reflection 机制来使用annotation，获取到model的annotation描述之后，根据class reflection 来动态的激活调用一些model中的方法，