导出数据到excel表格(注解+反射+枚举+poi)

一、导入所需要的jar包
poi.jar包下载地址：
https://archive.apache.org/dist/poi/release/bin/
commons-lang3-3.1.jar包下载地址
http://www.java2s.com/Code/Jar/c/Downloadcommonslang331jar.htm
二、创建注解类Excel

package excel;

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
import java.math.BigDecimal;

/**
 * 自定义导出Excel数据注解
 * 
 */
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface Excel
{
    /**
     * 导出时在excel中排序
     */
    public int sort() default Integer.MAX_VALUE;

    /**
     * 导出到Excel中的名字.
     */
    public String name() default "";

    /**
     * 日期格式, 如: yyyy-MM-dd
     */
    public String dateFormat() default "";

    /**
     * 读取内容转表达式(如: 0=男,1=女,2=未知)
     */
    public String readConverterExp() default "";
    /**
     * 分隔符，读取字符串组内容
     */
    public String separator() default ",";

    /**
     * 导出时在excel中每个列的高度 单位为字符
     */
    public double height() default 14;

    /**
     * 导出时在excel中每个列的宽 单位为字符
     */
    public double width() default 16;
    /**
     * 另一个类中的属性名称,支持多级获取,以小数点隔开
     */
    public String targetAttr() default "";
    /**
     * 当值为空时,字段的默认值
     */
    public String defaultValue() default "";
    /**
     * 文字后缀,如 % 90 变成90%
     */
    public String suffix() default "";
    /**
     * 导出类型（0数字 1字符串）
     */
    public ColumnType cellType() default ColumnType.STRING;

    public enum ColumnType
    {
        NUMERIC(0), STRING(1), IMAGE(2);
        private final int value;

        ColumnType(int value)
        {
            this.value = value;
        }

        public int value()
        {
            return this.value;
        }
    }
    /**
     * 导出字段对齐方式（0：默认；1：靠左；2：居中；3：靠右）
     */
    public Align align() default Align.AUTO;
    public enum Align
    {
        AUTO(0), LEFT(1), CENTER(2), RIGHT(3);
        private final int value;

        Align(int value)
        {
            this.value = value;
        }

        public int value()
        {
            return this.value;
        }
    }

}

