POI实现excel的导入导出-[采用反射实现的版本]

前段时间学习了POI实现excel的导入导出-[未采用反射实现的版本]的文章，觉得不采用反射实现非常不合理，在祥哥【我眼中的大佬】的指导下实现了POI实现excel的导入导出-[采用反射实现的版本]的实现。
A、具体的要点如下:

---

1、采用泛型，增加代码的灵活性和可扩展性；
2、使用适配器模式；
3、使用反射自动执行对应字段的方法；
4、绑定字段的值和表头文字【采用linkedhashmap()】实现；

B、代码

---

ExcelVO.java

package com.spring.boot.gp4zj.util;

import java.util.List;
import java.util.Map;

/**
 * excel导入导出相关的必须信息;
 * 
 * @author xdsm
 *
 */
public class ExcelVO<T> {
    // 表头列表;
    Map<String, String> workBookHeaderFields = null;
    // excel中的数据值对象;
    List<T> workBookVos = null;
    // 泛型数据对象;
    T clazzRes = null;

    public List<T> getWorkBookVos() {
        return workBookVos;
    }

    public void setWorkBookVos(List<T> workBookVos) {
        this.workBookVos = workBookVos;
    }

    public Map<String, String> getWorkBookHeaderFields() {
        return workBookHeaderFields;
    }

    public void setWorkBookHeaderFields(Map<String, String> workBookHeaderFields) {
        this.workBookHeaderFields = workBookHeaderFields;
    }

    public T getClazzRes() {
        return clazzRes;
    }

    public void setClazzRes(T clazzRes) {
        this.clazzRes = clazzRes;
    }
}

POIUtils.java

package com.spring.boot.gp4zj.util;

import java.io.FileInputStream;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import com.spring.boot.gp4zj.model.AttenceDetail;
import com.spring.boot.gp4zj.model.ExcelVOMapper;

/**
 * Excel操作相关的工具类;
 * 
 * @author:JunZhou
 * @Company:LongZheng
 * @Email:1769676159@qq.com
 * @2018年1月16日@下午3:19:25
 */
public class POIUtils2 {
    public static void main(String[] args) throws Exception {
        /*
         * AttenceDetail clazzRes = new AttenceDetail(); // 测试数据;
         * ExcelVO<AttenceDetail> excelVo = new ExcelVO<AttenceDetail>();
         * excelVo.setClazzRes(clazzRes);
         * 
         * @SuppressWarnings("unused") List<String> workBookHeaderFields = new
         * ArrayList<String>(); final Map<String, String> HEADER_FIELDS = new
         * LinkedHashMap<String, String>(); HEADER_FIELDS.put("考勤日期",
         * "attencedTime"); HEADER_FIELDS.put("考勤名称", "attencerName");
         * HEADER_FIELDS.put("员工编码", "attencerCode"); HEADER_FIELDS.put("出勤时长",
         * "workTimeLen"); HEADER_FIELDS.put("加班时长", "extraTimeLen");
         * HEADER_FIELDS.put("合计出勤时长", "totalTimeLen");
         * HEADER_FIELDS.put("报工单位", "applyCompany"); HEADER_FIELDS.put("结算单位",
         * "payCompany"); HEADER_FIELDS.put("创建人", "creator");
         * HEADER_FIELDS.put("出勤人天", "workDayNum"); HEADER_FIELDS.put("加班人天",
         * "extraDayNum"); HEADER_FIELDS.put("合计人天", "totalDayNum");
         * HEADER_FIELDS.put("是否删除汇总表", "isDelGather");
         * HEADER_FIELDS.put("是否确认", "isConfirmed"); HEADER_FIELDS.put("创建时间",
         * "createTime"); HEADER_FIELDS.put("修改时间", "editTime"); // 生成测试数据;
         * excelVo.setWorkBookHeaderFields(HEADER_FIELDS); List<AttenceDetail>
         * workBookVos = TestDataGenerater.attenceDetailTestDatasGenerater(2);
         * excelVo.setWorkBookVos(workBookVos); createExcel(excelVo);
         */
        String excelPath = "C:/Users/xdsm/Desktop/Excel-900500219.xls";
        AttenceDetail attenceDetailClazz = new AttenceDetail();
        List<AttenceDetail> attenctDetailList = readExcel(excelPath, attenceDetailClazz);
        for (AttenceDetail attenceDetail : attenctDetailList) {
            System.out.println(attenceDetail);
        }
    }

