Software project - > java annotation and reflection mechanism practice: excel tool class in springboot+mybatis project

chance

The software engineering teaching assistant developed this time involves the import of excel files. In the previous similar projects, they all wrote some code with poor reusability and cumbersome to deal with excel tables. The software engineering class systematically planned the time. It was found that this part of the code can be polished according to the time planning, and a convenient, fast and reusable tool class can be written, so it was decided to do so.

thinking

Before, I saw a great God who wrote that the tool class can use annotations to mark the attributes of the entity class, and then use the reflection mechanism to easily map the data in Excel into objects or store objects in Excel. It happened that I studied the annotation and reflection mechanism of java some time ago, so I decided to follow the writing method of great God and write a more lightweight excel tool class.
Customize an annotation to mark the column name and column sequence number of the entity class.
Then write an excel tool class, which implements a method of parsing annotations, and read Excel data or export Excel data according to the parsing results.

code

The first is the custom annotation. Since we need to use the reflection mechanism to resolve the entity class annotated by the annotation, our customized annotation must survive at runtime, so its life cycle should be set to @ Retention(RetentionPolicy.RUNTIME).
In addition, the annotation object of our custom annotation only has the attribute of entity class, so its annotation object should be set to @ Target(ElementType.FIELD)
The content of annotation is relatively simple, only column name and column serial number. So the final annotated code is

@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface ExcelAnnotation {
    String name();
    int col();

}

It is also very simple to use. The following is the student entity class

public class Student implements Serializable {
    private Integer id;

    @ExcelAnnotation(name = "Student number",col = 0)
    private Long stuID;

    @ExcelAnnotation(name = "full name",col = 1)
    private String name;

    @ExcelAnnotation(name = "password",col= 2)
    private String password;

    @ExcelAnnotation(name = "direction id",col = 3)
    private Integer p_field_id;
}

Just annotate the column name and column number above the attribute.
After the custom annotation is finished, we will start to write excel tool classes.
First of all, the target entity class resolved by the tool class is unique and unchanged in the whole processing flow, so we should use to limit the object of operation when declaring the tool class

public class ExcelUtil<T> {
}

In addition, if you want to use the reflection mechanism to resolve the target entity class, you need to obtain the class object of the entity class in the JVM, which can be used as a member of our tool class to facilitate subsequent calls

public class ExcelUtil<T> {
    private Class<T> tClass;
    //Constructor
    public ExcelUtil(Class<T> tClass){
        this.tClass = tClass;
    }
}

Then, whether it is the result of excel parsing or the entity data that may be used for export, we store it in the linked List. At this time, we need a List, and these data can also be used as members of our tool class to facilitate subsequent calls

public class ExcelUtil<T> {
    private Class<T> tClass;
    private List<T> data;
    //Constructor
    public ExcelUtil(Class<T> tClass){
        this.tClass = tClass;
        this.data = new ArrayList<T>();
    }
}

Next, we may also parse the attribute set. We can also store this in the linked list and use it as a member to facilitate subsequent calls

public class ExcelUtil<T> {
    private Class<T> tClass;
    private List<T> data;
    private List<Field> fields;

    //Constructor
    public ExcelUtil(Class<T> tClass){
        this.tClass = tClass;
        this.data = new ArrayList<T>();
        this.fields = new ArrayList<Field>();
    }
}

Next, we need to parse the annotation, and the parsing results can be stored in the member variable fields

    //Method of parsing annotation to obtain attribute set
    public void parseAnnotation(Class cClass){
        if(this.fields==null){
            this.fields = new ArrayList<Field>();
        }
        Field[] fieldsArray = cClass.getDeclaredFields();
        for(Field f:fieldsArray){
            //Add the attribute with Excel annotation annotation to the attribute list
            boolean hasAnnotation = f.isAnnotationPresent(ExcelAnnotation.class);
            if(hasAnnotation){
                //Set private members to be accessible through reflection
                f.setAccessible(true);
                this.fields.add(f);
            }
        }
        //If there is a superclass in this class (and the type of superclass is class), resolve the superclass recursively
        if(cClass.getSuperclass()!=null&&cClass.getSuperclass().equals(Object.class)){
            parseAnnotation(cClass.getSuperclass());
        }
    }

Then we wrap this method, because the initial parameter we pass in must be the class object tClass initialized in the constructor, so we can use this member as the default parameter

    //Overload the parseAnnotation method to set the default parameters
    public void parseAnnotation(){
        parseAnnotation(this.tClass);
    }

