自定义注解实现Excel的解析生成

1.自定义注解

import java.lang.annotation.ElementType;import java.lang.annotation.Retention;import java.lang.annotation.RetentionPolicy;import java.lang.annotation.Target;/** * Created by weili on 2017/7/24. */@Retention(RetentionPolicy.RUNTIME)@Target({ElementType.FIELD,ElementType.TYPE})public @interface ExcelAttribute {    /**     * excel里的sheet名，默认是"sheet1"     * @return     */    String sheetName() default "sheet1";    /**     * excel里对应的列名，默认为""     * @return     */    String columnName() default "";    /**     * 列对应的排序序号，默认是0     * @return     */    int order() default 0;    enum DataType {        String, Number, Date    }    /**     * 数据类型，可以是String，Number(数字型)，Date等类型     * @return     */    DataType type() default DataType.String;    /**     * 日期格式，默认是"yyyy-MM-dd HH:mm:ss"     * @return     */    String datePattern() default "yyyyMMdd HH:mm:ss";    /**     * 保留小数点后的位数，默认是0     * @return     */    int decimalNums() default 0;    /**     * 背景颜色,默认为白色"FFFFFF"，     * 表示形式为颜色的十六进制字符串，常见的：     * red: "FF0000",Orange: "FFA500",yellow: "FFFF00",     * green: "008000",blue: "0000FF",purple: "800080"     * @return     */    String fillColor() default "FFFFFF";    /**     * 字段是否放弃存储到excel里，默认为false     * @return     */    boolean skip() default false;}

2.生成excel的工具类

