需求

一次在查看項(xiàng)目代碼時(shí)，無意發(fā)現(xiàn)項(xiàng)目中一段導(dǎo)出excel文檔的接口。但是導(dǎo)出寫的很敷衍，就是純?nèi)斯?dǎo)出差別不大。大概有五個(gè)類型集合的導(dǎo)出，每個(gè)類型都寫了一個(gè)導(dǎo)出，整個(gè)類有2000多行代碼。簡(jiǎn)直不能忍，于是就決定修改這部分代碼。先搞個(gè)導(dǎo)出的工具類。四處查找之后 ，經(jīng)過幾次修改，最后成型。

引入jar包

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.9</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-excelant</artifactId>
            <version>3.9</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-scratchpad</artifactId>
            <version>3.9</version>
        </dependency>

自定義注解

實(shí)現(xiàn)導(dǎo)出的時(shí)候，我覺定采用注解的形式標(biāo)注column name
，列寬 ，還有列順序。等設(shè)置。

package com.xescm.whc.annotation;

import com.xescm.whc.utils.DateUtils;
import lombok.Data;

import java.lang.annotation.*;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.util.Date;

/**
 * @author huxingnan
 * @date 2018/3/2113:30
 */
@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Inherited
@Documented
public @interface ExcelColumn {
    String name() default "";//列名
    int index() default 0;//列的順序
    short width() default 10;//列寬

//注解內(nèi)部類 列的配置
    @Data
    class ExcellColumnConfig{

        private int index;//列順序
        private short width;//列寬
        private String columnName;//列名
        private Field field;//對(duì)應(yīng)屬性
    }
// 轉(zhuǎn)換器 ，本來可以設(shè)計(jì)一個(gè)接口的,但是目前能用，就沒有這么設(shè)計(jì)。
    class CellDataConvertor{
        public static String convertor(Object o){
            if(o == null){return "";}

            if(o instanceof Date){
               return DateUtils.getDate2String(DateUtils.YYYY_MM_DD_HH_MM_SS,(Date) o);
            }
            if(o instanceof BigDecimal){
                return ((BigDecimal) o).toPlainString();
            }
            return o.toString();
        }
    }
}

工具實(shí)現(xiàn)

直接上代碼

package com.xescm.whc.utils;

import com.xescm.whc.annotation.ExcelColumn;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Cell;
import java.lang.reflect.Field;
import java.util.*;

/**
 * 通用的 導(dǎo)出 Excel 文件
 * 需要配合 注解 ExcelColumn
 * @see ExcelColumn
 * @author huxingnan
 * @date 2018/3/21 13:14
 */
public class ExportBeanExcelUtil {

    /**
     *
     * @param dtoList 數(shù)據(jù)集合
     * @param type 數(shù)據(jù)類型
     * @param title 工作薄標(biāo)題
     * @param <T> 泛型類
     * @return HSSFWorkbook 對(duì)象
     */
    public static<T>  HSSFWorkbook exportExcell(List<T> dtoList,Class<T> type,String title){
        //1.解析注解  屬性名 和 列名
        List<ExcelColumn.ExcellColumnConfig> columnConfigList = processAnnotation(type);//
        //2. 按照指定順序 對(duì)列進(jìn)行排序
        columnConfigList = sortColumn(columnConfigList);
        //3.創(chuàng)建工作薄
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet(title);
        //4.初始化列寬 表頭
        initTableHeader(sheet,columnConfigList);
        //5. 導(dǎo)出數(shù)據(jù)
        createTableBody(sheet,columnConfigList,dtoList);
        return wb;
    }