```c
package excel;

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

/**
 * Excel注解集
 * 
 */
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface Excels
{
    Excel[] value();
}

三、创建实体类

package excel;

import java.util.Date;

public class User {
    @Excel(name="用户姓名" ,align = Excel.Align.LEFT)
    private String username;
    @Excel(name="用户密码",align = Excel.Align.CENTER)
    private String password;
    @Excel(name="用户出生日期",dateFormat = "yyyy-MM-dd",align = Excel.Align.RIGHT)
    private Date date;
    @Excel(name="用户性别",readConverterExp = "0=男,1=女")
    private String gender;
    @Excel(name="用户年龄",cellType = Excel.ColumnType.NUMERIC)
    private Integer age;
    /** 部门对象 */
    @Excels({
            @Excel(name = "部门名称", targetAttr = "deptName"),
            @Excel(name = "部门负责人", targetAttr = "leader")
    })
    private SysDept dept;

    public User(String username, String password, Date date, String gender, Integer age, SysDept dept) {
        this.username = username;
        this.password = password;
        this.date = date;
        this.gender = gender;
        this.age = age;
        this.dept = dept;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    public SysDept getDept() {
        return dept;
    }

    public void setDept(SysDept dept) {
        this.dept = dept;
    }

    public String getGender() {
        return gender;
    }

    public void setGender(String gender) {
        this.gender = gender;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public Date getDate() {
        return date;
    }

    public void setDate(Date date) {
        this.date = date;
    }
}

package excel;

public class SysDept {
    /** 部门名称 */
    private String deptName;
    /** 负责人 */
    private String leader;

    public SysDept(String deptName, String leader) {
        this.deptName = deptName;
        this.leader = leader;
    }

    public String getLeader() {
        return leader;
    }

    public void setLeader(String leader) {
        this.leader = leader;
    }
}

四、创建导出数据到excel表的工具类

package excel;


import java.io.FileOutputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.util.IOUtils;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;


/**
 * Excel相关处理
 *
 */
public class ExcelUtil<T>
{
    /**
     * Excel sheet最大行数，默认65536
     */
    public static final int sheetSize = 65536;

    /**
     * 工作表名称
     */
    private String sheetName;

    /**
     * 工作薄对象
     */
    private Workbook wb;

    /**
     * 工作表对象
     */
    private Sheet sheet;

    /**
     * 样式列表
     */
    private Map<String, CellStyle> styles;

    /**
     * 导入导出数据列表
     */
    private List<T> list;

    /**
     * 注解列表
     */
    private List<Object[]> fields;

    /**
     * 最大高度
     */
    private short maxHeight;

    /**
     * 数字格式
     */
    private static final DecimalFormat DOUBLE_FORMAT = new DecimalFormat("######0.00");

    /**
     * 实体对象
     */
    public Class<T> clazz;

    public ExcelUtil(Class<T> clazz)
    {
        this.clazz = clazz;
    }

    public void init(List<T> list, String sheetName)
    {
        if (list == null)
        {
            list = new ArrayList<T>();
        }
        this.list = list;
        this.sheetName = sheetName;
        createExcelField();
        createWorkbook();
    }
    /**
     * 得到所有定义字段
     */
    private void createExcelField()
    {
        this.fields = new ArrayList<Object[]>();
        List<Field> tempFields = new ArrayList<>();
        tempFields.addAll(Arrays.asList(clazz.getDeclaredFields()));
        for (Field field : tempFields)
        {
            if(field.isAnnotationPresent(Excel.class)){
                putToField(field,field.getAnnotation(Excel.class));
            }
            //多注解
            if(field.isAnnotationPresent(Excels.class)){
                Excels attrs=field.getAnnotation(Excels.class);
                Excel[] excels = attrs.value();
                for(Excel excel:excels){
                    putToField(field,excel);
                }
            }

        }

        this.fields = this.fields.stream().sorted(Comparator.comparing(objects -> ((Excel) objects[1]).sort())).collect(Collectors.toList());
        this.maxHeight = getRowHeight();
    }
    /**
     * 放到字段集合中
     */
    private void putToField(Field field,Excel attr){
        if(attr!=null){
            this.fields.add(new Object[]{field,attr});
        }
    }
    /**
     * 根据注解获取最大行高
     */
    public short getRowHeight()
    {
        double maxHeight = 0;
        for (Object[] os : this.fields)
        {
            Excel excel = (Excel) os[1];
            maxHeight = maxHeight > excel.height() ? maxHeight : excel.height();
        }
        return (short) (maxHeight * 20);
    }


    /**
     * 创建一个工作簿
     */
    public void createWorkbook()
    {
        this.wb = new SXSSFWorkbook(500);
    }


    /**
     * 编码文件名
     */
    public String encodingFilename(String filename)
    {
        filename = UUID.randomUUID().toString() + "_" + filename + ".xlsx";
        return filename;
    }

    /**
     * 对list数据源将其里面的数据导入到excel表单
     *
     * @return 结果
     */
    public void exportExcel()
    {
        OutputStream out = null;
        try
        {
            writeSheet();
            String filename = encodingFilename(sheetName);
            out = new FileOutputStream("D:\\"+filename);
            wb.write(out);

        }
        catch (Exception e)
        {
            System.out.println("导出数据失败");
        }
        finally
        {
            IOUtils.closeQuietly(wb);
            IOUtils.closeQuietly(out);
        }
    }


    /**
     * 创建写入数据到Sheet
     */
    public void writeSheet()
    {
        // 取出一共有多少个sheet.
        double sheetNo = Math.ceil(list.size() / sheetSize);
        for (int index = 0; index <= sheetNo; index++)
        {
            createSheet(sheetNo, index);

            // 产生一行
            Row row = sheet.createRow(0);
            int column = 0;
            // 写入各个字段的列头名称
            for (Object[] os : fields)
            {
                Excel excel = (Excel) os[1];
                this.createCell(excel, row, column++);
            }

            fillExcelData(index, row);

        }
    }
    /**
     * 填充excel数据
     *
     * @param index 序号
     * @param row 单元格行
     */
    public void fillExcelData(int index, Row row)
    {
        int startNo = index * sheetSize;
        int endNo = Math.min(startNo + sheetSize, list.size());
        for (int i = startNo; i < endNo; i++)
        {
            row = sheet.createRow(i + 1 - startNo);
            // 得到导出对象.
            T vo = (T) list.get(i);
            int column = 0;
            for (Object[] os : fields)
            {
                Field field = (Field) os[0];
                Excel excel = (Excel) os[1];
                // 设置实体类私有属性可访问
                field.setAccessible(true);
                this.addCell(excel, row, vo, field, column++);
            }
        }
    }
    /**
     * 添加单元格
     */
    public Cell addCell(Excel attr, Row row, T vo, Field field, int column)  {
        Cell cell = null;
        try {
            // 设置行高
            row.setHeight(maxHeight);
            // 根据Excel中设置情况决定是否导出,有些情况需要保持为空,希望用户填写这一列.
            // 创建cell
            cell = row.createCell(column);
            int align = attr.align().value();
            cell.setCellStyle(styles.get("data"+(align>=1&&align<=3?align:"")));


            Object value = getTargetValue(vo, field, attr);
            String dateFormat=attr.dateFormat();
            String readConverterExp=attr.readConverterExp();
            String separator=attr.separator();

            if(isNotEmpty(dateFormat)&&isNotNull(value)){
                cell.setCellValue(parseDateToStr(dateFormat,(Date)value));
            }else if(isNotEmpty(readConverterExp)&&isNotNull(value)){
                cell.setCellValue(convertByExp(toStr(value),readConverterExp,separator));
            } else{
                setCellVo(value,attr,cell);
            }

        }catch (Exception e){
            System.out.println("导入出现错误");
        }
        return cell;
    }

    //解析导出值 0=男 1=女
    public static String convertByExp(String propertyValue,String converterExp,String separator){
        StringBuilder propertyString = new StringBuilder();
        String[] convertSource=converterExp.split(",");

        for(String item:convertSource){
            String[] itemArray = item.split("=");
            if(StringUtils.containsAny(separator,propertyValue)){
                for(String value:propertyValue.split(separator)){
                    if (itemArray[0].equals(value))
                    {
                        propertyString.append(itemArray[1] + separator);
                        break;
                    }
                }
            }else{
                if (itemArray[0].equals(propertyValue))
                {
                    return itemArray[1];
                }

            }

        }
        return StringUtils.stripEnd(propertyString.toString(),separator);

    }
    //设置单元格信息
    public void setCellVo(Object value,Excel attr,Cell cell){
        if(Excel.ColumnType.STRING==attr.cellType()){
            cell.setCellValue(isNull(value)?attr.defaultValue():value+attr.suffix());
        }
        else if(Excel.ColumnType.NUMERIC==attr.cellType()){
            if(isNotNull(value)){
                cell.setCellValue(StringUtils.contains(toStr(value),".")?toDouble(value):toInt(value));
            }
        }

    }

    //获得属性值
    private Object getTargetValue(T vo, Field field, Excel excel) throws Exception {
        Object o = field.get(vo);
        if(isNotEmpty(excel.targetAttr())){
            String target=excel.targetAttr();
            if(target.indexOf(".")>-1){
                String[] targets=target.split("[.]");
                for(String name:targets){
                    o=getValue(o,name);
                }
            }else{
                o=getValue(o,target);
            }
        }
        return o;
    }
    /**
     * 以类的属性的get方法形式获取值
     */
    private Object getValue(Object o,String name) throws Exception{
        if (isNotNull(o) && isNotEmpty(name))
        {
            Class<?> clazz = o.getClass();
            Field field = clazz.getDeclaredField(name);
            field.setAccessible(true);
            o = field.get(o);
        }
        return o;
    }

    /**
     * 创建单元格
     */
    public void createCell(Excel attr, Row row, int column)
    {
        // 创建列
        Cell cell = row.createCell(column);
        // 写入列信息
        cell.setCellValue(attr.name());
        setDataValidation(attr,row,column);
        cell.setCellStyle(styles.get("header"));
    }
    /**
     * 创建单元格样式
     */
    public void setDataValidation(Excel attr,Row row,int column){
        //设置列宽
        sheet.setColumnWidth(column,(int)((attr.width()+0.72)*256));
    }

    /**
     * 创建工作表
     *
     * @param sheetNo sheet数量
     * @param index 序号
     */
    public void createSheet(double sheetNo, int index)
    {
        this.sheet = wb.createSheet();
        this.styles = createStyles(wb);
        // 设置工作表的名称.
        if (sheetNo == 0)
        {
            wb.setSheetName(index, sheetName);
        }
        else
        {
            wb.setSheetName(index, sheetName + index);
        }
    }
    /**
     * 创建表格样式
     *
     * @param wb 工作薄对象
     * @return 样式列表
     */
    private Map<String, CellStyle> createStyles(Workbook wb)
    {
        // 写入各条记录,每条记录对应excel表中的一行
        Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
        CellStyle style = wb.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setBorderRight(BorderStyle.THIN);
        style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setBorderLeft(BorderStyle.THIN);
        style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setBorderTop(BorderStyle.THIN);
        style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setBorderBottom(BorderStyle.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(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        Font headerFont = wb.createFont();
        headerFont.setFontName("Arial");
        headerFont.setFontHeightInPoints((short) 10);
        headerFont.setBold(true);
        headerFont.setColor(IndexedColors.WHITE.getIndex());
        style.setFont(headerFont);
        styles.put("header", style);

        style = wb.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        Font totalFont = wb.createFont();
        totalFont.setFontName("Arial");
        totalFont.setFontHeightInPoints((short) 10);
        style.setFont(totalFont);
        styles.put("total", style);

        style = wb.createCellStyle();
        style.cloneStyleFrom(styles.get("data"));
        style.setAlignment(HorizontalAlignment.LEFT);
        styles.put("data1", style);

        style = wb.createCellStyle();
        style.cloneStyleFrom(styles.get("data"));
        style.setAlignment(HorizontalAlignment.CENTER);
        styles.put("data2", style);

        style = wb.createCellStyle();
        style.cloneStyleFrom(styles.get("data"));
        style.setAlignment(HorizontalAlignment.RIGHT);
        styles.put("data3", style);

        return styles;
    }

    //转为string对象
    public static String toStr(Object value){
        return toStr(value,null);
    }
    public static String toStr(Object value,String defaultValue){
        if (null == value)
        {
            return defaultValue;
        }
        if (value instanceof String)
        {
            return (String) value;
        }
        return value.toString();
    }

    //判断字符串是否为非空串
    public static boolean isNotEmpty(String str){
        return !isEmpty(str);
    }

    //判断一个字符串是否为空串
    public static boolean isEmpty(String str){
        return isNull(str)||"".equals(str.trim());
    }
    //判断一个对象是否为空
    public static boolean isNull(Object object){
        return object==null;
    }
    //判断一个对象是否为非空
    public static boolean isNotNull(Object object)
    {
        return !isNull(object);
    }
    //转换日期格式
    public static final String parseDateToStr(final String format, final Date date)
    {
        return new SimpleDateFormat(format).format(date);
    }
    //转换为double
    public static Double toDouble(Object value){
        return toDouble(value,null);
    }
    public static Double toDouble(Object value,Double defaultValue){
        if (value == null)
        {
            return defaultValue;
        }
        if (value instanceof Double)
        {
            return (Double) value;
        }
        if (value instanceof Number)
        {
            return ((Number) value).doubleValue();
        }
        final String valueStr = toStr(value, null);
        if (StringUtils.isEmpty(valueStr))
        {
            return defaultValue;
        }
        try
        {
            // 支持科学计数法
            return new BigDecimal(valueStr.trim()).doubleValue();
        }
        catch (Exception e)
        {
            return defaultValue;
        }
    }

    public static Integer toInt(Object value){
        return toInt(value,null);
    }
    public static Integer toInt(Object value, Integer defaultValue)
    {
        if (value == null)
        {
            return defaultValue;
        }
        if (value instanceof Integer)
        {
            return (Integer) value;
        }
        if (value instanceof Number)
        {
            return ((Number) value).intValue();
        }
        final String valueStr = toStr(value, null);
        if (StringUtils.isEmpty(valueStr))
        {
            return defaultValue;
        }
        try
        {
            return Integer.parseInt(valueStr.trim());
        }
        catch (Exception e)
        {
            return defaultValue;
        }
    }

}

五、创建主函数类

package excel;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class Demo {
    public static void main(String[] args) {
        ExcelUtil<User> excelUtil=new ExcelUtil<User>(User.class);
        List<User> userList=new ArrayList<User>();
        userList.add(new User("张三","123",new Date(),"1",19,new SysDept("管理部门","赵六")));
        userList.add(new User("李四","456",new Date(),"0",17,new SysDept("系统部门","刘七")));
        userList.add(new User("王五","789",new Date(),"1,0",18,new SysDept("销售部门","杨八")));
        excelUtil.init(userList,"用户表单");
        excelUtil.exportExcel();
    }
}

六、运行结果