Then the method of parsing annotations can be put in the constructor to directly parse the contents of member fields. So the sorted code is as follows

public class ExcelUtil<T> {
    private Class<T> tClass;
    private List<T> data;
    private List<Field> fields;

    //Constructor
    public ExcelUtil(Class<T> tClass){
        this.tClass = tClass;
        this.data = new ArrayList<T>();
        this.fields = new ArrayList<Field>();
        //Parsing annotation to get attribute set
        parseAnnotation();
    }

    public List<T> getData() {
        return data;
    }

    //Method of parsing annotation to obtain attribute set
    public void parseAnnotation(Class cClass){
        if(this.fields==null){
            this.fields = new ArrayList<Field>();
        }
        Field[] fieldsArray = cClass.getDeclaredFields();
        for(Field f:fieldsArray){
            //Add the attribute with Excel annotation annotation to the attribute list
            boolean hasAnnotation = f.isAnnotationPresent(ExcelAnnotation.class);
            if(hasAnnotation){
                //Set private members to be accessible through reflection
                f.setAccessible(true);
                this.fields.add(f);
            }
        }
        //If there is a superclass in this class (and the type of superclass is class), resolve the superclass recursively
        if(cClass.getSuperclass()!=null&&cClass.getSuperclass().equals(Object.class)){
            parseAnnotation(cClass.getSuperclass());
        }
    }
    //Overload the parseAnnotation method to set the default parameters
    public void parseAnnotation(){
        parseAnnotation(this.tClass);
    }
}

So far, this tool class can resolve entity class annotations. In this way, we can focus on the processing of excel.
For reading excel files, poi needs to input stream and sheet names, so our import tool class also needs these two parameters. It should be noted here that for the read excel data, we need to construct the object as an entity class, and the type of the attribute of the object is unknown to us, so the processing method selected is as follows:

  • First, all the original data of excel are processed into strings
  • Then the reflection is used to judge the type of the target attribute, and then the string is forcibly typed
