自定义注解实现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.
 */
@Component
public 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);
    }


}