    /**
     * 根據(jù) 數(shù)據(jù) 集合 創(chuàng)建表體
     * @param sheet 工作薄對(duì)象
     * @param columnConfigArray 排序后的
     * @param dtoList 數(shù)據(jù)集合
     * @param <T> 泛型
     */
    private static <T> void createTableBody(HSSFSheet sheet,   List<ExcelColumn.ExcellColumnConfig> columnConfigArray, List<T> dtoList) {

        int lineNo = 1;
        HSSFRow row;
        HSSFCell cell;
        try {
            int size = columnConfigArray.size();
            for (T t : dtoList) {
                row = sheet.createRow(lineNo++);
                for (int i = 0; i < size; i++) {
                    cell = row.createCell(i);
                    ExcelColumn.ExcellColumnConfig excellColumnConfig = columnConfigArray.get(i);
                    Field field = excellColumnConfig.getField();
                    field.setAccessible(true);
                    Object cellData = field.get(t);//獲取數(shù)據(jù)
                    String cellDataStr = ExcelColumn.CellDataConvertor.convertor(cellData);//轉(zhuǎn)換數(shù)據(jù)
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    cell.setCellValue(cellDataStr);
                }
            }
        }catch (Exception e){
            e.printStackTrace();
        }
    }


    /**
     * 初始化 表頭 設(shè)置列寬
     * @param sheet 工作薄 對(duì)象
     * @param columnConfigArray 排序后的
     */
    private static void initTableHeader(HSSFSheet sheet, List<ExcelColumn.ExcellColumnConfig> columnConfigArray) {
        HSSFRow row = sheet.createRow(0);
        int size = columnConfigArray.size();
        HSSFCell cell;
       for (int i = 0 ; i < size;i++){
           cell = row.createCell(i, HSSFCellStyle.ALIGN_CENTER);
           ExcelColumn.ExcellColumnConfig excellColumnConfig =  columnConfigArray.get(i);
           cell.setCellType(Cell.CELL_TYPE_STRING);
           cell.setCellValue(excellColumnConfig.getColumnName());
           short width = excellColumnConfig.getWidth();
           sheet.setColumnWidth(i,width*256);
       }
    }

    /**
     * 解析 注解
     * @param type Class
     * @return columnConfig 集合
     */
    private static List<ExcelColumn.ExcellColumnConfig> processAnnotation(Class type){
        Field[] fields = type.getDeclaredFields();
        List<ExcelColumn.ExcellColumnConfig> columnConfigList  = new ArrayList<>();
        for (Field field : fields) {
            ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);
            if(annotation != null){
                //列順序
                int index = annotation.index();
                //列寬度
                short width = annotation.width();
                //當(dāng)前列名
                String name = annotation.name().equals("")?field.getName():annotation.name();
                ExcelColumn.ExcellColumnConfig excellColumnConfig = new ExcelColumn.ExcellColumnConfig();
                excellColumnConfig.setColumnName(name);
                excellColumnConfig.setField(field);
                excellColumnConfig.setIndex(index);
                excellColumnConfig.setWidth(width);
                columnConfigList.add(excellColumnConfig);
            }
        }
        return columnConfigList;

    }

    /**
     * 排序列 按照 columnConfig 中的index 排序
     *
     * @param columnConfigList columnCofig集合
     * @return columnConfig 排序后的
     */
    private static List<ExcelColumn.ExcellColumnConfig> sortColumn(List<ExcelColumn.ExcellColumnConfig> columnConfigList){
        Collections.sort(columnConfigList,new Comparator<ExcelColumn.ExcellColumnConfig>() {
            @Override
            public int compare(ExcelColumn.ExcellColumnConfig o1, ExcelColumn.ExcellColumnConfig o2) {
                return o1.getIndex()-o2.getIndex();
            }
        });

        return columnConfigList;
    }
}

用例

package com.xescm.whc.domain;

import com.xescm.whc.annotation.ExcelColumn;
import lombok.Data;

import java.io.Serializable;
import java.math.BigDecimal;
import java.util.Date;

/**
 * 庫存凍結(jié)單
 */
@Data
public class WhcFrozen implements Serializable {

    private static final long serialVersionUID = 1826699766249042796L;

    /**
     * 凍結(jié)單號(hào)
     */
    @ExcelColumn(name = "凍結(jié)單號(hào)")
    private String holdOrderCode;

    /**
     * 批次號(hào)
     */
    @ExcelColumn(name = "批次號(hào)")
    private String lotNum;

    /**
     * 庫位
     */
     //默認(rèn)的index = 0 
    @ExcelColumn(name = "庫位" index='-1')
    private String locationCode;

}