//Import excel file
    public void importExcel(InputStream inputStream,String sheetName){
        try {
            Workbook workbook = WorkbookFactory.create(inputStream);
            Sheet sheet;
            //If the table name is not set, the first table is obtained by default
            if(sheetName==null){
                sheet = workbook.getSheetAt(0);
            }else {
                sheet = workbook.getSheet(sheetName);
            }
            //Get the number of rows
            int rows = sheet.getPhysicalNumberOfRows();
            //Only when the table is not empty can it be processed
            if(rows>0){
                //It is considered that the first row is the header, so it is obtained from the second row
                for(int i = 1;i<rows;i++){
                    //Obtain the data of this bank
                    Row row = sheet.getRow(i);
                    T entity = null;
                    //Get attribute data
                    for(Field f : this.fields){
                        //If the attribute is empty, it will not be processed
                        if(f==null){
                            continue;
                        }
                        //Read the cell of the corresponding column
                        Cell cell = row.getCell( f.getAnnotation(ExcelAnnotation.class).col() );
                        //If the cell is empty, it will not be processed
                        if(cell==null){
                            continue;
                        }
                        //Set the value according to the type of cell
                        String str;
                        if(cell.getCellTypeEnum()== CellType.NUMERIC){
                            str = String.valueOf(cell.getNumericCellValue());
                        }else if(cell.getCellTypeEnum()==CellType.BOOLEAN){
                            str = String.valueOf(cell.getBooleanCellValue());
                        }else {
                            str = cell.getStringCellValue();
                        }
                        //If str is empty, it will not be processed
                        if( str==null || str.equals("")){
                            continue;
                        }
                        //If there is an instance, the instance will continue to be used, and if there is no instance, the instance will be instantiated (there is no instance in the first cycle)
                        entity = (entity==null?this.tClass.newInstance():entity);
                        Class<?> fieldType = f.getType();
                        //Assign a value according to the type of the current property
                        if(fieldType == String.class){
                            f.set(entity,str);
                        }else if(fieldType==Integer.TYPE||fieldType==Integer.class){
                            f.set(entity,new Integer((int)Double.parseDouble(str)));
                        }else if(fieldType==Float.TYPE||fieldType==Float.class){
                            f.set(entity,Float.parseFloat(str));
                        }else if(fieldType==Double.TYPE||fieldType==Double.class){
                            f.set(entity,Double.parseDouble(str));
                        }else if(fieldType==Short.TYPE||fieldType==Short.class){
                            f.set(entity,Short.parseShort(str));
                        }else if(fieldType==Long.TYPE||fieldType==Long.class){
                            f.set(entity,Long.parseLong(str));
                        }else if(fieldType==Byte.TYPE||fieldType==Byte.class){
                            f.set(entity,Byte.parseByte(str));
                        }else if(fieldType==Character.TYPE||fieldType==Character.class){
                            f.set(entity,str.charAt(0));
                        }

                    }
                    //Record bank data
                    if(entity!=null){
                        this.data.add(entity);
                    }

                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (InvalidFormatException e) {
            e.printStackTrace();
        }
    }

Since most of our data source is the MultipartFile object passed in by the springboot framework, we can overload this import method to adapt to the MultipartFile object

//Overloading the importExcel method to change parameters
public void importExcel(MultipartFile file){
    try {
        importExcel(file.getInputStream(),null);
    } catch (IOException e) {
        e.printStackTrace();
    }
}

Now that the user-defined annotation has been used, the export becomes very convenient, because we can use the reflection mechanism to obtain the annotation content to construct the exported header.

public HSSFWorkbook exportExcel(){
        HSSFWorkbook workbook =  new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet();
        HSSFRow row;
        HSSFCell cell;
        row = sheet.createRow(0);
        for(Field f:this.fields){
            //Property is empty and will not be processed
            if(f==null){
                continue;
            }
            //Get column
            int col = f.getAnnotation(ExcelAnnotation.class).col();
            //Generate cell
            cell = row.createCell(col);
            //Set cell value
            cell.setCellValue(f.getAnnotation(ExcelAnnotation.class).name());
        }
        for(int i=0;i<this.data.size();i++){
            row = sheet.createRow(i+1);
            for(Field f:this.fields){
                int col = f.getAnnotation(ExcelAnnotation.class).col();
                HSSFCellStyle textStyle = workbook.createCellStyle();
                HSSFDataFormat format = workbook.createDataFormat();
                textStyle.setDataFormat(format.getFormat("@"));
                cell = row.createCell(col);
                cell.setCellStyle(textStyle);//Format cells as text
                cell.setCellType(CellType.STRING);
                try {
                    cell.setCellValue( f.get(this.data.get(i))==null?"":String.valueOf(f.get(this.data.get(i))) );
                } catch (IllegalAccessException e) {
                    e.printStackTrace();
                }
            }
        }
        return workbook;
    }

The complete tool class code after sorting is as follows

public class ExcelUtil<T> {
    private Class<T> tClass;
    private List<T> data;
    private List<Field> fields;

    //Constructor
    public ExcelUtil(Class<T> tClass){
        this.tClass = tClass;
        this.data = new ArrayList<T>();
        this.fields = new ArrayList<Field>();
        //Parsing annotation to get attribute set
        parseAnnotation();
    }

    public List<T> getData() {
        return data;
    }

    public void setData(List<T> data) {
        this.data = data;
    }

    //Method of parsing annotation to obtain attribute set
    public void parseAnnotation(Class cClass){
        if(this.fields==null){
            this.fields = new ArrayList<Field>();
        }
        Field[] fieldsArray = cClass.getDeclaredFields();
        for(Field f:fieldsArray){
            //Add the attribute with Excel annotation annotation to the attribute list
            boolean hasAnnotation = f.isAnnotationPresent(ExcelAnnotation.class);
            if(hasAnnotation){
                //Set private members to be accessible through reflection
                f.setAccessible(true);
                this.fields.add(f);
            }
        }
        //If there is a superclass in this class (and the type of superclass is class), resolve the superclass recursively
        if(cClass.getSuperclass()!=null&&cClass.getSuperclass().equals(Object.class)){
            parseAnnotation(cClass.getSuperclass());
        }
    }

    //Overload the parseAnnotation method to set the default parameters
    public void parseAnnotation(){
        parseAnnotation(this.tClass);
    }

    //Import excel file
    public void importExcel(InputStream inputStream,String sheetName){
        try {
            Workbook workbook = WorkbookFactory.create(inputStream);
            Sheet sheet;
            //If the table name is not set, the first table is obtained by default
            if(sheetName==null){
                sheet = workbook.getSheetAt(0);
            }else {
                sheet = workbook.getSheet(sheetName);
            }
            //Get the number of rows
            int rows = sheet.getPhysicalNumberOfRows();
            //Only when the table is not empty can it be processed
            if(rows>0){
                //It is considered that the first row is the header, so it is obtained from the second row
                for(int i = 1;i<rows;i++){
                    //Obtain the data of this bank
                    Row row = sheet.getRow(i);
                    T entity = null;
                    //Get attribute data
                    for(Field f : this.fields){
                        //If the attribute is empty, it will not be processed
                        if(f==null){
                            continue;
                        }
                        //Read the cells of the corresponding column
                        Cell cell = row.getCell( f.getAnnotation(ExcelAnnotation.class).col() );
                        //If the cell is empty, it will not be processed
                        if(cell==null){
                            continue;
                        }
                        //Set the value according to the type of cell
                        String str;
                        if(cell.getCellTypeEnum()== CellType.NUMERIC){
                            str = String.valueOf(cell.getNumericCellValue());
                        }else if(cell.getCellTypeEnum()==CellType.BOOLEAN){
                            str = String.valueOf(cell.getBooleanCellValue());
                        }else {
                            str = cell.getStringCellValue();
                        }
                        //If str is empty, it will not be processed
                        if( str==null || str.equals("")){
                            continue;
                        }
                        //If there is an instance, the instance will continue to be used, and if there is no instance, the instance will be instantiated (there is no instance in the first cycle)
                        entity = (entity==null?this.tClass.newInstance():entity);
                        Class<?> fieldType = f.getType();
                        //Assign a value according to the type of the current property
                        if(fieldType == String.class){
                            f.set(entity,str);
                        }else if(fieldType==Integer.TYPE||fieldType==Integer.class){
                            f.set(entity,new Integer((int)Double.parseDouble(str)));
                        }else if(fieldType==Float.TYPE||fieldType==Float.class){
                            f.set(entity,Float.parseFloat(str));
                        }else if(fieldType==Double.TYPE||fieldType==Double.class){
                            f.set(entity,Double.parseDouble(str));
                        }else if(fieldType==Short.TYPE||fieldType==Short.class){
                            f.set(entity,Short.parseShort(str));
                        }else if(fieldType==Long.TYPE||fieldType==Long.class){
                            f.set(entity,Long.parseLong(str));
                        }else if(fieldType==Byte.TYPE||fieldType==Byte.class){
                            f.set(entity,Byte.parseByte(str));
                        }else if(fieldType==Character.TYPE||fieldType==Character.class){
                            f.set(entity,str.charAt(0));
                        }

                    }
                    //Record bank data
                    if(entity!=null){
                        this.data.add(entity);
                    }

                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (InvalidFormatException e) {
            e.printStackTrace();
        }
    }

    //Overloading the importExcel method to change parameters
    public void importExcel(MultipartFile file){
        try {
            importExcel(file.getInputStream(),null);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    

    //Export excel using entity class header
    public HSSFWorkbook exportExcel(){
        HSSFWorkbook workbook =  new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet();
        HSSFRow row;
        HSSFCell cell;
        row = sheet.createRow(0);
        //Construct header
        for(Field f:this.fields){
            //Property is empty and will not be processed
            if(f==null){
                continue;
            }
            //Get column
            int col = f.getAnnotation(ExcelAnnotation.class).col();
            //Generate cell
            cell = row.createCell(col);
            //Set cell value
            cell.setCellValue(f.getAnnotation(ExcelAnnotation.class).name());
        }
        //Fill data
        for(int i=0;i<this.data.size();i++){
            row = sheet.createRow(i+1);
            for(Field f:this.fields){
                int col = f.getAnnotation(ExcelAnnotation.class).col();
                HSSFCellStyle textStyle = workbook.createCellStyle();
                HSSFDataFormat format = workbook.createDataFormat();
                textStyle.setDataFormat(format.getFormat("@"));
                cell = row.createCell(col);
                cell.setCellStyle(textStyle);//Format cells as text
                cell.setCellType(CellType.STRING);
                try {
                    cell.setCellValue( f.get(this.data.get(i))==null?"":String.valueOf(f.get(this.data.get(i))) );
                } catch (IllegalAccessException e) {
                    e.printStackTrace();
                }
            }
        }
        return workbook;
    }



}

Posted by Nhoj on Sun, 08 May 2022 01:38:15 +0300