    /**
     * 创建Excel
     * 
     * @param <T>
     * 
     * @param list
     *            数据
     */
    @SuppressWarnings({ "unchecked", "unused" })
    public static <T> HSSFWorkbook createExcel(ExcelVO<T> excelVo) {
        @SuppressWarnings("rawtypes")
        Class clazz = excelVo.getClazzRes().getClass();
        // 表头信息列表;
        List<String> workBookHeaderFields = new ArrayList<String>();
        Map<String, String> headerFields = excelVo.getWorkBookHeaderFields();
        // 表格数据信息列表;
        List<T> dataList = excelVo.getWorkBookVos();
        // 创建一个Excel文件
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 创建一个工作表
        HSSFSheet sheet = workbook.createSheet("考勤明细表");
        // 添加表头行
        HSSFRow hssfRow = sheet.createRow(0);
        // 设置单元格格式居中
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 添加表头内容
        // -------第一行第0-2列---------
        // 设置表头的相关信息;
        int headTitleIndex = 0;
        HSSFCell headCell = null;// 此处使用同一个引用对象，可以节省内存;
        for (Entry<String, String> vo : headerFields.entrySet()) {
            // 表头的名称字段;
            if (headTitleIndex == 0) {
                headCell = hssfRow.createCell(headTitleIndex);
                headCell.setCellValue(vo.getKey());
                headCell.setCellStyle(cellStyle);
            } else {
                headCell = hssfRow.createCell(headTitleIndex);
                headCell.setCellValue(vo.getKey());
                headCell.setCellStyle(cellStyle);
            }
            headTitleIndex++;
        }
        // 填充数据到工作簿;
        for (int i = 0; i < dataList.size(); i++) {
            // 创建数据行;
            hssfRow = sheet.createRow((int) i + 1);
            T attenceDetail = dataList.get(i);
            // 创建单元格，并设置值
            int fieldVoIndex = 0;
            HSSFCell cell = null;// 此处使用同一个引用对象，可以节省内存;
            for (Entry<String, String> vo : headerFields.entrySet()) {
                // 字段值取值器;
                String fieldVoGetter = "get" + (vo.getValue()).substring(0, 1).toUpperCase()
                        + (vo.getValue()).substring(1);
                cell = hssfRow.createCell(fieldVoIndex);
                // TODO Auto-generated
                Method method;
                try {
                    String invokeResultStr = null;
                    method = clazz.getMethod(fieldVoGetter);
                    Object methodResult = method.invoke(attenceDetail);
                    if (methodResult != null) {
                        invokeResultStr = methodResult.toString();
                    }
                    cell.setCellValue(invokeResultStr);
                    cell.setCellStyle(cellStyle);
                } catch (Exception e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
                // method.invoke(clazz.newInstance());
                fieldVoIndex++;
            }
        }

        return workbook;
    }

    /**
     * 接收上传的excel并读取其中的信息;
     * 
     * @throws IllegalAccessException
     * @throws Exception
     */
    @SuppressWarnings({ "unchecked", "rawtypes" })
    public static <T> List<T> readExcel(String excelPath, T clazzRes) throws Exception {
        Class clazz = clazzRes.getClass();
        List<T> list = new ArrayList<T>();
        HSSFWorkbook workbook = null;
        try {
            // 读取Excel文件
            InputStream inputStream = new FileInputStream(excelPath);
            workbook = new HSSFWorkbook(inputStream);
            inputStream.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

        // 循环工作表
        for (int numSheet = 0; numSheet < workbook.getNumberOfSheets(); numSheet++) {
            HSSFSheet hssfSheet = workbook.getSheetAt(numSheet);
            if (hssfSheet == null) {
                continue;
            }
            // 循环行
            for (int rowNum = 1; rowNum < hssfSheet.getLastRowNum(); rowNum++) {
                HSSFRow hssfRow = hssfSheet.getRow(rowNum);
                if (hssfRow == null) {
                    continue;
                }
                // 填充数据到行;
                Map<String, String> HEADER_FIELDS = ExcelVOMapper.getAttenceDetailMapper();
                HSSFCell cell = null;// 公用cell，节省内存的开销;
                // 每行的数据对象;
                T dataVo = (T) clazz.newInstance();
                int fieldVoIndex = 0;
                for (Entry<String, String> vo : HEADER_FIELDS.entrySet()) {
                    // 字段值取值器;
                    String fieldVoSetter = "set" + (vo.getValue()).substring(0, 1).toUpperCase()
                            + (vo.getValue()).substring(1);
                    // TODO Auto-generated
                    Method method;
                    try {
                        cell = hssfRow.getCell(fieldVoIndex);
                        if (cell == null) {
                            continue;
                        }
                        method = clazz.getMethod(fieldVoSetter, String.class);
                        String cellValue = cell.getStringCellValue();
                        /*
                         * if(cellValue==null||"".equals(cellValue)){ // 空数据列测试;
                         * if (fieldVoIndex == 1) { throw new
                         * MyException("员工编码不能为空"); } }
                         */
                        // System.out.println(cellValue);
                        method.invoke(dataVo, cellValue);
                    } catch (Exception e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                    }
                    fieldVoIndex++;
                }
                list.add(dataVo);
            }
        }
        return list;
    }

    /**
     * 获取指定数据类型的所有声明字段;
     * 
     * @param dataObj:泛型对象;
     * @return:返回字段列表【String】;
     */
    public static <T> List<String> getAllFields(T dataObj) {
        Class<? extends Object> dataObjClazz = dataObj.getClass();
        Field[] fields = dataObjClazz.getDeclaredFields();
        List<String> fieldsList = new ArrayList<String>();
        for (int i = 0; i < fields.length; i++) {
            String fieldStr = fields[i].toString();
            String realFieldStr = fieldStr.substring(fieldStr.lastIndexOf(".") + 1);
            fieldsList.add(realFieldStr);
        }
        return fieldsList;
    }

    /**
     * 获取对象中的所有已声明的方法名;
     * 
     * @param dataObj:泛型对象;
     * @return:返回方法列表【String】;
     */
    public static <T> List<String> getAllMethods(T dataObj) {
        List<String> methodsList = new ArrayList<String>();
        Class<? extends Object> dataObjClazz = dataObj.getClass();
        Method[] methods = dataObjClazz.getDeclaredMethods();
        for (int i = 0; i < methods.length; i++) {
            String fieldStr = methods[i].toString();
            if (fieldStr.contains("get")) {
                String realFieldStr = fieldStr.substring(fieldStr.lastIndexOf(".") + 1, fieldStr.lastIndexOf("("));
                methodsList.add(realFieldStr);
            }
        }
        return methodsList;
    }

    /**
     * 获取对象中的所有已声明的方法名;
     * 
     * @param dataObj:泛型对象;
     * @return:返回方法列表【String】;
     */
    public static <T> List<String> getAllSetMethods(T dataObj) {
        List<String> methodsList = new ArrayList<String>();
        @SuppressWarnings("unused")
        Class<? extends Object> dataObjClazz = dataObj.getClass();
        List<String> methods = getAllFields(dataObj);
        for (int i = 0; i < methods.size(); i++) {
            String fieldStr = methods.get(i).toString();
            String methodName = "set" + fieldStr.substring(0, 1).toUpperCase() + fieldStr.substring(1);
            methodsList.add(methodName);
        }
        return methodsList;
    }
}

C、使用要点

---

1、本工具类的使用前提是所有的字段均采用驼峰命名方式，且前两个字母为小写【因为eclipse自动生成的getter和setter会根据前两个字母进行自适应处理，并非固定模式】
2、本工具类并未对导入数据进行全面的非空或者类型等校验，具体的校验可自行添加。
3、本工具类因为采用的是反射是想，因此执行效率较低;
D、参考文章.
设计模式–观察者模式初探和java Observable模式
EasyPoi教程V1.0