import com.creditease.microloan.mil.tasks.common.annotation.ExcelAttribute;import com.creditease.microloan.mil.tasks.exceptions.BusinessRuntimeException;import org.apache.commons.beanutils.BeanUtils;import org.apache.poi.ss.usermodel.*;import org.apache.poi.xssf.streaming.SXSSFSheet;import org.apache.poi.xssf.streaming.SXSSFWorkbook;import org.apache.poi.xssf.usermodel.XSSFCellStyle;import org.apache.poi.xssf.usermodel.XSSFColor;import org.springframework.stereotype.Component;import org.springframework.util.CollectionUtils;import java.awt.Color;import java.io.FileOutputStream;import java.lang.reflect.Field;import java.text.DateFormat;import java.text.DecimalFormat;import java.text.SimpleDateFormat;import java.util.*;import java.util.List;/** * Created byweili on 2017/7/20. */@Componentpublic class ExcelUtil<T> {    /**     * 根据filePath和dataset创建文件     * @param filePath     * @param dataset     * @param <T>     */    public static <T> void createFile(String filePath, List<T> dataset) {        SXSSFWorkbook wb = new SXSSFWorkbook();        createExcel( wb, dataset);        FileOutputStream out = null;        try {            out = new FileOutputStream(filePath);            wb.write(out);        }        catch (Exception e){            throw new BusinessRuntimeException("文件创建失败！",e);        }        finally {            if(out!=null) {                try {                    out.close();                }                catch (Exception e){                    throw new BusinessRuntimeException("关闭文件输出流失败！",e);                }            }        }    }    /**     * 创建sheet，并添加数据到里面     * @param wb     * @param dataset     * @param <T>     */    private static <T> void createExcel(SXSSFWorkbook wb, List<T> dataset ) {        if(CollectionUtils.isEmpty(dataset) ) {            return;        }        T t = dataset.get(0);        // 获取实体类的所有属性,即包括public、private和proteced，但是不包括父类的申明字段        //  一个field表示一个属性        Field[] fields = t.getClass().getDeclaredFields();        // 整个类的注解,得到了定义的sheetName        ExcelAttribute classAttribute = t.getClass().getAnnotation(ExcelAttribute.class);        SXSSFSheet sheet = wb.createSheet(classAttribute.sheetName());        // excel里存储类的部分属性和顺序号        Map<Field,Integer> map = new LinkedHashMap<>();        ExcelAttribute excelAttribute = null;        for (Field field : fields) {            // 某个属性上的注解,如果没写注解或者注解里的skip为true，表示该列不会存储到excel里            excelAttribute = field.getAnnotation(ExcelAttribute.class);            if (excelAttribute != null) {                if (!excelAttribute.skip()) {                    map.put(field, excelAttribute.order());                }            }        }        //  排序        List<Map.Entry<Field,Integer>> list = new ArrayList<Map.Entry<Field,Integer>>(map.entrySet());        Collections.sort(list, (o1, o2) -> o1.getValue().compareTo(o2.getValue()));        // 存储类的注解skip为false的排序后的属性对应的Field        List<Field> excelFields = new ArrayList<>();        for(Map.Entry<Field,Integer> mapping:list){            excelFields.add(mapping.getKey());        }        // excel里存储的列的ExcelAttribute        List<ExcelAttribute> attributes = new ArrayList<>();        for (int j = 0; j < excelFields.size(); j++) {            attributes.add(excelFields.get(j).getAnnotation(ExcelAttribute.class));        }        addDataToExcel(wb,dataset,excelFields, attributes,sheet);        // 自动调整列宽        sheet.trackAllColumnsForAutoSizing();        for(int i=0;i<excelFields.size();i++) {            sheet.autoSizeColumn(i);        }    }    /**     * 添加数据到excel中     * @param wb     * @param dataset     * @param excelFields     * @param attributes     * @param sheet     * @param <T>     */    private static <T> void addDataToExcel(SXSSFWorkbook wb, List<T> dataset,List<Field> excelFields, List<ExcelAttribute> attributes,Sheet sheet) {        XSSFCellStyle style = (XSSFCellStyle)wb.createCellStyle();        // 居中        style.setAlignment(HorizontalAlignment.CENTER);        style.setVerticalAlignment(VerticalAlignment.CENTER);        // excel放入第一行列的名称        Row row = sheet.createRow(0);        for (int j = 0; j < excelFields.size(); j++) {            Cell cell = row.createCell(j);            ExcelAttribute oneAttribute = attributes.get(j);            cell.setCellValue(oneAttribute.columnName());            cell.setCellStyle(style);        }        // 添加数据到excel        for(int i=0;i<dataset.size();i++) {            // 数据行号从1开始,因为第0行放的是列的名称            row = sheet.createRow(i+1);            for(int j=0;j<attributes.size();j++) {                Cell cell = row.createCell(j);                ExcelAttribute oneAttribute = attributes.get(j);                style = (XSSFCellStyle)wb.createCellStyle();                // 居中                style.setAlignment(HorizontalAlignment.CENTER);                style.setVerticalAlignment(VerticalAlignment.CENTER);                // 填充色                XSSFColor myColor = new XSSFColor(toColorFromString(oneAttribute.fillColor()));                style.setFillPattern(FillPatternType.SOLID_FOREGROUND);                style.setFillForegroundColor(myColor);                // 四个边框                style.setBorderBottom(BorderStyle.THIN);                style.setBorderLeft(BorderStyle.THIN);                style.setBorderRight(BorderStyle.THIN);                style.setBorderTop(BorderStyle.THIN);                cell.setCellStyle(style);                try{                    // 根据属性名获取属性值                    String cellValue = BeanUtils.getProperty( dataset.get(i), excelFields.get(j).getName());                    if(ExcelAttribute.DataType.Date.equals(oneAttribute.type()))                    {                        // CST格式的时间字符串转为Date对象                        String CST_FORMAT = "EEE MMM dd HH:mm:ss z yyyy";                        Date cstDate = new SimpleDateFormat(CST_FORMAT, Locale.US).parse(cellValue);                        DateFormat df = new SimpleDateFormat(                                oneAttribute.datePattern());                        cell.setCellValue( df.format(cstDate) );                    }                    else if(ExcelAttribute.DataType.Number.equals(oneAttribute.type())) {                        // 保留小数点后的位数                        int decimalNums = oneAttribute.decimalNums();                        StringBuilder format = new StringBuilder("#0");                        for(int w=0;w<decimalNums;w++) {                            if(w==0) {                                format.append(".");                            }                            format.append("0");                        }                        cell.setCellValue(String.valueOf(new DecimalFormat(format.toString()).format(Double.parseDouble(cellValue))));                    }                    else {                        cell.setCellValue(cellValue);                    }                }                catch (Exception e) {                    throw new BusinessRuntimeException("获取类的属性值失败！", e);                }            }        }    }    /**     * 颜色的16进制字符串转换成Color对象     * @param colorStr 例如蓝色为"0000FF"     * @return Color对象     * */    private static Color toColorFromString(String colorStr){        Color color =  new Color(Integer.parseInt(colorStr, 16)) ;        return color;    }}

3.被注解的实体类

import com.creditease.microloan.mil.tasks.common.annotation.ExcelAttribute;import lombok.AllArgsConstructor;import lombok.Data;import java.util.Date;/** * Created by weili on 2017/7/20. */@Data@AllArgsConstructor@ExcelAttribute(sheetName = "stuSheet")public class Stu {    @ExcelAttribute(columnName="学号",order=0,fillColor = "FF0000")    private Integer stuNo;    @ExcelAttribute(columnName="姓名",order=2,skip = true)    private String name;    @ExcelAttribute(columnName="成绩",order=5, type = ExcelAttribute.DataType.Number,decimalNums = 4)    private Double grade;    @ExcelAttribute(columnName="注册时间",order=0,datePattern = "yyyy-MM-dd HH:mm",type = ExcelAttribute.DataType.Date)    private Date loginDate;    @ExcelAttribute(columnName="是否男孩",order=4,fillColor = "FFFF00")    private Boolean isBoy;}

4.测试

package com.creditease.microloan.mil.tasks;import com.creditease.microloan.mil.tasks.model.Stu;import com.creditease.microloan.mil.tasks.util.ExcelUtil;import org.junit.Test;import java.util.ArrayList;import java.util.Date;import java.util.List;/** * Created by weili on 2017/7/20. */public class ExcelUtilTest {    @Test    public void test() {        List<Stu> dataset = new ArrayList<>();        dataset.add(new Stu(1,"zhangsan",67.9990,new Date(),true));        dataset.add(new Stu(2,"lisi",0.03,new Date(),false));        String[] fieldColumns ={"number","sname"};        String sheetName ="ss33";        String path = "/Users/apple/Desktop/11.xlsx";        ExcelUtil.createFile(path,dataset);    }}